Cet article présente la mise en place d'un cluster de réplication InnoDB utilisant la technologie "Group Replication" sur des serveurs debian.
Un cluster utilisant "Group Replication" est un ensemble de serveurs (ici 3 noeuds) possedant tous l'ensemble des données et dont, en mode single primary, un seul noeud joue le role de primaire (read-write), les autres étant secondaires (read only).
L'avantage par rapport à la réplication classique est que, ici, il y a un protocole de communication inter-noeud qui permet de détecter les défaillances et
ainsi de maintenir à jour une vue consistente des noeud actifs du cluster.
Le script ansible présenté ici permet de configurer un cluster RG.
Les prérequis sont :
Pour satisfaire les prérequis, notamment le reset d'une éventuelle réplication, les actios peuvent être exécutées :
# systemctl stop mysql # vi /etc/mysql/mysql.conf.d/mysqld.cnf [mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log # systemctl start # mysql -s -e "drop database test1;" # mysql -s -e "drop database test2;" # mysql -s -e "drop database test3;" # mysql -s -e "stop replica;" # mysql -s -e "reset replica all;" # mysql -s -e "reset master;" # mysql mysql> select * from mysql.slave_master_info; Empty set (0,00 sec) mysql> select * from mysql.slave_relay_log_info; Empty set (0,00 sec) mysql> select * from performance_schema.replication_group_members; Empty set (0,00 sec) mysql> exit
---
# Mise en place de la replication de group MySQL
- hosts: mysql
vars:
source_directory: /root/playbooks/mysql_group_replication_8.0_setup
replication_primary: mysql01
replication_secondary_01: mysql02
replication_secondary_02: mysql03
replication_user: repl
replication_pass: repl
remote_user: root
tasks:
- name: Stop replication
shell: mysql -s -N -e "stop replica;"
- name: Drop replication User
shell: mysql -s -N -e "drop user if exists '{{replication_user}}'@'%';"
- name: Create replication User
shell: mysql -s -N -e "create user '{{replication_user}}'@'%' identified with mysql_native_password by '{{replication_pass}}';"
- name: Grant replication slave to User
shell: mysql -s -N -e "grant replication slave on *.* to '{{replication_user}}'@'%';"
- name: Grant backup_admin to User
shell: mysql -s -N -e "grant backup_admin on *.* to '{{replication_user}}'@'%';"
- name: Reset replica
shell: mysql -s -N -e "reset replica all;"
- name: Reset master
shell: mysql -s -N -e "reset master;"
- name: Change replication source
shell: mysql -s -N -e "change replication source to source_user='{{replication_user}}', source_password='{{replication_pass}}' for channel 'group_r
eplication_recovery';"
- name: Stop mysql service
systemd:
name: mysql
state: stopped
- name: Setup variables for RG on {{ replication_primary }}
copy:
src: "{{ source_directory }}/mysqld.cnf.{{replication_primary}}"
dest: "/etc/mysql/mysql.conf.d/mysqld.cnf"
when: inventory_hostname_short == replication_primary
- name: Setup variables for RG on {{ replication_secondary_01 }}
copy:
src: "{{ source_directory }}/mysqld.cnf.{{replication_secondary_01}}"
dest: "/etc/mysql/mysql.conf.d/mysqld.cnf"
when: inventory_hostname_short == replication_secondary_01
- name: Setup variables for RG on {{ replication_secondary_02 }}
copy:
src: "{{ source_directory }}/mysqld.cnf.{{replication_secondary_02}}"
dest: "/etc/mysql/mysql.conf.d/mysqld.cnf"
when: inventory_hostname_short == replication_secondary_02
- name: Start Primary {{ replication_primary }}
systemd:
name: mysql
state: started
when: inventory_hostname_short == replication_primary
- name: Start RG on Primary {{ replication_primary }}
shell: mysql -s -N -e "set global group_replication_bootstrap_group=ON;" -e "start group_replication;" -e "set global group_replication_bootstrap_g
roup=OFF;"
when: inventory_hostname_short == replication_primary
- name: Wait 10 sec
shell: sleep 10
- name: Start Secondary {{ replication_secondary_01 }}
systemd:
name: mysql
state: started
when: inventory_hostname_short == replication_secondary_01
- name: Start RG on Secondary {{ replication_secondary_01 }}
shell: mysql -s -N -e "start group_replication;"
when: inventory_hostname_short == replication_secondary_01
- name: Wait 10 sec
shell: sleep 10
- name: Start Secondary {{ replication_secondary_02 }}
systemd:
name: mysql
state: started
when: inventory_hostname_short == replication_secondary_02
- name: Start RG on Secondary {{ replication_secondary_02 }}
shell: mysql -s -N -e "start group_replication;"
when: inventory_hostname_short == replication_secondary_02
Fichier mysqld.cnf.mysql01
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log server-id = 1 gtid-mode = ON enforce-gtid-consistency = ON disabled-storage-engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" log-bin = binlog log-slave-updates = ON binlog-format = ROW master-info-repository = TABLE relay-log-info-repository = TABLE transaction-write-set-extraction = XXHASH64 plugin-load-add = 'group_replication.so' group-replication-group-name = "f080cd9a-0a44-11ec-a21d-080027df3a71" group-replication-start-on-boot = OFF group-replication-local-address = "mysql01:33061" group-replication-group-seeds = "mysql01:33061,mysql02:33061,mysql03:33061" group-replication-bootstrap-group = OFF
Fichier mysqld.cnf.mysql02
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log server-id = 2 gtid-mode = ON enforce-gtid-consistency = ON disabled-storage-engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" log-bin = binlog log-slave-updates = ON binlog-format = ROW master-info-repository = TABLE relay-log-info-repository = TABLE transaction-write-set-extraction = XXHASH64 plugin-load-add = 'group_replication.so' group-replication-group-name = "f080cd9a-0a44-11ec-a21d-080027df3a71" group-replication-start-on-boot = OFF group-replication-local-address = "mysql02:33061" group-replication-group-seeds = "mysql01:33061,mysql02:33061,mysql03:33061" group-replication-bootstrap-group = OFF
Fichier mysqld.cnf.mysql03
[mysqld] pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock datadir = /var/lib/mysql log-error = /var/log/mysql/error.log server-id = 3 gtid-mode = ON enforce-gtid-consistency = ON disabled-storage-engines = "MyISAM,BLACKHOLE,FEDERATED,ARCHIVE,MEMORY" log-bin = binlog log-slave-updates = ON binlog-format = ROW master-info-repository = TABLE relay-log-info-repository = TABLE transaction-write-set-extraction = XXHASH64 plugin-load-add = 'group_replication.so' group-replication-group-name = "f080cd9a-0a44-11ec-a21d-080027df3a71" group-replication-start-on-boot = OFF group-replication-local-address = "mysql03:33061" group-replication-group-seeds = "mysql01:33061,mysql02:33061,mysql03:33061" group-replication-bootstrap-group = OFF
# ansible-playbook mysql_group_replication_8.0_setup.yml PLAY [mysql] ****************************************************************************************************************************************** TASK [Gathering Facts] ******************************************************************************************************************************** ok: [mysql02] ok: [mysql03] ok: [mysql01] TASK [Stop replication] ******************************************************************************************************************************* changed: [mysql01] changed: [mysql03] changed: [mysql02] TASK [Drop replication User] ************************************************************************************************************************** changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Create replication User] ************************************************************************************************************************ changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Grant replication slave to User] **************************************************************************************************************** changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Grant backup_admin to User] ********************************************************************************************************************* changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Reset replica] ********************************************************************************************************************************** changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Reset master] *********************************************************************************************************************************** changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Change replication source] ********************************************************************************************************************** changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Stop mysql service] ***************************************************************************************************************************** changed: [mysql01] changed: [mysql03] changed: [mysql02] TASK [Setup variables for RG on mysql01] ************************************************************************************************************** skipping: [mysql02] skipping: [mysql03] changed: [mysql01] TASK [Setup variables for RG on mysql02] ************************************************************************************************************** skipping: [mysql01] skipping: [mysql03] changed: [mysql02] TASK [Setup variables for RG on mysql03] ************************************************************************************************************** skipping: [mysql01] skipping: [mysql02] changed: [mysql03] TASK [Start Primary mysql01] ************************************************************************************************************************** skipping: [mysql02] skipping: [mysql03] changed: [mysql01] TASK [Start RG on Primary mysql01] ******************************************************************************************************************** skipping: [mysql02] skipping: [mysql03] changed: [mysql01] TASK [Wait 10 sec] ************************************************************************************************************************************ changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Start Secondary mysql02] ************************************************************************************************************************ skipping: [mysql01] skipping: [mysql03] changed: [mysql02] TASK [Start RG on Secondary mysql02] ****************************************************************************************************************** skipping: [mysql01] skipping: [mysql03] changed: [mysql02] TASK [Wait 10 sec] ************************************************************************************************************************************ changed: [mysql01] changed: [mysql02] changed: [mysql03] TASK [Start Secondary mysql03] ************************************************************************************************************************ skipping: [mysql01] skipping: [mysql02] changed: [mysql03] TASK [Start RG on Secondary mysql03] ****************************************************************************************************************** skipping: [mysql01] skipping: [mysql02] changed: [mysql03] PLAY RECAP ******************************************************************************************************************************************** mysql01 : ok=15 changed=14 unreachable=0 failed=0 skipped=6 rescued=0 ignored=0 mysql02 : ok=15 changed=14 unreachable=0 failed=0 skipped=6 rescued=0 ignored=0 mysql03 : ok=15 changed=14 unreachable=0 failed=0 skipped=6 rescued=0 ignored=0
Il est ensuite possible de vérifier la configuration du cluster :
mysql> select * from performance_schema.replication_group_members; +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | CHANNEL_NAME | MEMBER_ID | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE | MEMBER_ROLE | MEMBER_VERSION | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ | group_replication_applier | 36e909d0-c8a4-11eb-a259-080027df3a71 | mysql01 | 3306 | ONLINE | PRIMARY | 8.0.26 | | group_replication_applier | 77258615-c9ae-11eb-8ec0-080027a13959 | mysql02 | 3306 | ONLINE | SECONDARY | 8.0.26 | | group_replication_applier | dadd233f-08a4-11ec-9003-0800278ede4e | mysql03 | 3306 | ONLINE | SECONDARY | 8.0.26 | +---------------------------+--------------------------------------+-------------+-------------+--------------+-------------+----------------+ 3 rows in set (0,00 sec)
On remarquera que toutes les transactions exécutées contienne l'UUID du cluster :
mysql> select @@gtid_executed; +------------------------------------------+ | @@gtid_executed | +------------------------------------------+ | f080cd9a-0a44-11ec-a21d-080027df3a71:1-3 | +------------------------------------------+ 1 row in set (0,00 sec)