Useful SQL queries for migrating a WordPress database

First, if at all possible, use the WP Migrate DB Pro or BackupBuddy plugins to migrate your WordPress database. The money spent will save yourself a lot of frustration and hair pulling, and hours if not days of cleanup and massaging of the WordPress database.

But if that’s not possible, welcome to my world for the past week. I’ve had to migrate a WordPress database without my favorite migration tools. After scouring the web for tutorials and tips, I’m compiling them here for easy reference.

Gather Your Tools

First thing you need is to get your hands on the following:

1. Database name, prefix, user and password.

You can find these defined in the wp-config.php file. In the following examples, be sure to swap out the database name wordpress and prefix wp_ with the correct values from your situation.

2. Access to phpMyAdmin so that you can interact with the database.

We’ll be running the commands below through the SQL tab in phpMyAdmin.

Running SQL queries on your database with phpMyAdmin

You could also connect directly to MySQL through the terminal / shell prompt. Good luck if you don’t have phpMyAdmin.

3. Backup, Backup, Backup

Don’t say I didn’t warn you. Export your untouched database and lock it up. If all goes wrong, restore to this backup.

Change the Site URL

The first thing you would want to do is to change the site URL. You can browse the wp_options table for the siteurl and home options values directly, or you can use this SQL command:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'siteurl' OR option_name = 'home';

Next, you’ll want to replace all instances of oldsiteurl.com in your post’s contents and GUID.

UPDATE wp_posts SET post_content = REPLACE (post_content, '//www.oldsiteurl.com', '//www.newsiteurl.com');
UPDATE wp_posts SET guid = REPLACE (guid, '//www.oldsiteurl.com', '//www.newsiteurl.com');

Details on these and more useful queries from One Extra Pixel – 13 Useful WordPress SQL Queries You Wish You Knew Earlier

Add Yourself As An Admin Into WordPress

You’ve got access to the database (phpMyAdmin) and files (FTP), but you don’t have a WordPress login / can’t login to WordPress. What do you do? Just “let yourself in”.

SET @user_login := 'your_username';
SET @user_pass := 'Pa$$word';
SET @user_email := '[email protected]';

INSERT INTO `wp_users`
(`user_login`, `user_pass`, `user_email`, `user_registered`)
VALUES
(@user_login, MD5(@user_pass), @user_email, now());

SELECT @user_id := LAST_INSERT_ID();

INSERT INTO `wp_usermeta`
(`user_id`, `meta_key`, `meta_value`)
VALUES
(@user_id, 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}');

INSERT INTO `wp_usermeta`
(`user_id`, `meta_key`, `meta_value`)
VALUES
(@user_id, 'wp_user_level', '10');

Now you can login with the username and password at the top of the statement. Thanks Justin at 9seeds for this tip: Inserting yourself as admin into WordPress

Change Database Prefixes

To change your database prefix, use this SQL query. Be sure to use the same prefix as in wp-config.php or WordPress will try to install itself again.

Pro tip: Do this step last, so you can copy and paste the queries without having to modify them to match the new prefix.

RENAME table `wp_commentmeta` TO `newprefix_commentmeta`;
RENAME table `wp_comments` TO `newprefix_comments`;
RENAME table `wp_links` TO `newprefix_links`;
RENAME table `wp_options` TO `newprefix_options`;
RENAME table `wp_postmeta` TO `newprefix_postmeta`;
RENAME table `wp_posts` TO `newprefix_posts`;
RENAME table `wp_terms` TO `newprefix_terms`;
RENAME table `wp_term_relationships` TO `newprefix_term_relationships`;
RENAME table `wp_term_taxonomy` TO `newprefix_term_taxonomy`;
RENAME table `wp_usermeta` TO `newprefix_usermeta`;
RENAME table `wp_users` TO `newprefix_users`;

If you have additional tables, remember to rename those too. Next, we’ll replace any fields in the wp_options and wp_usermeta tables that use the wp_ prefix too.

SELECT * FROM `newprefix_options` WHERE `option_name` LIKE '%wp_%'
SELECT * FROM `newprefix_usermeta` WHERE `meta_key` LIKE '%wp_%'

The above query will return a few results and you’ll need to manually change them by hand.

Full details on WPBeginner: How to Change the WordPress Database Prefix to Improve Security

Clean Up Weird Characters In WordPress Posts

Due to differences in text encoding and database collation, you may end up with strange characters in your site content e.g. “, ”, ‘, ’. What an annoyance! Here’s a SQL query that will search your post contents and excerpts for these characters and replace them with the correct symbols.

UPDATE wp_posts SET post_content = REPLACE(post_content, '“', '“');
UPDATE wp_posts SET post_content = REPLACE(post_content, '”', '”');
UPDATE wp_posts SET post_content = REPLACE(post_content, '’', '’');
UPDATE wp_posts SET post_content = REPLACE(post_content, '‘', '‘');
UPDATE wp_posts SET post_content = REPLACE(post_content, '—', '–');
UPDATE wp_posts SET post_content = REPLACE(post_content, '–', '—');
UPDATE wp_posts SET post_content = REPLACE(post_content, '•', '-');
UPDATE wp_posts SET post_content = REPLACE(post_content, '…', '…');
UPDATE wp_posts SET post_content = REPLACE(post_content, 'Â', '…');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '“', '“');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '”', '”');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '’', '’');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '‘', '‘');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '—', '–');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '–', '—');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '•', '-');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, '…', '…');
UPDATE wp_posts SET post_excerpt = REPLACE(post_excerpt, 'Â', '…');

Hat tip to Jeff Starr for this tip. Check out his post to see what all the strange characters mean: Clean Up Weird Characters in Database

General Search and Replace

Here’s a useful query for you, just modify as needed.

# Example syntax
# UPDATE table_name SET column_name='replace with this' WHERE column_name='search for this';
UPDATE wp_posts SET post_author='correct_id' WHERE post_author='wpcom_id';

Delete Spam Comments and Post Revisions

This is also pretty useful as it may delete several thousand rows from your database, making it smaller and hence easier to move to a different server.

DELETE FROM wp_posts WHERE post_type = "revision";
# 0 = Comments awaiting moderation; spam = Comments marked as spam
DELETE from wp_comments WHERE comment_approved = 'spam';
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

Fix Incorrect Post Comment Counts

The final thing I had to do this past week was to fix the comments count on posts. The database I had to migrate used Disqus or some other plugin which caused the comment count to go out of sync. To fix that, I used this SQL query:

UPDATE wordpress.wp_posts wpp
LEFT JOIN
(SELECT comment_post_id AS c_post_id, count(*) AS cnt FROM wordpress.wp_comments
 WHERE comment_approved = 1 GROUP BY comment_post_id) wpc
ON wpp.id=wpc.c_post_id
SET wpp.comment_count=wpc.cnt
WHERE wpp.post_type IN ('post', 'page')
      AND (wpp.comment_count!=wpc.cnt OR (wpp.comment_count != 0 AND wpc.cnt IS NULL));

For details on this gem of a query, please see this post: How To Diagnose And Fix Incorrect Post Comment Counts In WordPress.

Now after all of that I’m sure you never want to see phpMyAdmin and tinker around with SQL queries any more. That’s why you should seriously consider WP Migrate DB Pro to migrate your files. It makes it super simple to migrate your site to a new URL. BackupBuddy is also an excellent tool. It’s migration feature is not touted as much as it’s backup features, but I’ve also used it often and with great success.

Happy SQL querying! (Or good luck, if you’re about to mess around with the database).


Leave a Reply

%d bloggers like this: