WordPress is driven by a MySQL database which makes updating content cake once in the database. All of the WordPress data such as posts, comments, categories, and settings are stored within the MySQL database.
If you needed to change some information site wide, going through each and every record would prove a very time consuming task and also be prone to human error. By executing a SQL query against your WordPress database however, you can make the necessary changes quickly and efficiently.
We will show some SQL queries that can be of great assistance to you when using WordPress.
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. Before you proceed with any changes, be sure to backup your database. It is a good practice to always backup your database before making any major changes. This ensures that even if anything were to go wrong, you would still be able to restore your data.
1. Change Siteurl & Homeurl
WordPress stores the absolute path of the site URL and home URL in the database. This technique would commonly be used following moving domain names or attempting to transfer your WordPress site from the localhost to your live server.
If you simply copy over your database, your site will not load properly online. This is because the absolute path URL is still pointing to your localhost. You will need to change the site URL and the home URL in order for the site to work.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl.com', 'http://www.newurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';
2. Change GUID
After you have migrated your blog from the old domain to a new domain, you will need to fix the URLs for the GUID (globally unique identifier) field in wp_posts table. This is crucial because GUID is used to translate your post or page slug to the correct article absolute path if it is entered wrongly.
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://www.newurl.com');
3. Change URL in Content
WordPress uses an absolute path in the URL instead of a relative path in the URL link when storing them in the database. The problem comes when moving your site to a new server any links within pages/posts will link to your old domain/URL.
Therefore you will need to change all these URLs within existing posts so that they use the new domain location.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldurl.com', 'http://www.newurl.com');
4. Change Image Path Only
This technique is most useful if you decide to use a service such as Amazon CloudFront as your Content Delivery Network (CDN) to offload the delivery of images from your server.
After your have created your CNAME record, you can change all the image paths in WordPress to load all your images from Amazon CloudFront instead.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="http://www.oldurl.com', 'src="http://yourcdn. newurl.com');
You will also need to update the GUID for Image Attachment with the following query:
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldurl.com', 'http://yourcdn. newurl.com') WHERE post_type = 'attachment';
5. Change Default “Admin” Username
Every default WordPress installation will create an account with a default Admin username. This can be a security risk as a hacker can brute force attack your WordPress admin panel. If you can change your default “Admin” username, you will give your WordPress admin panel additional security to prevent against such an attack.
UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';
6. Reset Password
Ever wanted to reset your password in WordPress, but the normal methods won’t do?
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';
7. Update all articles by Author B to Author A
If you want to transfer all articles written under Author B to merge with those under Author A, it will be very time consuming if you try to go article by article. You can easily go through all the records and assign articles by Author B to go under Author A using MySQL.
You will first need to obtain the author ID of both authors by going to your Author & User page in your WordPress admin panel. Click on the author’s name to view their profile. At the address bar, look for “user_id”. That is the author ID information we require.
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
8. Delete Revision
When you are editing an article in WordPress, there will be many revision copies being saved. This is a waste of resources because excessive revision records can increase the burden of the database. Over time, when you have thousands of entries, your database will have grown significantly. This will increase loop iterations, data retrieval and will affect the page loading time.
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';
9. Export all Comment Emails with no Duplicate
Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.
SELECT DISTINCT comment_author_email FROM wp_comments;
10. Erase Every Spam Comment in a Second
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 this SQL command, we simply delete any comments that haven’t been approved yet.
DELETE from wp_comments WHERE comment_approved = '0';
11. Delete Post Meta
Installing or removing WordPress plugins is a very common task. Some of the plugins make use of the post meta to store data pertaining to the plugin. After you have removed the plugin, there remains data still left inside the post_meta table, which will no longer be needed.
Run the following query to clean up the unused post meta value. This will help to speed up and reduce the size of your database.
DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';
If you need to do some maintenance and clean up or move WordPress across domains, the above SQL queries can be useful and help speed things up for you. If you have any additional handy SQL queries for WordPress, please share it with us below.
I might make a script that includes these functions so you can do it with one click and some input boxes instead of going into your db app or phpmyadmin.
Thanks for the great write up.
I just tested Hack #6 (Password Reset). Not sure if it worked or not: “Error establishing a database connection” is (now) kinda hindering my attempt to login. Hmmmm …..
Using that hack should not affect your WordPress SQL login. That error typically occurs when the SQL password is incorrect within your wp-config.
Might be something to do with the recent “Great Media Temple Mass-Password-Reset Post-Hack”? The Media Temple crew are checking it out as we speak: irrespective of anything else, their Twitter support is sensational (@mediatemple).
I’ll keep you posted: it’s more-likey my err somewhere.
*** Update ***
The problem was ……. well, some unknown gremlins. It resolved itself – somehow?
Thanks for this list of hacks Gino – I’ll continue to use them (with beyond-my-knowledge trepidation).
I’m glad to hear everything is resolved. Thanks for the update!
thanks for this – i’ve been moving a lot of sites and changing domains lately – this is really helpful!
These are some great tips here – great post.
I have a similar problem that probably needs one of your SQL hack’s – but with a twist!
Wonder what thoughts you’ve got on this:
I’ve had to uninstall wordpress due to a virus, completed a fresh install and reconnected with a backup database. Posts/Comments/Pages can be seen in the Dashboard, but do not appear on the website – instead I get a 404 Error page. I think it’s because the wordpress uses absolute rather relative url paths, and the urls are still pointing to the old location.
Any idea of how to fix? Something similar to Hack 1 or 2 would appear logical, but what would the command be if I don’t need to change the home or site url, but rather edit the absolute path.
Either that, or it’s an edit that I need to make on .htaccess?
Thanks for reading – I really appreciate your time.
Pingback: Find, Search, Replace, and Delete In A WordPress Database - WordCast
Life saver, thanks man!
I would just lke to say a big thank you for this fantastic post.
I have used this page countless times now when I have built a new wordpress site and its because a great reference guide.
Thank you for this! Very helpfull! 🙂
Pingback: Handy Wordpress sql snippets | Ekkachai.net
Pingback: Find, Search, Replace, and Delete in the WordPress Database « Lorelle on WordPress
Need to find the SQL server for our site.