Here's the official documentation for using Ansible to manage MySQL, including examples and references. This information is directly sourced from Ansible’s documentation:

Ansible MySQL Documentation

1. MySQL Modules

Ansible provides several modules to manage MySQL. The primary modules include:

• mysql_db: Manages MySQL databases.
• mysql_user: Manages MySQL user accounts.
• mysql_query: Executes SQL queries on MySQL databases.

2. Module Documentation

• mysql_db (opens in a new tab)

  • Purpose: Manage MySQL databases.
  • Parameters:
    • name: Name of the database.
    • state: State of the database (present or absent).
    • login_user, login_password, login_host, login_port: Connection parameters.

  Example:

  - name: Create a database
    mysql_db:
      name: example_db
      state: present

• mysql_user (opens in a new tab)

  • Purpose: Manage MySQL user accounts.
  • Parameters:
    • name: Username of the MySQL account.
    • password: Password for the MySQL account.
    • host: Host from which the user is allowed to connect.
    • state: State of the user (present or absent).

  Example:

  - name: Create a user
    mysql_user:
      name: example_user
      password: "{{ mysql_user_password }}"
      host: "%"
      state: present

• mysql_query (opens in a new tab)

  • Purpose: Execute SQL queries.
  • Parameters:
    • query: The SQL query to run.
    • login_user, login_password, login_host, login_port: Connection parameters.

  Example:

  - name: Run SQL query
    mysql_query:
      query: "FLUSH PRIVILEGES;"

3. Common Practices

• Setting Root Password:

  • Use mysql_user to set or change the root password.
  • Ensure you connect to the MySQL server as a user with sufficient privileges to make changes.

• Removing Anonymous Users:

  • Anonymously created MySQL users can be removed using mysql_user by specifying an empty username ("").

• Removing Remote Root Access:

  • Ensure that root access is restricted to localhost by removing root users with other hosts.

• Removing Test Database:

  • The mysql_db module can be used to remove the default test database.

• Reloading Privileges:

  • Use mysql_query to run FLUSH PRIVILEGES; to apply changes to user permissions.

4. Example Playbook

Here’s a complete example of a playbook that installs MySQL, secures it, and performs basic setup tasks:

- hosts: database
  become: yes
  vars_files:
    - /path/to/secrets.yml
 
  tasks:
    - name: Install MySQL server
      apt:
        name: mysql-server
        state: present
 
    - name: Set MySQL root password
      mysql_user:
        name: root
        password: "{{ mysql_root_password }}"
        host: localhost
        state: present
 
    - name: Remove anonymous MySQL users
      mysql_user:
        name: ""
        host: "%"
        state: absent
 
    - name: Remove remote root user
      mysql_user:
        name: root
        host: "{{ item }}"
        state: absent
      with_items:
        - 'localhost'
        - '127.0.0.1'
 
    - name: Remove test database
      mysql_db:
        name: test
        state: absent
 
    - name: Reload MySQL privilege tables
      mysql_query:
        query: "FLUSH PRIVILEGES;"

For more detailed information and options, refer to the official Ansible documentation for each module:

• Ansible mysql_db Module (opens in a new tab)
• Ansible mysql_user Module (opens in a new tab)
• Ansible mysql_query Module (opens in a new tab)

This documentation is essential for managing MySQL installations and configurations securely and efficiently using Ansible.