Skip to main content

Backup and restore Aiven for MySQL® data using mysqldump

Backing up your Aiven for MySQ® data to another storage service is a good way to ensure access to your data in case a failure occurs. Learn how to copy your Aiven for MySQL data to a file, back it up to another Aiven for MySQL database, and restore it using mysqldump.

Prerequisites​

  • The mysqldump tool installed. See the official MySQL documentation on how to install it.

  • A source MySQL® database to copy your data from, referred to as source-db.

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

  • A target MySQL database to dump your source-db data to, referred to as target-db.

  • Recommended: Pick a plan size that is large enough to store your data, so you can limit the downtime if you're performing a 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.

Back up the data​

Variables​

To backup the source-db data to a file called mydb_backup.sql you need to collect some information about your Aiven for MySQL source-db database. Go to Aiven Console > your Aiven for MySQL source-db service > Overview > the Connection information section and find the following information:

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

Commands​

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

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​

Variables​

To restore data saved in a file to an Aiven for MySQL database, you need to include the connection information for the service in the mysqldump command. Go to Aiven Console > your Aiven for MySQL target-db service > Overview > the Connection information section and find the following information:

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

Commands​

Run the following command to load your saved data into your Aiven for MySQL database:

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

The password will be requested at the prompt. You can paste TARGET_DB_PASSWORD into the terminal. Your data should be stored in your Aiven for MySQL target-db. See the mysqlcheck command to perform further analysis of your current MySQL data.

Read more about migrations​