如何搭建和测试MySQL高可用框架GroupReplication（组复制）

下面讲讲关于如何搭建和测试MySQL高可用框架Group Replication（组复制），文字的奥妙在于贴近主题相关。所以，闲话就不谈了，我们直接看下文吧，相信看完如何搭建和测试MySQL高可用框架Group Replication（组复制）这篇文章你一定会有所受益。                                                           

创新互联公司专注于滦平企业网站建设,成都响应式网站建设公司,商城建设。滦平网站建设公司,为滦平等地区提供建站服务。全流程按需设计网站，专业设计，全程项目跟踪，创新互联公司专业和态度为您提供的服务

[root@wallet01 ~]# uuidgen
8d1945a5-5c74-4ba0-8240-e9d731110753

[root@wallet01 ~]# vi /etc/my.cnf
##单主模型参数
[mysqld]
server-id = 1
gtid_mode = on
enforce_gtid_consistency = on
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
relay_log = relay-bin
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = 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_member_weight = 40
group_replication_local_address = "192.168.40.34:20001"
group_replication_group_seeds="192.168.40.34:20001,192.168.40.35:20001,192.168.40.36:20001"

[root@wallet01 ~]# vi /etc/my.cnf
##多主模型参数
[mysqld]
group_replication_enforce_update_everywhere_checks = on
group_replication_single_primary_mode = off

[root@wallet01 ~]# mysql -uroot -pabcd@2019
mysql> grant replication slave on *.* to 'repl'@'192.168.40.%' identified by 'repl@2019';
Query OK, 0 rows affected, 1 warning (0.02 sec)

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

mysql> reset master;
Query OK, 0 rows affected (0.10 sec)

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

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

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

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

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | a0fec232-d84a-11e9-a21c-000c2965b1d9 | wallet01    |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
1 row in set (0.00 sec)

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

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

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

[root@wallet01 ~]# su - mysql
[mysql@wallet01 ~]$ ssh-keygen
[mysql@wallet01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@wallet02
[mysql@wallet01 ~]$ ssh-copy-id -i ~/.ssh/id_rsa.pub mysql@wallet03

[mysql@wallet01 ~]$ ssh wallet02 date
Mon Feb 18 10:41:16 CST 2019

[mysql@wallet01 ~]$ ssh wallet03 date
Mon Feb 18 10:42:30 CST 2019

[mysql@wallet01 ~]$ xtrabackup --backup --user=xtrabackup --password=xtrabackup@2019 --stream=tar | ssh mysql@wallet02 \ "gzip >/home/mysql/backup/`date '+%Y-%m-%d_%H-%M-%S'`.tar.gz"
[mysql@wallet01 ~]$ xtrabackup --backup --user=xtrabackup --password=xtrabackup@2019 --stream=tar | ssh mysql@wallet03 \ "gzip >/home/mysql/backup/`date '+%Y-%m-%d_%H-%M-%S'`.tar.gz"

[root@wallet02 ~]# vi /etc/my.cnf
[mysqld]
server-id = 2
gtid_mode = on
enforce_gtid_consistency = on
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
relay_log = relay-bin
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = 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_member_weight = 30
group_replication_local_address = "192.168.40.35:20001"
group_replication_group_seeds="192.168.40.34:20001,192.168.40.35:20001,192.168.40.36:20001"

[root@wallet02 ~]# su - mysql
[mysql@wallet02 ~]$ cd backup
[mysql@wallet02 backup]$ tar izxvf 2019-08-22_10-48-40.tar.gz 
[mysql@wallet02 backup]$ xtrabackup --prepare --target-dir=/home/mysql/backup
[mysql@wallet02 backup]$ xtrabackup --copy-back --target-dir=/home/mysql/backup
[mysql@wallet02 backup]$ cat xtrabackup_binlog_info
mysql-bin.000001        50522845        8d1945a5-5c74-4ba0-8240-e9d731110753:1-10758

[root@wallet02 ~]# service mysqld start
Logging to '/var/lib/mysql/wallet02.err'.
Starting mysqld:                                           [  OK  ]

[root@wallet02 ~]# mysql -uroot -pabcd@2019
mysql> set global gtid_purged='8d1945a5-5c74-4ba0-8240-e9d731110753:1-10758';
Query OK, 0 rows affected (0.02 sec)

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

mysql> start group_replication;
Query OK, 0 rows affected (5.91 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 82a03979-df58-11e9-8a18-000c29b9ba00 | wallet02    |        3306 | ONLINE       |
| group_replication_applier | a0fec232-d84a-11e9-a21c-000c2965b1d9 | wallet01    |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
2 rows in set (0.00 sec)

[root@wallet03 ~]# vi /etc/my.cnf
[mysqld]
server-id = 3
gtid_mode = on
enforce_gtid_consistency = on
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
relay_log = relay-bin
master_info_repository = table
relay_log_info_repository = table
log_slave_updates = 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_member_weight = 20
group_replication_local_address = "192.168.40.36:20001"
group_replication_group_seeds="192.168.40.34:20001,192.168.40.35:20001,192.168.40.36:20001"

[root@wallet03 ~]# su - mysql
[mysql@wallet03 ~]$ cd backup
[mysql@wallet03 backup]$ tar izxvf 2019-08-22_11-03-21.tar.gz 
[mysql@wallet03 backup]$ xtrabackup --prepare --target-dir=/home/mysql/backup
[mysql@wallet03 backup]$ xtrabackup --copy-back --target-dir=/home/mysql/backup
[mysql@wallet03 backup]$ cat xtrabackup_binlog_info
mysql-bin.000001        50523169        8d1945a5-5c74-4ba0-8240-e9d731110753:1-10759

[root@wallet03 ~]# service mysqld start
Logging to '/var/lib/mysql/wallet03.err'.
Starting mysqld:                                           [  OK  ]

[root@wallet03 ~]# mysql -uroot -pabcd@2019
mysql> set global gtid_purged='8d1945a5-5c74-4ba0-8240-e9d731110753:1-10759';
Query OK, 0 rows affected (0.04 sec)

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

mysql> start group_replication;
Query OK, 0 rows affected (3.39 sec)

mysql> select * from performance_schema.replication_group_members;
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| CHANNEL_NAME              | MEMBER_ID                            | MEMBER_HOST | MEMBER_PORT | MEMBER_STATE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
| group_replication_applier | 0b7b9f83-df62-11e9-b3ab-000c2955056e | wallet03    |        3306 | ONLINE       |
| group_replication_applier | 82a03979-df58-11e9-8a18-000c29b9ba00 | wallet02    |        3306 | ONLINE       |
| group_replication_applier | a0fec232-d84a-11e9-a21c-000c2965b1d9 | wallet01    |        3306 | ONLINE       |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)

对于以上如何搭建和测试MySQL高可用框架Group Replication（组复制）相关内容，大家还有什么不明白的地方吗？或者想要了解更多相关，可以继续关注我们的行业资讯板块。