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)