Ansible Role mariadb_server
This role installs and configures a MariaDB server.
It also tunes the following Kernel settings:
fs.aio-max-nr = 1048576sunrpc.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.5
10.6 LTS
10.11 LTS
11.4 LTS
The role provides the mariadb_server:upgrade tag to update the MariaDB server. The tag upgrades to the newest available version, therefore make sure to switch the module stream or update the repository (optionally using the linuxfabrik.lfops.repo_mariadb role).
By default, Ansible runs each task on all hosts affected by a play before starting the next task on any host, using 5 forks. This role manages one MariaDB host at a time (serially), e.g. to make cluster management as reliable and save as possible.
Hardenings that can be covered by this role: See STIGs
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'
# old_password: 'previous-linuxfabrik'
Recommended Role Variables
Variable |
Description |
Default Value |
|---|---|---|
|
String. For mydumper: 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: |
|
|
Octal. Mode (permissions) of the MariaDB |
|
|
String/Bool. For mydumper: Compress output files. One of |
|
|
String. For mydumper: Dump output directory name. |
|
|
Integer. For mydumper: Set long query timer in seconds. |
|
|
String. For mydumper: Name of the „mydumper“ package. Also takes an URL to GitHub if no repo server is available, see the example below. |
|
|
String. For mydumper: The |
|
|
String. For mydumper: Any additional parameters you want to add. |
|
|
Integer. For mydumper: The number of threads to use for dumping data. |
|
|
Enables or disables the Systemd unit. |
|
|
Integer. Log files are rotated |
|
|
Integer. Systemd: LimitMEMLOCK setting. |
|
|
Integer. Systemd: Resource limit directive for the number of file descriptors. |
|
|
String. Systemd: Configures the time to wait for start-up. If the MariaDB server does not signal start-up completion within the configured time, the service will be considered failed and will be shut down again. |
|
|
String. Systemd: First, it configures the time to wait for the ExecStop= command. Second, it configures the time to wait for the MariaDB server itself to stop. If the MariaDB server doesn’t terminate in the specified time, it will be forcibly terminated by SIGKILL. |
|
|
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__datadir_mode__host_var: 0o750
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_raw: ''
mariadb_server__dump_threads: 4
mariadb_server__enabled: true
mariadb_server__logrotate: 7
mariadb_server__service_limit_memlock: 524288
mariadb_server__service_limit_nofile: 32768
mariadb_server__service_timeout_start_sec: '15min'
mariadb_server__service_timeout_stop_sec: '15min'
mariadb_server__skip_sys_schema: false
mariadb_server__state: 'started'
mariadb_server__users__host_var:
- username: 'user1'
host: 'localhost'
password: 'linuxfabrik'
priv:
- '{{ icingaweb2_db }}.*:create view,delete,drop,execute,index,insert,select,update'
- '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'
# user with client TLS auth
- username: 'admin1'
host: 'localhost'
tls_requires:
subject: '/CN=admin1'
priv:
- '*.*:ALL'
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 |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
10.11-: |
|
|
10.11-: |
|
|
String. mariadb.com |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
mariadb.com. Attention: the variable is not a boolean! Instead it either requires a string to enable it, or has to be unset. For convenience this role unsets the variable if it is set to |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
RHEL: |
|
|
|
|
|
400 |
|
|
|
|
|
|
|
|
|
Example:
# optional - cnf directives
mariadb_server__cnf_bind_address__host_var: '0.0.0.0'
mariadb_server__cnf_binlog_format__host_var: 'MIXED'
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_datadir__host_var: '/data/mariadb'
mariadb_server__cnf_default_storage_engine__host_var: 'InnoDB'
mariadb_server__cnf_expire_logs_days__host_var: 0.000000
mariadb_server__cnf_general_log__host_var: 'OFF'
mariadb_server__cnf_general_log_file__host_var: '/var/log/mariadb/mariadb-general.log'
mariadb_server__cnf_innodb_autoinc_lock_mode__host_var: 2
mariadb_server__cnf_innodb_buffer_pool_size__host_var: '{{ (ansible_facts["memtotal_mb"] * 0.8) | int }}M'
mariadb_server__cnf_innodb_doublewrite__host_var: 1
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_bin__host_var: 'log_bin'
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_socket__host_var: '/var/run/mariadb/mariadb.sock' # use /var/run instead of /run to avoid collisions with selinux fcontexts (`File spec /run/mariadb/mariadb\.sock conflicts with equivalency rule '/run /var/run'`)
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_tls_version__host_var: 'TLSv1.2,TLSv1.3'
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 SSL/TLS
This are a several options and system variables related to SSL/TLS Connections.
Variables for z00-linuxfabrik.cnf directives and their default values, defined and supported by this role for SSL/TLS.
Role Variable |
Documentation |
Default Value |
|---|---|---|
|
Client configuration. Path to the SSL CA file. Will be used to verify the server cert. |
|
|
Client configuration. Path to an SSL cert that will be used to authenticate against the server. |
|
|
Client configuration. Path to an SSL key that will be used to authenticate against the server. |
|
|
Boolean. Client configuration. Enables/disables verification of the server cert. |
10.11-: |
|
|
|
|
mariadb.com. Note: the CA should be world-readable (e.g. so that both the server & client can access it). |
|
|
mariadb.com. Note: the role changes the owner to |
|
|
mariadb.com. Note: the role changes the owner to |
|
Example:
# optional - SSL/TLS directives
mariadb_server__cnf_client_ssl_ca__host_var: '/etc/pki/tls/certs/ca.crt'
mariadb_server__cnf_client_ssl_cert__host_var: '/etc/pki/tls/certs/admin1.crt'
mariadb_server__cnf_client_ssl_key__host_var: '/etc/pki/tls/private/admin1.key'
mariadb_server__cnf_client_ssl_verify_server_cert__host_var: true
mariadb_server__cnf_require_secure_transport__host_var: 'ON'
mariadb_server__cnf_ssl_ca__host_var: '/etc/pki/tls/certs/ca.crt'
mariadb_server__cnf_ssl_cert__host_var: '/etc/pki/tls/certs/mariadb-server.crt'
mariadb_server__cnf_ssl_key__host_var: '/etc/pki/tls/private/mariadb-server.key'
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 - DARE directives
mariadb_server__cnf_encrypt_binlog__host_var: 'ON'
mariadb_server__cnf_encrypt_tmp_files__host_var: 'ON'
mariadb_server__cnf_file_key_management_encryption_algorithm__host_var: 'AES_CTR'
mariadb_server__cnf_file_key_management_filename__host_var: '/etc/my.cnf.d/keyfile'
mariadb_server__cnf_innodb_default_encryption_key_id__host_var: 1
mariadb_server__cnf_innodb_encrypt_log__host_var: 'ON'
mariadb_server__cnf_innodb_encrypt_tables__host_var: 'ON'
mariadb_server__cnf_innodb_encrypt_temporary_tables__host_var: 'ON'
mariadb_server__cnf_innodb_encryption_rotate_key_age__host_var: 1
mariadb_server__cnf_innodb_encryption_threads__host_var: 4
mariadb_server__cnf_plugin_load_add__host_var: 'file_key_management'
Optional Role Variables - mariadb_server__cnf_* Config Directives for MariaDB Audit Plugin
This are a several options and system variables related to the MariaDB Audit Plugin.
server_audit is set to FORCE_PLUS_PERMANENT, which always enables the plugin. If the plugin cannot be initialized, then the server will fail to start with an error. In addition, the plugin cannot be uninstalled with UNINSTALL SONAME or UNINSTALL PLUGIN while the server is running.
Role Variable |
Documentation |
Default Value |
|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Example:
# optional - MariaDB Audit Plugin directives
mariadb_server__cnf_server_audit_events: 'CONNECT,QUERY_DDL'
mariadb_server__cnf_server_audit_excl_users: ''
mariadb_server__cnf_server_audit_file_path: '/var/log/mariadb/server_audit.log'
mariadb_server__cnf_server_audit_file_rotate_now: 'OFF'
mariadb_server__cnf_server_audit_file_rotate_size: '10M'
mariadb_server__cnf_server_audit_file_rotations: '9'
mariadb_server__cnf_server_audit_incl_users: ''
mariadb_server__cnf_server_audit_logging: 'ON'
mariadb_server__cnf_server_audit_output_type: 'FILE'
mariadb_server__cnf_server_audit_query_log_limit: '1024'
mariadb_server__cnf_server_audit_syslog_facility: 'LOG_USER'
mariadb_server__cnf_server_audit_syslog_ident: 'mysql-server_auditing'
mariadb_server__cnf_server_audit_syslog_info: ''
mariadb_server__cnf_server_audit_syslog_priority: 'LOG_INFO'
Optional Role Variables - mariadb_server__cnf_* Config Directives for Galera
Install the first node with --extra-vars='{"mariadb_server__run_galera_new_cluster": true}' to bootstrap the cluster. Then run the role against the remaining nodes to add them to the cluster.
Set mariadb_server__admin_user to the same value for all nodes. Once the nodes are joined, users and databases will be shared, so they only need to be created on one of the nodes.
It is easily possible to add further nodes at a later date (e.g. two additional nodes to an existing 3-node system).
For Galera to work, also set the following variables:
mariadb_server__cnf_bind_address__group_var: '0.0.0.0'
mariadb_server__cnf_binlog_format__group_var: 'ROW'
mariadb_server__cnf_default_storage_engine__group_var: 'InnoDB'
mariadb_server__cnf_innodb_autoinc_lock_mode__group_var: 2 # ensure the InnoDB locking mode for generating auto-increment values is set to interleaved lock mode
mariadb_server__cnf_innodb_doublewrite__group_var: 'ON' # this is the default value, and should not be changed
mariadb_server__cnf_innodb_flush_log_at_trx_commit__group_var: 0 # inconsistencies can always be fixed by recovering from another node
Role Variable |
Documentation |
Default Value |
|---|---|---|
|
List of strings. mariadb.com. DNS names work as well, IPs are preferred for performance. |
unset |
|
String. mariadb.com |
|
|
String. mariadb.com |
|
|
String. mariadb.com |
|
|
Boolean. mariadb.com. Also installs the packages required for Galera. |
|
|
String. mariadb.com |
|
|
Integer. mariadb.com. Four slave threads can typically saturate one CPU core. |
|
|
Boolean. mariadb.com. Do not set in the inventory, use via |
|
Example:
# optional - Galera directives
mariadb_server__cnf_wsrep_cluster_addresses:
- '192.0.2.10'
- '192.0.2.20'
- '192.0.2.30'
mariadb_server__cnf_wsrep_cluster_name: 'lfops_galera_cluster'
mariadb_server__cnf_wsrep_node_address: '192.0.2.10'
mariadb_server__cnf_wsrep_node_name: 'db10'
mariadb_server__cnf_wsrep_on: true
mariadb_server__cnf_wsrep_provider_options: 'gcache.size=300M; gcache.page_size=300M'
mariadb_server__cnf_wsrep_slave_threads: 4
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.
Q: I always get [Warning] Access denied for user 'root'@'localhost' in mariadb.log when running this role.
A: This is due to check_implicit_admin: true. This checks if MariaDB allows login as root/nopassword before trying the supplied credentials. If successful, the login_user/login_password passed will be ignored. This is especially needed for the first run of this role.