mysql5.7 gtid复制_MySQL5.7启动gtid复制

gtid配置路线图

查看gtid配置mysql> show variables like 'gtid%';

+----------------------------------+------------------------------------------+

| Variable_name | Value |

+----------------------------------+------------------------------------------+

| gtid_executed_compression_period | 1000 |

| gtid_mode | ON |

| gtid_next | AUTOMATIC |

| gtid_owned | |

| gtid_purged | 00015630-1111-1111-1111-111111111111:1-7 |

+----------------------------------+------------------------------------------+

5 rows in set (0.06 sec)

my.cnf配置log-bin=mysql-bin

binlog_format=row

gtid_mode=ON

enforce_gtid_consistency=ON

enforce_gtid_consistency强制gtid一致性，一些sql在执行的时候会被拆分成2个SQLinsert into table … selec，参见 不支持的语法SQL。这在GTID下是有问题的，开启enforce_gtid_consistency后，遇到此类SQL会报错。

在每台机器上顺序执行下面的命令mysql > set @@global.enforce_gtid_consistency=warn;

Query OK, 0 rows affected (0.00 sec)

mysql > set @@global.enforce_gtid_consistency=on;

Query OK, 0 rows affected (0.00 sec)

mysql > set @@global.gtid_mode=off_permissive;

Query OK, 0 rows affected (0.03 sec)

mysql > set @@global.gtid_mode=on_permissive;

Query OK, 0 rows affected (0.03 sec)

mysql > show status like 'ongoing_anonymous_transaction_count';

+-------------------------------------+-------+

| Variable_name | Value |

+-------------------------------------+-------+

| Ongoing_anonymous_transaction_count | 0 |

+-------------------------------------+-------+

1 row in set (0.00 sec)

mysql > set @@global.gtid_mode=on;

Query OK, 0 rows affected (0.03 sec)

在master上创建rep1用户create user 'repl'@'%';

grant replication slave on *.* to 'repl'@'%' identified by '123456';

在slaver上执行命令，配置masterCHANGE MASTER TO MASTER_HOST='127.0.0.1', MASTER_PORT=15630, MASTER_USER='repl', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1;

在slaver上启动复制start slave;

在master上查看binlogmysql> show master logs;

+------------------+-----------+

| Log_name | File_size |

+------------------+-----------+

| mysql-bin.000001 | 4293 |

| mysql-bin.000002 | 201 |

| mysql-bin.000003 | 201 |

| mysql-bin.000004 | 1797 |

+------------------+-----------+

4 rows in set (0.00 sec)

查看binlog详情mysql> show binlog events in "mysql-bin.000004";

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------+

| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------+

| mysql-bin.000004 | 4 | Format_desc | 101 | 123 | Server ver: 5.7.29-log, Binlog ver: 4 |

| mysql-bin.000004 | 123 | Previous_gtids | 101 | 154 | |

| mysql-bin.000004 | 154 | Gtid | 101 | 219 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:1' |

| mysql-bin.000004 | 219 | Query | 101 | 366 | CREATE USER 'supermarketgame_rw'@'%' IDENTIFIED WITH 'mysql_native_password' |

| mysql-bin.000004 | 366 | Gtid | 101 | 431 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:2' |

| mysql-bin.000004 | 431 | Query | 101 | 563 | CREATE USER 'rep'@'%' IDENTIFIED WITH 'mysql_native_password' |

| mysql-bin.000004 | 563 | Gtid | 101 | 628 | SET @@SESSION.GTID_NEXT='00015630-1111-1111-1111-111111111111:3' |

| mysql-bin.000004 | 628 | Query | 101 | 761 | CREATE USER 'rep1'@'%' IDENTIFIED WITH 'mysql_native_password' |

| mysql-bin.000004 | 761 | Gtid | 101 | 826 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:4' |

| mysql-bin.000004 | 826 | Query | 101 | 1044 | GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |

| mysql-bin.000004 | 1044 | Gtid | 101 | 1109 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:5' |

| mysql-bin.000004 | 1109 | Query | 101 | 1257 | use `test`; create table office_dogs (id int(11), name varchar(255), primary key (id)) |

| mysql-bin.000004 | 1257 | Gtid | 101 | 1322 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:6' |

| mysql-bin.000004 | 1322 | Query | 101 | 1394 | BEGIN |

| mysql-bin.000004 | 1394 | Table_map | 101 | 1451 | table_id: 108 (test.office_dogs) |

| mysql-bin.000004 | 1451 | Write_rows | 101 | 1496 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000004 | 1496 | Xid | 101 | 1527 | COMMIT /* xid=80 */ |

| mysql-bin.000004 | 1527 | Gtid | 101 | 1592 | SET @@SESSION.GTID_NEXT= '00015630-1111-1111-1111-111111111111:7' |

| mysql-bin.000004 | 1592 | Query | 101 | 1664 | BEGIN |

| mysql-bin.000004 | 1664 | Table_map | 101 | 1721 | table_id: 108 (test.office_dogs) |

| mysql-bin.000004 | 1721 | Write_rows | 101 | 1766 | table_id: 108 flags: STMT_END_F |

| mysql-bin.000004 | 1766 | Xid | 101 | 1797 | COMMIT /* xid=101 */ |

+------------------+------+----------------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------+

22 rows in set (0.00 sec)

使用mysqlbinlog命令，可以查看binlog的更详细信息mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004

查看gitd_purgedshow variables like 'gtid_purged';

参考资料：