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.6 LTS
10.11 LTS
11.4 LTS
11.8 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¶
mariadb_server__admin_user
The main user account for the database administrator. To create additional ones, use the
mariadb_server__users__*variables.Type: Dictionary.
Subkeys:
username:Mandatory. Username.
Type: String.
password:Mandatory. Password.
Type: String.
host:Optional. Host-part(s).
Type: List.
Default:
["localhost", "127.0.0.1", "::1"]
old_password:Optional. The old password. Set this when changing the password.
Type: String.
Example:
# mandatory
mariadb_server__admin_user:
username: 'mariadb-admin'
password: 'linuxfabrik'
# old_password: 'previous-linuxfabrik'
Recommended Role Variables¶
mariadb_server__dump_user
For mydumper: User to whom backup privileges are granted to. Setting this user automatically enables daily MariaDB-Dumps.
Type: Dictionary.
Default: unset
Subkeys:
username:Mandatory. Username.
Type: String.
password:Mandatory. Password.
Type: String.
priv:Optional. User privileges.
Type: List.
Default:
["*.*:binlog monitor,event,lock tables,reload,select,show view,super,trigger"]
state:Optional. Possible Options:
present,absent.Type: String.
Default:
'present'
Example:
# recommended
mariadb_server__dump_user:
username: 'mariadb-backup'
password: 'linuxfabrik'
state: 'present'
Optional Role Variables - Specific to this role¶
mariadb_server__databases__host_var / mariadb_server__databases__group_var
List of dictionaries of databases to create.
For the usage in
host_vars/group_vars(can only be used in one group at a time).Type: List of dictionaries.
Default:
[]Subkeys:
name:Mandatory. Name of the database schema.
Type: String.
collation:Optional. DB collation.
Type: String.
encoding:Optional. DB encoding.
Type: String.
state:Optional.
presentorabsent.Type: String.
mariadb_server__datadir_mode__host_var / mariadb_server__datadir_mode__group_var
Mode (permissions) of the MariaDB
datadir. Use0o750for CIS, but make sure that the socket is not inside the datadir in that case, else other users (eg apache) cannot reach it.Type: Octal.
Default:
0o755
mariadb_server__dump_compress
For mydumper: Compress output files. One of
''orfalse(no compression, extremely fast),'ZSTD'or'GZIP'(both very slow).Type: String or Bool.
Default:
''(no compression)
mariadb_server__dump_directory
For mydumper: Dump output directory name.
Type: String.
Default:
'/backup/mariadb-dump'
mariadb_server__dump_long_query_guard
For mydumper: Set long query timer in seconds.
Type: Number.
Default:
60
mariadb_server__dump_mydumper_package
For mydumper: Name of the „mydumper“ package. Also takes an URL to GitHub if no repo server is available, see the example below.
Type: String.
Default:
'mydumper'
mariadb_server__dump_on_calendar
For mydumper: The
OnCalendardefinition for the systemd timer. Have a look atman systemd.time(7)for the format.Type: String.
Default:
'*-*-* 21:{{ 59 | random(start=0, seed=inventory_hostname) }}:00'
mariadb_server__dump_raw
For mydumper: Any additional parameters you want to add.
Type: String.
Default:
''
mariadb_server__dump_threads
For mydumper: The number of threads to use for dumping data.
0means to use number of CPUs.Type: Number.
Default:
0
mariadb_server__enabled
Enables or disables the Systemd unit.
Type: Bool.
Default:
true
mariadb_server__logrotate
Log files are rotated
countdays before being removed or mailed to the address specified in alogrotatemail directive. If count is0, old versions are removed rather than rotated. If count is-1, old logs are not removed at all (use with caution, may waste performance and disk space).Type: Number.
Default:
{{ logrotate__rotate | d(14) }}
mariadb_server__roles__group_var / mariadb_server__roles__host_var
List of dictionaries of MariaDB roles.
For the usage in
host_vars/group_vars(can only be used in one group at a time).Type: List of dictionaries.
Default:
[]Subkeys:
name:Mandatory. Role name.
Type: String.
priv:Mandatory. List of privileges for the role.
Type: List.
state:Optional. Possible Options:
present,absent.Type: String.
Default:
'present'
mariadb_server__service_limit_memlock
Systemd: LimitMEMLOCK setting.
Type: Number.
Default:
524288
mariadb_server__service_limit_nofile
Systemd: Resource limit directive for the number of file descriptors.
Type: Number.
Default:
32768
mariadb_server__service_timeout_start_sec
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.
Type: String.
Default:
'15min'
mariadb_server__service_timeout_stop_sec
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.
Type: String.
Default:
'15min'
mariadb_server__skip_sys_schema
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
sysschema exists, it will never be overwritten.Type: Bool.
Default:
false
mariadb_server__state
Controls the Systemd service. One of
started,stopped,reloaded.Type: String.
Default:
'started'
mariadb_server__users__host_var / mariadb_server__users__group_var
List of dictionaries of users to create (this is NOT used for the first DBA user - use
mariadb_server__admin_userfor that).For the usage in
host_vars/group_vars(can only be used in one group at a time).Type: List of dictionaries.
Default:
[]Subkeys:
username:Mandatory. Username.
Type: String.
host:Optional. Host-part.
Type: String.
Default:
'localhost'
password:Optional. Password.
Type: String.
plugin:Optional. Plugin to authenticate the user with.
Type: String.
tls_requires:Optional. Specify for client TLS auth. Have a look at the example.
Type: Dictionary.
priv:Optional. List of privileges for the user. Note: Never set to
[], the least privileges are['*.*:USAGE'].Type: List.
roles:Optional. List of roles.
Type: List.
Default:
[]
default_role:Optional. Default role for the user, will be activated upon login.
Type: String.
state:Optional. Possible Options:
present,absent.Type: String.
Default:
'present'
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__roles__host_var:
- name: 'dba'
priv:
- '*.*:ALL'
state: 'present'
- name: 'read_only'
priv:
- '*.*:SELECT'
state: 'present'
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' # Note: Make sure to include the CN in the SAN of the certificate.
priv:
- '*.*:ALL'
state: 'present'
# user with `unix_socket` auth
- username: 'mariadb_admin'
host: 'localhost'
plugin: 'unix_socket'
state: 'present'
# user with roles
- username: 'user2'
password: 'linuxfabrik'
roles:
- 'dba'
- 'read_only'
default_role: 'read_only'
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.
mariadb_server__cnf_bind_address__group_var / mariadb_server__cnf_bind_address__host_var
Type: String.
Default:
''
mariadb_server__cnf_binlog_expire_logs_seconds__group_var / mariadb_server__cnf_binlog_expire_logs_seconds__host_var
Only available in MariaDB 10.6+. mariadb.com
Type: Number.
Default:
0
mariadb_server__cnf_binlog_format__group_var / mariadb_server__cnf_binlog_format__host_var
Type: String.
Default:
'MIXED'
mariadb_server__cnf_bulk_insert_buffer_size__group_var / mariadb_server__cnf_bulk_insert_buffer_size__host_var
Type: String.
Default:
'8M'
mariadb_server__cnf_character_set_server__group_var / mariadb_server__cnf_character_set_server__host_var
Type: String.
Default: 10.11-:
'utf8mb4', 11.1+:'uca1400'
mariadb_server__cnf_collation_server__group_var / mariadb_server__cnf_collation_server__host_var
Type: String.
Default: 10.11-:
'utf8mb4_unicode_ci', 11.1+:'utf8mb3=utf8mb3_uca1400_ai_ci,ucs2=ucs2_uca1400_ai_ci,utf8mb4=utf8mb4_uca1400_ai_ci,utf16=utf16_uca1400_ai_ci,utf32=utf32_uca1400_ai_ci'
mariadb_server__cnf_datadir__group_var / mariadb_server__cnf_datadir__host_var
Type: String.
Default:
'/var/lib/mysql/'
mariadb_server__cnf_default_storage_engine__group_var / mariadb_server__cnf_default_storage_engine__host_var
Type: String.
Default:
'InnoDB'
mariadb_server__cnf_extra_max_connections__group_var / mariadb_server__cnf_extra_max_connections__host_var
Type: Number.
Default:
3
mariadb_server__cnf_extra_port__group_var / mariadb_server__cnf_extra_port__host_var
Type: Number.
Default:
3307
mariadb_server__cnf_general_log__group_var / mariadb_server__cnf_general_log__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_general_log_file__group_var / mariadb_server__cnf_general_log_file__host_var
Type: String.
Default:
'/var/log/mariadb/mariadb-general.log'
mariadb_server__cnf_innodb_autoinc_lock_mode__group_var / mariadb_server__cnf_innodb_autoinc_lock_mode__host_var
Type: Number.
Default:
1
mariadb_server__cnf_innodb_buffer_pool_chunk_size__group_var / mariadb_server__cnf_innodb_buffer_pool_chunk_size__host_var
Type: String or Number.
Default: 10.08-:
'128M', 10.8+:0(autosize)
mariadb_server__cnf_innodb_buffer_pool_size__group_var / mariadb_server__cnf_innodb_buffer_pool_size__host_var
Type: String.
Default:
'128M'
mariadb_server__cnf_innodb_doublewrite__group_var / mariadb_server__cnf_innodb_doublewrite__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_innodb_file_per_table__group_var / mariadb_server__cnf_innodb_file_per_table__host_var
mariadb.com (Deprecated: MariaDB 11.0.1)
Type: String.
Default:
'ON'
mariadb_server__cnf_innodb_flush_log_at_trx_commit__group_var / mariadb_server__cnf_innodb_flush_log_at_trx_commit__host_var
Type: Number.
Default:
1
mariadb_server__cnf_innodb_io_capacity__group_var / mariadb_server__cnf_innodb_io_capacity__host_var
Type: Number.
Default:
200
mariadb_server__cnf_innodb_log_buffer_size__group_var / mariadb_server__cnf_innodb_log_buffer_size__host_var
Type: String.
Default:
'16M'
mariadb_server__cnf_innodb_log_file_size__group_var / mariadb_server__cnf_innodb_log_file_size__host_var
Type: String.
Default:
'32M'
mariadb_server__cnf_innodb_strict_mode__group_var / mariadb_server__cnf_innodb_strict_mode__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_interactive_timeout__group_var / mariadb_server__cnf_interactive_timeout__host_var
Type: Number.
Default:
28800
mariadb_server__cnf_join_buffer_size__group_var / mariadb_server__cnf_join_buffer_size__host_var
Type: String.
Default:
'256K'
mariadb_server__cnf_log_bin__group_var / mariadb_server__cnf_log_bin__host_var
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
'OFF'.Type: String.
Default:
''
mariadb_server__cnf_log_error__group_var / mariadb_server__cnf_log_error__host_var
Type: String.
Default:
'/var/log/mariadb/mariadb.log'
mariadb_server__cnf_log_slave_updates__host_var / mariadb_server__cnf_log_slave_updates__group_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_lower_case_table_names__group_var / mariadb_server__cnf_lower_case_table_names__host_var
Type: Number.
Default:
0
mariadb_server__cnf_max_allowed_packet__group_var / mariadb_server__cnf_max_allowed_packet__host_var
Type: String.
Default:
'16M'
mariadb_server__cnf_max_connections__group_var / mariadb_server__cnf_max_connections__host_var
Type: Number.
Default:
64
mariadb_server__cnf_max_heap_table_size__group_var / mariadb_server__cnf_max_heap_table_size__host_var
Type: String.
Default:
'16M'
mariadb_server__cnf_performance_schema__group_var / mariadb_server__cnf_performance_schema__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_query_cache_limit__group_var / mariadb_server__cnf_query_cache_limit__host_var
Type: String.
Default:
'1M'
mariadb_server__cnf_query_cache_size__group_var / mariadb_server__cnf_query_cache_size__host_var
Type: Number.
Default:
0
mariadb_server__cnf_query_cache_type__group_var / mariadb_server__cnf_query_cache_type__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_server_raw
Raw content which will be appended to the
[server]section of the MariaDB cnf.Type: String.
Default: unset
mariadb_server__cnf_skip_name_resolve__group_var / mariadb_server__cnf_skip_name_resolve__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_slow_query_log__group_var / mariadb_server__cnf_slow_query_log__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_slow_query_log_file__group_var / mariadb_server__cnf_slow_query_log_file__host_var
Type: String.
Default:
'/var/log/mariadb/mariadb-slowquery.log'
mariadb_server__cnf_socket__group_var / mariadb_server__cnf_socket__host_var
Type: String.
Default: RHEL:
'/run/mariadb/mariadb.sock', Debian:'/run/mysqld/mysqld.sock'
mariadb_server__cnf_sql_mode__group_var / mariadb_server__cnf_sql_mode__host_var
Type: String.
Default:
'STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'
mariadb_server__cnf_table_definition_cache__group_var / mariadb_server__cnf_table_definition_cache__host_var
Type: Number.
Default:
400
mariadb_server__cnf_table_open_cache__group_var / mariadb_server__cnf_table_open_cache__host_var
Type: Number.
Default:
2000
mariadb_server__cnf_tls_version__group_var / mariadb_server__cnf_tls_version__host_var
Type: String.
Default:
'TLSv1.2,TLSv1.3'
mariadb_server__cnf_tmp_table_size__group_var / mariadb_server__cnf_tmp_table_size__host_var
Type: String.
Default:
'16M'
mariadb_server__cnf_tmpdir__group_var / mariadb_server__cnf_tmpdir__host_var
Type: String.
Default:
''(means it defaults either to$TMPDIRor/tmp)
mariadb_server__cnf_wait_timeout__group_var / mariadb_server__cnf_wait_timeout__host_var
Type: Number.
Default:
28800
Example:
# optional - cnf directives
mariadb_server__cnf_bind_address__host_var: '0.0.0.0'
mariadb_server__cnf_binlog_expire_logs_seconds__host_var: '9000' # 2.5hrs
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_extra_max_connections__host_var: 10
mariadb_server__cnf_extra_port__host_var: 3308
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_chunk_size__host_var: '{{ (mariadb_server__cnf_innodb_buffer_pool_size__host_var / 64 ) | int }}'
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_buffer_size__host_var: '20M'
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_log_slave_updates__host_var: 'ON'
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_server_raw: |
encrypt-binlog
encrypt-tmp-disk-tables
encrypt-tmp-files
innodb-encrypt-log
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_table_open_cache__host_var: 3000
mariadb_server__cnf_tls_version__host_var: 'TLSv1.2,TLSv1.3'
mariadb_server__cnf_tmp_table_size__host_var: '16M'
mariadb_server__cnf_tmpdir__host_var: '/data/tmpdir'
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.
mariadb_server__cnf_client_ssl_ca__group_var / mariadb_server__cnf_client_ssl_ca__host_var
Client configuration. Path to the SSL CA file. Will be used to verify the server cert.
Type: String.
Default:
'{{ mariadb_server__cnf_ssl_ca__combined_var }}'
mariadb_server__cnf_client_ssl_cert__group_var / mariadb_server__cnf_client_ssl_cert__host_var
Client configuration. Path to an SSL cert that will be used to authenticate against the server.
Type: String.
Default:
''
mariadb_server__cnf_client_ssl_key__group_var / mariadb_server__cnf_client_ssl_key__host_var
Client configuration. Path to an SSL key that will be used to authenticate against the server.
Type: String.
Default:
''
mariadb_server__cnf_client_ssl_verify_server_cert__group_var / mariadb_server__cnf_client_ssl_verify_server_cert__host_var
Client configuration. Enables/disables verification of the server cert.
Type: Bool.
Default: 10.11-:
false, 11.4+:true
mariadb_server__cnf_require_secure_transport__group_var / mariadb_server__cnf_require_secure_transport__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_ssl_ca__group_var / mariadb_server__cnf_ssl_ca__host_var
mariadb.com. Note: the CA should be world-readable (e.g. so that both the server & client can access it).
Type: String.
Default:
''
mariadb_server__cnf_ssl_cert__group_var / mariadb_server__cnf_ssl_cert__host_var
mariadb.com. Note: the role changes the owner to
mysqlto make sure that mariadb can read the file.Type: String.
Default:
''
mariadb_server__cnf_ssl_key__group_var / mariadb_server__cnf_ssl_key__host_var
mariadb.com. Note: the role changes the owner to
mysqlto make sure that mariadb can read the file.Type: String.
Default:
''
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.
mariadb_server__cnf_encrypt_binlog__group_var / mariadb_server__cnf_encrypt_binlog__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_encrypt_tmp_files__group_var / mariadb_server__cnf_encrypt_tmp_files__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_file_key_management_encryption_algorithm__group_var / mariadb_server__cnf_file_key_management_encryption_algorithm__host_var
Type: String.
Default:
'AES_CTR'
mariadb_server__cnf_file_key_management_filename__group_var / mariadb_server__cnf_file_key_management_filename__host_var
Type: String.
Default:
'/etc/my.cnf.d/keyfile'
mariadb_server__cnf_innodb_default_encryption_key_id__group_var / mariadb_server__cnf_innodb_default_encryption_key_id__host_var
Type: Number.
Default:
1
mariadb_server__cnf_innodb_encrypt_log__group_var / mariadb_server__cnf_innodb_encrypt_log__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_innodb_encrypt_tables__group_var / mariadb_server__cnf_innodb_encrypt_tables__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_innodb_encrypt_temporary_tables__group_var / mariadb_server__cnf_innodb_encrypt_temporary_tables__host_var
Type: String.
Default:
'ON'
mariadb_server__cnf_innodb_encryption_rotate_key_age__group_var / mariadb_server__cnf_innodb_encryption_rotate_key_age__host_var
Type: Number.
Default:
1
mariadb_server__cnf_innodb_encryption_threads__group_var / mariadb_server__cnf_innodb_encryption_threads__host_var
Type: Number.
Default:
4
mariadb_server__cnf_plugin_load_add__group_var / mariadb_server__cnf_plugin_load_add__host_var
Type: String.
Default:
'file_key_management'
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.
mariadb_server__cnf_server_audit_events
Type: String.
Default:
'CONNECT,QUERY_DDL'
mariadb_server__cnf_server_audit_excl_users
Type: String.
Default:
''
mariadb_server__cnf_server_audit_file_path
Type: String.
Default:
'/var/log/mariadb/server_audit.log'
mariadb_server__cnf_server_audit_file_rotate_now
Type: String.
Default:
'OFF'
mariadb_server__cnf_server_audit_file_rotate_size
Type: String.
Default:
'10M'
mariadb_server__cnf_server_audit_file_rotations
Type: Number.
Default:
9
mariadb_server__cnf_server_audit_incl_users
Type: String.
Default:
''
mariadb_server__cnf_server_audit_logging
Type: String.
Default:
'ON'
mariadb_server__cnf_server_audit_output_type
Type: String.
Default:
'file'
mariadb_server__cnf_server_audit_query_log_limit
Type: Number.
Default:
1024
mariadb_server__cnf_server_audit_syslog_facility
Type: String.
Default:
'LOG_USER'
mariadb_server__cnf_server_audit_syslog_ident
Type: String.
Default:
'mysql-server_auditing'
mariadb_server__cnf_server_audit_syslog_info
Type: String.
Default:
''
mariadb_server__cnf_server_audit_syslog_priority
Type: String.
Default:
'LOG_INFO'
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
mariadb_server__cnf_sst_encrypt
Type: Number.
Default: unset
mariadb_server__cnf_sst_tcert
Type: String.
Default: unset
mariadb_server__cnf_sst_tkey
Type: String.
Default: unset
mariadb_server__cnf_wsrep_cluster_addresses
mariadb.com. DNS names work as well, IPs are preferred for performance.
Type: List of strings.
Default: unset
mariadb_server__cnf_wsrep_cluster_name
Type: String.
Default:
'lfops_galera_cluster'
mariadb_server__cnf_wsrep_gtid_mode__group_var / mariadb_server__cnf_wsrep_gtid_mode__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_wsrep_log_conflicts__group_var / mariadb_server__cnf_wsrep_log_conflicts__host_var
Type: String.
Default:
'OFF'
mariadb_server__cnf_wsrep_node_address
Type: String.
Default:
'{{ ansible_facts["default_ipv4"]["address"] }}'
mariadb_server__cnf_wsrep_node_name
Type: String.
Default:
'{{ ansible_facts["nodename"] }}'
mariadb_server__cnf_wsrep_on
mariadb.com. Also installs the packages required for Galera.
Type: Bool.
Default:
false
mariadb_server__cnf_wsrep_provider_options
Type: String.
Default:
'gcache.size=300M; gcache.page_size=300M'
mariadb_server__cnf_wsrep_retry_autocommit__group_var / mariadb_server__cnf_wsrep_retry_autocommit__host_var
Type: Number.
Default:
1
mariadb_server__cnf_wsrep_slave_threads
mariadb.com. Four slave threads can typically saturate one CPU core.
Type: Number.
Default:
'{{ 1 if ansible_facts["processor_nproc"] == 1 else (ansible_facts["processor_nproc"] * 2) }}'
mariadb_server__run_galera_new_cluster
mariadb.com. Do not set in the inventory, use via
--extra-vars. This bootstraps the Galera cluster. Only set this totrueduring the deployment of the first node, or when recovering / restarting a stopped cluster.Type: Bool.
Default:
false
Example:
# optional - Galera directives
mariadb_server__cnf_sst_encrypt: 3 # TLS using OpenSSL encryption with Galera-compatible certificates and keys
mariadb_server__cnf_sst_tcert: '{{ mariadb_server__cnf_ssl_cert__combined_var }}'
mariadb_server__cnf_sst_tkey: '{{ mariadb_server__cnf_ssl_key__combined_var }}'
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_gtid_mode__host_var: 'ON'
mariadb_server__cnf_wsrep_log_conflicts__host_var: 'ON'
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_retry_autocommit__host_var: 3
mariadb_server__cnf_wsrep_slave_threads: 4
mariadb_server__cnf_wsrep_sst_auth: 'sst_user:linuxfabrik'
mariadb_server__cnf_wsrep_sst_method: 'mariabackup'
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.
Q: I get IndexError: list index out of range during „Create, update or delete MariaDB users“.
A: Check that the user’s priv is not set to []. The lowest privileges allowed are ['*.*:USAGE'].