Ansible Role: mysql

This Ansible role installs MySQL or MariaDB server on Fedora, Debian, and Ubuntu.

For Debian and Ubuntu, MySQL is installed from https://dev.mysql.com/downloads/repo/apt/, and for Fedora, it's from https://dev.mysql.com/downloads/repo/yum/. For MariaDB, repositories are used from https://downloads.mariadb.org/mariadb/repositories/.

Requirements

Make sure the firewalld service is running and the python-firewall package is installed to configure the firewall.

Role Variables

Here are the available variables and their default values:

• mysql_vendor: mysql

• mysql_origin: distribution

• mysql_upstream_version: ~ # MariaDB: 10.2, MySQL: 5.7

• mysql_root_password: "My $3cr3t password"

• mysql_import_timezones: yes

• mysql_bind_address: 0.0.0.0

• MySQL configuration settings:

  • mysql_key_buffer_size: 256M
  • mysql_max_allowed_packet: 1M
  • mysql_table_open_cache: 256
  • etc.

• Backup settings:

  • mysql_backup: no
  • mysql_backup_destination: /var/lib/backup/database
  • mysql_backup_password: "My $3cr3t password"

• mysql_firewall_zones: [] (empty by default)

• mysql_databases: [] (empty by default)

• mysql_users: [] (empty by default)

Vendor and Origin

Choose between installing MySQL and MariaDB from the distribution or from upstream packages by setting mysql_vendor (either mysql or mariadb) and mysql_origin (default is distribution).

If mysql_origin is set to upstream, you can specify the version with mysql_upstream_version.

Root User

The password in mysql_root_password will be the root password during installation. It’s important to change this to a secure password. The root user can only connect from the local host, and anonymous users or test databases will be removed.

Timezone Import

Timezone data will be imported by default. To change this, set mysql_import_timezones to no.

Networking

By default, the server listens on all IPv4 addresses. You can change this by setting mysql_bind_address to a specific address.

Options

There are various adjustable options with default values available in the configuration.

SSL

To enable SSL, set the variables mysql_ssl_ca, mysql_ssl_cert, and mysql_ssl_key.

Custom Configuration

You can add more configuration options in mysql_custom_config.

Backup

Backups are off by default but can be enabled by setting mysql_backup to yes, which creates daily backups in the specified mysql_backup_destination. A user for backups will be created automatically with the password in mysql_backup_password.

Firewall

Use mysql_firewall_zones to specify firewall zones that should allow access to MySQL. Only firewalld is currently supported.

Databases

You can define databases to create in mysql_databases. Each entry can specify:

• name: database name
• collation: default is utf8_general_ci
• encoding: default is utf8
• import_file: specify a path to an SQL file for importing

Users

You can define database users in mysql_users. Each user can specify:

• name: username
• password: user password
• host: where the user can connect from
• privileges: specify user privileges

Dependencies

None

Example Playbook

- hosts: all
  roles:
    - { role: mjanser.mysql }
  vars:
    mysql_root_password: secret
    mysql_databases:
      - name: my_db
    mysql_users:
      - name: my_user
        password: secret
        privileges: "my_db.*:ALL"

License

MIT