Lesson Learned #112: Exporting Azure Database for MySQL to a Blob Storage

[Jose_Manuel_Jurado]

We received multiple questions about how to export databases of Azure Database for MySQL, PostgreSQL or MariaDB to an Azure Blob Storage. We have several ways, for example:

• Using Cloud Shell https://docs.microsoft.com/en-us/azure/cloud-shell/overview that you have MySQL/PostgreSQL client tools that allows you to import/export data https://docs.microsoft.com/en-us/azure/cloud-shell/features#tools, 
• Using our the client tools in your local environment. But, please, remember about the limits of OutBound data transfer because you could have more charges in your invoice.

This time I would like to share with you a lesson learned about how to export a database of MySQL using the Azure File Share creating a folder where saved my exported file and making accessible from anywhere using my credentials:

• Phase 1: I created a virtual machine in Azure in the same region that your Azure Database for MySQL is running on.
• Phase 2: Depending on the size of your database, you could attach a storage or create a File Share in any storage account. In this case, I chosen to use Azure File Share.
• Phase 2.1: I created a File Share following the instructions given here: https://docs.microsoft.com/en-us/azure/storage/files/storage-how-to-use-files-windows called mysqlexport
• Phase 2.2: I attached the File Share running the following command using Microsoft Command Prompt:

cmdkey /add:myblobstoragename.file.core.windows.net /user:Azure\myblobstoragename /pass:AccessKey

net use Z: \\myblobstoragename.file.core.windows.net\mysqlexport /persistent:Yes

• Phase 3: After installing MySQLWorkbench or MySQL Utilities I executed the following command to export the database, creating the backup file z:\bkp18112019.sql

• “C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysqldump” --host mysqlservername.mysql.database.azure.com --user username@mysqlservername -p --databases databasename --column-statistics=0 --compress --verbose > c:\temp\bkp31082019.sql
• I used –column-statistics due to my database, if you have any issue with the statistics please use this modifier.

• Phase 4: I created a new Azure Database for MySQL and I restored the database - https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html

• "C:\Program Files\MySQL\MySQL Workbench 8.0 CE\mysql.exe" --host newmysqlservername.mysql.database.azure.com --user username@newmysqlservername -p <z:\bkp18112019.sql

With this process, you will be able to export and import the database. Using Azure File Share you could connect to this service from any Windows, Linux or MacOS platform to manage the file.