Ansible Role: MySQL Replication

This role is implemented as an extension to [tleguern's ansible-role-mysql][tleguern-mysql], itself a redesign of [geerlingguy's][geerlingguy-mysql] only containing the bare minimum.

The content evolved from Jeff Geerling original replication tasks and now include GTID support.

Requirements

An ansible role dedicated to the installation of MySQL/MariaDB such as [tleguern.mysql][tleguern-mysql].

The following set of parameters must be configured on the primary(s):

[mysqld]
log_bin =
server_id =
expire_logs_days =

Role Variables

Variable	Description	Default
mysql_replication_gtid	Whether or not mysql is using gtid for the replication	true
mysql_replication_user	Defines the replication user	mandatory
mysql_socket	MySQL Unix domain socket used for connections	{{ __mysql_socket }}
mysql_replication_primary	IP address or FQDN of the primary	mandatory (for all replica servers)
mysql_primary_use_gtid	The value passed to [primary_use_gtid]	slave_pos
mysql_replication_role_primary	Define if the server is the primary	false
mysql_replication_role_replica	Define if the server is a replica	false

mysql_replication_user

This variable represents the replication user and its associated rights as a YAML dictionary using the following structure:

Variable	Description	Default
name	Name of the replication user	mandatory
host	The 'host' part of the MySQL username	%
password	User's password	mandatory
priv	MySQL privileges string in the format	*.*:REPLICATION SLAVE,REPLICATION CLIENT

Example:

mysql_replication_user:
  name: replication
  host: 192.168.25.%
  password: "{{ vault_replication_password }}"

Dependencies

None.

Example of a multi primary configuration

Variable DB1:

mysql_config:
...
  - name: mysqld
    content: |
      server_id = 1
      expire_logs_days = 3
      gtid-domain-id = 1
      log_bin = binlog-m1
...
mysql_replication_user:
  name: replication
  host: 192.68.25.%
  password: "P@ssW0rD!"
mysql_replication_primary: db2.exemple.com
mysql_replication_role_primary: true
mysql_replication_role_replica: true

Variable DB2:

mysql_config:
...
  - name: mysqld
    content: |
      server_id = 2
      expire_logs_days = 3
      gtid-domain-id = 1
      log_bin = binlog-m1
...
mysql_replication_user:
  name: replication
  host: 192.68.25.%
  password: "P@ssW0rD!"
mysql_replication_primary: db1.exemple.com
mysql_replication_role_primary: true
mysql_replication_role_replica: true

Variable DB3:

mysql_config:
...
  - name: mysqld
    content: |
      server_id = 3
...
mysql_replication_user:
  name: replication
  host: 192.68.25.%
  password: "P@ssW0rD!"
mysql_replication_primary: db1.exemple.com
mysql_replication_role_replica: true

Playbook:

- hosts: DBS
  roles:
    - role: ansible-role-mysql
    - role: ansible-role-mysql-replication

`̀̀``

## License

MIT / ISC

## Contributing

To get in touch with the author you can create a issue on github when requesting features.

## Author Information

This role was created in 2021 by Olivier Pouilly on the behalf of Deveryware.

[tleguern-mysql]: https://git.sr.ht/~tleguern/ansible-role-mysql
[geerlingguy-mysql]: https://github.com/geerlingguy/ansible-role-mysql
[master-use-gtid]: https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_replication_module.html#parameter-master_use_gtid