Ansible Role mariadb_server
This role installs and configures a MariaDB server.
It also tunes the following Kernel settings:
fs.aio-max-nr
:1048576
sunrpc.tcp_slot_table_entries
: 128vm.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 compatible with the following MariaDB versions:
10.11 LTS
10.6 LTS
10.5
10.4
10.3
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-PyMySQL
library. 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'
# old_password: 'previous-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 |
Example:
# 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: |
|
|
Compress output files. One of |
|
|
Dump output directory name. |
|
|
Set long query timer in seconds. |
|
|
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 |
|
Example:
# optional - role variables
mariadb_server__databases__host_var:
- name: 'mydb'
collation: 'utf8mb4_unicode_ci'
encoding: 'utf8mb4'
state: 'present'
mariadb_server__dump_compress: 'GZIP'
mariadb_server__dump_directory: '/backup/mariadb-dump'
mariadb_server__dump_long_query_guard: 60
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 |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
400 |
|
|
|
|
|
|
Example:
# optional - cnf directives
mariadb_server__cnf_bulk_insert_buffer_size__host_var: '8M'
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: 'OFF'
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
Optional Role Variables - mariadb_server__cnf_*
Config Directives for DARE
To enable Data Encryption at rest (DARE) using the File Key Management plugin, you have to define the DARE keys in your inventory like so (every encryption key itself needs to be provided in hex-encoded form using 128-bit/16-byte/32 chars, 192-bit/24-byte/48 chars or 256-bit/32-byte/64 chars):
# using 256-bit/32-byte/64 chars encryption keys
mariadb_server__dare_keys:
- key_id: 1
key: 'a7addd9adea9978fda19f21e6be987880e68ac92632ca052e5bb42b1a506939a'
- key_id: 2
key: '49c16acc2dffe616710c9ba9a10b94944a737de1beccb52dc1560abfdd67388b'
- key_id: 100
key: '8db1ee74580e7e93ab8cf157f02656d356c2f437d548d5bf16bf2a56932954a3'
Variables for z00-linuxfabrik.cnf
directives and their default values, defined and supported by this role for DARE.
Role Variable |
Documentation |
Default Value |
---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example:
# optional - cnf directives
mariadb_server__cnf_encrypt_binlog: 'ON'
mariadb_server__cnf_encrypt_tmp_files: 'ON'
mariadb_server__cnf_file_key_management_encryption_algorithm: 'AES_CTR'
mariadb_server__cnf_file_key_management_filename: '/etc/my.cnf.d/keyfile'
mariadb_server__cnf_innodb_default_encryption_key_id: 1
mariadb_server__cnf_innodb_encrypt_log: 'ON'
mariadb_server__cnf_innodb_encrypt_tables: 'ON'
mariadb_server__cnf_innodb_encrypt_temporary_tables: 'ON'
mariadb_server__cnf_innodb_encryption_rotate_key_age: 1
mariadb_server__cnf_innodb_encryption_threads: 4
mariadb_server__cnf_plugin_load_add: 'file_key_management'
Troubleshooting
Q: A 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.
A: Install the python3-PyMySQL
library. This can be done using the linuxfabrik.lfops.python role, or run the full mariadb_server
playbook, not limited by some tags.