I often see people asking how to move a WordPress database between servers, and it usually seems like they’re doing it the hard way. I move sites between domains, from PROD to dev, dev to UAT, and so on, and this is how I’ve been doing it for years, 100% fool-proof1,2,3,4.
Prerequisites
First, you need WP CLI installed, and second, you’ll need CLI access to the server. If you don’t have either, don’t bother with the rest of this. Also, if you have questions about those things, you’ll need to find a tutorial.
Second, if you think you can skip parts of this and just use phpMyAdmin, you are on your own, too.
Third, I’m going to use the terms “source server” and “destination server”. Hopefully obvious, but just to be explicit, if you want to get a copy of your live site to your local machine for development purposes, the source server is your live site, and the destination server is your local machine.
Fourth, I’m going to assume you have a hosting setup on the destination server. For local development I use Symfony’s binary but you can use whatever, XAMPP, WAMP, Docker, native Nginx/Apache. If you were moving between PROD and STAGE, I’m assuming you’ve got that already. Related to this, I’m assuming you’ve got a domain name for this environment, it will be used in this process.
Steps
Source server
- On the source server,
cdinto the site root and runwp db dump. This will give you a SQL file. - Get that file to your destination server somehow. FTP, SFTP, SCP, RSYNC, RFC 1149 (make sure to implement RFC 2549, too). Place the file in the site root.
- Delete the file from your source server. This one’s for Bob.
Destination server
- On the destination server,
cdinto the site root and runwp db import file-2023-05-26-df8b15c.sql, replacing the file name appropriately. - Run
wp option get siteurl. This will give you the official site URL from WordPress’s perspective. Don’t get cocky and assume you know it. You can very easily screw up the protocol, the WWW, or something else. Only trust the result of this command. - Run the following command. The first URL is the result of the previous command. The second URL is the site’s new URL.
wp search-replace"https://production.example.com" "https://example.localhost" --recurse-objects --all-tables --dry-run- DO NOT include a trailing slash in either
- READ THAT PREVIOUS NOTE AGAIN
- Make sure to include the protocols in both steps
- Inspect the output of the previous command, it should tell you how many replacement would have been made. If it is zero, you are doing something wrong, stop. If it looks correct, re-run the command but remove the
--dry-runparameter to update the database. - Run
wp cache flush
Browser
DO NOT visit the site of your destination server in your normal browser. Instead, make sure to use an incognito/private browsing window. If for whatever reason there was a problem (see additional cleanup and notes), it is possible that you will be redirected to your source server’s site, and your browser will remember this redirection. Always use a private browser first to test this. It will drive you nuts otherwise.
If you did not heed my advice, and there was a problem, and you were able to correct it, try manually going to your destination server with an extra query string in the URL, such as https://example.localhost/?a=b. The contents of the query string don’t really manner, it is just getting around the browser’s redirect cache. If that works, you are good. Links back to / will still redirect for you, however, until your browser decides to forget that. Everyone else should be fine, this is just your local browser’s redirect cache.
Additional cleanup
Let’s say your site’s canonical address is https://www.example.com. That is what you should use as the first parameter to the search-replace command. However, there’s also a very good chance that someone put content into the CMS with the non-secure protocol, so you might want to re-run the command with http://www.example.com. There’s also a good chance that someone put content in without the www, so probably run it with https://example.com, too. And… there’s also a small chance that someone used the non-secure protocol with the www, so run it with http://example.com. Sigh. When you perform these additional replacements, DO NOT change the destination URL. This step is only to fix the links in the system by canonicalizing them. This is what the full process looks like:
wp search-replace "https://www.example.com" "https://example.localhost" --recurse-objects --all-tables --dry-run
wp search-replace "http://www.example.com" "https://example.localhost" --recurse-objects --all-tables --dry-run
wp search-replace "http://www.example.com" "https://example.localhost" --recurse-objects --all-tables --dry-run
wp search-replace "http://example.com" "https://example.localhost" --recurse-objects --all-tables --dry-runRemember, all of my samples include the --dry-run parameter because there’s a lot of “copy and pasters” out there that don’t read, so make sure to remove those after correcting things.
The above process can also be run on your production system in order to just correct those incorrect URLs, too.
Notes
- Okay, maybe not 100% fool proof. Check your wp-config.php file for constants such as
WP_SITEURLorWP_HOMEorFORCE_SSL_*. Older tutorials used to use these and I’d really recommend removing them, but that’s a little more complicated. - Also, these steps work with multisite, but extra care is needed to make sure you are addressing the appropriates tables and sites.
- If you were to use this process because company B bought company A, and you wanted to rename the site, this would not fix email addresses, although a similar one could be used.
- This process doesn’t change anything hard-coded into files. If you find just cases, fix the files, they shouldn’t be aware of their own domain usually.