Connect Your Application to Azure Database for MySQL

This tutorial guides you through connecting your application to an Azure Database for MySQL instance. We'll cover common connection patterns and best practices.

Prerequisites

An Azure Database for MySQL flexible server instance. If you don't have one, create one now.
Application development environment set up (e.g., Node.js, Python, Java).
Basic understanding of SQL and your chosen programming language.

1. Obtain Connection Details

You need the following information to connect to your Azure Database for MySQL server:

Server Name: The fully qualified domain name (FQDN) of your server (e.g., your-server-name.mysql.database.azure.com).
Server Admin Login: The username you specified when creating the server (e.g., azure_user).
Password: The password for the server admin login.
Database Name: The name of the database you want to connect to. You can use an existing database or create a new one.

You can find these details in the Azure portal under your Azure Database for MySQL server's Overview and Connection security pages.

2. Configure Firewall Rules

By default, Azure Database for MySQL has firewall rules that prevent external connections. You need to allow connections from your application's IP address or range.

Navigate to your Azure Database for MySQL server in the Azure portal.

Under Settings, select Connection security.

Under Firewall rules, click Add firewall rule. You can add specific IP addresses, IP address ranges, or allow access for Azure services.

Security Note: For production environments, it's recommended to restrict firewall rules to only necessary IP addresses or virtual network service endpoints rather than allowing all client IPs.

3. Connect Using a Programming Language

The specific connection method depends on your programming language and the MySQL driver or ORM you are using. Below are examples for common languages.

Example: Node.js with `mysql2`


const mysql = require('mysql2/promise');

async function connectToDatabase() {
    const connection = await mysql.createConnection({
        host: 'your-server-name.mysql.database.azure.com',
        user: 'azure_user',
        password: 'your_password',
        database: 'your_database_name',
        port: 3306, // Default MySQL port
        ssl: {
            rejectUnauthorized: false // For Azure Database for MySQL, it's recommended to use SSL
        }
    });

    console.log('Successfully connected to Azure Database for MySQL!');

    // Example query
    const [rows] = await connection.execute('SELECT NOW()');
    console.log('Current server time:', rows[0]);

    await connection.end();
    return connection;
}

connectToDatabase().catch(err => {
    console.error('Error connecting to database:', err);
});

Example: Python with `mysql.connector`


import mysql.connector

try:
    conn = mysql.connector.connect(
        host='your-server-name.mysql.database.azure.com',
        user='azure_user',
        password='your_password',
        database='your_database_name',
        port=3306, # Default MySQL port
        ssl_ca='/path/to/DigiCertGlobalRootG2CA.crt.pem' # Path to SSL certificate for Azure Database for MySQL
    )
    print("Successfully connected to Azure Database for MySQL!")

    cursor = conn.cursor()
    cursor.execute("SELECT NOW()")
    result = cursor.fetchone()
    print("Current server time:", result[0])

    cursor.close()
    conn.close()

except mysql.connector.Error as err:
    print(f"Error connecting to database: {err}")

Note for Python: You might need to download the appropriate SSL certificate file for Azure Database for MySQL. Refer to the official Azure documentation for the correct certificate and configuration.

Example: Java with JDBC


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;

public class AzureMySqlConnect {
    public static void main(String[] args) {
        String url = "jdbc:mysql://your-server-name.mysql.database.azure.com:3306/your_database_name?useSSL=true&requireSSL=true";
        Properties properties = new Properties();
        properties.setProperty("user", "azure_user");
        properties.setProperty("password", "your_password");
        // For Azure Database for MySQL, you might need to configure SSL properties.
        // Refer to Azure documentation for specific SSL settings.

        try (Connection conn = DriverManager.getConnection(url, properties);
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery("SELECT NOW()")) {

            System.out.println("Successfully connected to Azure Database for MySQL!");

            while (rs.next()) {
                System.out.println("Current server time: " + rs.getString(1));
            }

        } catch (Exception e) {
            System.err.println("Error connecting to database: " + e.getMessage());
            e.printStackTrace();
        }
    }
}

4. Connection Pooling and Best Practices

For production applications, consider using connection pooling to improve performance by reusing database connections. Libraries like HikariCP (Java), generic-pool (Node.js), or SQLAlchemy (Python) provide robust connection pooling solutions.

Secure Credentials: Never hardcode database credentials directly in your code. Use environment variables, Azure Key Vault, or other secure secret management services.
SSL/TLS: Always enable SSL/TLS encryption for connections to protect data in transit.
Error Handling: Implement comprehensive error handling and logging for database operations.
Resource Management: Ensure connections are properly closed or returned to the pool when no longer needed.

Next Steps

Explore more advanced topics like performance tuning, high availability, and security best practices for Azure Database for MySQL.