Working with clients with WordPress sites can be difficult when you have to migrate their site locally so you can develop on it. If you’re using Local by Flywheel the site is hosted on Flywheel, you will have no problem. If their site is not a big one, you’ll also have no problem using some of the existing solutions such as WP Migrate DB Pro and alike. But what if your site has large databases (read tens of GB), what to do then?
In this tutorial, I’ll show you how to migrate such large databases in Local by Flywheel through SSH.
If you don’t have Local By Flywheel, please go download it and install it so you could follow this guide. Also, create a site using it. I mostly use the custom environment. This tutorial will also assume that you have commands such as
gunzip installed on your environment. If not, you can still follow most of it (ignore the downloading part).
1. Get the Database
This is an obvious step, right? You will need to get the database. When we’re working with such large databases, you will probably get them gzipped. If not, your database will be either in
.sql or in
Some hosting providers will have an option to export the database and you’ll probably get an SQL file. Then you’ll just download this file.
If you have an SSH connection to that site and you can access MySQL on that site, you can get everything by using SSH because it will be much faster.
Exporting SQL from MySQL
mysqldump -uUSERNAME -p DATABASE > backup.sql
Here you need to define your username and the name of your database. This will also ask you the password for the database. If you don’t know the username and the password, you can read them in
This will place the SQL file in the folder from where you’ve run the command.
Exporting SQL using WP CLI
Maybe your hosting provider installed WP CLI on your site. You can check that out just by running the command
wp in the command line. If that’s correct, then you can export the SQL even easier.
wp db export backup.sql
Is your SQL too big? We can make it much smaller. But be sure that you have those tools also installed on your local machine. If not, Local by Flywheel should have those commands available when you connect to your local site through SSH.
Try typing these commands to find out if they’re defined:
If both commands tell you something about how to use it, then you have them installed. Let’s get back to our ssh connected site. If we are in the folder where the SQL is located, we can run this:
tar -cvzf backup.sql backup.tar.gz
Download the File with SCP
If you have SSH, you’ll probably have the SCP command also there. If not, find a way to install it or download the optimized file from your host. Now, if you’ve created a site, go to it’s own
app folder. On my computer, a site would be installed at
/Users/yourname/Local Sites/mysite. We can place ourselves in the
app folder (the next command will work on a MacOS).
cd /Users/yourname/Local Sites/mysite/app
Now we can download the file with SCP.
scp firstname.lastname@example.org:/location/to/backup.tar.gz .
With the last dot
. we have defined our current directory as the download target.
Unpacking the minimized SQL
To unpack it, we need to unzip it (gunzip it) and also to unpack it from
.tar. If you don’t have such commands locally installed, then open your site with SSH in Local by Flywheel.
We should now get the regular
backup.tar in our
tar -xvf backup.tar .
This should get us our
backup.sql right there in our
2. Importing SQL
This is the most important part. The problem with big databases can sometimes be that there are some errors in the SQL. Sometimes and Index is missing or similar. When you try to import the SQL through WP CLI, it will stop its execution when an error has been triggered.
We can import it with the native mysql command by ignoring errors. Let’s say that our SQL is now in the
app folder. We are now inside of our site through SSH and we have access to MySQL there.
mysql -u root -proot -f local < /app/backup.sql
This will now import the SQL and with the option
-f we are forcing the import to continue even if an error has been triggered.
Be aware that with big databases, you’ll probably have to wait for a long time. Power your computer (if it’s a laptop) and remove any
sleep option. A 50GB database could take about 7-8 hours to import completely.
3. Change URL
Open the site in the Local by Fylwheel database and check out the URL you’ve got from import. This can be found in the
*_options table under
siteurl. Again, with the help of SSH, connect to the Local by Flywheel. We will use WP CLI to replace the URL.
wp search-replace 'http://yoursite.com' 'http://yourlocalsite.local'
If it’s a
https site, then change it like that.
That is about it. To summarize what you need to do:
- Get the database (SQL) into your Local by Flywheel site (in
- Import the database through
- Replace the URLs with WP CLI
Local by Flywheel is a really great tool that enables you to start a local development environment in just a few minutes. And you can easily customize the environment on the go.Become a Sponsor
This doesn’t work anymore. I keep getting “MYSQL- login Warning: Using a password on the command line interface can be insecure”
Hi Stef, sorry for replying just now. That’s a normal output when using the import. I get it as well. You just have to leave it there and let it process the whole import.
WP cli command not working for multisite :/ i got info about site “my site name” not found
I get this error when try to “2. Importing SQL” step. Couldn’t find a solution can you please help..
ERROR 1153 (08S01) at line 2670: Got a packet bigger than ‘max_allowed_packet’ bytes
Hi Manoj, that’s something different. Seems like your DB size is much larger than Flywheel MySQL is configured for. I was able to imported databases of 10GB and more.