lechuckroh.mariadb

Panoramica Versioni Approfondimenti

Ansible Role `mariadb`

This is an Ansible role for managing MariaDB.

Installs MariaDB from the official repository.
Sets or resets the root password.
Removes the test database.
Deletes anonymous users.
Creates users and databases.
Manages the configuration file my.cnf.
Supports replication.

Fixed settings:

Storage engine: InnoDB

Tested on:

CentOS 7.4
Ubuntu 16.04 (Xenial)
Ubuntu 18.04 (Bionic)
MariaDB 10.3

Requirements

No special requirements.

Role Variables

Variable	Default	Comments
`mariadb_bind_address`	'127.0.0.1'	The IP address to listen on; '0.0.0.0' for all interfaces.
`mariadb_binlog_format`	'ROW'	Format for binary logging (`STATEMENT`, `ROW`, `MIXED`).
`mariadb_charset`	'utf8'	Character set.
`mariadb_collation`	'utf8_general_ci'	Collation type.
`mariadb_databases`	[]	Databases to create. Refer to mysql_db.
`mariadb_enabled_on_startup`	true	Whether to enable MariaDB on startup.
`mariadb_expire_logs_days`	10	Days before binary log files are automatically removed.
`mariadb_group_concat_max_len`	1024	Maximum length for group_concat.
`mariadb_innodb_buffer_pool_size`	256M	Size for the InnoDB buffer pool.
`mariadb_innodb_file_per_table`	ON	Whether to use one file per table.
`mariadb_innodb_flush_log_at_trx_commit`	1	Flush settings for InnoDB.
`mariadb_innodb_log_buffer_size`	8M	Size of the InnoDB log buffer.
`mariadb_innodb_log_file_size`	64M	Size of InnoDB log files.
`mariadb_innodb_lock_wait_timeout`	50	Lock wait timeout.
`mariadb_join_buffer_size`	256K	Size for join buffers.
`mariadb_key_buffer_size`	256M	Size of the key buffer.
`mariadb_lower_case_table_names`	1	How to handle case sensitivity for table names.
`mariadb_max_allowed_packet`	64M	Maximum packet size.
`mariadb_max_binlog_size`	100M	Maximum size for binary logs.
`mariadb_max_connections`	151	Maximum number of connections.
`mariadb_max_heap_table_size`	16M	Maximum size for heap tables.
`mariadb_myisam_sort_buffer_size`	64M	Size for MyISAM sort buffers.
`mariadb_mysqldump_max_allowed_packet`	64M	Maximum packet size for mysqldump.
`mariadb_overwrite_global_mycnf`	true	Whether to overwrite the global `my.cnf` on each run.
`mariadb_port`	3306	Port number for MariaDB.
`mariadb_read_buffer_size`	1M	Read buffer size.
`mariadb_read_rnd_buffer_size`	4M	Random read buffer size.
`mariadb_replication_master`	''	Replication master.
`mariadb_replication_master_host`	''	Host for the replication master.
`mariadb_replication_role`	''	Replication role (`master`, `slave`).
`mariadb_replication_user`	[]	User for replication (requires `name`, `password`).
`mariadb_root_password`	'root'	Password for the root user.
`mariadb_root_remote`	`no`	Whether remote root login is allowed.
`mariadb_root_remote_host`	'%'	Host for allowing remote root login.
`mariadb_server_id`	''	Server ID for replication.
`mariadb_skip_name_resolve`	1	Skip DNS resolution for host names.
`mariadb_sort_buffer_size`	1M	Sort buffer size.
`mariadb_sql_mode`	NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION, STRICT_TRANS_TABLES, ERROR_FOR_DIVISION_BY_ZERO	SQL modes to use.
`mariadb_table_open_cache`	256	Cache size for open tables.
`mariadb_thread_cache_size`	8	Size of the thread cache.
`mariadb_tmp_table_size`	16M	Maximum size for temporary tables.
`mariadb_users`	[]	Users to create.
`mariadb_version`	'10.3'	Version of MariaDB to install.
`mariadb_wait_timeout`	28800	Timeout duration.

Dependencies

No dependencies.

Example Playbook

Creating Databases and Users

---
- name: example
  hosts: all
  become: true
  vars:
    mariadb_bind_address: '0.0.0.0'
    mariadb_root_password: 'root'
    mariadb_root_remote: yes
    mariadb_databases:
    - name: dev
    mariadb_users:
    - name: dev
      password: 'devdev'
      priv: "dev.*:ALL"
      host: "%"
  roles:
  - lechuckroh.mariadb

Replication

Host inventory:

mariadb-master ansible_host=192.168.50.11 ansible_user=vagrant
mariadb-slave ansible_host=192.168.50.12 ansible_user=vagrant

Master playbook:

---
- name: Install mysql on Master
  hosts: mariadb-master
  become: true
  vars:
    mariadb_bind_address: '0.0.0.0'
    mariadb_root_password: 'root'
    mariadb_databases:
    - name: dev1
    - name: dev2
      replicate: yes
    - name: dev3
      replicate: no
    mariadb_users:
    - name: dev
      password: 'devdev'
      priv: "dev*.*:ALL"
      host: "%"

    mariadb_server_id: 1
    mariadb_max_binlog_size: 100M
    mariadb_binlog_format: ROW
    mariadb_expire_logs_days: 10
    mariadb_replication_role: master
    mariadb_replication_master: mariadb-master
    mariadb_replication_user:
      name: repl
      password: repl

  tasks:
  - include_role:
      name: lechuckroh.mariadb

Slave playbook:

---
- name: Install mysql on Slave
  hosts: mariadb-slave
  become: true
  vars:
    mariadb_bind_address: '0.0.0.0'
    mariadb_root_password: 'root'
    mariadb_databases:
    - name: dev1
    - name: dev2

    mariadb_server_id: 2
    mariadb_replication_role: slave
    mariadb_replication_master: mariadb-master
    mariadb_replication_user:
      name: repl
      password: repl

  tasks:
  - include_role:
      name: lechuckroh.mariadb