Skip to content

Database Imports

Import your data from an external MySQL database into your PlanetScale database.

Overview

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

This feature is currently in Limited Beta.

Note: You must be an Organization Administrator to use this feature.

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 having to update any database name references throughout your application code. If you'd prefer to use a different database name, just make sure to update your app where applicable once you fully switch over to PlanetScale.
Note

Importing a database will not count towards your read or write usage.

Select name and region for new database importSelect name and region for new database import

  1. 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.
  • SSL verification mode — If your database server provides a valid SSL certificate, please set this to Required, otherwise select Disabled.
  • Database name — The exact name of the database you want to import.
  • Username — The username of the user 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.

  1. You'll have the option to Authenticate with password or Authenticate with mTLS. To authenticate with 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
  1. From here you, can proceed to testing the connection or click "Show advanced settings" for more options.

  2. (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 you have all of your connection information 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. From here, you can click the "Begin database import" button to import your data.

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.

Importing large databases

If you're importing a large database (that uses over 5 GB of storage) and are on the Hobby plan, you will receive a prompt as shown below to upgrade your plan:

Upgrade your hobby plan if importing more than 5 GBUpgrade your hobby plan if importing more than 5 GB

You can use the Add new card button here to add a credit card and upgrade your account.

Once you've successfully upgraded your plan, you should be able to continue importing your external database into PlanetScale.

Account upgraded, continue to importing databaseAccount upgraded, continue to importing database

Import process

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

Note

Do not execute DDL (Data Definition Language) statements, CREATE, DROP, ALTER, etc., on either database during the import process. Schema changes are not replicated between databases in either direction.

Step 1: Performing initial import

Step 1 of database import - Initial importStep 1 of database import - Initial import

During step one, the data and schema from your external database are imported into your PlanetScale database. The connection remains open during this phase and in read-only mode.

While in read-only mode, you're already able to take advantage of PlanetScale features. 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.

At this point, you can also view Query Insights, branches, and database settings by clicking around in the top nav.

Once the import is finished, your database is in Replica mode.

Step 2: Replica mode

Step 2 of database import - Replica modeStep 2 of database import - Replica mode

While in Replica mode, we'll continue watching your external database and automatically update your PlanetScale database with any changes by utilizing Binary Log File Replication.

Queries will be served by PlanetScale and writes will be routed back to your external database, and then replicated back to PlanetScale to stay in sync.

Behind the scenes, we're using Vitess's powerful Schema Routing rules, which essentially lets your PlanetScale database act as a "data router".

At this point, you can connect your live application to the PlanetScale database and ensure that it is fully compatible with your app.

Note

If you gave your PlanetScale database a different name than the one you're importing from, make sure your application is referencing the correct database name.

In other words, any changes made to your data in your external database will also reflect in your PlanetScale database. For example, if a user makes a new comment on your blog application while you're importing, your PlanetScale database will be updated as well. This ensures you won't experience any data loss while going through the import process.

While in Replica mode, if any data changes are made to your PlanetScale database, those will not be replicated back to your external database. To replicate both ways, you need to turn on "Primary mode".

So, once you're ready for PlanetScale to become your primary database, click "Enable primary mode" to head to the next step.

Step 3: Primary mode

Step 3 of database import - Primary modeStep 3 of database import - Primary mode

During this step, PlanetScale becomes the primary database instead of your external database. It is now ready to accept both reads and writes. Behind the scenes, we essentially reverse the direction of the data routing from the previous step. This means that all reads and writes will go straight to your PlanetScale database and updates will be replicated back to your external database.

In the context of the blog application example, this means that if a user makes a new comment, the comment will be written straight to the PlanetScale database and then essentially copied back to your external database as well. Why copy it back? If you decide you want 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, you can click on the "Finish" to finalize the import.

Once you finish 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 the "data routing" ability, 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 overview 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 protected from direct schema changes. Your database is also on an automatic backup schedule whose frequency depends on your plan.

Next steps

You just fully migrated over 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.
  • 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 that was 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 that you may come across 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.

Copied
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.

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 do run into any compatibility issues while importing your external database to PlanetScale, we'd like to help you troubleshoot.

Below are a few common errors you may run into 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.

Foreign key constraints disallowed

PlanetScale does not support database schemas that have tables with foreign key constraints in them.

Please take a look at our help document on how your application can operate without foreign key constraints.

No unique key

PlanetScale requires that all tables have a unique, not-null key that remains unchanged during the migration. If you run into this error, you can read through our Change single unique key 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. If your table uses any other charset, please consult the official MySQL documentation about charsets.

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:

VariableRequired ValueDocumentation
gtid_modeONDocumentation
binlog_formatROWDocumentation
expire_logs_days*> 2Documentation
binlog_expire_logs_seconds*> 172800Documentation

* 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.

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:

Copied
SHOW VARIABLES
WHERE Variable_Name LIKE '%gtid_mode'
OR Variable_Name LIKE '%binlog_format'
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:

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

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

Additional assistance

If you need additional assistance, contact our the PlanetScale support team, or join our GitHub Discussion board.

Was this page useful?
Last updated on August 1, 2022
Help us improve this page
PrivacyTerms© 2022 PlanetScale Inc.