MariaDB
MariaDB is an opensource relational database based on MySQL.
Ansible Role: db.maria
Manage MariaDB.
Role handles all steps that are provided in this documentation.
# DB
Manage Maria and Postgres DB's.
## Requirements
No additional requirements.
## Role Variables
Settings have been throughly documented for usage.
* [defaults/maria.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/maria.yml).
* [defaults/postgres.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/postgres.yml).
* [defaults/ports.yml](https://github.com/r-pufky/ansible_db/blob/main/defaults/main/ports.yml).
## Dependencies
None. Based on:
* https://github.com/geerlingguy/ansible-role-mysql
* https://github.com/geerlingguy/ansible-role-postgresql
With patches, postgres 13 support, bullseye support.
## Example Playbook
Maria (Mysql) Configuration
host_vars/db.example.com/vars/maria.yml
``` yaml
db_maria_root_home: '/root'
db_maria_root_username: 'root'
db_maria_root_password: '{{ vault_db_maria_root_password }}'
db_maria_databases:
- name: 'digikam'
- name: 'firefly'
db_maria_users:
- name: 'digikam'
host: '%' # If recreated set to server host.
password: '{{ vault_users_db_maria_digikam_password }}'
priv: 'digikam.*:ALL'
- name: 'firefly'
host: '%' # If recreated set to server host.
password: '{{ vault_users_db_maria_firefly_password }}'
priv: 'firefly.*:ALL'
db_maria_mysqld_bind_address: '*'
db_maria_mysqld_port: 3306
```
Prostgres Configuration
host_vars/db.example.com/vars/postgres.yml
``` yaml
db_postgres_version: '13'
db_postgres_data_dir: '/var/lib/postgresql/{{ db_postgres_version }}/main'
db_postgres_backup_dir: '/var/lib/postgresql-backup'
db_postgres_bin_path: '/usr/lib/postgresql/{{ db_postgres_version }}/bin'
db_postgres_config_path: '/etc/postgresql/{{ db_postgres_version }}/main'
db_postgres_packages:
- 'postgresql-{{ db_postgres_version }}'
db_postgres_databases:
- name: 'gitea'
owner: 'gitea'
- name: 'roundcube'
owner: 'roundcube'
db_postgres_users:
- name: 'gitea'
password: '{{ vault_users_db_postgres_gitea_password }}'
priv: 'all'
db: 'gitea'
- name: 'roundcube'
password: '{{ vault_users_db_postgres_roundcube_password }}'
priv: 'all'
db: 'roundcube'
db_postgres_ssl: 'off'
db_postgres_connections_authentication_extensions: |
listen_addresses = '*'
superuser_reserved_connections = 1
db_postgres_log_timezone: 'America/Los_Angeles'
db_postgres_timezone: 'America/Los_Angeles'
db_postgres_lc_messages: 'en_US.utf8'
db_postgres_lc_monetary: 'en_US.utf8'
db_postgres_lc_numeric: 'en_US.utf8'
db_postgres_lc_time: 'en_US.utf8'
db_postgres_hba:
- {type: 'host', database: 'sameuser', user: 'gitea', address: 'samenet', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'sameuser', user: 'roundcube', address: 'samenet', method: '{{ db_postgres_auth_method }}'}
- {type: 'local', database: 'all', user: 'postgres', address: '', method: 'peer'}
- {type: 'local', database: 'all', user: 'all', address: '', method: 'peer'}
- {type: 'host', database: 'all', user: 'all', address: '127.0.0.1/32', method: '{{ db_postgres_auth_method }}'}
- {type: 'host', database: 'all', user: 'all', address: '::1/128', method: '{{ db_postgres_auth_method }}'}
```
site.yml
``` yaml
- name: 'db server'
hosts: 'db.example.com'
become: true
roles:
- 'r_pufky.db/postgres'
- 'r_pufky.db/maria'
```
## Issues
Create a bug and provide as much information as possible.
Associate pull requests with a submitted bug.
## License
[AGPL-3.0 License](https://github.com/r-pufky/ansible_db/blob/main/LICENSE)
## Author Information
https://keybase.io/rpufky
BLOCKING OS Distribution upgrades REQUIRE: Requires upstream source repo update.
Role Details: Updated: 2022-10-09 galaxy source service docs
Ports
---
###############################################################################
# Ports Configuration
###############################################################################
# Ports should be managed externally via an OS role.
#
# Reference:
# * https://docs.ansible.com/ansible/latest/collections/community/general/ufw_module.html
ports:
- {proto: 'tcp', from_ip: 'any', to_port: 3306, direction: 'in', comment: 'mysql service'}
- {proto: 'tcp', from_ip: 'any', to_port: 5432, direction: 'in', comment: 'postgresql service'}
Defaults
---
###############################################################################
# mariadb Role Configuration
###############################################################################
# From https://github.com/geerlingguy/ansible-role-mysql with patches.
#
# Debian auto-install 'mysql' user. Use ansible template to ensure managed.
# User that maria will run under. Assumes externally managed.
db_maria_user: 'mysql'
db_maria_group: 'mysql'
# Create 'maria' user if not detected? See: vars/main.yml.
db_maria_create_user: false
# Maria version to install.
db_maria_version: '10.5'
db_maria_packages:
- 'mariadb-server-{{ db_maria_version }}'
db_maria_python_packages:
- 'python3-pymysql'
- 'python3-mysqldb'
db_maria_locales:
- 'en_US.UTF-8'
# MariaDB root user & local folder for login configuration.
db_maria_root_home: '/root'
db_maria_root_username: 'root'
db_maria_root_password: ''
# Backup location
db_maria_backup_dir: '/var/lib/mysql-backup'
# mysqldump options
db_maria_backup_options: >
--routines
--events
--triggers
--complete-insert
--create-options
--single-transaction
# Enable separate weekly cronjob to backup databases (includes compression).
db_maria_enable_weekly: true
# databases created if non-existent, and auto-imported from latest backup.
# https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_db_module.html
db_maria_databases: []
# - name: exampledb # required
# collation: # default: 'utf8_general_ci'
# encoding: # default: 'utf8'
# state: # default: 'present'
# https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_user_module.html
db_maria_users: []
# - name: example # required
# host: # default: 'localhost'
# password: # required (use vault)
# priv: # default: '*.*:USAGE'
# resource_limits: # default: ''
# state: # default: 'present'
# append_privs: # default: 'no'
# encrypted: # default: 'no'
###############################################################################
# mariadb mariadb.cnf Configuration
###############################################################################
# Defaults for maria 10.5 on debian 10
#
# Only explicitly defined vars int the default config have definitions. Use
# _extensions to add in additional config settings per section as needed.
# [client-server]
db_maria_client_server_socket: '/run/mysqld/mysqld.sock'
# Freeform configuration text for adding additional section options
db_maria_client_server_extensions: ''
###############################################################################
# mariadb mariadb.conf.d/50-server.cnf Configuration
###############################################################################
# Defaults for maria 10.5 on debian 10
#
# Only explicitly defined vars int the default config have definitions. Use
# _extensions to add in additional config settings per section as needed.
# [server]
# Freeform configuration text for adding additional section options
db_maria_server_extensions: ''
# [mysqld]
db_maria_mysqld_user: '{{ db_maria_user }}'
db_maria_mysqld_pid_file: '/run/mysqld/mysqld.pid'
db_maria_mysqld_base_dir: '/usr'
db_maria_mysqld_data_dir: '/var/lib/mysql'
db_maria_mysqld_tmp_dir: '/tmp'
db_maria_mysqld_lc_messages_dir: '/usr/share/mysql'
db_maria_mysqld_lc_messages: 'en_US'
db_maria_mysqld_skip_external_locking: true
db_maria_mysqld_bind_address: '127.0.0.1'
# Default: commented out in mariadb.cnf
db_maria_mysqld_port: 3306 # mariadb tcp port
db_maria_mysqld_expire_logs_days: 10
db_maria_mysqld_max_binlog_size: '100M'
db_maria_mysqld_character_set_server: 'utf8mb4'
db_maria_mysqld_collation_server: 'utf8mb4_general_ci'
# Freeform configuration text for adding additional section options
db_maria_mysqld_extensions: ''
# [embedded]
# Freeform configuration text for adding additional section options
db_maria_embedded_extensions: ''
# [mariadb]
# Freeform configuration text for adding additional section options
db_maria_mariadb_extensions: ''
# [mariadb-10.5]
# Freeform configuration text for adding additional section options
db_maria_mariadb_10_5_extensions: ''
###############################################################################
# mariadb mariadb.conf.d/60-galera.cnf Configuration
###############################################################################
# Defaults for maria 10.5 on debian 10
#
# Only explicitly defined vars int the default config have definitions. Use
# _extensions to add in additional config settings per section as needed.
# [galera]
# Freeform configuration text for adding additional section options
db_maria_galera_extensions: ''
###############################################################################
# mariadb mariadb.conf.d/50-mysql-clients.cnf Configuration
###############################################################################
# Defaults for maria 10.5 on debian 10
#
# Only explicitly defined vars int the default config have definitions. Use
# _extensions to add in additional config settings per section as needed.
# [mysql]
# Freeform configuration text for adding additional section options
db_maria_mysql_extensions: ''
# [mysql_upgrade]
# Freeform configuration text for adding additional section options
db_maria_mysql_upgrade_extensions: ''
# [mysqladmin]
# Freeform configuration text for adding additional section options
db_maria_mysqladmin_extensions: ''
# [mysqlbinlog]
# Freeform configuration text for adding additional section options
db_maria_mysqlbinlog_extensions: ''
# [mysqlcheck]
# Freeform configuration text for adding additional section options
db_maria_mysqlcheck_extensions: ''
# [mysqldump]
# Freeform configuration text for adding additional section options
db_maria_mysqldump_extensions: ''
# [mysqlimport]
# Freeform configuration text for adding additional section options
db_maria_mysqlimport_extensions: ''
# [mysqlshow]
# Freeform configuration text for adding additional section options
db_maria_mysqlshow_extensions: ''
# [mysqlslap]
# Freeform configuration text for adding additional section options
db_maria_mysqlslap_extensions: ''
###############################################################################
# mariadb mariadb.conf.d/50-client.cnf Configuration
###############################################################################
# Defaults for maria 10.5 on debian 10
#
# Only explicitly defined vars int the default config have definitions. Use
# _extensions to add in additional config settings per section as needed.
# [client]
# Freeform configuration text for adding additional section options
db_maria_client_extensions: ''
# [client-mariadb]
# Freeform configuration text for adding additional section options
db_maria_client_mariadb_extensions: ''