Ansible Role mariadb_server
This role installs and configures a MariaDB server.
It also tunes the following Kernel settings:
fs.aio-max-nr:1048576vm.swapiness:10
Note that this role does NOT let you specify a particular MariaDB server version. It simply installs the latest available MariaDB server version from the repos configured in the system. If you want or need to install a specific MariaDB server version, use the linuxfabrik.lfops.repo_mariadb beforehand.
This role is only compatible with the following MariaDB versions:
10.3
10.4
10.5
10.6 (preferred - long-term support MariaDB stable)
We will add the next long-term support release as soon as it’s available (therefore currently not implementing 10.7+).
Runs on
RHEL 8 (and compatible)
Mandatory Requirements
For some machines you might need to set
ansible_python_interpreter: '/usr/bin/python3'to prevent the error messageA MySQL module is required: for Python 2.7 either PyMySQL, or MySQL-python, or for Python 3.X mysqlclient or PyMySQL. Consider setting ansible_python_interpreter to use the intended Python version..On RHEL-compatible systems, enable the EPEL repository. This can be done using the linuxfabrik.lfops.repo_epel role.
Install the
python3-PyMySQLlibrary. This can be done using the linuxfabrik.lfops.python role.
Optional Requirements
Enable the official MariaDB Package Repository. This can be done using the linuxfabrik.lfops.repo_mariadb role.
Enable the a repository for mydumper. This can be done using the linuxfabrik.lfops.repo_mydumper role.
Mandatory Role Variables
Variable |
Description |
|---|---|
|
The main user account for the database administrator. To create additional ones, use the |
Example:
# mandatory
mariadb_server__admin_user:
username: 'mariadb-admin'
password: 'linuxfabrik'
Recommended Role Variables
Variable |
Description |
Default Value |
|---|---|---|
|
User to whom backup privileges are granted to. Setting this user automatically enables daily MariaDB-Dumps. Subkeys: |
unset |
# recommended
mariadb_server__dump_user:
username: 'mariadb-backup'
password: 'linuxfabrik'
state: 'present'
Optional Role Variables - Specific to this role
Variable |
Description |
Default Value |
|---|---|---|
|
List of dictionaries of databases to create. Subkeys: |
|
|
Dump output directory name. |
|
|
Name of the „mydumper“ package. Also takes an URL to GitHub if no repo server is available, see the example below. |
|
|
The |
|
|
The number of threads to use for dumping data. |
|
|
Enables or disables the Systemd unit. |
|
|
Number. Log files are rotated |
|
|
Skip the deployment of the MariaDB sys schema (a collection of views, functions and procedures to help MariaDB administrators get insight in to MariaDB Database usage). If a |
|
|
Controls the Systemd service. One of |
|
|
List of dictionaries of users to create (this is NOT used for the first DBA user - here, use |
|
# optional - role variables
mariadb_server__databases__host_var:
- name: 'mydb'
collation: 'utf8mb4_unicode_ci'
encoding: 'utf8mb4'
state: 'present'
mariadb_server__dump_directory: '/backup/mariadb-dump'
mariadb_server__dump_mydumper_package: 'https://github.com/mydumper/mydumper/releases/download/v0.12.6-1/mydumper-0.12.6-1.el8.x86_64.rpm'
mariadb_server__dump_on_calendar: '*-*-* 21:{{ 59 | random(start=0, seed=inventory_hostname) }}:00'
mariadb_server__dump_threads: 4
mariadb_server__enabled: true
mariadb_server__logrotate: 7
mariadb_server__skip_sys_schema: false
mariadb_server__state: 'started'
mariadb_server__users__host_var:
- username: 'user1'
host: 'localhost'
password: 'linuxfabrik'
priv:
- '{{ icingaweb2_db }}.*:SELECT,INSERT,UPDATE,DELETE,DROP,CREATE VIEW,INDEX,EXECUTE'
- 'wiki.*:ALL'
state: 'present'
- username: 'mariadb-dump'
host: '127.0.0.1'
password: 'linuxfabrik'
priv:
- '*.*:event,lock tables,reload,select,show view,super,trigger'
state: 'present'
Optional Role Variables - mariadb_server__cnf_* Config Directives
Variables for z00-linuxfabrik.cnf directives and their default values, defined and supported by this role.
Role Variable |
Documentation |
Default Value (v10.6) |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
400 |
|
|
|
|
|
|
Example:
# optional - cnf directives
mariadb_server__cnf_bulk_insert_buffer_size__host_var: 8388608
mariadb_server__cnf_character_set_server__host_var: 'utf8mb4'
mariadb_server__cnf_collation_server__host_var: 'utf8mb4_unicode_ci'
mariadb_server__cnf_expire_logs_days__host_var: 0.000000
mariadb_server__cnf_innodb_buffer_pool_size__host_var: '128M'
mariadb_server__cnf_innodb_file_per_table__host_var: 'ON'
mariadb_server__cnf_innodb_flush_log_at_trx_commit__host_var: 1
mariadb_server__cnf_innodb_io_capacity__host_var: 200
mariadb_server__cnf_innodb_log_file_size__host_var: '96M'
mariadb_server__cnf_interactive_timeout__host_var: 28800
mariadb_server__cnf_join_buffer_size__host_var: '256K'
mariadb_server__cnf_log_error__host_var: '/var/log/mariadb/mariadb.log'
mariadb_server__cnf_lower_case_table_names__host_var: 0
mariadb_server__cnf_max_allowed_packet__host_var: '16M'
mariadb_server__cnf_max_connections__host_var: 64
mariadb_server__cnf_max_heap_table_size__host_var: '16M'
mariadb_server__cnf_performance_schema__host_var: 'ON'
mariadb_server__cnf_query_cache_limit__host_var: '1M'
mariadb_server__cnf_query_cache_size__host_var: 0
mariadb_server__cnf_query_cache_type__host_var: 'OFF'
mariadb_server__cnf_skip_name_resolve__host_var: 'ON'
mariadb_server__cnf_slow_query_log__host_var: 0
mariadb_server__cnf_slow_query_log_file__host_var: '/var/log/mariadb/mariadb-slowquery.log'
mariadb_server__cnf_sql_mode__host_var: 'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
mariadb_server__cnf_table_definition_cache__host_var: 400
mariadb_server__cnf_tmp_table_size__host_var: '16M'
mariadb_server__cnf_wait_timeout__host_var: 28800