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

To start with you'll need an Aiven MySQL database, in order to create one you can follow the instructions in our Aiven MySQL Getting Started guide.

Aiven 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 enough for the task. This allows you to limit the downtime during the migration process by having a sufficiently powerful MySQL plan.

Users & databases in the target database

If just using the defaultdb that Aiven creates for you automatically is not sufficient you can create more databases and database users. The default user account avnadmin can also directly create databases but for convenience's sake we recommend creating them through our Web console. To create these you can go to the web console and create them through the "User & Databases" tab.

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. 

Backup 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.

Assuming you have InnoDB tables, the following command will create 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

The password will be prompt and has to be entered, this is a more secure procedure than including the password straight away.

The --single-transaction flag  will start a transaction before running. Rather than lock the entire database, this will let mysqldump read the database in the current state at the time of the transaction, making for a consistent data dump.

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

Importing and restoring the data

To import the backup file, can be done with a simple mysql command to import the data.

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

Once done with the data loading you should switch your applications 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. Remember to check the new values from the "Users & Databases" tab.

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?