8 useful tricks for WordPress database

We must pay tribute to WordPress. This is really an amazing system for building blogs and even full-fledged sites. Nice and logical admin (especially in versions 2.7 and above, tasty), a considerable number of fairly high-quality templates ... and in general everything is very nice and relatively simple. I want to pay tribute to this system and do something useful for its users.

Over the past 10 years, MySQL has become incredibly popular on the web. Each WordPress blog is based on the MySQL database, which stores all your posts, settings, comments, and more.

Although plug-ins and even so-called hacks (I prefer “code inserts”) can solve some problems, sometimes you have no choice but to enter SQL queries in phpMyAdmin or directly into the database via SSH. So let's take a look at 8 useful tricks for a WordPress database .
')

1. Creating a backup of your database

The problem . Although the tips in this article have been verified, you should not put them into practice before creating a backup of your MySQL database (who knows ...)

The decision . To manually back up your database, follow these simple steps:

1. First you need to log in to phpMyAdmin and select your WordPress database there.

2. Click on the “Export” button, which is in the horizontal menu.

3. Select a data compression method (I personally use gzip) and click on the “Execute” button.

4. Your browser will ask if you want to download the backup. Of course, tell him solid Yes and save the file somewhere on your computer.

Note Please note that creating backups of the WordPress database is much more convenient to do with the help of the special plug-in WP-DB-Backup . WordPress users can not hesitate to install this plugin right now if for some reason they still have not done so.

2. Batch deletion of revisions of records

The problem . Post revisions are a new feature of WordPress since version 2.6. It can be very useful, and it can also increase the size of the MySQL database. Of course, you can manually delete post revisions from the admin panel. But it is very long and tedious. There is a better solution.

The decision . And the solution to the problem is very simple: we package, I mean everything at one stroke, delete post revisions using a simple SQL query. The result can be amazing if you have a lot of records. Your database can lose weight by half!

1. You need to log in to phpMyAdmin and select your WordPress database there.

2. Then click on the “SQL” button. A window will appear in which you must insert the following query:

DELETE FROM wp_posts WHERE post_type = "revision";

3. That's it! Depending on the number of entries, you saved a lot of precious time and cleaned the base.

Explanation of the code . In the wp_posts table there is a field called post_type . This field can have multiple values, such as post , page, or revision . When we want to get rid of the revision of the records, we simply run the command so that it deletes all the values ​​in the wp_posts table, in the field of which the post_type is the revision value. Here is how.

3. Remove 5000 spam comments in one second.

The problem . True story: a friend of mine recently opened his own blog and started actively promoting it all over the internet. After a few weeks of intensive work, he spent a couple of days on vacation, without a network.

When he returned home and looked at his blog ... he saw more than 5,000 posts that were awaiting moderation! How to be?

The decision . Fortunately, my friend told me about his problem with fucking spammers. He had already spent 45 minutes manually checking and deleting spam, as I showed him a very useful trick:

1. Login to phpMyAdmin and select your WordPress database there.

2. Click on the “SQL” button. A window will appear in which you must insert the following query:

DELETE from wp_comments WHERE comment_approved = '0';

3. And forgive spammers! Enjoying cleanliness and comfort ...

Explanation . The wp_comments table contains the comment_approved field, which contains a boolean value (1 or 0). Approved comments have a value of 1, and comments that are pending moderation are 0. The above command simply deletes unapproved comments. It's simple.

But be careful! Although this solution is awesomely convenient for automatically deleting millions of spam comments, it also deletes normal unapproved comments. If you are still not using a plugin like Akismet , then it's time to start to prevent blog spamming.

4. How to change a record attribute

The problem . When you install WordPress, a default admin account is created. Some bloggers make the mistake of using this account to create their posts until they realize that this is somehow impersonal.

The decision . Modification of the attribute of the author for each entry takes a lot of time. Fortunately, SQL can help us:

1. Login to phpMyAdmin and select your WordPress database there.

2. First we need to determine the correct user IDs. So click on the “SQL” button. A window will appear in which you must insert the following query:

SELECT ID, display_name FROM wp_users;

3. phpMyAdmin will display a list of “IT members” that are associated with WordPress users. By the way, NEW_AUTHOR_ID is the ID of the most recently created author, and OLD_AUTHOR_ID is the ID of the original admin account.

4. After you have defined the aydshniki NEW_AUTHOR_ID and OLD_AUTHOR_ID, run the following command:

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

5. That's all. All entries that were associated with the admin account will now be the property of the user you selected.

5. Reset password

The problem . To protect their blogs, people often choose strong passwords, such as 7 * KoF5i8_. This, of course, is commendable, but everyone has heard many stories about how admins forget their passwords :)

The decision . When you forget your password, WordPress can send you a link to reset it by email. But if you do not have access to the soap that is specified in the WordPress database, or if you think that the issue can be solved in some other way, then here’s a way of “hacking”:

1. Login to phpMyAdmin, select your WordPress database there and open the SQL window.

2. Enter the following command (taking into account that your login was “admin”):

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

3. Well, actually, that's all. Your password will successfully update to the one you specified in the place marked as “PASSWORD”.

Explanation . User passwords are stored in the wp_users table . Of course, MD5 hash is used to protect them from viewing.

We sent the “UPDATE” SQL query and used the MySQL built-in function - MD5 () to convert our password to MD5 and update it. Using "WHERE" ensures that we have updated only the administrator password. The same query, but without using the “WHERE” parameter, will update all passwords in the database!

6. Change your domain name

The problem . Although it is not recommended, but you may at some point want to change the domain name of your blog and at the same time save all its data. Since WordPress stores the domain name in the database, you will have to change the base a little to link your new domain and WordPress blog.

The decision .

1. As you might have guessed: log in to phpMyAdmin, select your WordPress database there and open the SQL window

2. To change the URL of the Wordpress press the following 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';

3. Then we need to replace the relative URL (GUID) for each entry. The following command will do this for you:

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

4. This is almost the end. It remains only to find and replace absolute URLs in the wp_posts table for a conclusive final:

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

5. And this is the end. You can log in to the admin area of ​​your blog using the new URL.

7. Display the number of SQL queries of your blog.

The problem . When you try to optimize your blog's loading time, knowing the number of database queries helps a lot. In order to reduce the number of requests, the first thing you need to know how many requests occur on any page.

Decision. Trick: we don’t need to go into phpMyAdmin :) You just need to open the footer.php file for editing (it definitely is in your theme) and add these lines of code there:

<?php if (is_user_logged_in()) { ?>
<?php echo get_num_queries(); ?> <?php timer_stop(1); ?> .
<?php } ?>

Save the file and visit your blog. In the "basement" you will see the number of requests to the database of WordPress and the time spent on their creation.

Note It seems that many WordPress users are not aware of this wonderful opportunity. The get_num_queries () function returns the number of queries created during page loading.

Please note that the code above displays the number of requests only for logged in users, since blog guests and search bots do not have to know this information. But you can make the mapping public by simply removing the conditional if (is_user_logged_in ()) operator from the code.

8. Restoring your database

The problem . Let's say ... for some reason, such as hacking or an issue with updating, you may lose your blog data or find it hopelessly corrupted. So if you have a backup copy (really there is, yes?), You need to import it into your WordPress database. And then everything will be fine. Probably.

The decision .

1. Login to phpMyAdmin, choose your WordPress database there.

2. Click on the “Import” button in the horizontal menu.

3. Click the "Open" button (Browse) and select the most recent copy of the database from your disk.

4. Click on the button "Go" (Execute). If everything goes well and the gods are on your side, the database will again be fully functional.

This post is a free translation of article 8 Useful WordPress SQL Hacks . Thanks to Jean-Baptiste Jung, the author of the original. The post is designed for beginners WordPress and I hope that the recommendations will be useful to someone.