Blog Post

Azure Database for MySQL Blog
1 MIN READ

Backup Azure Database for MySQL to a Blob Storage

Bashar-MSFT's avatar
Bashar-MSFT
Former Employee
Aug 14, 2019
Often customers want to backup Azure Database for MySQL to a Blob storage. mysqldump utility can't directly write the output file on a Blob Storage, in this post I will explain how this can be done using https://docs.microsoft.com/en-us/azure/storage/files/storage-files-introduction.

 

1- Navigate to your Azure Database for MySQL server on the portal and Run https://docs.microsoft.com/en-us/azure/cloud-shell/quickstartIf you run this for the first time it will ask you to create a Storage container and and this will mount an Azure File in it.

2- Type df in the cloud shell and collect the Azure File path

3- Change directory to the cloud drive using the cd command
     in the example below I used cd /usr/bashar/clouddrive

4- Now that you are in that directory run mysql command to extract the backup dump

5- Backup file is ready and in this example it is under File System "cs47e4f0dddd931x4619xbf7", navigate on the Azure Portal to that file system, as in the following screenshot.

6- Download backup file if needed or alternatively move it to Blob Storage using https://docs.microsoft.com/en-us/azure/storage/common/storage-use-azcopy-v10utility

 

Note: this technique leverages the cloud shell storage, if you are interested in extracting the dump to another blob storage please check the steps here: https://techcommunity.microsoft.com/t5/azure-database-for-mysql/steps-to-automate-backups-of-your-azure-database-for-mysql/ba-p/1791157

Updated Oct 17, 2020
Version 3.0

11 Comments

Sort By
Show More
  • hmikhan's avatar
    hmikhan
    Copper Contributor
    Sep 15, 2019

    Backup Azure Database for MySQL

     

    --- backup single database:
    mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p -d databaseName1 > databaseName1_backup.sql

     

    --- backup multiple database:

    mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p  --databases db1 db2 db3  > databases-backup.sql


    --- backup all databases


    mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p --all-databases > all_databases_backup.sql


    If you see issue "mysqldump: 1044 Access denied when using LOCK TABLES" Or
    "Access denied for use '@'%' to database 'mysql' when using LOCK TABLES"

     

    A quick workaround is to pass the –-single-transaction option to mysqldump:


    mysqldump -Fc -v -h XXXXXXX.mysql.database.azure.com -u XXXXXXX -p --single-transaction --all-databases > all_databases_backup.sql

     

     

    ref:

    https://docs.microsoft.com/en-us/azure/mysql/