Ansible Role: MySQL

Installs MySQL/Mariadb server on RHEL/CentOS or Debian/Ubuntu servers. Derived from the geerlingguy.mysql role, this fork provides the following additional functionality:

• Secure TLS setup
• Encryption at rest setup If you don't need these you would be advised to use the base geerlingguy.mysql role

Requirements

No special requirements; note that this role requires root access, so either run it in a playbook with a global become: true, or invoke the role in your playbook like:

- hosts: database
  roles:
    - role: ansible-role-mysql
      become: true

Role Variables

Available variables are listed below, along with default values (see defaults/main.yml):

    mysql_user_home: /root
    mysql_user_name: root
    mysql_user_password: root

The home directory inside which Python MySQL settings will be stored, which Ansible will use when connecting to MySQL. This should be the home directory of the user which runs this Ansible role. The mysql_user_name and mysql_user_password can be set if you are running this role under a non-root user account and want to set a non-root user.

mysql_root_home: /root
mysql_root_username: root
mysql_root_password: root

The MySQL root user account details.

mysql_root_password_update: false

Whether to force update the MySQL root user's password. By default, this role will only change the root user's password when MySQL is first configured. You can force an update by setting this to true.

Note: If you get an error like ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) after a failed or interrupted playbook run, this usually means the root password wasn't originally updated to begin with. Try either removing the .my.cnf file inside the configured mysql_user_home or updating it and setting password='' (the insecure default password). Run the playbook again, with mysql_root_password_update set to true, and the setup should complete.

Note: If you get an error like ERROR 1698 (28000): Access denied for user 'root'@'localhost' (using password: YES) when trying to log in from the CLI you might need to run as root or sudoer.

mysql_enabled_on_startup: true

Whether MySQL should be enabled on startup.

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

The main my.cnf configuration file and include directory.

overwrite_global_mycnf: yes

Whether the global my.cnf should be overwritten each time this role is run. Setting this to no tells Ansible to only create the my.cnf file if it doesn't exist. This should be left at its default value (yes) if you'd like to use this role's variables to configure MySQL.

mysql_config_include_files: []

A list of files that should override the default global my.cnf. Each item in the array requires a "src" parameter which is a path to a file. An optional "force" parameter can force the file to be updated each time ansible runs.

mysql_databases: []

The MySQL databases to create. A database has the values name, encoding (defaults to utf8), collation (defaults to utf8_general_ci) and replicate (defaults to 1, only used if replication is configured). The formats of these are the same as in the mysql_db module.

mysql_users: []

The MySQL users and their privileges. A user has the values:

• name
• host (defaults to localhost)
• password (can be plaintext or encrypted—if encrypted, set encrypted: yes)
• encrypted (defaults to no)
• priv (defaults to *.*:USAGE)
• append_privs (defaults to no)
• state (defaults to present)

The formats of these are the same as in the mysql_user module.

You can also supply a hashed password for the user (generated by SELECT PASSWORD('mypass');) If you do then set the 'encrypted: True' is set for the user.

mysql_packages:
  - mysql
  - mysql-server

(OS-specific, RedHat/CentOS defaults listed here) Packages to be installed. In some situations, you may need to add additional packages, like mysql-devel.

mysql_enablerepo: ""

(RedHat/CentOS only) If you have enabled any additional repositories (might I suggest geerlingguy.repo-epel or geerlingguy.repo-remi), those repositories can be listed under this variable (e.g. remi,epel). This can be handy, as an example, if you want to install later versions of MySQL.

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 MySQL connection configuration.

mysql_log: ""
mysql_log_error: *default value depends on OS*
mysql_syslog_tag: *default value depends on OS*

MySQL logging configuration. Setting mysql_log (the general query log) or mysql_log_error to syslog will make MySQL log to syslog using the mysql_syslog_tag.

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

Slow query log settings. Note that the log file will be created by this role, but if you're running on a server with SELinux or AppArmor, you may need to add this path to the allowed paths for MySQL, or disable the mysql profile. For example, on Debian/Ubuntu, you can run sudo ln -s /etc/apparmor.d/usr.sbin.mysqld /etc/apparmor.d/disable/usr.sbin.mysqld && sudo service apparmor restart.

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

The rest of the settings in defaults/main.yml control MySQL's memory usage and some other common settings. The default values are tuned for a server where MySQL can consume ~512 MB RAM, so you should consider adjusting them to suit your particular server better.

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: []

Replication settings. Set mysql_server_id and mysql_replication_role by server (e.g. the master would be ID 1, with the mysql_replication_role of master, and the slave would be ID 2, with the mysql_replication_role of slave). The mysql_replication_user uses the same keys as mysql_users, and is created on master servers, and used to replicate on all the slaves.

Performance

There are a number of settings that affect performance

Transaction Isolation

mysql_transaction_isolation: 'READ-COMMITTED' The default is REPEATABLE-READ but this affects performamce and is rarely needed READ-COMMITTED is recommended for most installations

Autoincrement id

When autoincrement ids are set on bulk inserts the table can be locked It is better to have the ids obtained individually especially when it can't be known how nmany records are inserted the default is 1 mysql_innodb_autoinc_lock_mode: '2' https://mariadb.com/kb/en/library/auto_increment-handling-in-innodb/

Both of the above can cause issues with statement based binlogs (the default) https://mariadb.com/kb/en/library/binary-log-formats/ So if you set the above then also set the binlog format to ROW mysql_binlog_format: 'ROW' So a good performance setting would be: mysql_transaction_isolation: 'READ-COMMITTED' mysql_innodb_autoinc_lock_mode: '2' mysql_binlog_format: 'ROW' Which would set the following in the my.cnf (not in this order as other settings are made too)

[mysqld]
innodb_autoinc_lock_mode=2
binlog_format=row
transaction-isolation=READ-COMMITTED

Note that last setting actually sets the variable tx_isolation This often casues confusion

Encryption at rest

This sets up database encryption

Secure TLS communication

This sets up certificates for SSL/TLS communication with clients Turn on this setup with

mysql_tls_setup: true

Takes the following vars

# on | off
mysql_tls_state: off  <-- turn on or off  ssl in the db
mysql_tls_path: '/etc/mysql/ssl'
mysql_tls_server_config_file:
   - {name: '55-server-tls.cnf', path: '/etc/mysql/mariadb.conf.d'}
mysql_tls_client_config_file:
   - {name: '55-clients-tls.cnf', path: '/etc/mysql/mariadb.conf.d'}

Whether to generate the certificates on the server False means you will provide your own (useful for clusters)

mysql_tls_generate_certs: true

If generating your own then you need a prexix for the certs This will prexis the CA, Server and client certificates and keys

mysql_tls_cert_prefix: mysqldb

If providing your own then provide a list of at least the following: Note the file name should be the full path to the source file

mysql_tls_cert_files:
  - name: 'mysqldbdb-cert.pem'                          <-- the ssl-ca
    src:  '{{ inventory_dir }}/common/files/ssl/DB-TLS'
  - name: 'mysqldbdbserver-cert.pem'                    <-- the ssl server cert
    src:  '{{ inventory_dir }}/common/files/ssl/DB-TLS'
  - name: 'mysqldbdbserver-key.pem'                     <-- the ssl server key
    src: '{{ inventory_dir }}/common/files/ssl/DB-TLS'

and possibly the client files

 - name: 'mysqldbdbclient-cert.pem'                     <-- the ssl client cert
    src: '{{ inventory_dir }}/common/files/ssl/DB-TLS'
  - name: 'mysqldbdbclient-key.pem'                     <-- the ssl client key
    src: '{{ inventory_dir }}/common/files/ssl/DB-TLS'

Later versions of MySQL on CentOS 7

If you want to install MySQL from the official repository instead of installing the system default MariaDB equivalents, you can add the following pre_tasks task in 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"

MariaDB usage

This role works with either MySQL or a compatible version of MariaDB. On RHEL/CentOS 7+, the mariadb database engine was substituted as the default MySQL replacement package. No modifications are necessary though all of the variables still reference 'mysql' instead of mariadb.

Ubuntu 14.04 and 16.04 MariaDB configuration

On Ubuntu, the package names are named differently, so the mysql_package variable needs to be altered. Set the following variables (at a minimum):

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

Dependencies

None.

Example Playbook

- hosts: db-servers
  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"

License

MIT / BSD

Author Information

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