geerlingguy.mysql

Ansible Role: MySQL

This Ansible role installs and sets up MySQL or MariaDB on RHEL/CentOS or Debian/Ubuntu servers.

Requirements

You don’t need any special requirements, but this role needs root access. You can run it in a playbook with become: yes or include it like this in your playbook:

- hosts: database
  roles:
    - role: geerlingguy.mysql
      become: yes

Role Variables

Below are available variables with their default values (see defaults/main.yml):

mysql_user_home: /root
mysql_user_name: root
mysql_user_password: root

The home directory where Python MySQL settings are stored. Ansible uses this when connecting to MySQL. This should be the home directory of the user running the Ansible role. You can set mysql_user_name and mysql_user_password if not using the root account.

mysql_root_home: /root
mysql_root_username: root
mysql_root_password: root

Details for the MySQL root user account.

mysql_root_password_update: false

Whether to force an update of the MySQL root user's password. By default, the password changes only when MySQL is first set up. Set this to yes to force an update.

Note: If you see an error like ERROR 1045, your root password may not have been updated. Try removing the .my.cnf file in the configured mysql_user_home or update it with password='' (the default insecure password), and run the playbook with mysql_root_password_update set to yes.

mysql_enabled_on_startup: true

Whether MySQL should start automatically on boot.

mysql_config_file: *default value depends on OS*
mysql_config_include_dir: *default value depends on OS*

Main configuration file and include directory for MySQL.

overwrite_global_mycnf: true

If the global configuration file should be overwritten each time this role is run. Keep it as yes to use this role's variables for configuration.

mysql_config_include_files: []

Files that override the default global configuration file. Each file needs a "src" parameter pointing to the file's path and can have a "force" parameter.

mysql_databases: []

Databases to create in MySQL. A database has values for name, encoding (default is utf8), collation (default is utf8_general_ci), and replicate (default is 1). You can delete a database by setting state to absent.

mysql_users: []

MySQL users and their permissions. A user should have:

name
host (defaults to localhost)
password (can be plaintext or encrypted)
encrypted (defaults to no)
priv (defaults to *.*:USAGE)
append_privs (defaults to no)
state (defaults to present)

mysql_packages:
  - mysql
  - mysql-server

Packages to install (these defaults are for RedHat/CentOS). You may need to add extra packages like mysql-devel.

mysql_enablerepo: ""

(RedHat/CentOS only) You can specify additional repositories if needed (e.g., remi,epel).

mysql_python_package_debian: python3-mysqldb

(Ubuntu/Debian only) Override the MySQL Python package if necessary.

mysql_port: "3306"
mysql_bind_address: '0.0.0.0'
mysql_datadir: /var/lib/mysql
mysql_socket: *default value depends on OS*
mysql_pid_file: *default value depends on OS*

Default connection configuration for MySQL.

mysql_log_file_group: mysql *adm on Debian*
mysql_log: ""
mysql_log_error: *default value depends on OS*
mysql_syslog_tag: *default value depends on OS*

Logging configuration for MySQL. Set mysql_log or mysql_log_error to syslog to log to syslog.

mysql_slow_query_log_enabled: false
mysql_slow_query_log_file: *default value depends on OS*
mysql_slow_query_time: 2

Settings for logging slow queries. If using SELinux or AppArmor, ensure the log path is allowed.

mysql_key_buffer_size: "256M"
mysql_max_allowed_packet: "64M"
mysql_table_open_cache: "256"
...

Other settings that control MySQL's memory and behavior. The default values are meant for a server with 512 MB RAM.

mysql_server_id: "1"
mysql_max_binlog_size: "100M"
mysql_binlog_format: "ROW"
mysql_expire_logs_days: "10"
mysql_replication_role: ''
mysql_replication_master: ''
mysql_replication_user: {}

Settings for replication. Set mysql_server_id and mysql_replication_role based on whether the server is a master or slave.

mysql_hide_passwords: false

Should tasks' output with passwords be hidden during execution?

Installing Later Versions of MySQL on CentOS 7

To install MySQL from the official repository instead of the default MariaDB, add these pre_tasks to your playbook:

  pre_tasks:
    - name: Install the MySQL repo.
      yum:
        name: http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
        state: present
      when: ansible_os_family == "RedHat"
  
    - name: Override variables for MySQL (RedHat).
      set_fact:
        mysql_daemon: mysqld
        mysql_packages: ['mysql-server']
        mysql_log_error: /var/log/mysqld.err
        mysql_syslog_tag: mysqld
        mysql_pid_file: /var/run/mysqld/mysqld.pid
        mysql_socket: /var/lib/mysql/mysql.sock
      when: ansible_os_family == "RedHat"

Using MariaDB

This role works with both MySQL and compatible MariaDB versions. On RHEL/CentOS 7+, MariaDB replaces MySQL by default. No changes needed; variables still use 'mysql'.

Ubuntu 14.04 and 16.04 MariaDB Configuration

Package names differ on Ubuntu, so change the mysql_package variable like this:

mysql_packages:
  - mariadb-client
  - mariadb-server
  - python-mysqldb

Dependencies

If you have ansible installed (e.g., pip3 install ansible), none are needed.

If you only have ansible-core, you’ll need to include community.mysql in your collections/requirements.yml or install it with ansible-galaxy collection install community.mysql.

Example Playbook

- hosts: db-servers
  become: yes
  vars_files:
    - vars/main.yml
  roles:
    - { role: geerlingguy.mysql }

Inside vars/main.yml:

mysql_root_password: super-secure-password
mysql_databases:
  - name: example_db
    encoding: latin1
    collation: latin1_general_ci
mysql_users:
  - name: example_user
    host: "%"
    password: similarly-secure-password
    priv: "example_db.*:ALL"