Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1013764%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23112%3A%20Exporting%20Azure%20Database%20for%20MySQL%20to%20a%20Blob%20Storage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1013764%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20received%20multiple%20questions%20about%20how%20to%20export%20databases%20of%20Azure%20Database%20for%20MySQL%2C%20PostgreSQL%20or%20MariaDB%20to%20an%20Azure%20Blob%20Storage.%20We%20have%20several%20ways%2C%20for%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EUsing%20Cloud%20Shell%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Foverview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Foverview%3C%2FA%3E%26nbsp%3Bthat%20you%20have%20MySQL%2FPostgreSQL%20client%20tools%20that%20allows%20you%20to%20import%2Fexport%20data%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Ffeatures%23tools%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Ffeatures%23tools%3C%2FA%3E%2C%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3EUsing%20our%20the%20client%20tools%20in%20your%20local%20environment.%20But%2C%20please%2C%20remember%20about%20the%20limits%20of%20OutBound%20data%20transfer%20because%20you%20could%20have%20more%20charges%20in%20your%20invoice.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EThis%20time%20I%20would%20like%20to%20share%20with%20you%20a%20lesson%20learned%20about%20how%20to%20export%20a%20database%20of%20MySQL%20using%20the%20Azure%20File%20Share%20creating%20a%20folder%20where%20saved%20my%20exported%20file%20and%20making%20accessible%20from%20anywhere%20using%20my%20credentials%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EPhase%201%3A%20I%20created%20a%20virtual%20machine%20in%20Azure%20in%20the%20same%20region%20that%20your%20Azure%20Database%20for%20MySQL%20is%20running%20on.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EPhase%202%3A%20Depending%20on%20the%20size%20of%20your%20database%2C%20you%20could%20attach%20a%20storage%20or%20create%20a%20File%20Share%20in%20any%20storage%20account.%20In%20this%20case%2C%20I%20chosen%20to%20use%20Azure%20File%20Share.%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CUL%3E%0A%3CLI%3E%3CSTRONG%3EPhase%202.1%3A%20I%20created%20a%20File%20Share%20following%20the%20instructions%20given%20here%3A%3C%2FSTRONG%3E%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fstorage%2Ffiles%2Fstorage-how-to-use-files-windows%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fstorage%2Ffiles%2Fstorage-how-to-use-files-windows%3C%2FA%3E%20%3CSTRONG%3Ecalled%20mysqlexport%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3CLI%3E%3CSTRONG%3EPhase%202.2%3A%20I%20attached%20the%20File%20Share%20running%20the%20following%20command%20using%20Microsoft%20Command%20Prompt%3A%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%20style%3D%22padding-left%3A%2090px%3B%22%3Ecmdkey%20%2Fadd%3Amyblobstoragename.file.core.windows.net%20%2Fuser%3AAzure%5Cmyblobstoragename%20%2Fpass%3AAccessKey%3C%2FP%3E%0A%3CP%20style%3D%22padding-left%3A%2090px%3B%22%3Enet%20use%20Z%3A%20%5C%5Cmyblobstoragename.file.core.windows.net%5Cmysqlexport%20%2Fpersistent%3AYes%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EPhase%203%3A%20After%20installing%20MySQLWorkbench%20or%20MySQL%20Utilities%20I%20executed%20the%20following%20command%20to%20export%20the%20database%2C%20creating%20the%20backup%20file%20z%3A%5Cbkp18112019.sql%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%E2%80%9CC%3A%5CProgram%20Files%5CMySQL%5CMySQL%20Workbench%208.0%20CE%5Cmysqldump%E2%80%9D%20--host%20mysqlservername.mysql.database.azure.com%20--user%20username%40mysqlservername%20-p%20--databases%20databasename%20--column-statistics%3D0%20--compress%20--verbose%20%26gt%3B%20c%3A%5Ctemp%5Cbkp31082019.sql%3C%2FLI%3E%0A%3CLI%3EI%20used%20%E2%80%93column-statistics%20due%20to%20my%20database%2C%20if%20you%20have%20any%20issue%20with%20the%20statistics%20please%20use%20this%20modifier.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EPhase%204%3A%20I%20created%20a%20new%20Azure%20Database%20for%20MySQL%20and%20I%20restored%20the%20database%20-%20%3CSTRONG%3E%3CA%20href%3D%22https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fmysqldump.html%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F8.0%2Fen%2Fmysqldump.html%3C%2FA%3E%3C%2FSTRONG%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CUL%3E%0A%3CLI%3E%22C%3A%5CProgram%20Files%5CMySQL%5CMySQL%20Workbench%208.0%20CE%5Cmysql.exe%22%20--host%20newmysqlservername.mysql.database.azure.com%20--user%20username%40newmysqlservername%20-p%20%26lt%3B%26gt%3B%0A%3C%2FLI%3E%3C%2FUL%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20this%20process%2C%20you%20will%20be%20able%20to%20export%20and%20import%20the%20database.%20Using%20Azure%20File%20Share%20you%20could%20connect%20to%20this%20service%20from%20any%20Windows%2C%20Linux%20or%20MacOS%20platform%20to%20manage%20the%20file.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1013764%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20received%20multiple%20questions%20about%20how%20to%20export%20databases%20of%20Azure%20Database%20for%20MySQL%2C%20PostgreSQL%20or%20MariaDB%20to%20an%20Azure%20Blob%20Storage.%20We%20have%20several%20ways%2C%20for%20example%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EUsing%20Cloud%20Shell%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Foverview%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Foverview%3C%2FA%3E%26nbsp%3Bthat%20you%20have%20MySQL%2FPostgreSQL%20client%20tools%20that%20allows%20you%20to%20import%2Fexport%20data%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Ffeatures%23tools%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%20target%3D%22_blank%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fcloud-shell%2Ffeatures%23tools%3C%2FA%3E%2C%26nbsp%3B%3C%2FLI%3E%0A%3CLI%3EUsing%20our%20the%20client%20tools%20in%20your%20local%20environment.%20But%2C%20please%2C%20remember%20about%20the%20limits%20of%20OutBound%20data%20transfer%20because%20you%20could%20have%20more%20charges%20in%20your%20invoice.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EThis%20time%20I%20would%20like%20to%20share%20with%20you%20a%20lesson%20learned%20about%20how%20to%20export%20a%20database%20of%20MySQL%20using%20the%20Azure%20File%20Share%20creating%20a%20folder%20where%20saved%20my%20exported%20file%20and%20making%20accessible%20from%20anywhere%20using%20my%20credentials.%3C%2FP%3E%3C%2FLINGO-TEASER%3E

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:

 

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.

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.

 

 

    • "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.