Edit

Share via

Tutorial: Configure databases in Azure Database for MySQL using Ansible

Important

Ansible 2.7 (or later) is required to run the sample playbooks in this article.

Azure Database for MySQL is a relational database service based on the MySQL Community Edition. Azure Database for MySQL enables you to manage MySQL databases in your web apps.

In this article, you learn how to:

  • Create a MySql server
  • Create a MySql database
  • Configure a firewall rule so that an external app can connect to your server
  • Connect to your MySql server from the Azure Cloud Shell
  • Query your available MySQL servers
  • List all databases in your connected servers

Prerequisites

  • Azure subscription: If you don't have an Azure subscription, create a free account before you begin.

Create a resource group

The playbook code in this section creates an Azure resource group. A resource group is a logical container in which Azure resources are deployed and managed.

Save the following playbook as rg.yml:

- hosts: localhost
  vars:
    resource_group: myResourceGroup
    location: eastus 
  tasks:
    - name: Create a resource group
      azure_rm_resourcegroup:
        name: "{{ resource_group }}"
        location: "{{ location }}"

Before running the playbook, see the following notes:

  • A resource group named myResourceGroup is created.
  • The resource group is created in the eastus location:

Run the playbook using ansible-playbook

ansible-playbook rg.yml

Create a MySQL server and database

The playbook code in this section creates a MySQL server and an Azure Database for MySQL instance. The new MySQL server is a Gen 5 Basic Purpose server with one vCore and is named mysqlserveransible. The database instance is named mysqldbansible.

For more information about pricing tiers, see Azure Database for MySQL pricing tiers.

Save the following playbook as mysql_create.yml:

- hosts: localhost
  vars:
    resource_group: myResourceGroup
    location: eastus
    mysqlserver_name: mysqlserveransible
    mysqldb_name: mysqldbansible
    admin_username: mysqladmin
    admin_password: <server_admin_password> 
  tasks:
    - name: Create MySQL Server
      azure_rm_mysqlserver:
        resource_group: "{{ resource_group }}"
        name: "{{ mysqlserver_name }}"
        sku:
          name: B_Gen5_1
          tier: Basic
        location: "{{ location }}"
        version: 5.6
        enforce_ssl: True
        admin_username: "{{ admin_username }}"
        admin_password: "{{ admin_password }}"
        storage_mb: 51200
    - name: Create instance of MySQL Database
      azure_rm_mysqldatabase:
        resource_group: "{{ resource_group }}"
        server_name: "{{ mysqlserver_name }}"
        name: "{{ mysqldb_name }}"

Before running the playbook, see the following notes:

  • In the vars section, the value of mysqlserver_name must be unique.
  • In the vars section, replace <server_admin_password> with a password.

Run the playbook using ansible-playbook

ansible-playbook mysql_create.yml

Configure a firewall rule

A server-level firewall rule allows an external app to connect to your server through the Azure MySQL service firewall. Examples of external apps are the mysql command-line tool and the MySQL Workbench.

The playbook code in this section creates a firewall rule named extenalaccess that allows connections from any external IP address.

Save the following playbook as mysql_firewall.yml:

- hosts: localhost
  vars:
    resource_group: myResourceGroup
    mysqlserver_name: mysqlserveransible
  tasks:
  - name: Open firewall to access MySQL Server from outside
    azure_rm_resource:
      api_version: '2017-12-01'
      resource_group: "{{ resource_group }}"
      provider: dbformysql
      resource_type: servers
      resource_name: "{{ mysqlserver_name }}"
      subresource:
        - type: firewallrules
          name: externalaccess
      body:
        properties:
          startIpAddress: "0.0.0.0"
          endIpAddress: "255.255.255.255"

Before running the playbook, see the following notes:

  • In the vars section, replace startIpAddress and endIpAddress. Use the range of IP addresses that correspond to the range from which you'll be connecting.
  • Connections to Azure Database for MySQL communicate over port 3306. If you try to connect from within a corporate network, outbound traffic over port 3306 might not be allowed. In that case, you can't connect to your server unless your IT department opens port 3306.
  • The playbook uses the azure_rm_resource module, which allows direct use of the REST API.

Run the playbook using ansible-playbook

ansible-playbook mysql_firewall.yml

Connect to the server

In this section, you use the Azure Cloud Shell to connect to the server you created previously.

  1. Open shell.azure.com by selecting below.

    Embed launch

  2. Enter the following code:

    mysql -h mysqlserveransible.mysql.database.azure.com -u mysqladmin@mysqlserveransible -p

  3. At the prompt, enter the following command to query the server status:

    mysql> status

    If everything goes well, you see output similar to the following results:

    demo@Azure:~$ mysql -h mysqlserveransible.mysql.database.azure.com -u mysqladmin@mysqlserveransible -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 65233
Server version: 5.6.39.0 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.23, for Linux (x86_64) using  EditLine wrapper

Connection id:          65233
Current database:
Current user:           mysqladmin@13.76.42.93
SSL:                    Cipher in use is AES256-SHA
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.6.39.0 MySQL Community Server (GPL)
Protocol version:       10
Connection:             mysqlserveransible.mysql.database.azure.com via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 36 min 21 sec

Threads: 5  Questions: 559  Slow queries: 0  Opens: 96  Flush tables: 3  Open tables: 10  Queries per second avg: 0.256
--------------

Query MySQL servers

The playbook code in this section queries MySQL servers in myResourceGroup and lists the databases on the found servers.

Save the following playbook as mysql_query.yml:

- hosts: localhost
  vars:
    resource_group: myResourceGroup
    mysqlserver_name: mysqlserveransible
  tasks:
    - name: Query MySQL Servers in current resource group
      azure_rm_mysqlserver_facts:
        resource_group: "{{ resource_group }}"
      register: mysqlserverfacts

    - name: Dump MySQL Server facts
      debug:
        var: mysqlserverfacts

    - name: Query MySQL Databases
      azure_rm_mysqldatabase_facts:
        resource_group: "{{ resource_group }}"
        server_name: "{{ mysqlserver_name }}"
      register: mysqldatabasefacts

    - name: Dump MySQL Database Facts
      debug:
        var: mysqldatabasefacts

Run the playbook using ansible-playbook

ansible-playbook mysql_query.yml

After running the playbook, you see output similar to the following results:

"servers": [
    {
        "admin_username": "mysqladmin",
        "enforce_ssl": false,
        "fully_qualified_domain_name": "mysqlserveransible.mysql.database.azure.com",
        "id": "/subscriptions/aaaa0a0a-bb1b-cc2c-dd3d-eeeeee4e4e4e/resourceGroups/myResourceGroup/providers/Microsoft.DBforMySQL/servers/mysqlserveransible",
        "location": "eastus",
        "name": "mysqlserveransible",
        "resource_group": "myResourceGroup",
        "sku": {
            "capacity": 1,
            "family": "Gen5",
            "name": "B_Gen5_1",
            "tier": "Basic"
        },
        "storage_mb": 5120,
        "user_visible_state": "Ready",
        "version": "5.6"
    }
]

You also see the following output for the MySQL database:

"databases": [
    {
        "charset": "utf8",
        "collation": "utf8_general_ci",
        "name": "information_schema",
        "resource_group": "myResourceGroup",
        "server_name": "mysqlserveransible"
    },
    {
        "charset": "latin1",
        "collation": "latin1_swedish_ci",
        "name": "mysql",
        "resource_group": "myResourceGroup",
        "server_name": "mysqlserveransibler"
    },
    {
        "charset": "latin1",
        "collation": "latin1_swedish_ci",
        "name": "mysqldbansible",
        "resource_group": "myResourceGroup",
        "server_name": "mysqlserveransible"
    },
    {
        "charset": "utf8",
        "collation": "utf8_general_ci",
        "name": "performance_schema",
        "resource_group": "myResourceGroup",
        "server_name": "mysqlserveransible"
    }
]

Clean up resources

  1. Save the following code as delete_rg.yml.

    ---
- hosts: localhost
  tasks:
    - name: Deleting resource group - "{{ name }}"
      azure_rm_resourcegroup:
        name: "{{ name }}"
        state: absent
      register: rg
    - debug:
        var: rg

  2. Run the playbook using the ansible-playbook command. Replace the placeholder with the name of the resource group to be deleted. All resources within the resource group will be deleted.

    ansible-playbook delete_rg.yml --extra-vars "name=<resource_group>"

    Key points:

    • Because of the register variable and debug section of the playbook, the results display when the command finishes.

Next steps

Ansible on Azure