How to backup and restore Azure maria DB to onpremises My SQL server

Question

Dear Team,

Im looking for some support here, to know in detail. How to backup and restore Azure maria DB to onpremises MySQL server

Answer 1

Hi Abrar Hussain Khan,

the primary tool for this is mysqldump. it is a command line utility that creates a logical backup of your database. you will use it to export your data from azure and then import it into your on prem mysql server.

you need to get mysqldump installed on a machine that can connect to both your azure mariadb server and your on prem mysql server. this could be your local laptop or a jump box in azure.

the command to dump your database from azure looks like this.

mysqldump --host=<your-azure-server-name>.mariadb.database.azure.com --user=<admin-user>@<server-name> --password --ssl-mode=REQUIRED --databases <database-name> > backup.sql

this will create a file called backup.sql with all your schema and data. the --ssl-mode=REQUIRED part is crucial because azure requires encrypted connections.

once you have the backup file, you can import it into your on prem mysql server using the mysql command.

mysql --host=<on-prem-server> --user=<user> --password <database-name> < backup.sql

our docs on using mysqldump with azure database for mariadb are here https://learn.microsoft.com/azure/mariadb/howto-migrate-dump-restore

for very large databases, mysqldump can be slow. in that case, you might want to look into mydumper and myloader. they are third party tools that can do parallel exports and imports, which is much faster for big datasets. this might help in other migration scenarios too.

test the restore process with a small, non critical database first. this helps you work out any kinks in the process before you do it for real. make sure the versions of mariadb and mysql are compatible to avoid syntax errors.

remember that any ongoing writes to your azure database during the dump will not be captured. so schedule this during a quiet period or use a read replica to get a consistent backup without affecting your production users.

Best regards,

Alex

and "yes" if you would follow me at Q&A - personaly thx.
P.S. If my answer help to you, please Accept my answer

https://ctrlaltdel.blog/

Answer 2

Hi Abrar Hussain Khan,

Thanks for posting your query on Microsoft Q&A!

I would like to include more details in detailed way in addition to Akex Answer.

You are trying to do backup and restore an Azure MariaDB database to your on-premises MySQL server. Here's how you can do it using mysqldump, which is the primary tool for this task:

1. Exporting from Azure MariaDB:

• First, ensure you have mysqldump installed on your machine (this can be your local laptop or a jump box in Azure that can connect to both servers).
  • Use the following command to create a backup of your Azure MariaDB:

         mysqldump --host=<your-azure-server-name>.mariadb.database.azure.com --user=<username> --password=<password> --ssl-mode=REQUIRED <database-name> > backup.sql
         
    

    • Replace <your-azure-server-name>, <username>, <password>, and <database-name> with your actual values. This will generate a file named backup.sql containing your database schema and data.

1. Importing to On-Premises MySQL:
   • After you have your backup, you can import it into your on-premises MySQL server using:

          mysql --host=<on-prem-server> --user=<username> --password=<password> <database-name> < backup.sql
          
     

   • Again, replace <on-prem-server>, <username>, <password>, and <database-name> with the appropriate values for your setup.

Please go through the below Microsoft documents for more details on how to migrate the Maria DB

• https://learn.microsoft.com/azure/mariadb/howto-migrate-dump-restore
• Here you can see step wise details on how to backup and restore the Maria DB https://techcommunity.microsoft.com/blog/adformysql/migrating-from-azure-database-for-mariadb-to-azure-database-for-mysql-using-mydu/4403467

Please let us know if you have further questions on this.

---

Kindly consider upvoting and Accepting the Answers, if the information provided is helpful. This can assist other community members in resolving similar issues.

Thanks!

Kalyani