Azure Documentation

Connect to Azure Database for MySQL

This tutorial guides you through the process of connecting to your Azure Database for MySQL instance from various client applications and programming languages.

Prerequisites

• An Azure Database for MySQL server instance. If you don't have one, follow the Create Azure Database for MySQL tutorial.
• Azure CLI or Azure Portal access to manage your server.
• Network connectivity to your Azure Database for MySQL server. Ensure firewall rules are configured to allow access from your client IP.

1. Get Connection Information

You'll need the following details to connect to your MySQL server:

• Server name: The fully qualified domain name of your server (e.g., your-server-name.mysql.database.azure.com).
• Username: The admin username you created during server setup (e.g., your_admin_user).
• Password: The password for the admin username.
• Database name: The name of the database you want to connect to.

You can find these details in the Azure Portal under your MySQL server's Overview page or its Connection security settings.

2. Configure Firewall Rules

By default, Azure Database for MySQL is protected by a firewall. You need to configure firewall rules to allow access from your client's IP address or a range of IP addresses.

1. In the Azure Portal, navigate to your Azure Database for MySQL server.
2. Under Settings, select Connection security.
3. Click Add current client IP address to automatically add your current public IP address, or click Add client IP to manually enter an IP address or range.
4. For development and testing, you might enable Allow access to Azure services, but this is generally not recommended for production environments.

3. Connecting with Different Clients

3.1. Using MySQL Workbench

MySQL Workbench is a popular graphical tool for managing MySQL databases.

1. Open MySQL Workbench.
2. Click the + button to create a new connection.
3. In the Setup New Connection dialog:
   • Connection Name: Give your connection a descriptive name (e.g., "Azure MySQL").
   • Username: Enter your Azure MySQL admin username.
   • Password: Click Store in Vault... to securely store your password.
   • Hostname: Enter your Azure MySQL server name.
   • Port: Use the default 3306.
4. Click Test Connection to verify.
5. Click OK to save the connection.
6. Double-click the new connection to open the MySQL session.

3.2. Using Command-Line Interface (CLI)

You can use the standard mysql command-line client.

mysql -h your-server-name.mysql.database.azure.com -u your_admin_user -p your_database_name

You will be prompted to enter your password.

3.3. Connecting from Python (using `mysql.connector`)

Install the MySQL connector for Python:

pip install mysql-connector-python

Use the following Python code to connect:

import mysql.connector

try:
    conn = mysql.connector.connect(
        host="your-server-name.mysql.database.azure.com",
        user="your_admin_user",
        password="your_password",
        database="your_database_name",
        port=3306,
        ssl_ca="<path-to-DigiCertBaltimoreCyberTrustRoot.crt.pem>"
    )

    if conn.is_connected():
        print("Successfully connected to Azure Database for MySQL!")
        cursor = conn.cursor()
        cursor.execute("SELECT @@version")
        db_version = cursor.fetchone()
        print(f"Database version: {db_version[0]}")

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

finally:
    if 'conn' in locals() and conn.is_connected():
        cursor.close()
        conn.close()
        print("Connection closed.")

SSL Certificate: For secure connections, it's highly recommended to use SSL. You'll need to download the DigiCertBaltimoreCyberTrustRoot.crt.pem certificate from the DigiCert website and provide its path in the ssl_ca parameter.

3.4. Connecting from Node.js (using `mysql2`)

Install the `mysql2` package:

npm install mysql2

Use the following Node.js code to connect:

const mysql = require('mysql2');
const fs = require('fs');

const connection = mysql.createConnection({
    host: "your-server-name.mysql.database.azure.com",
    user: "your_admin_user",
    password: "your_password",
    database: "your_database_name",
    port: 3306,
    ssl: {
        ca: fs.readFileSync('<path-to-DigiCertBaltimoreCyberTrustRoot.crt.pem>')
    }
});

connection.connect(function(err) {
    if (err) {
        console.error('Error connecting: ' + err.stack);
        return;
    }
    console.log('Successfully connected to Azure Database for MySQL as id ' + connection.threadId);
});

// Example query
connection.query('SELECT @@version', function (error, results, fields) {
    if (error) throw error;
    console.log('Database version: ', results[0]['@@version']);
});

// Close the connection when done
// connection.end();

Similar to Python, ensure you have the SSL certificate downloaded and its path is correctly specified.

Troubleshooting Connection Issues

• Firewall: Double-check your firewall rules in the Azure Portal.
• Credentials: Verify that your username, password, and server name are correct.
• Network: Ensure your client machine has stable network connectivity and no local firewalls are blocking port 3306.
• SSL: If you're encountering SSL errors, ensure you're using the correct SSL certificate and that it's properly configured in your connection string/options.

For more advanced connection scenarios, such as connecting from Azure App Service or Azure Virtual Machines, please refer to the official Azure documentation.