Skip to main content

Backup and restore Aiven for MySQL® with mysqldump or mydumper

Copy your Aiven for MySQL® data to a file, back it up to another Aiven for MySQL database, and restore it using mysqldump/restore or mydumper/myloader.

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.

Prerequisites​

Make sure your service has the required computational power (vCPUs) and memory capacity to process data migration without resource exhaustion or downtime.

  • mysqldump tool: install if missing
  • Source database to copy your data from: source-db
  • Target database to dump your source-db data to: target-db

You can use Aiven for MySQL databases both as source-db and as target-db. Create additional Aiven for MySQL® databases as needed.

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.

Back up the data​

Collect connection details​

To back up the source-db data to the mydb_backup.sql file, collect connection details on your Aiven for MySQL source-db service:

  1. Log in to the Aiven Console and go to your source-db service page.

  2. On the Overview page, find Connection information and note the following:

    VariableDescription
    SOURCE_DB_HOSTHost name for the connection
    SOURCE_DB_USERUser name for the connection
    SOURCE_DB_PORTConnection Port number
    SOURCE_DB_PASSWORDConnection Password
    DEFAULTDBDatabase that contains the source-db data

Back up to a file​

Use the following command to back up your Aiven for MySQL data to the mydb_backup.sql file:

mysqldump \
-p DEFAULTDB -P SOURCE_DB_PORT \
-h SOURCE_DB_HOST --single-transaction \
-u SOURCE_DB_USER --set-gtid-purged=OFF \
--password > mydb_backup.sql

With this command, the password will be requested at the prompt; paste SOURCE_DB_PASSWORD to the terminal, then a file named mydb_backup.sql will be created with your backup data. Note that having the prompt request for the password is more secure than including the password straight away in the command.

The --single-transaction flag starts a transaction in isolation mode REPEATABLE READ before running. This allows mysqldump to read the database in its current state at the time of the transaction, ensuring consistency of the data.

warning

If you are using Global Transaction Identifiers (GTIDs) with InnoDB use the --set-gtid-purged=OFF option. The reason is that GTID's are not available with MyISAM.

Restore the data​

Collect connection details​

To restore the saved data from the file to your target-db, collect connection details on your Aiven for MySQL target-db service:

  1. Log in to the Aiven Console and go to your target-db service page.
  2. On the Overview page, find Connection information and note the following:
VariableDescription
TARGET_DB_HOSTHost name for the connection
TARGET_DB_USERUser name for the connection
TARGET_DB_PORTConnection Port number
TARGET_DB_PASSWORDConnection Password
DEFAULTDBDatabase that contains the target-db data

Restore from the file​

Run the following command to load the saved data into your target-db service:

mysql \
-p DEFAULTDB -P TARGET_DB_PORT \
-h TARGET_DB_HOST \
-u TARGET_DB_USER \
--password < mydb_backup.sql

When the password is requested at the prompt, paste TARGET_DB_PASSWORD into the terminal. When the restore or load process is complete and the data is stored in your target-db, you can use the mysqlcheck command to perform data analysis.

Related pages