For AI agents: the complete documentation index is available at https://docs.ovhcloud.com/de/llms.txt, the full documentation bundle is available at https://docs.ovhcloud.com/de/llms-full.txt, and this page is available as Markdown at https://docs.ovhcloud.com/de/guides/public-cloud/databases/mysql-external-database-migration.md.
Zum Inhalt springen

Migrate to OVHcloud Public Cloud Databases for MySQL

Als Markdown ansehen

Find out how to migrate an external MySQL database to an OVHcloud managed MySQL database with minimal downtime

Objective

This guide explains how to migrate an external MySQL database — hosted on your own servers, on a virtual machine or with another provider — to an OVHcloud Public Cloud Databases for MySQL service.

The migration first copies your data, then keeps replicating the changes made on the source until you stop it. You can therefore switch your application over to the new service whenever you choose, with minimal downtime.

The migration is only available through the OVHcloud API, not in the OVHcloud Console.

Requirements

  • A Public Cloud project in your OVHcloud account.
  • A Public Cloud Databases for MySQL service on the public network (the Getting started with Public Cloud Databases guide can help you to meet this requirement). For a service on a private network, the source must be reachable from that private network; this case is not covered by this guide.
  • Access to the .
  • Administrator access to the source server: you need to change its configuration and its firewall rules, and create a user on it.

Instructions

Step 1 - Check that your source database is compatible

The source database must run MySQL 5.7 or 8.0. You can migrate a MySQL 8.0 source to a service running a more recent version: the upgrade happens during the migration.

The migration relies on MySQL replication. The source server must use the following settings:

SettingRequired valuePurpose
gtid_modeONReplication is based on global transaction identifiers
enforce_gtid_consistencyONRequired to enable gtid_mode
log_binenabledWithout a binary log, changes cannot be replicated
binlog_formatROWReplication format used by the migration
binlog_row_imageFULLFull row images in the binary log
server_idany value other than 0Identifies the server in the replication

TLS must also be enabled on the source server, as the migration connects to it over an encrypted connection.

Run the following query on the source to check the current values:

SELECT @@gtid_mode, @@enforce_gtid_consistency, @@log_bin, @@binlog_format, @@binlog_row_image, @@server_id;

If a value differs, set it in the server configuration file (my.cnf, in the [mysqld] section), for example:

[mysqld]
server_id = 1
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON
Warning

Changing these settings usually requires restarting the MySQL server. Plan a maintenance window. To enable GTIDs without stopping a server already in production, follow the MySQL procedure for enabling GTIDs online.

Step 2 - Allow your OVHcloud service to reach the source

The migration works in the opposite direction from your applications: it is your OVHcloud service that connects to your source database, on its MySQL port. The source must therefore accept connections from your service's address.

To find this address, open your service in the . In the Connection details card of the Overview tab, copy the host, then resolve it:

dig +short <service-host>

On the source side, allow inbound TCP connections on the MySQL port (3306 by default) from this address only. You will remove this rule once the migration is over.

Warning

The migration stops, and does not restart on its own, in the following cases:

  • Your service's node is replaced, for example during maintenance. The monitoring call of step 5 then returns the failed status with the error Primary node got replaced during migration. Your service's address also changes: resolve the host again, then update the firewall rule and the allowed host of the migration user.
  • The source cannot be reached or refuses the connection for about 10 minutes, for example after you change the password of the migration user. The monitoring call of step 5 returns the failed status with the connection error.

Once the cause is fixed, restart the migration: stop it with the call of step 7, run the check of step 4 again, then start the migration again as in step 5. Repeating the start call of step 5 without stopping the migration first does not restart it.

The migration then starts over with a full copy of the source: in the databases being migrated, data written directly to your OVHcloud service is overwritten. If the restart fails, contact OVHcloud support.

Info

The migration check in step 4 tests this connection: if the source cannot be reached, the check fails and tells you why.

Step 3 - Create a dedicated migration user on the source

Warning

This user's password is sent in plain text in the body of the API calls in steps 4 and 5. It can therefore appear in your scripts, your shell history or your tools' logs. Create a user dedicated to the migration, with a strong password used nowhere else.

This user is only needed for the duration of the migration: you delete it in step 8, once the switchover is done. Its password then becomes useless, even if a copy of it was kept somewhere.

Create the user on the source, restricted to your OVHcloud service's address (found in step 2), and grant it the privileges the migration needs:

CREATE USER 'migration'@'<service-address>' IDENTIFIED BY '<strong-password>';

GRANT SELECT, RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT,
      SHOW VIEW, TRIGGER, EVENT, LOCK TABLES, PROCESS
      ON *.* TO 'migration'@'<service-address>';

Step 4 - Check that the migration is possible

The API calls in this guide need the ID of your Public Cloud project (serviceName) and the ID of your MySQL service (clusterId). To find them, run the following API calls and copy the ID of your project, then the ID of your service, from the lists they return:

GET/cloud/project

Create a task that checks whether the migration is possible:

{
  "sourceHost": "<source-host>",
  "sourcePort": 3306,
  "sourceUsername": "migration",
  "sourcePassword": "<strong-password>",
  "sourceSsl": true
}

The task starts in the running status. Get its result with the following API call:

When the check is over, the status field is done. If the success field is true, you can continue. The result field also tells you which migration method will be used:

{
  "status": "done",
  "result": "All pre-checks passed successfully, preferred migration method will be [Replication]",
  "success": true
}

If success is false, the result field explains what is missing — for example a source setting from step 1 or the network access from step 2. Fix it, then run the check again.

Step 5 - Start the migration

Warning

Do not write to your OVHcloud service while the migration is running. The service remains writable during the migration, and any data written to it directly would diverge from the source without any error being reported.

Create the migration task, with the same body as the check:

The API confirms that the migration has started:

{
  "status": "running",
  "error": "",
  "method": "replication",
  "message": "The migration process is running"
}

Follow its progress with the following API call:

Once the data has been copied, the migration keeps replicating the changes made on the source. The status field then changes to done, even though the replication continues, and the message field reads Take care, the replication still synchronise your source database, feel free to stop the replication whenever you want. If the migration is interrupted, the status field becomes failed and the error field gives the reason.

You can also check the replication by connecting to your OVHcloud service as avnadmin (the Connect using the CLI for Public Cloud Databases for MySQL guide explains how to connect). While the migration replicates, the following query returns ON:

SELECT SERVICE_STATE FROM performance_schema.replication_connection_status;

Step 6 - Check the migrated data

Before switching your application over, compare the source and the service:

  • the number of rows in your main tables;

  • their checksums, which must be identical on both sides:

    CHECKSUM TABLE <database>.<table>;
  • a test write: insert or update a row on the source, then check that it appears on your OVHcloud service a few moments later.

Tables, views and generated columns are migrated. Users and their privileges are not: you recreate them in step 7.

Step 7 - Switch your application over

  1. Stop your application from writing to the source database.

  2. Compare the checksums of step 6 again to check that the latest changes have reached your OVHcloud service.

  3. Stop the migration:

  4. Check the migration status with the API call from step 5:

    The call returns either the done status or a Bad Request error.

    Warning

    The done status does not prove that the migration is stopped: it is already displayed while the replication runs. To be certain, check that the query of step 5 on performance_schema.replication_connection_status no longer returns ON, and that a write made on the source no longer reaches your service.

  5. Recreate your application's users on your OVHcloud service, from the Users tab of the OVHcloud Console or in SQL, and grant them the privileges they need.

  6. Point your application to the host and port of your OVHcloud service, and authorise your application's IP addresses on the service (the Configure incoming connections of a Public Cloud Databases for MySQL service guide explains how to do this).

Warning

If you do not stop the migration, it keeps replicating the source: data deleted on the source is also deleted on your OVHcloud service.

Step 8 - Clean up the source

Once your application runs on your OVHcloud service:

  • delete the migration user on the source: DROP USER 'migration'@'<service-address>';
  • remove the firewall rule that allows your OVHcloud service to reach the source.

Go further

Capabilities and Limitations of Public Cloud Databases for MySQL

Configure incoming connections of a Public Cloud Databases for MySQL service

Visit our dedicated Discord channel: https://discord.gg/ovhcloud. Ask questions, provide feedback and interact directly with the team that builds our databases services.

For training or technical assistance implementing our solutions, contact your sales representative or visit our Professional Services page to request a quote and have your project analysed by our experts.

Join our community of users.

War diese Seite hilfreich?