Database Imports

February 16, 2024

Overview

PlanetScale provides an import tool in the dashboard that allows you to painlessly import an existing internet-accessible MySQL or MariaDB database into a PlanetScale database with no downtime.

Before you begin, it may be helpful to check out our general MySQL compatibility guide.

Connect to your external database

To import an existing database into PlanetScale:

1. Head to your PlanetScale dashboard and click on "New database" > "Import database," which will bring you to the Import Setup page.

2. Give your imported database a name and select a region from the dropdown.

3. We recommend using the same name as the database you're importing from to avoid updating any database name references throughout your application code. If you'd prefer to use a different database name, make sure to update your app where applicable once you fully switch over to PlanetScale.

4. Fill in the following connection values with information from your existing hosted database:

   • Host name — The address where the database is hosted.
   • Port — The port where your database is hosted. The default MySQL port is 3306.
   • Database name — The exact database name you want to import.
   • SSL verification mode — If your database server provides a valid SSL certificate, please set this to Required, otherwise select Disabled.
   • Username — The user's username used to connect to the database. This user must have read and write access.

   Note

   You must have binary logs enabled on the database you're importing.

5. You'll have the option to Authenticate with password or Authenticate with mTLS. To authenticate with a password, type in the password for the username you entered. Make sure the user has read and write access to this database.

   For the "Authenticate with mTLS option," you'll need to provide the following:

   • SSL client certificate — Certificate to authenticate PlanetScale with your database server.
   • SSL client key — This is the private key for the client certificate

6. From here, you can proceed to testing the connection or click "Show advanced settings" for more options.

7. (Optional) Under Advanced settings, you have the option to enter the following:

   • SSL server name override
   • SSL CA certificate chain — If your database server provides a certificate with a non-trusted root CA, please provide the full CA certificate chain here.

   For more information about certificates from a Certificate Authority, check out our Secure connections documentation.

Test the connection

1. Once your connection information is plugged in, click the "Connect to database" button. PlanetScale will attempt to connect to your database. If successful, you'll see a green checkmark with a success message.

2. If the connection is successful, click “Begin database import” to start migrating your data to PlanetScale.

Or, if the connection fails, you'll get an error message in the dashboard. Double-check your connection information or see our Troubleshooting section for more information.

Foreign key constraints

If your database uses foreign key constraints, we will detect them after successfully connecting to your external database and automatically enable foreign key constraint support.

There are a couple of important things to know about doing database imports with foreign key constraints:

• We recommend importing using a replica: The foreign key constraint-specific import process holds a long-run transaction on the source database. This might cause an increased load on the source database. As a result, we recommend that users connect to a replica when importing their database.
• Import retries: If your import fails, the import must start from the beginning again. This is unlike other imports, where we retry from where we left off.

For most cases, foreign key constraints should work as expected in PlanetScale. There are a few cases to be aware of as they are currently unsupported or result in less ideal behavior. See the foreign key constraints limitation documentation for more info.

Import process

There are three simple steps to the database import process. You can cancel the import anytime by clicking "Cancel import" in the top right corner. If you cancel, we'll delete all connection information.

Step 1: Copying schema and data

During this phase, the data and schema from your external database are imported into your PlanetScale database. The PlanetScale database is in read-only mode.

While in read-only mode, you're already able to take advantage of PlanetScale features such as:

1. You can generate a password to connect to your new database in a MySQL client or in your application locally by clicking the "Connect" dropdown underneath the import flow.
2. You can also view Query Insights, branches, and database settings by clicking around in the top nav.

Once the initial import has finished, your PlanetScale database will be in Replica mode.

Step 2: Replica mode

In this mode, your external database is the primary, but PlanetScale will directly read and write to the appropriate database. Connect your live application to the PlanetScale database and ensure it is fully compatible with your app.

Queries sent to PlanetScale will be served directly from the PlanetScale database. However, writes will be routed back to your external database, and their results will be replicated back to PlanetScale to keep both databases in sync. Behind the scenes, we're using Vitess's powerful Schema Routing rules to allow your PlanetScale database to act as a "data router."

We'll continue watching your external database and automatically update your PlanetScale database with any changes using Binary Log File Replication. In other words, any writes made directly to your external database will also appear in your PlanetScale database. This ensures you won't experience any data loss while updating your application's connection string to point at PlanetScale. For example, if a user makes a new comment on your blog application during this stage of the import, the comment will be written to both databases, no matter which database the application was pointed to. This ensures you won't experience any data loss while going through the import process or if you decide to cancel the import into PlanetScale.

Once you're ready for PlanetScale to become your primary database, click "Enable primary mode" to move to the next stage.

Step 3: Primary mode

During this stage, PlanetScale replaces your external database as the primary database. It will serve both reads and writes. Behind the scenes, we essentially reverse the direction of the routing in the previous step. All reads and writes will go straight to the PlanetScale database. Updates will be replicated back to your external database.

In the blog application example context, if a user makes a new comment, the comment will be written to the PlanetScale database first and then copied back to your external database. Why copy it back? If you decide to cancel the import and switch back to your external database, you can be confident that you didn't lose any new or changed data while going through the import process.

If you're happy with everything and ready to fully switch over, click the "Finish import" button to finalize the import.

When you finalize the import, we will detach your external database. The connection to that database will be closed, and all connection information will be deleted from PlanetScale. This will also cut off replication, so your PlanetScale database will no longer update your external database.

Import complete

Your database has been fully imported into PlanetScale and is ready to use!

Next, you'll be taken to your database dashboard page. If you click on "Branches," you'll see that you now have one production branch, main, that contains all of the data from your external database. Production branches are highly available and have optional additional protections, such as safe migrations, to prevent downtime.

Next steps

You just fully migrated your database to PlanetScale with no downtime and no fear of data loss. So what's next? Here are some next steps you can take with your database:

• Connect to your application — If you haven't already, you can also connect to your application locally.
• Enable safe migrations — To protect your database from accidental schema changes an enable zero-downtime migrations, it is highly recommended to enable safe migrations on your new production branch.
• Create a development branch — Add PlanetScale to your development workflow with our powerful branching feature. You can branch off of your main branch to test schema changes in development and then merge them into production with our non-blocking schema change workflow. Again, no downtime!
• Create a deploy request — Once your branch is in production, you can safeguard against unwanted or accidental changes by creating a development branch off of your production branch. This is where you can test out schema changes or any modifications you need to make. Once it's ready, you can create a deploy request that your team can review before deploying to production.

Import limitations

PlanetScale will automatically stop the import process and detach your external database after 7 days of no activity once the initial import is finished. This will not impact your PlanetScale database created for the import, but it will stop all replication into and out of it.

Troubleshooting

The following section covers common issues and compatibility limitations you may encounter during the import process. You should also check out our general MySQL compatibility guide.

Connectivity issues

If you're running into issues connecting with a username and password, try to plug that same connection information into the MySQL CLI or a MySQL GUI. Below is the command for the MySQL CLI.

mysql -u <USERNAME> -p <PASSWORD> -h <HOST> -P <PORT> -D <DATABASE>

If the connection works there but not on PlanetScale, please reach out for additional assistance.

Host can't target a PlanetScale database

The importer can not be used to duplicate a database already hosted on PlanetScale. If you need to copy or move a database between PlanetScale organizations, we recommend dumping the origin database and restoring that dump to a new database with the same name in the new organization. You can use the pscale database dump and restore-dump commands of our CLI application pscale to create and restore those dumps.

Compatibility issues

PlanetScale is built on Vitess, which gives us the power to perform data migrations at scale. This does, however, come with some trade-offs that could cause you to run into errors while importing an existing database. We believe these small trade-offs are worth the massive benefits, such as unlimited scaling through horizontal sharding, non-blocking schema changes, branching, and more. So, if you encounter compatibility issues while importing your external database to PlanetScale, we'd like to help you troubleshoot.

Below are a few common errors you may encounter while importing or connecting to an external database. If you're still having trouble importing, please feel free to reach out to support for additional assistance.

No unique key

PlanetScale requires that all tables have a unique, not-null key. When you modify a table, both the old and the new schema must have a unique key as described, and the columns covered by those keys must exist in both the old and the new schema. If you run into this error, read through our Changing unique keys documentation for more information.

It may also help to check out the official MySQL documentation about Primary Keys.

Invalid charset

PlanetScale supports the following charsets: utf8, utf8mb4, utf8mb3, latin1, and ascii. If your table uses any other charset, please consult the official MySQL documentation about charsets.

Table names with special characters

Tables named with characters outside of the standard ASCII set are not supported. These tables may cause the import process to fail during the "Copying schema and data" phase of the import, resulting in a generic message stating that something went wrong.

If any of your table names include special characters, they must be renamed before the import succeeds.

Views

Views will not be imported or re-created on import. These views though can be created after a successful import completes.

Unsupported feature

You might see this error if your table requires a storage engine other than InnoDB. Please consult the official MySQL documentation about alternative storage engines for more information.

Server configuration issues

To ensure that we can migrate your data to PlanetScale with zero downtime, we check your database for some required configuration values, as described below:

* PlanetScale requires that either expire_logs_days or binlog_expire_logs_seconds is set to a valid value. If both of these values are set, the value of binlog_expire_logs_seconds takes precedence over expire_logs_days.

Import user account issues

Please follow the Import tool user requirements guide to ensure that the database user performing the import has the correct set of grants and permissions on the external database.

MySQL error number 1045: Access denied

If you get MySQL error number 1045; Symbol: ER_ACCESS_DENIED_ERROR; SQLSTATE: 28000. For example, with a message like: Message: Access denied for user '%s'@'%s' (using password: %s). You should check your MySQL grants and permissions.

When importing a database with foreign key constraints, we try with FLUSH TABLES WITH READ LOCK. If we get a permission error, we resort to an explicit LOCK TABLES ... statement. You will get the above error if both permissions are unavailable.

Ideally, the username will have FLUSH_TABLES or RELOAD privileges, but if not possible, then LOCK TABLES at minimum.

Check these values for your database server

To check what your database server currently has these values set to, run the following query in a MySQL console connected to your database server:

SHOW VARIABLES
   WHERE Variable_Name LIKE '%gtid_mode'
      OR Variable_Name LIKE '%binlog_format'
      OR Variable_Name LIKE '%binlog_row_image'
      OR Variable_Name LIKE '%expire_logs_days'
      OR Variable_Name LIKE '%binlog_expire_logs_seconds';

Cross-check these values with the table above and update if needed.

Other issues

If you see an error when querying your PlanetScale database after the initial import that looks like this:

ERROR 1105 (HY000): keyspace importkeyspace fetch error: node doesn't exist: [...]

This means the import process is underway, and this error should clear in 10-15 minutes after some additional processing by PlanetScale. If it does not, contact our PlanetScale support team, and we will help troubleshoot the issue.

Need help?

Get help from the PlanetScale Support team, or join our GitHub discussion board to see how others are using PlanetScale.