If you have been using MySQL, most likely you have heard about  mysqldump to backup your database.  We recommend to read beforehand the mysqldump client utility reference manual, in case you are not familiar with it or in any doubt regarding to the options available. 

Aiven Database

Begin by setting up an Aiven for MySQL database. Follow the instructions in our Aiven for MySQL Getting Started guide to create one.

Aiven for MySQL allows you to easily switch between different plan sizes, but at least for the duration of the dumping process it usually makes sense to conservatively pick a plan size that is large and powerful enough for the task. This way you can limit the downtime during migration.

Users & databases in the target database

If the defaultdb that Aiven creates for you automatically is not enough, you can create more databases and database users. The default user account avnadmin can also directly create databases, but for convenience, we recommend creating them through our Web console.

To create more databases and users, go to the web console and click the Users or Databases tab respectively.

For this exercise, we have created a service within Aiven called testdump1, which has our data in defaultdb. We will migrate that data to a new database called uploadhere also created within an Aiven service called testload1. 

Back up your database

The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

If you have InnoDB tables, the following command creates a dump (backup) file named mydb_backup.sql.

mysqldump -p defaultdb -P 24048 -h testdump1-test-marcelo-test.avns.net --single-transaction -u avnadmin --set-gtid-purged=OFF --password > mydb_backup.sql

This command requires the password to be entered at the prompt; this is more secure than including the password straight away.

The --single-transaction flag starts a transaction before running. Rather than lock the entire database, this lets mysqldump read the database in the current state at the time of the transaction. This makes the data dump consistent.

If you are using Global Transaction Identifier’s (GTID’s) with InnoDB (GTID’s aren’t available with MyISAM), use the --set-gtid-purged=OFF option.

Importing and restoring the data

To import the backup file, use the following mysql command:

mysql -p uploadhere -P 24048 -h testload1-test-marcelo-test.avns.net -u avnadmin --password < mydb_backup.sql

Once you're done with the data loading, switch your application's connection settings to use the new database in Aiven. When doing this, remember that the user passwords will be different than on the server you migrated from. Check the Users and Databases tabs for the new passwords.

Another thing that is good to remember is to invoke a mysqlcheck  in order to get proper database statistics in place for the newly loaded data.

Got here by accident? Learn how Aiven’s hosted and managed MySQL solution will simplify your workflow:


Did this answer your question?