Migrating WordPress Databases To More Recent Versions

I’m going to interrupt my previous project with a note on how to migrate WordPress databases to more recent versions. It turns out that all of my sites were using version 5.1 of MySQL, support for which seems to have been discontinued right around the end of 2014. My current host creates new databases in version 5.5 so I thought it prudent to update. This discussion assumes that you are supporting your own copies of WordPress on an internet host and not simply using a subdomain at WordPress.com.

After looking through the WordPress admin UI I didn’t see an obvious upgrade route like those that existed for some migrations of phpBB versions (never mind that those could be problematic, particularly if your database was corrupted). Some Googling indicated that you have to move the database by hand, which means exporting it from one database and importing it to another. When that is done you have to update the wp-config.php file in the WordPress root directory on your host.

I was comfortable enough with phpMyAdmin but there is a minor hitch you need to know about to make the export->import process work correctly. You can use the quick export function to generate an SQL file that you save to your local drive. (It goes without saying that you want to be sure you are careful to select the correct database in phpMyAdmin for each export and import operation.) If you try to import that file into a new database, however, it will balk because the imported file does not contain a command that allows the SQL to work with the target database. Mine threw “Error 1046 No database Selected.” I found the guidance for what to do here. It took a little bit of doing but I found that editing the SQL file and adding a command near the beginning in the form:

…gave the importing database enough information to process the file correctly. You do not have to include the create database command discussed at the link, because of course you’ve already created the new database on your host. I didn’t experiment with the form of the apostrophes (my example uses backticks or grave, apparently pronounced “grah-vuh” marks). I moved on when I got it working but there are a lot of discussions on this topic.

The wp-config.php file refers to the database name, user, and host, all of which will presumably be readily identifiable and will share some common text. I made it a point to duplicate each line and comment the original out so I could backtrack in case the process didn’t work. I then edited the uncommented copy of each line, saved the file, and copied it back to the host.

If you navigate to the site in your browser and see your latest post(s), can log in, and can otherwise manipulate and edit things then congratulations, your migration was successful. If not then either you’ve made a mistake somewhere in the process or there is some other requirement of hiccup I did not encounter.

As I mentioned above there are situations that may be more complex. If WordPress (or similar packages like phpBB) change their database schema then the upgrade or migration process will necessarily be different. In those cases there will be (almost certainly be) a ready-made upgrade procedure that includes the use of ready-made scripts that will handle changing the schema and moving the data as needed. That said, it may change the schema within an existing database version and you still may need to move the updated information to a new database version.

Over the years I’ve encountered discussions of other differences between database versions that had to be accounted for (e.g., here and here), and those would have to be dealt with as you find them.

Here is a description of the process in its simplest form:

1. Back everything up. Document whatever you did.
2. Log into the phpMyAdmin facility on you internet host.
3. Create a new MySQL database.
4. I use the same text description and password I used for the original database. You could presumably change these things if you’d like, but especially with the database password I can’t guarantee that a change will work. I didn’t test it.
5. Record the name, username, host, and password information for the newly created database.
6. Select the old database in the phpMyAdmin tool.
7. Perform a quick export of the database and save it someplace where you can edit it (like your local drive). I saved mine as uncompressed SQL files since they may be edited as simple text files.
8. Add a command to use the new database by name near the top of the file (before any other important operations are performed). I placed mine just under a commented line that contained the name of the old database. The command to add takes the following form:
• USE my_database_name ;
9. Select the newly created database in the phpMyAdmin tool.
10. If you have not already done so, ensure that your host has finished creating the new database.
11. Perform an import from the SQL file you saved and edited.
12. Locate the wp-config.php file in the root directory of your WordPress installation on your server.
13. Copy the wp-config.php file from your server to a place where it can be edited.
14. Locate the commands for defining the following three variables:
• define(‘DB_NAME’, ‘my_database_name’);
• define(‘DB_USER’, ‘my_database_username’);
• define(‘DB_HOST’, ‘my_database_hostname’);
15. Duplicate each of the lines and comment out the first of each (use /* and */).
16. Change the uncommented form of each assignment to use the appropriate name for the new database.
17. Modify the ‘DB_PASSWORD’ value using the same method, if necessary.
18. Save the file and copy it back to its original location on the host.
19. Navigate to your website and see if the WordPress page works correctly.
20. If everything looks like it’s working, try editing an existing post or adding a new post.
21. Look at the listing of databases on your phpMyAdmin page. If you have successfully transitioned to using the newly created database, the interface should now show that it is storing more data than the older database did.