Azure Data Factory (ADF) is a powerful cloud-based data integration service that allows you to create, schedule, and orchestrate data pipelines with ease. One of the most common tasks is connecting to a database source—MySQL Database in Azure Data Factory being a popular setup for structured data workflows.
This guide walks you through every step involved in creating a MySQL database in Azure, configuring secure access, connecting it with MySQL Workbench, and preparing the environment for seamless use with MySQL Database in Azure Data Factory. By following these steps, you’ll be fully equipped to integrate MySQL into your cloud-based data pipelines.
Step 1: Access the Azure Portal
Start by logging into the Azure Portal (https://portal.azure.com/). The Azure Portal is a web-based interface for managing all your cloud resources.
On the main dashboard, click “Create a resource” in the left-hand menu to open the Azure Marketplace.
In the search bar, enter “MySQL” and select “Azure Database for MySQL – Flexible Server” This fully managed service provides high availability, automatic backups, and scalability.
Step 2: Configure and Deploy the MySQL Server
Click “Create” to begin configuring your new MySQL server. You’ll be prompted to fill in the following details:
- Subscription & Resource Group: Choose your Azure subscription and either select an existing resource group or create a new one.
- Server Name: Enter a globally unique name for your MySQL server (e.g., myazuremysql-demo).
- Region: Choose a location close to your users or applications for optimal performance.
- Authentication Type: Select Password authentication and set an admin username and password.
- Compute + Storage: Configure the number of vCores, storage size, and backup retention period based on your workload needs.
Once you’ve entered all necessary information, click “Review + Create” and then “Create.” The deployment process may take several minutes.
Step 3: Configure Network Access
Once your MySQL server is deployed, navigate to its overview page. Click on the “Networking” settings. By default, Azure MySQL servers are protected by a firewall that blocks all external traffic.
To enable access from your local machine or any client, add your current IP address to the firewall rules by clicking “Add current client IP address.” Save the rule to permit connections from your machine.
This step is essential for securely managing who can access your database server.
Step 4: Connect Using MySQL Workbench
MySQL Workbench is a widely used graphical client for managing MySQL databases. Open Workbench and click the “+” icon to create a new connection.
Enter a connection name (e.g., “Azure MySQL”), and in the Hostname field, provide the fully qualified server name (e.g., myazuremysql-demo.mysql.database.azure.com).
Enter the admin username and password you set during server creation, and keep the default port as 3306. Click “Test Connection” to verify connectivity. If successful, save the connection profile.
Step 5: Set Up the Database Schema
After establishing the connection in MySQL Workbench, you can create a new database for your projects. Open the SQL editor and run commands such as:
CREATE DATABASE azuredemo;
USE azuredemo;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);
This confirms your server is operational and ready for integration.
Step 6: Integrate MySQL Database in Azure Data Factory
With your MySQL database set up and accessible, you can now integrate it with Azure Data Factory. In ADF, create a new Linked Service, select Azure Database for MySQL, and input your server details and credentials. This connection enables ADF to read from or write to your MySQL database, allowing you to build pipelines that move and transform data between various sources and destinations.
Final Tips and Best Practices
- Security: For enhanced security, enable SSL connections in both Azure and MySQL Workbench.
- Backups and Monitoring: Set up automatic backups and monitor your server’s performance using Azure’s built-in tools.
- Firewall Management: Regularly update firewall rules to ensure only authorized IPs can access your database.
By following these steps, you have successfully created a MySQL Database in Azure Data Factory, configured secure access, and prepared it for integration with Azure Data Factory. This setup allows you to efficiently build secure, scalable data solutions in the cloud.
Watch the full video here: