SQL database files can be imported to a VIP Platform WordPress environment with VIP-CLI. Customers can import a full database file to migrate a site to the VIP Platform, or import a partial database file to update specific tables.
A separate process can be followed for importing databases to a VIP Local Development Environment.
Professional Service Upgrade
Customers who add WordPress VIP Migration Services to their support package receive expert assistance with importing their database, media files, and codebase to the VIP Platform. Migration Services also includes WordPress VIP Launch Day Services, providing customers with comprehensive support for a smooth site migration and successful launch on the VIP Platform.
- For unlaunched environments the database must be smaller than 100GB in size.
- For launched environments the database must be smaller than 5GB in size.
- The allowed characters that can be used in the name of the file for import are limited to:
- Non-production environments will go into maintenance mode during a SQL import. This will block users from making database updates (e.g., creating or editing posts, changing site settings) for the duration of the import process.
Export a SQL file from a non-VIP WordPress install
In order to migrate existing database content to VIP, an SQL file must be exported from a current WordPress site. Recommended methods for exporting a WordPress SQL database:
- Install a third-party plugin designed to export WordPress database content.
- Run a
mysqldumpcommand in a command line interface. For example:
mysqldump --add-drop-table --hex-blob --no-create-db --quote-names -h dbhost -u user -p dbname --default-character-set=utf8mb4 --result-file=dbname-full.sql
Exports from phpMyAdmin are highly configurable and some settings may include SQL statements that are not compatible with VIP’s import tooling (e.g.,
CREATE DATABASE IF NOT EXISTS,
ALTER USER, or
SET PASSWORD). To ensure compatibility, validate a SQL database file export from phpMyAdmin with VIP-CLI before importing it into a WordPress VIP application.
Database imports for multisite environments
Database structure for a WordPress multisite differs from the database structure of WordPress single site. In order to import a database to a VIP WordPress multisite network, the database must be exported from a WordPress multisite.
If the origin site is a WordPress single site, the database will need to be converted to a multisite database prior to import. A working version of a VIP application on a VIP Local Development Environment set up as multisite is a helpful tool for migrating WordPress single sites into a multisite.
- The database must have been exported from a WordPress multisite.
- If importing the database for an entire multisite network, ensure that the export has all tables listed in network-level tables shared by all sites.
- If importing tables for a specific site, ensure the table prefixes match the intended destination site ID (e.g., tables prefixed
wp_3_will be imported to site ID 3). Table prefix values will be visible in the confirmation output of the import command.
- If importing a selection of tables for one or more sites, the database export should include only the tables for those sites.
- If the SQL file being imported includes tables for the main site (ID 1), and the domain value in the file for site ID 1 does not match a domain that has been mapped to the environment for import, the import will fail. To prevent this issue, use
--search-replaceeither before or during the import to update the domain value for site ID 1 with a domain that is mapped to that environment.
Import database files with VIP-CLI
Before importing the database file to a VIP environment, it is recommended to perform a test import locally to a VIP Local Development Environment to ensure that the prepared database provides expected import results.
Importing a SQL database to a VIP site will replace data on the site for any SQL tables included in the import file. Pre-existing data that is overwritten by an imported table will be gone.
1. Identify the absolute local path of the SQL file
Example local paths for a SQL file named file.sql for a current user named “example-user”:
2. Validate the SQL file
VIP-CLI command examples
For demonstration purposes, the
mytestsite and the
production are used in the VIP-CLI command examples below. Read more about how to target environments in VIP-CLI commands.
Verify that the SQL database file is correctly formatted for import with the
vip import validate-sql VIP-CLI command. This example command is using the absolute local path from the macOS example in the previous step:
vip @mytestsite.production import validate-sql /Users/example-user/file.sql
Any errors reported by the
validate-sql command must be resolved before importing the SQL file to the VIP Platform environment.
3. Import the SQL file
Import a SQL database to a VIP Platform environment with the
vip import sql VIP-CLI command.
In this example command, a SQL database file named file.sql is imported to the production environment of the “mytestsite” application:
vip @mytestsite.production import sql /Users/example-user/file.sql
--search-replace to update domain name values
If the database for import contains domain name values that do not match the VIP Platform destination site’s domain name, a
--search-replace operation must be done before or during import.
- The domain name value for an unlaunched VIP Platform environment is typically a convenience domain (e.g.,
- Search and replace values are separated by a comma only. There are no spaces between the values.
In this command example, the domain
http://localhost in a database file that was exported from a local machine is replaced during import with the convenience domain
https://example.go-vip.net of the unlaunched destination VIP site:
vip @mytestsite.production import sql /Users/example-user/file.sql --search-replace="http://localhost,https://example.go-vip.net"
--search-replace to update multiple domain name values
Imports to a WordPress multisite should include a
--search-replace operation for each network site included in the SQL database file. In this example command, a
--search-replace operation is included to replace the domains of each network site in the database file (
local-site.com/site-three) to those of the destination site(
vip @mytestsite.production import sql /Users/example-user/file.sql --search-replace="local-site.com,example.go-vip.net" --search-replace="site-two.local-site.com,example.go-vip.net/site-two" --search-replace="local-site.com/site-three,example.go-vip.net/site-three"
- In some cases, the order that
search-replacecommands are run will make a difference. If
--search-replace="local-site.com,example-site.com"is run before
--search-replace="site-two.local-site.com,site-two.com", the result will be a site with the domain
example-site.com, a site with the domain
site-two.example-site.com, and no site with the domain
- It may be necessary to to run additional
search-replaceoperations using using WP-CLI commands after the import is complete. To accurately run a
search-replaceoperation, the command structure will depend on whether the
search-replaceis targeting an entire single site, multisite, or specific tables.
Check the status of an import
If an import is currently running, the output from the
vip import sql status command will be for the currently running import. If an import is not currently running, the output will be for the import that ran most recently. The Audit Log panel, located in the Organization view of the VIP Dashboard, can provide insight into when an import was requested.
Output from the
status command lists the series of steps executed during an import, and displays a progress indicator for each step:
An overall status report on the import process appears below the listed import steps. The
Status: field will display either
$ vip @mytestsite.production import sql status ============================================================= Checking the SQL import status for your environment... ✓ Performing Search and Replace ✓ Uploading file ✓ Queueing Import ✓ Import preflights ✓ Downloading file from s3 ✓ Uncompressing gzip ✓ Checking md5 hashes ✓ Backup db ✓ Importing db ✓ Validating db ✓ Update wp blogs table ✓ Update primary domain ============================================================= Status: Success ✓ imported data should be visible on your site mytestsite.go-vip.net. Site: mytestsite (PRODUCTION) SQL Import Started: 07/01/2022, 1:14:54 pm (2022-01-07T02:14:54.000Z) SQL Import Completed: 07/01/2022, 1:19:31 pm (2022-01-07T02:19:31.000Z) =============================================================
|Import step||Operation performed|
|The search and replace operation(s) running on the file if a search and replace was specified in the import command.|
|File compression and uploading to S3.|
|Queueing the import job in the system.|
|Preparing the import job is in the system.|
|Downloading the file from S3.|
|Uncompressing the file.|
|Validating the integrity of the file and its data.|
|Creating a backup of the current state of the database.|
|Importing the SQL file to the site.|
|Validating the post-import state of the database with the imported data.|
|Updating the wp_blogs table: For a WordPress multisite environment, this is the database table that identifies a site in its network.|
|Updating the primary domain: For a WordPress multisite environment, if the process determines that the wp_site table contains a different domain at the first row than the primary domain that is currently set in VIP.|
**If an import fails at the
Importing db step:
- An unlaunched environment’s database automatically rolls back to the backup taken prior to the import job.
- A launched environment will be locked against any additional import attempts and needs to be reset by VIP Support in order for a new import to be attempted.
Failures can occur due to an invalid SQL file, or if there is an error present in the file. If the import fails, the
Restore db step is returned in the status output.
- It is possible for the import process to complete successfully, but for other conflicting errors to prevent the site from loading properly (e.g., PHP errors in the theme). If the results of an import are not as expected after the process has completed, the process can be re-tried.
- Once a database import into a VIP environment has completed, site testing is recommended to ensure that the results match the expectations of the import.
The following options are protected during SQL imports, meaning they are backed up beforehand and restored afterward. Protected options are intended to prevent overwriting or accidentally sharing environment-specific configs. It is not possible to extend or modify this list.
"WP Options" = [ 'jetpack_options', 'jetpack_private_options', 'vaultpress', 'vip_jetpack_connection_pilot_heartbeat', 'wordpress_api_key', 'vip_search_index_versions', 'vip_search_global_index_versions', ]; // On multisites "Site Meta" = [ 'vip_search_global_index_versions', ];
Custom cleanup operations
The vip_sqlimport_cleanup hook can be used for custom cleanup or changes of application data after an import. Some examples of cleanup operations include switching out production API keys, or performing brief data manipulation. It is not ideal to do any longer-running operations here as it keeps the site under maintenance mode until it import is completed. Instead, consider registering a one-time cron event on this hook to do something immediately afterwards via cron.
Last updated: January 24, 2023