8 Useful WordPress SQL Hacks

Advertisement

By Jean-Baptiste Jung

Over the past 10 years, the MySQL database has become incredibly popular on the Web. Every WordPress blog is driven by a MySQL database, which contains the blog’s posts, settings, comments and much more.

While plug-ins and even coding hacks can solve some problems and achieve some tasks, sometimes you don’t have any other choice than to execute SQL commands in phpMyAdmin or directly to the database via SSH. Let’s take a look at 8 useful SQL hacks for WordPress. Each section of this post presents a problem, suggests a solution and provides an explanation to help you understand the solution.

1. Creating a Backup of Your Database

8 Useful WordPress SQL Hacks

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-Backup1 plug-in. WordPress users should install this plug-in if they have not yet done so and create regular backups of their data.

2. Batch Delete Post Revisions

8 Useful WordPress SQL Hacks

The problem. Post revisions, a new WordPress 2.6 feature, can be very useful, but they also increase the size of your MySQL database. Sure, you can manually delete posts revisions, but that’s very long and boring work.

The solution. The solution to this problem is simple: we batch delete post revisions by using a simple SQL query. The result can be almost unbelievable if you have a lot of posts: Your database size will be reduced by half!

  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_posts WHERE post_type = "revision";
  3. You’re done. Depending on how many posts you had in your WordPress database, you may have saved lots of precious space!

Code explanation. The wp_posts table has a field named post_type. This field can have one of many values, such as “post,” “page” or “revision.” When we want to get rid of post revisions, we simply run a command to delete any entry in the wp_posts table in which the post_type field is equal to “revision.”

Source:

3. Erase 5000 Spam Comments in a Second

8 Useful WordPress SQL Hacks

The problem. True story: a friend of mine recently created his own blog and started to promote it everywhere on the Internet. After some weeks of intensive work, he spent some days on vacation without Internet access.

When he came back home, he looked at his blog and saw… 5000+ comments awaiting moderation! Of course, most of them were spam, but he was actually about to check them all to make sure he did not delete a valid comment made by one of his regular readers.

The solution. Happily, my friend told me about his spam problem. He had already spent 45 minute manually deleting spam when I showed him this useful SQL tip.

  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';
  3. 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 Akismet3, install it now to prevent spamming.

Source:

4. Change the Post Attribution

8 Useful WordPress SQL Hacks

The problem. When you installed WordPress, an “admin” account was created. Some bloggers make the mistake of using that account to write their posts, until they realize that it’s not personal at all.

The solution. Modifying author attribution on each post takes a lot of time. Happily, SQL can help you get things done:

  1. Log in to your phpMyAdmin and select your WordPress database.
  2. First, we have to get the right user IDs. To do so, open the SQL command window and execute the following command:
    SELECT ID, display_name FROM wp_users;
  3. phpMyAdmin will display a list of user IDs associated with WordPress users. Let’s say that NEW_AUTHOR_ID is the ID of the more recently created author, and OLD_AUTHOR_ID is the original admin account ID.
  4. After you swap the NEW_AUTHOR_ID and OLD_AUTHOR_ID IDs, run the following command:
    UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;
  5. That’s all. All posts previously attributed to admin are now attributed to whichever valid user you have selected.

Source:

5. Manually Reset Your Password

8 Useful WordPress SQL Hacks

The problem. In order to protect their blogs, people often pick strong passwords, such as u7*KoF5i8_. Of course, this is a good thing, but I have heard many stories of forgotten admin passwords.

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!

Source:

6. Change Your WordPress Domain Name

8 Useful WordPress SQL Hacks

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';
  3. 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');
  4. 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');
  5. You’re done. You should be able to log in to your WordPress dashboard using your new URL.

Explanation. To easily change our WordPress domain name, I took advantage of the super-useful MySQL function “replace,” which allows you to replace one term by another.

Source:

7. Display the Number of SQL Queries on Your blog

8 Useful WordPress SQL Hacks

The problem. When trying to optimize your blog’s loading time, knowing the number of queries made to the database is important. In order to reduce queries, the first thing to know is how many queries are made on a single page.

The solution.

  1. This time, no need to log in to phpMyAdmin. Simply open the footer.php file in your theme and append the following lines of code:
    <?php if (is_user_logged_in()) { ?>
        <?php echo get_num_queries(); ?> queries in <?php timer_stop(1); ?> seconds.
    <?php } ?>
  2. Save the file and visit your blog. In the footer, you’ll see the number of queries made to the WordPress database as well as the time it took to make them.

Explanation. Seems that many WordPress users aren’t aware of this useful function. The get_num_queries() function returns the number of executed queries during a page load.

Note that the above code will only display the number of queries to logged-in users, because regular visitors and search engine bots don’t need to know about it. But, if you’d like to make it public, simply remove the if (is_user_logged_in()) conditional instruction.

Source:

8. Restore Your WordPress Database

8 Useful WordPress SQL Hacks

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.

(al)

↑ Back to topShare on Twitter

This guest post was written by Jean-Baptiste Jung, a 28-year-old blogger from Belgium, who blogs about Web Development on Cats Who Code, about WordPress at WpRecipes and about blogging on Cats Who Blog . You can stay in touch with Jean by following him on Twitter.

  1. 1

    Number 1 can also be done automatically as a cron job with the file emailed to you at a set time.

    -2
  2. 2

    Nice and useful tips (especially the DB ones). Thanks for sharing!

    1
  3. 3

    Christopher LCP Mendes

    December 18, 2008 3:56 pm

    The Search and Replace plugin can do most of this in a user-friendly way: http://wordpress.org/extend/plugins/search-and-replace/

    I recommend it, but be sure to use WP-Backup (mentioned in the article) before hand.

    0
  4. 4

    Ahem if the wordpress database is a big big (more than 2Mo usually. It depends of the php.ini configuration), the phpmyadmin importer won’t work because the file won’t be uploaded.

    The best solution is and stays the mysql one, not the phpmyadmin one.
    Generate the backup : http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html
    And to get it back, in a shell, enter the following :
    mysql -u [username] -p [password] [database_to_restore] < [backupfile]

    It requires a shell access but it’ll be working on any database, big or smaller (and the bigger are usually much more painfull with phpmyadmin than smaller ones).

    -1
  5. 5

    Wow, those are exceptional! I could have used the Password hack today without bugging my boss to reset the password.

    Going to try the Spam hack tommorow… We have about 500 comments in spam.

    Are these WP Version specific? I upgraded to 2.7 yesterday.

    0
  6. 6

    I love the post BTW.

    What I need is to do is change the /Dir/ for uploaded images listed in posts inside the SQL data base. I have posted in the wordpress forums and recived not one peep of help.

    0
  7. 7

    Just a tip, to backup a database from the command line, do “mysqldump -u username -p databasename > databasename.sql

    0
  8. 8

    restoring a database would be “mysqldump -u username -p databasename < databasename.sql”

    0
  9. 9

    @Damien
    Most of the hosting provider doesn’t give you shell access. So, it’s a good idea to stick to phpmyadmin whenever it’s possible.
    One trick that I do to overcome this limitation is to enable built in phpmyadmin Webserver upload/save/import directories (by editing config.inc.php file of phpmyadmin). Search for this string: $cfg['SaveDir'] and enter a directory in your web server. Next time you do any exporting, you have new option to save it to server directory, then later you can download it using F TP.

    0
  10. 10

    Nice tips. The most useful thing I’ve ever done to my WordPress installations is to install and update them with Subversion- it’s an absolute lifesaver. Now I don’t worry when they release a new version, because it take me 20 seconds to log into my server and run “svn sw”… That would be a good follow-up post topic.

    0
  11. 11

    Im not sure if it is md5 hash for the password. As you can see the characters generated contains characters such as * _ ^ $ and all. MD5 only contains 0-9 and A-F, or the so called hex code. correct me if Im wrong though

    0
  12. 12

    A lot of good tips here, e.g. the one to recover the password. But some of these tips can also be done by using the WordPress admin section, e.g. changing the name shown when using the administrator-account

    0
  13. 13

    very interesting art. ty bro.

    0
  14. 14

    good post really…thanx :)
    P.S something similar should be done for joomla

    0
  15. 15

    Nice article.
    An appendix for point 2:
    You can easy configure how many post revisions will be saved.
    Just insert the following code into your wp_config.php
    define('WP_POST_REVISIONS', 3);

    Explanation: WP_POST_REVISIONS specifies how many revisions will be stored in database (0=no revisions saved)

    see: article in wordpress codex

    0
  16. 16

    I agree with Dani – A joomla version would be great :) thanks for this!

    0
  17. 17

    Beau travail JBJ ! Continue ainsi !

    0
  18. 18

    I love the hack that uses mysql command, that make wordpress make easier specially if you have access on shell :)

    0
  19. 19

    # 7 is a handy one indeed. I,m using it sinds I use the WP admin.

    0
  20. 20

    Luciano Passuello

    December 19, 2008 3:00 am

    The “Batch Delete Post Revisions” doesn’t delete the associated metadata with post revisions.
    To make sure you delete the post revision WITH all related data, use the following command:


    DELETE a,b,c
    FROM wp_posts a
    LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
    LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
    WHERE a.post_type = 'revision'

    (code stolen from Lester Chan).

    0
  21. 21

    Thanks for the these! Will use in the future!

    0
  22. 22

    Thanks for good set of tips….keep up the good work !!

    DKumar M.
    http://www.instantshift.com

    0
  23. 23

    don’t try to jump on programmers turf SM. just be the designer. last time a designer tried to touch a db, he just dropped all the tables “by mistake”

    0
  24. 24

    It’s perfectly true that any SQL dump over about 2Mb will be difficult to import. I had a 24Mb database and phpMyAdmin choked on it repeatedly.The solution was a very cool php script called (amusingly) BigDump (http://www.ozerov.de/bigdump.php) which enables you to upload the entire db archive file via FTP and then BigDump will incrementaly import it into your mySQL database. This is extremely useful as you do not require any shell access as some other solutions require. :)

    0
  25. 25

    ^^ Typical of elitist programmers. There’s a lot of designers out there becoming web developers via WP. It’s a great way to learn PHP and MySQL.

    Great post by the way. A lot of good tips for WP/MySQL n00bs like myself.

    0
  26. 26

    What is the WordPress? I can use it for internet?

    0
  27. 27

    The mysql manual an be found at:

    http://www.mysql.com/

    I don’t think the article suitably warns sql newbies of the dangers of execute queries. There is NO UNDO feature, and yes, you can totally hose an entire database with a few words.

    while someone labeled it “elitist”, #23′s post (above) is a valid warning. I’ve personally witnessed drop table incidents.

    0
  28. 28

    I really wouldn’t call these “hacks,” more like workarounds. Love the idea though. What about doing a list of real hacks? Like adding custom, multiple TinyMCE fields in the page and post editor, or hacks extending the CMS functionality of the wordpress core.

    0
  29. 29

    I agree with Ansell Cruz Password recovery method didn’t work for me. I found village-idiot dot org who provide emergency password recovery that worked fine.

    0
  30. 30

    Instead of using phpmyadmin with it’s PHP limits on file sizes, just download the MySQL client. It works awesome, and you can open an entire local .sql script and execute. For a database software, it has a very nice interface, and even includes an easy to use manual for creating and executing your own queries.

    Dear Elitest Programmer,
    Why are you reading a design blog? Last time I saw a design from a programmer, I barfed in my mouth a little because of all the solid gray tones. That was sarcasm if you didn’t get it.

    0
  31. 31

    Pretty good stuff.
    To change the domain of a wordpress site, I just use the search feature in phpMyAdmin. I search the old url (all the tables), it will return two queries in the wp_options table. Edit and replace both with the new URL. Works for me.

    0
  32. 32

    Thanks for the these! nice data.

    0
  33. 33

    Nice post! Very useful

    0
  34. 34

    Great article! Keep on good work!

    Cheers

    1
  35. 35

    Most of this tips are bad, especially the one with deleting spam comments! The only two that could be applicable is tutorial on how to restore and backup DB. Author should better highlight the importance of messing with DB, especially with ‘delete’ and ‘update’ syntax!!!

    0
  36. 36

    It’s very nice that you ive writing the subjects about seo. (WP)
    Esperially it is nice as visual.

    0
  37. 37

    Great info!

    0
  38. 38

    Thank you so much for the tips, especially the URL moving one.
    :)

    0
  39. 39

    I’m definitely gonna refer back to this post next time one of these problems pop up B-)

    0
  40. 40

    I wrote a little one-line script to backup my WP database to a different partition using mysqldump and run it as a cron job every morning in the wee hours.

    Anyone who’s interested can read about it here

    0
  41. 41

    Oh, yeah, I then embedded the command in a longer script which creates a folder named with today’s date and sends the *.sql file into that folder. The longer script is the cron job.

    0
  42. 42

    Number 1 and 8 I most recently had to use, Upgrading to wordpress 2.7 causes problems, but luckily i remembered to backup my database before i upgraded.

    0
  43. 43

    Great tips! I’m sure I’ll be using them. The easiest way that I’ve found to reset a WordPress password is to create a user with the password that you want and then just copy the password from that user to the user that you’re trying to reset. For example, the MD5 hash for “password” is “$P$Bq8Zk0hCxoUDcKV8jcmymS1Q10dQbz0″. Password reset.
    I really like the easy method of bulk deleting post revisions. Very nice! I hope I never have to change the domain name of an existing WP install.

    0
  44. 44

    I wish I had known about no.6 when I transferred a test site to its correct domain name. I ended up doing a lot of manual editing. I am now going to reduce my own database hopefully dramatically by ridding it of post revisions. Thank you for these tips and the clear directions.

    0
  45. 45

    I have a question in regards to #6.

    What if you’re wanting to switch from one web host to another and also change your blog’s address from my-site.com/blog to my-site.com?

    Would #6 still apply or do I have to do something else?
    Thanks

    0
  46. 46

    These are not `hacks’ — they’re just simple SQL queries (with little thought necessary to come up with them). And most of this stuff can be done from within the WordPress Dashboard.

    0
  47. 47

    THANK YOU! You just saved me a lot of misery on 4 different blogs over-run with Spam by posting #3.

    0
  48. 48

    Great tips :) really valuable!

    0
  49. 49

    Thankyou, amazingly helpful.

    0
  50. 50

    @Luciano Passuello – #20 – Used this SQL just now. Backed up the DB before hand (4.5MB), ran the SQL, backed up afterwards (3.0MB). Saving space already. Good find and update to the original post. Thanks.

    0
  51. 51

    Thanks for the tips! It really help a lots in my blog :D

    0
  52. 52

    Is the password tip (these aren’t hacks) relevant anymore? I know WP added a salt to the password at some point — straight MD5 hashes aren’t that safe because of rainbow tables — so I don’t think this tip will actually work.

    0
  53. 53

    Very useful thanks for the good article as always

    0
  54. 54

    Scott Kingsley Clark

    April 17, 2009 6:25 am

    on #6, you can really screw up cforms by doing this, so I’d suggest adding this extra WHERE statement:

    option_name NOT LIKE ‘%cforms%’

    0
  55. 55

    Thanks #6 saved me heaps of time that I would have had to spend probably installing phpmyadmin! Tagging in delicious for future reference! :D

    Rory
    What is the Science

    0
  56. 56

    awesome information thanks

    0
  57. 57
  58. 58

    Hello. I’ve been using WordPress for a couple of years, but still feel like a newbie. In particular, I never even looked into the Mysql database till the other day. I couldn’t make heads or tails out of it.

    I think there is something fishy going on with my blogs at this web host – perhaps their servers have a quirk. I installed a plugin to make images pop up on my e-cards page. It was working beautifully. I paused to send myself a card. Got the card, all was well. Went back to my e-cards page to send another — the popup had totally stopped working — without any intervention on my part. Uninstalling and reinstalling had no effect. I had to “shop” for another similar plugin, which worked. I immediately backed up the whole site (using Simple Backup).

    Then, my social icons suddenly changed! I was using Trackable social icons, and I liked them. There were precisely two even rows of them at the bottom of my posts and pages. Suddenly, ONE icon vanished, and would not come back even when deactivated and reactivated; uninstalled and reinstalled. Therefore, I no longer had two evenly balanced rows of icons under my posts.

    I then went “shopping” for a new social icon plugin. I wasn’t happy with the plugins, because none that I could find will open their windows in a popup container. They all seem to open full-size windows that block off my blog from the visitor’s view.

    Suspecting that someone might be tampering in the background of my blog to disable my various plugins, I decided to make my own social icons bar for people to link their social services to my blog.

    I therefore found icons I liked, and I hosted my icons in Photobucket, so that nobody at my web host with access to my cpanel could disable the image icons.

    I then collected the urls to put into the popups, arranged all the icons in a table, and then I went to find a new popup plugin to open a window in a container.

    I found one. I installed it. I saw it in the Settings menu, just sitting there waiting for me to use it. Then, while I was staring at my screen for an instant, my entire plugins page changed as if someone else where using it and had scrolled all the way down the page. Only a few plugins were on the screen, as opposed to the whole installed menu of plugins.

    And then, when I clicked on Settings again — the newly installed “Informational popup” plugin admin was GONE.

    This was like the e-card plugin that suddenly stopped working for no reason. And again, the new popup plugin wouldn’t come back by deactivating and reactivating; or uninstalling and reinstalling.

    So, I decided to see if I could “see” anything in the database. I logged into cpanel, opened up mysql — and there were “tables” in there for some plugins I had long ago uninstalled! But no sign of ANY “table” for the e-card popup plugin that had stopped working; or, now, for the “Informational popups” plugin that had just disappeared in front of my eyes. This presumes, of course, that there should BE a “table” for every plugin — I don’t know if there should be, I have never looked into this before.

    What I would like to know is this:

    a) should there be a “table” in MySql for each and every plugin I have ever installed, even if I later uninstalled it?

    b) if I have installed a plugin, and there is no “table” for it, is this normal?

    c) is it possible my web host has a bored staff member who is following me online and sabotaging my plugins and my images? If so, how could the sabotage be detected; and then how could it be corrected? Could sabotage be done from the MySql database?

    Another suspicious occurrence in a different blog with the same host — one day, I was writing a draft post about black ants. I paused for 20 minutes to go to the store. When I returned, I found my Admin icon had been changed to… a black ant. Previously, it had been the defaut set of icons you get when you instal WordPress.

    There is no plugin I could find anywhere that could have accomplished that. I have no idea how to change my own Admin icon. How did it manage to change into an image reflecting the draft story I had planned to post?

    In that same blog, images I had installed suddenly “vanished” and refused to come back even when re-uploaded! Instead, when I tried to reupload them to the Media Library, they showed up as white blanks. I ended up generating NEW versions of the images in a different file format, and that worked, the new images showed up, but never the old ones. And some of the old ones were actually icons in a social media plugin! So there have been “image icon” problems with social media plugins at two blogs with this same host.

    If you could recommend links to read, or videos to watch, to help me resolve this, I would appreciate it.

    Thanks for your time.

    0

↑ Back to top