Importing MySQL Data From a Remote Server

Ready to upgrade to fully managed MySQL hosting? Easily migrate your MySQL deployments to ScaleGrid and we'll take those time-consuming management tasks off your plate so you can get back to product.

MySQL Import Prerequisites

Here are the things you'll need to import your MySQL data to ScaleGrid:

• Your remote server name, port, and authentication.
• Your current MySQL version at source should be 5.6.x or 5.7.x.
• If your existing MySQL server is protected by a firewall, you should be able to modify the firewall settings on the source to allow the nodes of your ScaleGrid MySQL deployment to connect to it.

In this tutorial, we're going to show you how to import into an existing, newly created MySQL deployment at ScaleGrid.

Importing Into A ScaleGrid MySQL Deployment

Before starting your import into your newly created MySQL deployment at ScaleGrid, verify that there is no data in this deployment so that any existing data isn't overwritten. If you'd like to override your existing data in the import, first create a backup to ensure your existing data is preserved.

1. Log into your ScaleGrid account, or start a free 7-day trial.
2. Go to your MySQL dashboard, and select the cluster you'd like to import to.
3. If your source MySQL server is protected by a firewall, you will need to add the IP address of the node which will connect to your existing server to the firewall whitelist. Perform the following additional steps:

4. Next, under the 'Overview' tab, click the 'Import Data' button.

5. Enter your ‘Server name’ and ‘Port’.

6. Enter the ‘Username’ and ‘Password’ of the user that will be used to connect to your MySQL user and execute import operation. This user needs to have the permissions on the selected database(s):
   -- SELECT
   -- LOCK TABLES
   -- SHOW VIEW
   -- TRIGGER
   -- EVENT

7. If your server has SSL enabled, and the import needs to be done using SSL connection - please check the ‘SSL Enabled’ checkbox.

8. Review the information you’ve entered is correct, and click 'Next'.

9. We will test connectivity to the remote server and do some pre-import validation once you click Import. If there are errors or warning during these pre-checks, you will need to take corrective actions based on the error or warning displayed. If pre-checks are successful, you will see an option to select the databases to import after which your import job can be initiated.

👍

Your MySQL import has now begun!

Your data will be migrated as soon as the import job is complete, and the time will vary based on the size of your data.

Limitations

• This is a one-time import operation and not a live-sync.

Handling sql_mode Incompatibilities Between Source and Destination MySQL Instances During Import

By default, your ScaleGrid MySQL deployment enables strict SQL mode settings. If your source cluster does not have strict sql_mode settings enabled, it is very likely that you will encounter failures during your import operations.

For example, without strict_mode, MySQL allows values like '0000-00-00' for a DATE data type. However, when we try to import data containing such values to a MySQL server with strict_mode, it would not accept it and result in the failure of import operations. ScaleGrid import operation detects such incompatibilities before hand and throws a warning message like below:

If you see such a warning, it is advised that you disable the incompatible sql_mode settings on your destination ScaleGrid deployment from the Admin -> Configuration Menu. In the above example, you will have to disable the following modes:

• NO_ZERO_IN_DATE
• NO_ZERO_DATE
• STRICT_ALL_TABLES.

Please see the help topic on MySQL Configuration Settings to learn about setting the sql_mode.