How To Download Wordpress Database
Camie Fons
WordPress is almost certainly using a MySQL database for storing its content, and those are usually stored elsewhere on the system, often /var/lib/mysql/some_db_name. Open up your wp-config.php file and start looking at your MySQL settings. These should help you figure out your environment and where to look next. You can see a sample wp-config.php file here.
If you are trying to get a backup of your Wordpress database, it may be the easiest option to contact the tech support of your hosting company since they likely have a one click process they can use to provide a backup. Backups are often text files with SQL statements that build the database and insert the data.
If you do not have SSH access and the database is MySQL, you can install the client on your your computer. You can find the client here You will still need to contact tech support or use a control panel to allow a connection from your computer. This is not the easiest option, but gives you the most control. If you use this option, localhost must be replaced with the IP address of the server. You will also need port 3306 open at the hosting site.
If you have access to a control panel, then there should be a backup mechanism for the installed backup that will create a single text file with SQL statements that can build the database and insert the data. You will be able to put the file into a directory that you can FTP or HTTP download.
Lastly, if you know it is MySQL, you know what version, and you have access to the database files directly, then if you can stop the database, simply copy the files to your computer. To make work somewhere else, install a compatible version of MySQL, stop the server, and copy the backup files to a new directory. Edit the database location using a client, then restart the server.
Focusing on the Wordpress access you might take a look at a few of the database export and management plugins to help you get started. You could have the database saved locally where the install is hosted and pull it via ftp or integrate the code directly into your script.
Your WordPress database stores all of your website content. This includes blog posts, pages, comments, and custom post types such as links, form entries, and portfolio items. It also stores your website settings, theme settings, and plugin settings.
If you update your website regularly, your database will grow larger over time. A large database can greatly affect the performance of your website as it takes longer for your server to retrieve information from database tables. This is why database optimization is so important.
If you are using WordPress to publish content on the internet, I believe it is in your benefit to have an understanding of the core WordPress database tables; particularly if you are planning on optimizing your WordPress database.
WordPress currently has 12 core tables (this could, of course, change in a future version of WordPress). Most WordPress websites have dozens of tables because plugins save settings and other data in the WordPress database. Themes may also save settings and other data in your database.
phpMyAdmin is the most common way to manage a WordPress database. If you are not using cPanel as your hosting control panel, your hosting plan may be using a different MySQL management tool to phpMyAdmin. Do not be too concerned about this as most database management tools have a similar interface and work in the same way.
You can also manage your WordPress database using a plugin. Although a database WordPress plugin can make accessing your database simpler, I would advise against managing your database in this manner as it is a large security risk.
If you check your database, you will see two columns at the end: size and overhead. The size of a table depends on the amount of data that is stored in it. If more rows are stored in a table, the size of the table increases.
It is perfectly normal to have overhead in your WordPress database and it should not affect performance unless overhead gets high (though the actual amount of overhead that should be considered too high is a grey area).
* Please note that the above screenshot is taken from a new WordPress installation I created for screenshots. That is why the database prefix is still wp_. To make your website more secure, always change your WordPress database prefix through wp-config.php to something different.
Every database will, over time, require some form of maintenance to keep it at an optimal performance level. Purging deleted rows, resequencing, compressing, managing index paths, defragmenting, etc. is what is known as OPTIMIZATION in mysql and other terms in other databases. For example, IBM DB2/400 calls it REORGANIZE PHYSICAL FILE MEMBER.
WordPress has a tool that allows you to repair and optimize your database. You can find out more about this tool in the Automatic Database Optimizing section of the WordPress.org guide on wp-config.php.
The WordPress revision system makes many WordPress databases unnecessarily large. First introduced in WordPress 2.6, the feature stores a copy of every draft and update of your blog posts. It is a useful feature as it allows you to revert back to older copies of articles and check earlier drafts.
Unfortunately, WordPress places no limitation on the number of revisions that are saved. If you are working on a long article, this could result in hundreds of revisions being saved. Even though the published article will only take up one row in your database, the corresponding revisions could use dozens or hundreds of rows in your database.
I would advise against disabling post revisions completely. While disabling post revisions will undoubtedly reduce the size of your database, it removes the fail-safe system that revisions provide. Therefore, in the event of you closing your browser in error or losing your internet connection, you could lose everything you worked on since the last save of your draft.
Reducing the number of revisions that are saved, or disabling post revisions altogether does not affect the revisions that are already saved. Therefore, post revisions attached to older blog posts will still be stored in your database.
The plugin allows you to define the number of revisions that are saved. It also lets you delete trashed items, spam items, unused tags, and expired transients. Specific database tables can be removed from the optimization process.
A lot of bloggers have advised disabling autosave as autosave saves multiple copies of your posts and pages. That is simply not true. Autosave only ever saves one copy of your article and will not use up much room in your database.
The autosave feature is an important fail-safe that will help you if you lose your internet connection or close your browser by mistake. The feature does not use up much space in your database; therefore I encourage you to keep it activated.
If your website receives a lot of spam, you may find that spam comments are taking up a lot of space in your database. By default, spam comments are automatically deleted after 30 days; however, during that time they can take up hundreds or even thousands of rows in your wp_coments table.
Akismet is a good solution as it allows you to discard obvious spam so that the comment is deleted from your database right away (though be concious of the fact legitimate comments could be deleted automatically).
Unless you are deleting many items regularly, you should not need to worry about the space deleted items take up in your database. However, it is worth understanding how the trash system works, particularly if you have to delete hundreds or thousands of items from a WordPress website (whether it be posts, comments, images, or whatever).
Expired transient records can add bloat to your database and make your website run slower. There are a number of plugins that help you manage your transients and delete expired transient records that are no longer needed.
Due to this, WordPress databases can accumulate a lot of additional bloat over time. It is not uncommon for a WordPress database to contain dozens of tables for plugins that were removed months ago, or even years ago.
Unused tables can be removed from your database manually through a database management tool such as phpMyAdmin. However, even if you have a good understanding of the 11 core WordPress tables, you will find it difficult to distinguish tables from active plugins and tables from uninstalled plugins.
Each plugin you install on your website increases the size of your website database. The space that some plugins use in your database is negligible, but some WordPress plugins can add a lot of weight to your database.
Throughout this article, I have given advice on how to optimize your database using phpMyAdmin and wp-config.php. If the thought of using phpMyAdmin worries you, you may prefer to use a WordPress plugin to optimize your database. There are a number of good options available.
In addition to a vast suite of award-winning WordPress optimization features, Hummingbird includes a Database Cleanup tool (Hummingbird > Advanced Tools > Database Cleanup) with automatic cleanup scheduling and helpful tooltips that let you know what type of unnecessary data you are deleting from your WP database.
WP-Optimize can be used to remove post revisions, drafts, spam comments, unapproved comments, comments in the trash, transient options, pingbacks, and trackbacks. It also includes a page that shows the data size, index size, and overhead, of each database table.
I have helped many website owners over the years with WordPress-related problems. Sadly, very few people appear to take steps to keep their website database running efficiently. There really is no excuse for this as it only takes a few minutes to configure a WordPress website correctly.
To reduce the number of calls to my database, I install a cache WordPress plugin. This does not reduce the size of my database, but it puts less stress on my MySQL server and ensures my pages load quickly.
760c119bf3