Ansible Role: MySQL

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

Requirements

No special needs; this role requires root access. You can either run it in a playbook with become: yes, or call the role in your playbook like this:

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

Role Variables

Here are the variables you can use and their default values (check defaults/main.yml):

mysql_user_home: /root
mysql_user_name: root
mysql_user_password: root

This is the home directory where Python MySQL settings will be saved, used by Ansible when connecting to MySQL. Usually, this is the home directory of the user running the Ansible role. If you're not using the root user, you can set mysql_user_name and mysql_user_password.

mysql_root_home: /root
mysql_root_username: root
mysql_root_password: root

Details for the MySQL root user account.

mysql_root_password_update: false

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

Note: If you receive an error ERROR 1045 after a failed playbook run, it likely means the root password was never updated. Try removing the .my.cnf file in the configured mysql_user_home or update it and set password=''. Then run the playbook again with mysql_root_password_update set to yes.

mysql_enabled_on_startup: true

Indicates whether MySQL should start automatically when the server boots.

mysql_config_file: *depends on OS*
mysql_config_include_dir: *depends on OS*

The locations of the main MySQL configuration file and include directory.

overwrite_global_mycnf: true

If set to yes, the global my.cnf will be overwritten each time this role runs. You can set this to no to only create the file if it doesn't already exist.

mysql_config_include_files: []

A list of files that will override the default global my.cnf. Each file needs a "src" parameter to specify the path, and you can optionally set "force" to update the file each time Ansible runs.

mysql_databases: []

List of MySQL databases to create, which includes the values for name, encoding, collation, and replicate. You can also remove a database by setting state to absent.

mysql_users: []

List of MySQL users and their privileges. Each user will have values like name, host, password, and priv.

mysql_packages:
  - mysql
  - mysql-server

Packages to install specific to the OS. You might need to add others like mysql-devel.

mysql_enablerepo: ""

For RedHat/CentOS only, list any additional repositories you wish to enable.

mysql_python_package_debian: python3-mysqldb

For Ubuntu/Debian, you can specify the MySQL Python package here.

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

Default settings for MySQL connection.

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

Settings for MySQL logging.

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

Settings for logging slow queries. The log file will be created by this role, but ensure SELinux or AppArmor is configured to allow this path.

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

Additional settings controlling MySQL memory usage.

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 the server ID and the replication role for master/slave setups.

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 either MySQL or MariaDB. On RHEL/CentOS 7+, MariaDB replaces MySQL, but no adjustments are needed; all variables will still refer to 'mysql'.

Ubuntu 14.04 and 16.04 MariaDB Configuration

For Ubuntu, the package names are different, so adjust the mysql_packages variable like this:

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

Dependencies

No dependencies if you have ansible installed. If you only have ansible-core, you should include community.mysql in your collections/requirements.yml or install it manually.

Example Playbook

Here's a sample playbook:

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

In 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"

License

MIT / BSD

Author Information

This role was created in 2014 by Jeff Geerling, author of Ansible for DevOps.