Useful Mysql Commands for Working with WordPress

When I develop a new wordpress site for a client, I typically work on a development server, only moving it to the clients web host when the site is finished and has been approved by the client, including adding the final content and pictures.

As a result, I need to be able to update the wordpress site’s url via the database, as well as several other fields, in order to ensure that the website will work on the new server.

What follows are several queries and commands I use to migrate a wordpress site from one server to another.

Note:This guide assumes you are using the default “wp_” table prefix. However, if you are using something different, you would need to replace this with the correct table prefix, as specified by the wp-config.php ‘$table_prefix’ setting.

Backup, Backup, Backup!

The following command would make a backup of your wordpress database, saving it as “your_database_backup_date.sql” You should replace your_database with the name of the database and date with the current date.

If you are using something like phpMyAdmin, you use their export feature.

mysqldump -u your_user -p your_database > your_database_backup_date.sql

If you need to revert your database, you can use the following command:

mysql -u your_user -p your_database < your_database_backup_date.sql

Changing Your WordPress Site’s URL via Mysql

The following commands can be used to change the url of your wordpress site via the database. The commands will be explained below:

select * from wp_options where option_name = 'home' or option_name = 'siteurl';

update wp_options set option_value = replace(option_value, 'old_url', 'new_url') where option_name = 'home' or option_name = 'siteurl';

update wp_posts set guid = replace(guid, 'old_url','new_url');

update wp_posts set post_content = replace(post_content, 'old_url', 'new_url');

Line 1: This select will display your current wordpress url, you can use this in place of “old_url” in the subsequent queries.

Line 3: This uses the mysql “replace” command to replace old_url with the new_url of your website. Note that there is NO trailing slash(\) after the url.

Line 5: This replaces the GUID of the post in the wp_posts table to the new url. While probably not 100% necessary, it is good to remove the old values

Line 7: This replaces all instances of the old url within the actual content of your posts. This should affect both pages and posts, ensuring that links or image urls are updated to the new website.

Regarding Line #7, it is sometimes a good idea to change this to use a relative path(/) if you use SSL on your website. This way, you will not get an error if the page is visited via HTTPS.

Updating Custom Fields and Other Meta Values

If you use custom fields in your posts, you may want to update the wp_postmeta table. However, you should be extremely careful that there is not serialized or json encoded data, as if there is, the below will probaby break your meta values! Instead, you may want to do something like I describe here.

Again extremely careful doing the below!!

select * from wp_postmeta where meta_value like '%old_url%';

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'old_url', 'new_url');

Line 1: This checks for any data in your wp_postmeta table, which includes things like custom menu items, as well as custom fields. It is a good idea to run this first, in order to see what would be affected by the next query.

Line 3: This will update all of your meta_values to use the new website url. Note that some plugins make use of custom post types and custom fields, like popular contact form and search optimization plugins, so even if you haven’t set any yourself, there may still be some values that need to be updated.

Updating Menu Items with Your New Url

Custom Menus, accessible via Appearance->Menus, is a really awesome feature of wordpress, which makes adding/updating/ordering navigation menus very easy.

If you just use a “custom link” menu item, as opposed to the standard page option, you may need to update it to the new url too.

select * from wp_postmeta where meta_key ="_menu_item_url" and meta_value != "";

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'old_url', 'new_url') where meta_key ="_menu_item_url" and meta_value != "";

Line 1: This checks for any menu items that have a custom value. You can use this to see if there are any that need to be updated.

Line 3: This will update any menu items, identified by the meta_key of _menu_item_url, which use the old url.

Again, depending on whether you are use SSL, you may want to use relative paths here, instead of a full url.

Deleting Old Revisions

Post/Page Revisions are another very cool feature of wordpress, allowing you to go back and revert posts to an earlier version.

If you don’t see the revision option, make sure they are being displayed by editing a post and then clicking on the “Screen Options” tab, top right. After that, check the “Revisions” checkbox and you should see the revisions at the bottom.

Once displayed, you can easily look back, view differences, and restore old versions of a post.

After the client has approved all website content, I like to remove all old revisions, so going forward it is easy to identify the original revision that was present at launch. You can do that by issuing the following command:

DELETE FROM wp_posts WHERE post_type = "revision";

This deletes all posts/pages that have the post type of revision.

Copying WordPress Post Categories from one Database to Another

While not typically something you would do when changing urls, this can be useful if you have a base set of categories you want to start with in a new wordpress database.

delete from new_wordpress_database.wp_term_taxonomy;

delete from new_wordpress_database.wp_terms;

insert into new_wordpress_database.wp_terms select * from old_wordpress_database.wp_terms;

insert into new_wordpress_database.wp_term_taxonomy select * from old_wordpress_database.wp_term_taxonomy;

Lines 1/3: These commands deletes the content from the wp_term_taxonomy and wp_terms table in the NEW database.

Lines 5/7: These commands copy the terms and taxonomies(category, custom post categories, ect) from the Old database into the New Database.

Again, make a backup and this is not advised except from a brand new wordpress installation. Also, if your old_wordpress_database has a number of custom post types, it may add extra content to your new database.