Events
Sep 15, 6 AM - Sep 17, 3 PM
The best SQL community-led learning event. Sept 2025. Save €200 with code FABLEARN.
Get registeredThis browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Important
Azure Database for MariaDB is on the retirement path. We strongly recommend that you migrate to Azure Database for MySQL. For more information about migrating to Azure Database for MySQL, see What's happening to Azure Database for MariaDB?.
This article explains two common ways to back up and restore databases in your Azure database for MariaDB:
Before you begin migrating your database, do the following:
Use common utilities and tools such as MySQL Workbench or mysqldump to remotely connect and restore data into your Azure database for MariaDB. Use these tools on your client machine with an internet connection to connect to the Azure database for MariaDB. Use an SSL-encrypted connection as a best security practice. For more information, see Configure SSL connectivity in Azure Database for MariaDB. You don't need to move the dump files to any special cloud location when you migrate data to your Azure database for MariaDB.
You can use MySQL utilities such as mysqldump and mysqlpump to dump and load databases into an Azure database for MariaDB server in several common scenarios.
Use database dumps when you're migrating an entire database. This recommendation holds when you're moving a large amount of data, or when you want to minimize service interruption for live sites or applications.
Make sure that all tables in the database use the InnoDB storage engine when you're loading data into your Azure database for MariaDB. Azure Database for MariaDB supports only the InnoDB storage engine, and no other storage engines. If your tables are configured with other storage engines, convert them into the InnoDB engine format before you migrate them to your Azure database for MariaDB.
For example, if you have a WordPress app or a web app that uses MyISAM tables, first convert those tables by migrating them into InnoDB format before you restore them to your Azure database for MariaDB. Use the clause ENGINE=InnoDB
to set the engine to use for creating a new table, and then transfer the data into the compatible table before you restore it.
INSERT INTO innodb_table SELECT * FROM myisam_table ORDER BY primary_key_columns
To avoid any compatibility issues when you're dumping databases, ensure that you're using the same version of MariaDB on the source and destination systems. For example, if your existing MariaDB server is version 10.2, you should migrate to your Azure database for MariaDB that's configured to run version 10.2. The mysql_upgrade
command doesn't function in an Azure Database for MariaDB server, and it isn't supported. If you need to upgrade across MariaDB versions, first dump or export your earlier-version database into a later version of MariaDB in your own environment. You can then run mysql_upgrade
before you try migrating into your Azure database for MariaDB.
To optimize performance when you're dumping large databases, keep in mind the following considerations:
exclude-triggers
option in mysqldump. Exclude triggers from dump files to avoid having the trigger commands fire during the data restore.single-transaction
option to set the transaction isolation mode to REPEATABLE READ and send a START TRANSACTION SQL statement to the server before dumping data. Dumping many tables within a single transaction causes some extra storage to be consumed during the restore. The single-transaction
option and the lock-tables
option are mutually exclusive. This is because LOCK TABLES causes any pending transactions to be committed implicitly. To dump large tables, combine the single-transaction
option with the quick
option.extended-insert
multiple-row syntax that includes several VALUE lists. This approach results in a smaller dump file and speeds up inserts when the file is reloaded.order-by-primary
option in mysqldump when you're dumping databases, so that the data is scripted in primary key order.disable-keys
option in mysqldump when you're dumping data, to disable foreign key constraints before the load. Disabling foreign key checks helps improve performance. Enable the constraints and verify the data after the load to ensure referential integrity.defer-table-indexes
option in mysqlpump when you're dumping databases, so that index creation happens after table data is loaded.To back up an existing MariaDB database on the local on-premises server or in a virtual machine, run the following command by using mysqldump:
mysqldump --opt -u <uname> -p<pass> <dbname> > <backupfile.sql>
The parameters to provide are:
For example, to back up a database named testdb on your MariaDB server with the user name testuser and with no password to a file testdb_backup.sql, use the following command. The command backs up the testdb
database into a file called testdb_backup.sql
, which contains all the SQL statements needed to re-create the database.
mysqldump -u root -p testdb > testdb_backup.sql
To select specific tables to back up in your database, list the table names, separated by spaces. For example, to back up only table1 and table2 tables from the testdb, follow this example:
mysqldump -u root -p testdb table1 table2 > testdb_tables_backup.sql
To back up more than one database at once, use the --database switch and list the database names, separated by spaces.
mysqldump -u root -p --databases testdb1 testdb3 testdb5 > testdb135_backup.sql
Create an empty database on the target Azure Database for MariaDB server where you want to migrate the data. Use a tool such as MySQL Workbench to create the database. The database can have the same name as the database that contains the dumped data, or you can create a database with a different name.
To get connected, locate the connection information on the Overview pane of your Azure database for MariaDB.
In MySQL Workbench, add the connection information.
After you've created the target database, you can use the mysql command or MySQL Workbench to restore the data into the newly created database from the dump file.
mysql -h <hostname> -u <uname> -p<pass> <db_to_restore> < <backupfile.sql>
In this example, you restore the data into the newly created database on the target Azure Database for MariaDB server.
mysql -h mydemoserver.mariadb.database.azure.com -u myadmin@mydemoserver -p testdb < testdb_backup.sql
To export, you can use the common tool phpMyAdmin, which might already be installed locally in your environment. To export your MariaDB database, do the following:
The importing process is similar to the exporting process. Do the following:
Events
Sep 15, 6 AM - Sep 17, 3 PM
The best SQL community-led learning event. Sept 2025. Save €200 with code FABLEARN.
Get registered