Migrate to Aiven for MySQL® via CLI

Migrate your external MySQL database to an Aiven-hosted one using either a one-time dump-and-restore or continuous data synchronization through MySQL's built-in replication.

note

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

How it works

The Aiven for MySQL migration process begins with an initial data transfer and can be followed by continuous data synchronization if your setup supports it.

Initial data transfer

A bulk copy of your data is first created. This is done using one of the following tools:

mysqldump for small and medium-sized databases
mydumper/myloader Early availability for large databases

mydumper/myloader

mydumper/myloader is an early availability feature designed for large database migrations. It offers faster performance and reduced downtime compared to mysqldump, which can consume significant resources and time when processing large datasets.

Continuous data synchronization

After the initial data copy, the Aiven for MySQL service can be configured as a replica of your external database, enabling ongoing data synchronization through MySQL's built-in replication feature.

Prerequisites

The source server is publicly available or accessible via a virtual private cloud (VPC) peering connection between the private networks, and firewalls are 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. Review the current GTID setting by running the following command on the source cluster:
```
show global variables like 'gtid_mode';
```
You have a running Aiven for MySQL service with a destination database. If missing, create it in the Aiven Console or the Aiven CLI.
If you use mydumper/myloader for the initial data transfer, make sure your service has sufficient computational power (multiple vCPUs) and high memory capacity to avoid the resource exhaustion during migration.

note

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

Collect source and destination details

Variable	Description
`SRC_HOSTNAME`	Hostname for source MySQL connection
`SRC_PORT`	Port for source MySQL connection
`SRC_USERNAME`	Username for source MySQL connection
`SRC_PASSWORD`	Password for source MySQL connection
`SRC_IGNORE_DBS`	Comma-separated list of databases to ignore in migration
`SRC_SSL`	SSL setting for source MySQL connection
`DEST_NAME`	Name of the destination Aiven for MySQL service
`DEST_PLAN`	Aiven plan for the destination Aiven for MySQL service (for example, `startup-4` or `business-32`)

Migrate your database

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.

Create a user in the source database with sufficient privileges for the pre-flight checks, the bulk copy (using mysqldump or mydumper in Early availability), and the ongoing replication. Replace % 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'@'%'
```

Set the migration details using the avn service update Aiven CLI command. Replace the placeholders with meaningful values.

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

Check the migration status using the avn service migration-status Aiven CLI command:

avn service migration-status --project PROJECT_NAME DEST_SERVICE_NAME

When the migration process is ongoing, migration_detail.status is 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"
    }
  ]
}

Ongoing replication starts a few minutes after the initial data copy.

tip

Monitor the ongoing replication 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 when the replication is active, the data is also removed on the migration target.

How it works​

Initial data transfer​

Continuous data synchronization​

Prerequisites​

Collect source and destination details​

Migrate your database​

Stop the replication​