CarlosLongarela.mariadb

Ansible Role: MariaDB

This role installs MariaDB, sets up basic security, and provides tools for database optimization and backup using cron jobs.

Requirements

No special requirements.

Changes

PhpMyAdmin installation has been removed. It now has its own role: CarlosLongarela.phpmyadmin

Role Variables

mariadb_version: 10.1.22

mariadb_service_name: mysql
mariadb_secure_installation: True

mariadb_root_password: changeme

mariadb_server_path_utils: /root/utils

mariadb_path_backups: /home/db_backups/

mariadb_databases: []
# To define databases, remove [] and add:
# - database1
# - database2

mariadb_users: []
# To define users, remove [] and add:
# user1:
#    name: "user1"
#    password: "password1"
#    priv: "*.*:ALL"
# user2:
#    name: "user2"
#    password: "password2"
#    priv: "db.table:priv1,priv2"
# user3:
#    name: "user3"
#    password: "password3"
#    priv: "testdb.*:ALL"

mariadb_options:
  bind_address: '127.0.0.1'
  performance_schema: on
  skip_name_resolve: 1
  max_connections: 100
  connect_timeout: 2
  max_allowed_packet: 10M
  innodb_buffer_pool_instances: 1
  innodb_buffer_pool_size: 100M
  innodb_log_file_size: 25M
  table_cache: 1000
  tmp_table_size: 50M
  max_heap_table_size: 50M
  query_cache_limit: 256K
  query_cache_type: 0
  query_cache_size: 0
  query_cache_min_res_unit: 2k
  join_buffer_size: 2M
  sort_buffer_size: 256K
  read_buffer_size: 128K
  read_rnd_buffer_size: 4M
  key_buffer: 500M
  slow_query_log: true
  long_query_time: 5
  log_slow_admin_statements: true
  log_queries_not_using_indexes: true

  mariadb_utils_db: false
  mariadb_cron_backup: false
  mariadb_cron_optimization: false

mariadb_cron_backup_db:
  minute: "15"
  hour: "3"
  day: "*"
  weekday: "*"

mariadb_cron_optimize_db:
  minute: "1"
  hour: "3"
  day: "*"
  weekday: "0"

aptget_update_cache_valid_time: 3600

Dependencies

None.

Example Playbook

- hosts: servers

  gather_facts: no
  become: true

  vars:
    mariadb_root_password: change_me

    mariadb_databases:
      - mydatabase

    mariadb_users:
      user1:
        name: "user1"
        password: "password1"
        priv: "mydatabase.*:ALL"

    mariadb_utils_db: true
    mariadb_cron_backup: true
    mariadb_cron_optimization: true

  roles:
     - { role: CarlosLongarela.mariadb }