mysql数据库group_key_【MySQL】数据库复制：组复制（Group Replication）

灰太狼 2023-01-11 03:54 241阅读 0赞

\[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;

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

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;

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

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;

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

\+---------------------------+--------------------------------------+-------------+-------------+--------------+

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.