Tutorials

Tricks/Techniques To Manage WordPress

1. Creating a Backup of Your Database

The problem. While the tips in the rest of this post have been tested, you should definitely not try any of them without first having a proper backup of your MySQL database.

The solution. To create a manual backup of your WordPress database, follow these simple steps:

1. Log in to phpMyAdmin and select your WordPress database.
2. Once done, click the “Export” button located in the horizontal menu.
3. Choose a compression method (personally, I use gzip), and click the “Execute” button.
4. Your browser will ask you if you want to download the backup. Of course, select “Yes,” and then store it on your hard drive.

Explanation. Note that creating a backup of your WordPress database can be more easily executed with the WP-DB-Backup plug-in. WordPress users should install this plug-in if they have not yet done so and create regular backups of their data.

2. Erase 5000 Spam Comments in a Second

1. Log in to phpMyAdmin and select your WordPress database.
2. Click the “SQL” button. Paste the following code in the SQL command window:

DELETE from wp_comments WHERE comment_approved = ’0′;

DELETE from wp_comments WHERE comment_approved = '0';

1. Goodbye bad comments! Enjoy your spam-free database!

Explanation. The wp_comments table contains a field named comment_approved, which is a boolean value (1 or 0). Approved comments have a value of 1, and comments awaiting moderation have a value of 0. By running the above command, we simply delete any comments that haven’t been approved yet.

Be careful. While this solution can be pretty useful if you have millions of spam comments to delete, it will also erase valid unapproved comments. If you don’t already use Akismet, install it now to prevent spamming.

3. Manually Reset Your Password

The solution. When you lose your password, WordPress can email you a link to reset it. But if you don’t have access to the email address recorded in the WordPress database anymore, or if you prefer just running a simple command instead, here is the hack.

1. Log in to your phpMyAdmin, select your WordPress database and open the SQL window.
2. Insert the following command (assuming your username is “admin”):

UPDATE `wp_users` SET `user_pass` = MD5(‘PASSWORD’) WHERE `wp_users`.`user_login` =`admin` LIMIT 1;

3. You’re done. Your password has been successfully replaced by whatever you inserted in space above marked “PASSWORD.”

Explanation. User passwords are stored in the wp_users table. Of course, an MD5 hash is used to secure the password.

We have to set up an “UPDATE” SQL request and use the built-in MD5() MySQL function to convert our password to MD5 and then update it. The “WHERE” clause ensures that we’re updating only the admin’s password. The same request without the “WHERE” clause would result in all passwords being updated!

4. Change Your WordPress Domain Name

The problem. Although it is not recommended, you may want at some point to change your domain name while keeping your blog and its data. Because WordPress records your domain name in the database, you have to change the database in order to connect your new domain name to your WordPress blog.

The solution.

1. You guessed it: the first thing to do is log in to your phpMyAdmin and select your WordPress database.
2. Click the “SQL” button to open the SQL command window. In order to change your WordPress URL, execute this first command:

UPDATE wp_options SET option_value = replace(option_value, ‘http://www.oldsite.com’, ‘http://www.newsite.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl‘;

1. Then, we have to replace the relative URL (guid) of each post. The following command will do that job:

UPDATE wp_posts SET guid = replace(guid, ‘http://www.oldsite.com’,'http://www.newsite.com’);

1. We’re almost done. The last thing to do is a search and replace in the wp_posts table to make sure that no absolute URL is still here:

UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.oldsite.com’, ‘http://www.newsite.com’);

You’re done. You should be able to log in to your WordPress dashboard using your new URL.

5. Restore Your Database

The problem. Let’s say, for some reason, such as a hacking or upgrade problem, you have lost your blog data or it has become corrupted. If you have a backup (and I hope you do!) you will have to import it to your WordPress database.

The solution.

1. Log in to phpMyAdmin and select your WordPress database.
2. Click the “Import” button in the horizontal menu.
3. Click the “Browse” button and select the most recent database backup on your hard disk.
4. Click the “Execute” button. If everything went well, your WordPress database is fully functional again.