ansible.mysql Collection 5.0 — MySQL Automation Guide

Introduction

The ansible.mysql collection version 5.0.0 is the official successor to community.mysql, which has been sunset. All MySQL automation in Ansible now uses ansible.mysql. This guide covers installation, migration from community.mysql, and practical examples for every key module.

Installation

# Install the collection
ansible-galaxy collection install ansible.mysql

# Install with specific version
ansible-galaxy collection install ansible.mysql:==5.0.0

# Install Python MySQL driver (required)
pip install PyMySQL
# or
pip install mysqlclient

Migration from community.mysql

The community.mysql collection 4.2.1 is the last release. All future development happens in ansible.mysql.

# BEFORE (deprecated):
- community.mysql.mysql_db:
    name: myapp
    state: present

# AFTER (current):
- ansible.mysql.mysql_db:
    name: myapp
    state: present

Bulk Migration

# Find and replace in all playbooks
find . -name "*.yml" -o -name "*.yaml" | \
  xargs sed -i 's/community\.mysql\./ansible\.mysql\./g'

# Update requirements.yml
cat > collections/requirements.yml << EOF
collections:
  - name: ansible.mysql
    version: ">=5.0.0"
EOF

Managing Databases

---
- name: MySQL database management
  hosts: db_servers
  become: true

  vars:
    mysql_root_password: "{{ vault_mysql_root_pass }}"

  tasks:
    - name: Create application database
      ansible.mysql.mysql_db:
        name: myapp_production
        state: present
        encoding: utf8mb4
        collation: utf8mb4_unicode_ci
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Import database schema
      ansible.mysql.mysql_db:
        name: myapp_production
        state: import
        target: /tmp/schema.sql
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Create multiple databases
      ansible.mysql.mysql_db:
        name: "{{ item }}"
        state: present
        login_user: root
        login_password: "{{ mysql_root_password }}"
      loop:
        - myapp_production
        - myapp_staging
        - myapp_test

    - name: Backup database
      ansible.mysql.mysql_db:
        name: myapp_production
        state: dump
        target: "/backups/myapp_{{ ansible_date_time.date }}.sql.gz"
        login_user: root
        login_password: "{{ mysql_root_password }}"

Managing Users and Privileges

    - name: Create application user
      ansible.mysql.mysql_user:
        name: myapp
        password: "{{ vault_mysql_app_pass }}"
        host: "10.0.0.%"
        priv: "myapp_production.*:SELECT,INSERT,UPDATE,DELETE"
        state: present
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Create read-only user
      ansible.mysql.mysql_user:
        name: readonly
        password: "{{ vault_mysql_readonly_pass }}"
        host: "%"
        priv: "myapp_production.*:SELECT"
        state: present
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Create admin user with multiple privileges
      ansible.mysql.mysql_user:
        name: dba
        password: "{{ vault_mysql_dba_pass }}"
        host: "localhost"
        priv:
          "*.*": "ALL,GRANT"
        state: present
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Remove deprecated user
      ansible.mysql.mysql_user:
        name: old_app
        host_all: true
        state: absent
        login_user: root
        login_password: "{{ mysql_root_password }}"

Running Queries

    - name: Check replication status
      ansible.mysql.mysql_query:
        query: "SHOW REPLICA STATUS"
        login_user: root
        login_password: "{{ mysql_root_password }}"
      register: repl_status

    - name: Set global variable
      ansible.mysql.mysql_query:
        query: "SET GLOBAL max_connections = 500"
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Run multiple queries
      ansible.mysql.mysql_query:
        query:
          - "CREATE TABLE IF NOT EXISTS sessions (id INT PRIMARY KEY AUTO_INCREMENT)"
          - "CREATE INDEX idx_created ON sessions(created_at)"
        login_db: myapp_production
        login_user: root
        login_password: "{{ mysql_root_password }}"

Replication Setup

- name: Configure MySQL replication
  hosts: db_replicas
  become: true
  tasks:
    - name: Configure replica
      ansible.mysql.mysql_replication:
        mode: changeprimary
        primary_host: "{{ mysql_primary_host }}"
        primary_user: repl_user
        primary_password: "{{ vault_repl_password }}"
        primary_log_file: "{{ primary_log_file }}"
        primary_log_pos: "{{ primary_log_pos }}"
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Start replication
      ansible.mysql.mysql_replication:
        mode: startreplica
        login_user: root
        login_password: "{{ mysql_root_password }}"

MySQL Variables

    - name: Set server variables
      ansible.mysql.mysql_variables:
        variable: max_connections
        value: "500"
        login_user: root
        login_password: "{{ mysql_root_password }}"

    - name: Get variable value
      ansible.mysql.mysql_variables:
        variable: innodb_buffer_pool_size
        login_user: root
        login_password: "{{ mysql_root_password }}"
      register: buffer_pool

Module Reference

Module	Purpose
`ansible.mysql.mysql_db`	Create, drop, import, dump databases
`ansible.mysql.mysql_user`	Manage users and privileges
`ansible.mysql.mysql_query`	Execute SQL queries
`ansible.mysql.mysql_replication`	Configure replication
`ansible.mysql.mysql_variables`	Get/set server variables
`ansible.mysql.mysql_info`	Gather server information
`ansible.mysql.mysql_role`	Manage MySQL roles (8.0+)

Troubleshooting

Issue	Solution
`No module named pymysql`	Install: `pip install PyMySQL`
Access denied	Check `login_user`, `login_password`, and `host`
`community.mysql` not found	Migrate to `ansible.mysql` (see migration section)
Socket connection error	Set `login_unix_socket: /var/run/mysqld/mysqld.sock`
SSL required	Add `ca_cert`, `client_cert`, `client_key` parameters

Best Practices

Migrate from community.mysql now — it's end-of-life
Use Vault for all passwords — never plain text in playbooks
Restrict user hosts — use 10.0.0.% not %
Least privilege — application users get only needed permissions
Backup before changes — dump database before schema modifications
Use check_mode — test user/privilege changes safely

Conclusion

The ansible.mysql collection 5.0 is the future of MySQL automation in Ansible. If you're still using community.mysql, migrate now — it's a simple find-and-replace. The module names and parameters are identical; only the collection namespace changed.