Import a database via WP-CLI

There may be certain occasions that require you to manually upload your site via WordPress Command Line (a.k.a. WP-CLI). For example, if you are uploading a site larger than 50MB, restoring a site manually, or migrating an existing site to our platform.

WP-CLI is available via SSH for all sites hosted on our Flywheel Cloud Platform.

Import a database via WP-CLI

1. To start, it’s a good practice to always create a backup before touching anything database-related. You never know when you may need to restore something.
2. Next, upload the database (.sql file) you’re wanting to import to the site’s root directory via SFTP. Follow this help doc for information on SFTP.
3. Connect to the site via SSH.
   ssh ssh.getflywheel.com
   Then select a site by number to connect to it.
4. Before we import the new database, we’ll want to ensure we are working from a blank slate to avoid any data duplication. Run the following command to reset the database.
   wp db reset --yes
5. Import the new database (replace database.sql in the command below with the name of the file you imported in step 2)
   wp db import database.sql
6. For security reasons, once the database has been imported, it’s always a good idea to remove the database file from the site’s root directory. We can do this via CLI with following command (again replace database.sql with the name of your database file from steps 2 & 5)
   rm database.sql

Update the database prefix

It may be necessary to update the database prefix. If so, your site’s prefix can be configured from the Advanced tab of the site’s Flywheel dashboard. You’ll find the option to Manage Database on the Database card.

1. To confirm your database’s actual prefix, click Manage Database from your site’s Advanced tab on the Flywheel Dashboard. 
2. Enter “users” into the search bar. This will bring up the users table, which is a trustworthy indicator of the database’s prefix.
3. The database prefix is an identical string of characters at the beginning of every single table name in your database. The default is wp_ but frequently varies. Prefixes almost always end with an underscore. In this example, the database prefix is wp_.
4. If the prefix value on your site’s Database card differs, you can update it.
5. 3 dots will appear when you hover next to the database prefix on the Database card of the Advanced tab. If you continue to hover a drop-down with the option to Change the prefix will appear.
   
6. Enter the database’s correct prefix and click Change Prefix to save.

Search & Replace

Depending on how the original site was built and how it stores data in the database, this step may not be necessary. Often, the domain which was being used to build the original site imported will remain in the database. You can easily adjust the URLs within the database via CLI. To update any URLs via WP-CLI, you can run the commands listed below.

1.  As always, before updating anything in the database, it’s always best to take a backup in case you need to revert your changes.
2. First, it’s best to do a dry run. This shows how many changes are to be made without actually making them. This gives you a chance to make any adjustments to the command before actually running it if something’s not quite right like the number of URLs or tables impacted. wp search-replace '//domainyouwishtoreplace.com' '//domainyouwanttoreplacewith.com' --all-tables --skip-columns=guid --dry-run

   Note

   The  --skip-columns=guid  flag excludes the GUID column. That column contains the unique value that things like RSS readers use for each post, to mark them as read/unread, and things like that, so you don’t want to update it – even when the site’s domain changes.
   
3. To run the search and replace for real, you can remove the --dry-run tag.wp search-replace '//domainyouwishtoreplace.com' '//domainyouwanttoreplacewith.com' --all-tables --skip-columns=guid

   
   

   Note

   To avoid updating any email addresses within the database, it’s super important that the URLs you input in the command begin with a slash or a protocol (http://, https://, // or just /)

If your site is using a theme or page builder, after these steps, you may also need to clear the theme or page builder’s cache in order to see the adjustments on the front end of the site. Here’s a handy help doc on how to do that.

If you don’t feel comfortable with this, you can also easily run a search-and-replace by using a plugin like Better Search Replace.

Need help?

If you have any questions our Happiness Engineers are here to help!