Azure Database for MySQL: How to connect and migrate databases with dbForge Studio for MySQL

November 13th, 2020

In this article, you’ll learn how to connect to Azure Database for MySQL and discover three scenarios of database migration from MySQL to Azure using dbForge Studio for MySQL.

The article demonstrates how to connect to Azure Database for MySQL Server via dbForge Studio for MySQL. It also explores three common approaches of using the Studio to migrate a database from MySQL to Azure MySQL server. The choice of an approach depends on the circumstances and project requirements.

Connect to Azure Database for MySQL using dbForge Studio for MySQL

Migrate a database using the Backup and Restore functionality

Migrate a database using the Copy Databases functionality

Migrate a database using Schema and Data Compare tools

Prerequisites

To walk through the steps of this guide, you need to:

Connect to Azure Database for MySQL using dbForge Studio for MySQL

To connect to Azure Database for MySQL using dbForge Studio for MySQL:

  1. On the Database menu, click New Connection.
  2. Provide a host name and login credentials.
  3. Click the Test Connection button to check the configuration.
How to connect to Azure MySQL database

Migrate a database using the Backup and Restore functionality

The Studio allows migrating databases to Azure in a number of ways, the choice of which depends solely on your needs. If you need to move the entire database, it’s best to use the Backup and Restore functionality.

In this worked example, we will migrate the sakila database that resides on MySQL server to Azure Database for MySQL. The logic behind the migration process using the Backup and Restore functionality of dbForge Studio for MySQL is to create a backup of the MySQL database and then restore it in Azure Database for MySQL.

Step 1. Backup the database

1. On the Database menu, point to Backup and Restore, and then click Backup Database.
The Database Backup Wizard will appear.

Backup to move a database to Azure MySQL

2. On the Backup content tab of the Database Backup Wizard, select database objects you want to backup.

Select backup contents to migrate to 
Azure MySQL database

3. On the Options tab, configure the backup process to fit your requirements.

Backup options to transfer a database to Azure MySQL Server

4. Specify errors processing behavior and logging options.

Configure backup to migrate to Azure database for MySQL

5. Click Backup.

Backup complete for migration to Azure database for MySQL Server

Step 2. Restore the database

1. Connect to Azure for Database for MySQL as described above.
2. Right-click the Database Explorer body, point to Backup and Restore, and then click Restore Database.

Restore database in Azure for MySQL

3. In the Database Restore Wizard that opens, select a file with a database backup.

Restore wizard - restore a database in MySQL for Azure

4. Click Restore.

Restore in Azure MySQL successful

Migrate a database using the Copy Databases functionality

The Copy Databases functionality is somewhat similar to the Backup and Restore, except that with it you do not need two steps to migrate a database. And what is more, the feature allows transferring two or more databases in one go. The Copy Databases functionality is only available in the Enterprise edition of dbForge Studio for MySQL.

In this worked example, we will migrate the world_x database from MySQL server to Azure Database for MySQL.

To migrate a database using the Copy Databases functionality:

1. On the Database menu, click Copy Databases.

Copy database to Azure MySQL

2. In the Copy Databases tab that appears, specify the source and target connection and select the database(s) to be migrated. We enter Azure MySQL connection and select the world_x database.
Click the green arrow to initiate the process.

Copy database to Azure MySQL database - wizard

3. Check the result.
As a result of our database migration efforts, the world_x database has successfully appeared in Azure MySQL.

Copy database to Azure for MySQL database - result

Migrate a database using Schema and Data Compare tools

dbForge Studio for MySQL incorporates a few tools that allow migrating MySQL databases, MySQL schemas and\or data to Azure. The choice of functionality depends on your needs and the requirements of your project. If you need to selectively move a database, i.e. migrate certain MySQL tables to Azure, it’s best to use Schema and Data Compare functionality.

In this worked example, we will migrate the world database that resides on MySQL server to Azure Database for MySQL. The logic behind the migration process using Schema and Data Compare functionality of dbForge Studio for MySQL is to create an empty database in Azure Database for MySQL, synchronize it with the required MySQL database first using Schema Compare tool and then using Data Compare tool. This way MySQL schemas and data will be accurately moved to Azure.

Step 1. Connect to Azure Database for MySQL and create an empty database

Create database in Azure MySQL database

Step 2. Schema synchronization

1. On the Comparison menu, click New Schema Comparison.
The New Schema Comparison Wizard appears.

Schema compare - Azure MySQL server

2. Select the Source and the Target, then specify the schema comparison options. Click Compare.

Schema compare options - to deploy to Azure database for  MySQL

3. In the comparison results grid that appears, select objects for synchronization. Click the green arrow button to open the Schema Synchronization Wizard.

Schema comparison results - Azure database for MySQL server

4. Walk through the steps of the wizard configuring synchronization. Click Synchronize to deploy the changes.

Schema comparison wizard to deploy schema to Azure for MySQL

Step 3. Data Comparison

1. On the Comparison menu, click New Data Comparison. The New Data Comparison Wizard appears.

Deploy data to Azure MySQL

2. Select the Source and the Target, then specify the data comparison options and change mappings if necessary. Click Compare.

Data comparison wizard - Azure MySQL database

3. In the comparison results grid that appears, select objects for synchronization. Click the green arrow button to open the Data Synchronization Wizard.

Data comparison results - Azure MySQL server

4. Walk through the steps of the wizard configuring synchronization. Click Synchronize to deploy the changes.

Azure database for  MySQL - data deploy wizard

5. Enjoy the result.

Azure for MySQL - database migrated successfully

Summary

Nowadays more and more businesses move their databases to Azure Database for MySQL, as this database service is easy to set up, manage, and scale. That migration doesn’t need to be painful. dbForge Studio for MySQL boasts immaculate migration tools that can significantly facilitate the process. The Studio allows database transfer to be easily configured, saved, edited, automated and scheduled. More than that, dbForge Studio for MySQL supports a bunch of database servers to move your workloads from and to.

Leave a Comment