【MySQL 5.7】通过组复制（MGR）实现primary的failover

原创

dbprofessional 2021-06-11 13:48:40 博主文章分类：MySQL ©著作权

文章标签 mysql 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者dbprofessional的原创作品，谢绝转载，否则将追究法律责任

[mysql@node01 ~]# uuidgen
8d1945a5-5c74-4ba0-8240-e9d731110753  --组复制名称

在node01修改与组复制相关的参数
[mysql@node01 ~]# vim /etc/my.cnf
[mysqld]
server-id = 101
log_bin = mysql-bin
binlog_format = row
binlog_checksum = none
binlog_cache_size = 16M
max_binlog_cache_size = 4G
max_binlog_size = 128M
expire_logs_days = 7
binlog_rows_query_log_events = on

relay_log = relay-bin
relay_log_recovery = 1
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = on

#GTID复制
gtid_mode = on
enforce_gtid_consistency = on

#组复制
plugin_load_add='group_replication.so'
transaction_write_set_extraction = XXHASH64
group_replication_group_name = "8d1945a5-5c74-4ba0-8240-e9d731110753"
group_replication_start_on_boot = off
group_replication_local_address = "192.168.1.101:20001"
group_replication_group_seeds="192.168.1.101:20001,192.168.1.102:20001,192.168.1.103:20001"

通过ssh登录node02,node03，无需输入mysql用户密码
[mysql@node01 ~]$ ssh-keygen
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node02
[mysql@node01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@node03

[mysql@node01 ~]$ mysql -umysql -pabcd.1234 -hnode01
(mysql@node01) > create user repl@'%' identified by 'repl';
Query OK, 0 rows affected (0.02 sec)

(mysql@node01) > grant replication slave on *.* to repl@'%';
Query OK, 0 rows affected (0.03 sec)

(mysql@node01) > show grants for repl@'%';
+----------------------------------------------+
| Grants for repl@%                            |
+----------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' |
+----------------------------------------------+
1 row in set (0.00 sec)

(mysql@node01) > create user xtrabackup@'localhost' identified by 'xtrabackup';
Query OK, 0 rows affected (0.07 sec)

(mysql@node01) > grant reload,lock tables,replication client,process on *.* to xtrabackup@'localhost';
Query OK, 0 rows affected (0.02 sec)

(mysql@node01) > show grants for xtrabackup@'localhost';
+-------------------------------------------------------------------------------------------+
| Grants for xtrabackup@localhost                                                           |
+-------------------------------------------------------------------------------------------+
| GRANT RELOAD, PROCESS, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'xtrabackup'@'localhost' |
+-------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

(mysql@node01) > flush privileges;
Query OK, 0 rows affected (0.03 sec)

(mysql@node01) > reset master;
Query OK, 0 rows affected (0.15 sec)

(mysql@node01) > change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 2 warnings (0.25 sec)

(mysql@node01) > set @@global.group_replication_bootstrap_group=on;
Query OK, 0 rows affected (0.00 sec)

(mysql@node01) > start group_replication;
Query OK, 0 rows affected (2.46 sec)

(mysql@node01) > set @@global.group_replication_bootstrap_group=off;
Query OK, 0 rows affected (0.00 sec)

(mysql@node01) > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5c7336f7-e029-11ed-89b3-000c2991c856 | node01      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

备份数据库
[mysql@node01 ~]$ xtrabackup --backup --user=xtrabackup --password=xtrabackup --target-dir=/home/mysql/backup

通过scp复制备份文件到node02
[mysql@node01 ~]$ scp -r backup/* node02:/home/mysql/backup/

通过scp复制备份文件到node03
[mysql@node01 ~]$ scp -r backup/* node03:/home/mysql/backup/

在node02修改与组复制相关的参数
[mysql@node02 ~]# vim /etc/my.cnf
[mysqld]
server-id = 102
log_bin = mysql-bin
binlog_format = row
binlog_checksum = none
binlog_cache_size = 16M
max_binlog_cache_size = 4G
max_binlog_size = 128M
expire_logs_days = 7
binlog_rows_query_log_events = on

relay_log = relay-bin
relay_log_recovery = 1
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = on

#GTID复制
gtid_mode = on
enforce_gtid_consistency = on

#组复制
plugin_load_add='group_replication.so'
transaction_write_set_extraction = XXHASH64
group_replication_group_name = "8d1945a5-5c74-4ba0-8240-e9d731110753"
group_replication_start_on_boot = off
group_replication_local_address = "192.168.1.102:20001"
group_replication_group_seeds="192.168.1.101:20001,192.168.1.102:20001,192.168.1.103:20001"

[mysql@node02 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup
[mysql@node02 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup
[mysql@node02 ~]$ cat backup/xtrabackup_binlog_info
mysql-bin.000002        129302170       8d1945a5-5c74-4ba0-8240-e9d731110753:1-41891

[mysql@node02 ~]$ /etc/init.d/mysql.server start
[mysql@node02 ~]$ /etc/init.d/mysql.server status

[mysql@node02 ~]$ mysql -umysql -pabcd.1234 -hnode02
(mysql@node02) > reset master;
Query OK, 0 rows affected (0.00 sec)

(mysql@node02) > set global gtid_purged='8d1945a5-5c74-4ba0-8240-e9d731110753:1-41891';
Query OK, 0 rows affected (0.00 sec)

(mysql@node02) > change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.00 sec)

(mysql@node02) > start group_replication;
Query OK, 0 rows affected (5.78 sec)

(mysql@node02) > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 5c7336f7-e029-11ed-89b3-000c2991c856 | node01      |        3306 | ONLINE       |
| group_replication_applier | f8719e03-e32f-11ed-8dbd-000c29a3a188 | node02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

在node03修改与组复制相关的参数
[mysql@node03 ~]# vim /etc/my.cnf
[mysqld]
server-id = 103
log_bin = mysql-bin
binlog_format = row
binlog_checksum = none
binlog_cache_size = 16M
max_binlog_cache_size = 4G
max_binlog_size = 128M
expire_logs_days = 7
binlog_rows_query_log_events = on

relay_log = relay-bin
relay_log_recovery = 1
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = on

#GTID复制
gtid_mode = on
enforce_gtid_consistency = on

#组复制
plugin_load_add='group_replication.so'
transaction_write_set_extraction = XXHASH64
group_replication_group_name = "8d1945a5-5c74-4ba0-8240-e9d731110753"
group_replication_start_on_boot = off
group_replication_local_address = "192.168.1.103:20001"
group_replication_group_seeds="192.168.1.101:20001,192.168.1.102:20001,192.168.1.103:20001"

[mysql@node03 ~]$ xtrabackup --prepare --target-dir=/home/mysql/backup
[mysql@node03 ~]$ xtrabackup --copy-back --target-dir=/home/mysql/backup
[mysql@node03 ~]$ cat backup/xtrabackup_binlog_info
mysql-bin.000002        129302170       8d1945a5-5c74-4ba0-8240-e9d731110753:1-41891

[mysql@node03 ~]$ /etc/init.d/mysql.server start
 Starting MySQL....... SUCCESS! 
[mysql@node03 ~]$ /etc/init.d/mysql.server status
 SUCCESS! MySQL running (2806)

[mysql@node03 ~]$ mysql -umysql -pabcd.1234 -hnode03
(mysql@node03) > reset master;
Query OK, 0 rows affected (0.08 sec)

(mysql@node03) > set global gtid_purged='8d1945a5-5c74-4ba0-8240-e9d731110753:1-41891';
Query OK, 0 rows affected (0.03 sec)

(mysql@node03) > change master to master_user='repl',master_password='repl' for channel 'group_replication_recovery';
Query OK, 0 rows affected, 1 warning (0.09 sec)

(mysql@node03) > start group_replication;
Query OK, 0 rows affected (3.47 sec)

(mysql@node03) > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2b0a0270-e331-11ed-bc61-000c2957c2d6 | node03      |        3306 | ONLINE       |
| group_replication_applier | 5c7336f7-e029-11ed-89b3-000c2991c856 | node01      |        3306 | ONLINE       |
| group_replication_applier | f8719e03-e32f-11ed-8dbd-000c29a3a188 | node02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

node01是组复制中的primary
(mysql@node03) > show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | 5c7336f7-e029-11ed-89b3-000c2991c856 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

关闭node01
[mysql@node01 ~]$ /etc/init.d/mysql.server stop
Shutting down MySQL.. SUCCESS! 

自动选举node02成为新primary
(mysql@node02) > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2b0a0270-e331-11ed-bc61-000c2957c2d6 | node03      |        3306 | ONLINE       |
| group_replication_applier | f8719e03-e32f-11ed-8dbd-000c29a3a188 | node02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

node02是组复制中的primary
(mysql@node02) > show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name                    | Value                                |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | f8719e03-e32f-11ed-8dbd-000c29a3a188 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

恢复旧primary
[mysql@node01 ~]$ /etc/init.d/mysql.server start
Starting MySQL... SUCCESS! 

旧primary重新加入
(mysql@node01) > start group_replication;
Query OK, 0 rows affected (2.46 sec)

(mysql@node02) > select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 2b0a0270-e331-11ed-bc61-000c2957c2d6 | node03      |        3306 | ONLINE       |
| group_replication_applier | 5c7336f7-e029-11ed-89b3-000c2991c856 | node01      |        3306 | ONLINE       |
| group_replication_applier | f8719e03-e32f-11ed-8dbd-000c29a3a188 | node02      |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)