Skip to main content

Migrate to Aiven for MySQL® via Aiven CLI

Aiven for MySQL® offers a managed process for migrating from an external MySQL into the Aiven-hosted database. It supports both a one-off dump-and-restore process and using the ongoing replication functionality built-in to MySQL.

note

To use the Aiven Console to migrate your database, see Migrate MySQL® databases to Aiven using the Console.

The process will first do a mysqldump to seed the schema and bulk-copy the data, if the preconditions are met for ongoing replication then it will configure MySQL as a replica of the external database.

Requirements​

To perform a migration from an external MySQL to Aiven for MySQL the following requirements need to be satisfied:

  • The source server needs to be publicly available or accessible via a virtual private cloud (VPC) peering connection between the private networks, and any firewalls need to be open to allow traffic between the source and target servers.

  • You have a user account on the source server with sufficient privileges to create a user for the replication process.

  • GTID is enabled on the source database. To review the current GTID setting, run the following command hon the source cluster:

    show global variables like 'gtid_mode';
note

If you are migrating from MySQL in GCP, enable backups with PITR for GTID to be set to on

Variables​

You can use the following variables in the code samples provided:

VariableDescription
SRC_HOSTNAMEHostname for source MySQL connection
SRC_PORTPort for source MySQL connection
SRC_USERNAMEUsername for source MySQL connection
SRC_PASSWORDPassword for source MySQL connection
SRC_IGNORE_DBSComma-separated list of databases to ignore in migration
SRC_SSLSSL setting for source MySQL connection
DEST_NAMEName of the destination Aiven for MySQL service
DEST_PLANAiven plan for the destination Aiven for MySQL service (for example, startup-4, business-32, etc)

Perform the migration​

warning

To avoid conflicts and replication issues, follow these guidelines while your data is being migrated:

  • Do not write to any tables in the target database that are being processed by the migration tool.
  • Do not change the replication configuration of the source database manually. Don't modify binlog_format or reduce max_connections.
  • Do not make database changes that can disrupt or prevent the connection between the source database and the target database. Do not change the source database's listen address and do not modify or enable firewalls on the databases.

  1. Create a user in the source database with sufficient privileges for the pre-flight checks, the mysqldump, and the ongoing replication (you can substitute % in the below command with the IP address of the Aiven for MySQL database, if already existing):

    create user 'SRC_USERNAME'@'%' identified by 'SRC_PASSWORD';
    grant replication slave on *.* TO 'SRC_USERNAME'@'%';
    grant select, process, event on *.* to 'SRC_USERNAME'@'%'

  2. If you don't have an Aiven for MySQL database yet, create it via Aiven Console or the dedicated Aiven CLI command

  3. Set the migration details via the avn service update Aiven CLI command substituting the parameters accordingly:

    avn service update --project PROJECT_NAME \
        -c migration.host=SRC_HOSTNAME \
        -c migration.port=SRC_PORT \
        -c migration.username=SRC_USERNAME \
        -c migration.password=SRC_PASSWORD \
        -c migration.ignore_dbs=SRC_IGNORE_DBS \
        -c migration.ssl=SRC_SSL \
        DEST_NAME

  4. Check the migration status via the dedicated avn service migration-status Aiven CLI command:

    avn service migration-status --project PROJECT_NAME DEST_SERVICE_NAME

While the migration process is ongoing, the migration_detail.status will be syncing:

{
  "migration": {
      "error": null,
      "method": "replication",
      "seconds_behind_master": 0,
      "source_active": true,
      "status": "done"
  },
  "migration_detail": [
      {
          "dbname": "migration",
          "error": null,
          "method": "replication",
          "status": "syncing"
      }
  ]
}
note

The migration will initially do a bulk-copy of your data, and several minutes after that has finished it will use the built-in replication feature of MySQL to commence ongoing data copying. You can see MySQL's internal status by running show replica status on the destination database.

Stop the replication​

After confirming that the migration is complete, stop the ongoing replication by removing the configuration from the destination service via the avn service update Aiven CLI command:

avn service update --project PROJECT_NAME --remove-option migration DEST_NAME
warning

If you don't stop the ongoing replication, you might lose data. For example, if you remove the data on the migration source, the data is also removed on the migration target as a result of the active replication.

Related pages