Before WordPress, you may have manually saved all your website’s content to HTML or PHP files. However, this script saves all your blog data, including posts, categories, comments and options, in a database on your host’s server. This central location makes backing up your website easy, but you’ll get the most out of WordPress if you follow these tips for maintaining your database.
Create Backups of Your WordPress Database
If you have more than one installation in the same database, WP DB-Backup lets you download them all to your computer. You can also schedule the plugin to email you a copy of the database on a recurring basis. You’ll always have your content even if something happens to your host or database.
Optimize Your Database
Plugins like WP-Optimize optimize the tables in your database. Your database might have more tables than a basic installation because you’ve added plug-ins or have multiple blog installations. These plugins look for tables that remain open and close them, and you don’t need PHPMyAdmin access to clean up your database.
Delete Revisions, Spam Comments and Pingbacks
WordPress includes the ability to revert to an earlier edition of your post; however, these revisions can quickly clog up your database, especially if you often save drafts. WP-Optimize is among the plugins that let you delete revisions, spam comments and other content after you no longer need it. Delete Revision is another plugin that exists solely for deleting revisions.
You can manually delete spam comments and pingbacks, which take up database space. Run the following query in PHPMyAdmin to delete pingbacks if you don’t use them on your blog:
If you’re like to remove all the spam comments from your blog, the following query deletes those comments:
DELETE from wp_comments WHERE comment_approved = ‘0’LETE from wp_1_comments WHERE comment_approved = ‘0’
Finally, you can delete tags that you may have used in the past but no longer use on your WordPress-powered site with this query:
SELECT * From wp_terms wt INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy=’post_tag’ AND wtt.count=0;
Reset a Lost Password
If you can’t reset your password via email for any reason, you can do so by editing your database. Log in to PHPMyAdmin from your host’s control panel. Select your database and find the “users” table, which may have a prefix in your installation. Click the “Browse” tab. Choose “Edit” in the line that contains your username. Delete the encrypted text from the “user_pass“ field. Type in your new password, choose “MD5” from the drop down menu that lists functions. Click “Go” to change the password. You can now log in to your WordPress dashboard.
Reduce Stress by Caching
Your popular website might be straining the database if WordPress is constantly looking up information. Consider a caching plugin like WP Super Cache or WP-Cache to create temporary HTML files on your server and reduce strain on the database.
Maintaining your database improves load time, prevents you from losing data and gives you more ways to change options for your WordPress-powered website. Your host and visitors will both thank you.