There are many reasons for moving a WordPress database to a new server. The most common is when you want to move an entire website. For example, you might have developed the website on one server - or locally on your computer - and now want to push it live. Or you might simply want to change your website host and need to move your files and database to the new server.
Whatever your reasons, the thought of moving is often daunting. How do you do it without losing all the work you have done? And if you are taking down an old site to replace it with your new one, how do you minimize downtime?
You have to deal with your website files as well as your database, but that is the easy part. That said, migrating a database is not difficult, so long as you follow some important steps.
Backup the Database
The first step is to make a backup of your database, and then make a copy of that backup. You can work on the copy while keeping the backup to help you recover if anything goes wrong.
To back up your database, start by logging into phpMyAdmin. This is usually found in your site's cPanel. Select the database that you want to migrate and click Export. The Quick option is fine for most users, so leave that checked and click Go.
Now you need to create a new database. This can be done in cPanel or Plesk. You will also need to create a new user for this database with a username and password. Make sure the new user has full access to the database.
You now need to give your Wordpress installation the details of the new database. It holds this information in a file called wp-config.php. Save a copy of this file so you can revert to its original state if you need to.
Open the wp-config.php and search for "db_name" in its contents. Change the value to match the name of your new database. Do the same for "db-user" and "db_password".
Now save wp-config.php and close it.
The next step is to import the database you exported to your computer earlier to the new database on your server. You will have to go back to phpMyAdmin to do this. Click on Import and select the database file.
Edit the Database
This step only applies if you are changing your domain name. If you are moving to a new server but keeping your existing domain name, you can skip this step.
If you have to do it, it is the most complicated part of the process. Essentially, you have to replace all instances of your old URL address in your database with your new address. The quick way to do this is by using search and replace, but this can cause problems and break functions and links on your website. This is because your WordPress website probably uses serialized data. An example of this is where theme functions, plugins and widgets use values determined by the length of your URL. So a simple search and replace will not fix everything.
The best way to deal with this is to upload and run a script that keeps serialized PHP strings during a search and replace. The Search Replace DB script is one of the most popular.
Once you have run this script save your database. You might need to log into WordPress at this stage and save your permalink structure again.
That's it - you have successfully moved your WordPress database to a new server.