mysql
Ansible Role: mysql
An Ansible role that installs MySQL or MariaDB server on Fedora, Debian and Ubuntu.
Upstream versions of MySQL will be installed from https://dev.mysql.com/downloads/repo/apt/ for Debian and Ubuntu and from https://dev.mysql.com/downloads/repo/yum/ for Fedora.
For upstream MariaDB the repositories from https://downloads.mariadb.org/mariadb/repositories/ will be used.
Requirements
For configuring the firewall the service firewalld
has to run and the package python-firewall
needs to be installed.
Role Variables
Available variables are listed below, along with 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_key_buffer_size: 256M
mysql_max_allowed_packet: 1M
mysql_table_open_cache: 256
mysql_sort_buffer_size: 1M
mysql_read_buffer_size: 1M
mysql_read_rnd_buffer_size: 4M
mysql_net_buffer_length: 1M
mysql_myisam_sort_buffer_size: 64M
mysql_thread_cache_size: 8
mysql_query_cache_size: 16M
mysql_max_connections: ~
mysql_thread_concurrency: ~
mysql_ssl_ca: ~
mysql_ssl_cert: ~
mysql_ssl_key: ~
mysql_custom_config: ~
mysql_backup: no
mysql_backup_destination: /var/lib/backup/database
mysql_backup_password: "My $3cr3t password"
mysql_firewall_zones: []
mysql_databases: []
mysql_users: []
Vendor and origin
This Ansible role supports the installation of MySQL and MariaDB from distribution or upstream packages.
The vendor can be set in the variable mysql_vendor
, which supports the values mysql
and mariadb
.
The default vendor is mysql
.
The variable mysql_origin
defines where the packages come from.
The default value distribution
means that the packages from the distribution will be installed.
With this configuration the distribution defines the version and cannot be changed.
If the variable mysql_origin
is set to upstream
the package from MySQL/MariaDB will be installed.
This is done using the repositories from https://downloads.mariadb.org/mariadb/repositories/.
In this setup the version can be specified in the variable mysql_upstream_version
.
Root user
The password defined in the variable mysql_root_password
will be set as the root password during installation.
This should be changed to a secure password.
The root user will only be able to connect from the local host. All remote host entries will be removed.
Additionally the anonymous users and the test database will be removed.
Timezone import
Timezone data will be imported by default (see https://dev.mysql.com/doc/refman/5.7/en/mysql-tzinfo-to-sql.html).
To change this behavior adjust the variable mysql_import_timezones
to no
.
Networking
By default the server listens on all IPv4 interfaces on the host.
This can changed with setting the variable mysql_bind_address
to another address than 0.0.0.0
.
Options
There are some options which can be adjusted and have default values. See above or in defaults/tasks.yml and the documentation for more information.
SSL
To enable SSL support the variables mysql_ssl_ca
, mysql_ssl_cert
and mysql_ssl_key
must be configured.
Custom configuration
Additional configuration can be defined in the variable mysql_custom_config
, for example:
mysql_custom_config: |
skip_name_resolve
skip-locking
Backup
While backups are disabled by default, an automatic daily backup script can be enabled by setting mysql_backup
to yes
.
It simply dumps all databases to the directory defined in mysql_backup_destination
as an SQL file with a timestamp.
Additionally it links the latest backup file per database to [database]_latest.sql
.
A database user for the backup will be created automatically with the password set in the variable mysql_backup_password
.
Be sure to set this variable to a sensible password, as it is empty be default and an error will be thrown if backups are enabled and this password is not set.
Firewall
The variable mysql_firewall_zones
can be used to declare firewall zones in which nginx should be accessible.
This means the ports 3306/tcp
will be opened.
Currently only firewalld
is supported which is default on Fedora.
Databases
Databases to create can be defined in the variable mysql_databases
.
Possible values for each entry in mysql_databases
are, along with default values:
name: ~
collation: utf8_general_ci
encoding: utf8
import_file: ~
Name
In the key name
you can set the name of the database.
Collation and encoding
To adjust the collection and encoding, you can set them in collaction
and encoding
.
Import
There is the possibility to set a path to an SQL file in import_file
which will be imported after creating the database.
This can be used for importing backups.
If the database already exists, nothing will be imported.
Users
Database users can be defined in the variable mysql_users
.
Possible values for each entry in mysql_users
are, along with default values:
name: ~
password: ~
host: localhost
privileges: "*.*:USAGE"
append_privileges: no
Credentials
The keys name
and password
define the credentials of the user.
The user can only access the server from the host set in host
. A value of %
will allow it from every host.
Privileges
Privileges can be defined in privileges
as a string, see MySQL or MariaDB manual for more information.
If append_privileges
is set to yes
, the defined privileges will be appended to the already existing ones.
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
ansible-galaxy install mjanser/ansible-role-mysql