[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
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = 1
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.1.201:20001"
group_replication_group_seeds="192.168.1.201:20001,192.168.1.202:20001,192.168.1.203: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.1.%' 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 | d386a328-5494-11eb-8ff3-000c29f06629 | 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
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = 1
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.1.202:20001"
group_replication_group_seeds="192.168.1.201:20001,192.168.1.202:20001,192.168.1.203:20001"
[root@wallet02 ~]# su - mysql
[mysql@wallet02 ~]$ cd backup
[mysql@wallet02 backup]$ tar izxvf 2019-08-22_10-48-1.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 | d386a328-5494-11eb-8ff3-000c29f06629 | wallet02 | 3306 | ONLINE |
| group_replication_applier | da0cec61-549c-11eb-a0a4-000c29bd9bc5 | 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
slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 4
slave_preserve_commit_order = 1
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.1.203:20001"
group_replication_group_seeds="192.168.1.201:20001,192.168.1.202:20001,192.168.1.203: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 | d386a328-5494-11eb-8ff3-000c29f06629 | wallet01 | 3306 | ONLINE |
| group_replication_applier | da0cec61-549c-11eb-a0a4-000c29bd9bc5 | wallet02 | 3306 | ONLINE |
| group_replication_applier | f11984e0-549d-11eb-b271-000c29906702 | wallet03 | 3306 | ONLINE |
+---------------------------+--------------------------------------+-------------+-------------+--------------+
3 rows in set (0.00 sec)
mysql> show status like 'group_replication_primary_member';
+----------------------------------+--------------------------------------+
| Variable_name | Value |
+----------------------------------+--------------------------------------+
| group_replication_primary_member | d386a328-5494-11eb-8ff3-000c29f06629 |
+----------------------------------+--------------------------------------+
1 row in set (0.00 sec)
mysql> select * from performance_schema.replication_group_member_stats\G;
*************************** 1. row ***************************
CHANNEL_NAME: group_replication_applier
VIEW_ID: 16104300602378309:3
MEMBER_ID: f11984e0-549d-11eb-b271-000c29906702
COUNT_TRANSACTIONS_IN_QUEUE: 0
COUNT_TRANSACTIONS_CHECKED: 3825
COUNT_CONFLICTS_DETECTED: 0
COUNT_TRANSACTIONS_ROWS_VALIDATING: 18
TRANSACTIONS_COMMITTED_ALL_MEMBERS: 8d1945a5-5c74-4ba0-8240-e9d731110753:1-3851
LAST_CONFLICT_FREE_TRANSACTION: 8d1945a5-5c74-4ba0-8240-e9d731110753:3851
1 row in set (0.00 sec)组复制限制
仅支持innodb引擎的表,能够创建非innodb引擎的表,但是无法写入数据,向非innodb表写数据直接报错。
mysql> create table tb_myisam(id int, name varchar(50), primary key(id)) engine=myisam;
Query OK, 0 rows affected (0.05 sec)
mysql> insert into tb_myisam select 1, '1';
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
仅支持innodb引擎的表,并且该表必须有显式的主键,或者非Null的唯一键,否则即使能够创建表,也无法向表中写入数据。
# 创建没有主键的表,写入数据失败
mysql> create table tb_no_primary_key(name varchar(50));
Query OK, 0 rows affected (0.15 sec)
mysql> insert into tb_no_primary_key select '1';
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
# 创建Null唯一索引的表,写入数据失败
mysql> create table tb_null_unique_key(name varchar(50), unique key(name));
Query OK, 0 rows affected (0.09 sec)
mysql> insert into tb_null_unique_key select '1';
ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.
# 创建非Null唯一索引的表,写入数据成功
mysql> create table tb_no_null_unique_key(name varchar(50) not null, unique key(name));
Query OK, 0 rows affected (0.04 sec)
mysql> insert into tb_no_null_unique_key select '1';
Query OK, 1 row affected (0.06 sec)
Records: 1 Duplicates: 0 Warnings: 0
GTID限制
CREATE TABLE … SELECT语句不支持
因为该语句会被拆分成 CREATE TABLE 和 INSERT 两个事务,并且这个两个事务被分配了同一个 GTID,这会导致 INSERT 被备库忽略掉。
mysql> create table history01 select * from history;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
解决方法
mysql> create table history01 like history;
Query OK, 0 rows affected (0.32 sec)
mysql> insert into history01 select * from history;
Query OK, 301277 rows affected (7.27 sec)
Records: 301277 Duplicates: 0 Warnings: 0
临时表事务内部不能执行
创建临时表语句,但可以在事务外执行,但必须设置 set autocommit=1 。另外 procedures, functions, and triggers在使用GTID时不能 CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE 语句
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> create temporary table test(id INT);
ERROR 1787 (HY000): Statement violates GTID consistency: CREATE TEMPORARY TABLE and DROP TEMPORARY TABLE can only be executed outside transactional context.
These statements are also not allowed in a function or trigger because functions and triggers are also considered to be multi-statement transactions.