--1、GTID基本特性
主服务器产生GTID，并保存到binlog中，发送binlog到从库上 存储在relay log中，从库读取gtid并设置gtid_next值为该gtid值，从库执行gtid事务，从库不生成gtid
--2、动态的传统复制变为GTID复制，所有服务器统一设置如下：
#所有事物可违反GTID事务一致性#
mysql> set global enforce_gtid_consistency = warn;
Query OK, 0 rows affected (0.06 sec)
#所有事物不可违反GTID事务一致性#
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.02 sec)
mysql> set global gtid_mode = on_permissive;
Query OK, 0 rows affected (0.02 sec)
#从服务器查询该状态，必须为0才能下一步，为0表示无事务等待被处理#
mysql> show status like 'ongoing_anonymous_transaction_count';
+-------------------------------------+-------+
| Variable_name | Value |
+-------------------------------------+-------+
| Ongoing_anonymous_transaction_count | 0 |
+-------------------------------------+-------+
1 row in set (0.12 sec)
#开启gtid模式#
mysql> set global gtid_mode=on;
Query OK, 0 rows affected (0.08 sec)
--2、此时还是根据binlog的position位置的，可以如下设置 变成基于GTID模式的复制 如下：
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.57
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000145
Read_Master_Log_Pos: 154
Relay_Log_File: hostmysql-s-relay-bin.000051
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000145
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 627
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 8100117b-a99d-11e8-b4d4-08002733c0ea
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.15 sec)
mysql> stop slave;
Query OK, 0 rows affected (0.28 sec)
mysql> change master to master_auto_position = 1;
Query OK, 0 rows affected (0.08 sec)
mysql> start slave;
Query OK, 0 rows affected (0.12 sec)
#中继日志重新开始了，Auto_Position生效了#
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.56.57
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000145
Read_Master_Log_Pos: 154
Relay_Log_File: hostmysql-s-relay-bin.000002
Relay_Log_Pos: 367
Relay_Master_Log_File: mysql-bin.000145
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 154
Relay_Log_Space: 580
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 8100117b-a99d-11e8-b4d4-08002733c0ea
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
--3、在my.cnf里设置参数，使重启生效
gtid_mode=ON
enforce_gtid_consistency=1
--4、GTID=source_id:sequence_id
#主服务器，这里source_id为8100117b-a99d-11e8-b4d4-08002733c0ea(唯一标识)，sequence_id为事物提交了4次 1-4 #
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000148
Position: 194
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set: 8100117b-a99d-11e8-b4d4-08002733c0ea:1-4
1 row in set (0.00 sec)
--主服务器，gtid记录表，source_uuid和事物的开始和结束ID(reset master 会情况该表)
mysql> select * from mysql.gtid_executed;
+--------------------------------------+----------------+--------------+
| source_uuid | interval_start | interval_end |
+--------------------------------------+----------------+--------------+
| 8100117b-a99d-11e8-b4d4-08002733c0ea | 1 | 4 |
+--------------------------------------+----------------+--------------+
1 row in set (0.04 sec)
--5、查看binlog和relay log情况
#主服务器执行一个插入语句#
mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> insert into table_gtid_test values (4);
Query OK, 1 row affected (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> flush logs;
Query OK, 0 rows affected (0.04 sec)
#主服务器的binlog#
[root@hostmysql-m mysql]# mysqlbinlog -vv mysql-bin.000149
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190102 16:59:47 server id 1 end_log_pos 123 CRC32 0x3e87529f Start: binlog v 4, server v 5.7.23-log created 190102 16:59:47
BINLOG '
g30sXA8BAAAAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AZ9Shz4=
'/*!*/;
# at 123
#190102 16:59:47 server id 1 end_log_pos 194 CRC32 0xc7d182f5 Previous-GTIDs
# 8100117b-a99d-11e8-b4d4-08002733c0ea:1-5
# at 194
#190102 17:01:37 server id 1 end_log_pos 259 CRC32 0x9be3f11e GTID last_committed=0 sequence_number=1 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '8100117b-a99d-11e8-b4d4-08002733c0ea:6'/*!*/;
# at 259
#190102 17:01:36 server id 1 end_log_pos 337 CRC32 0x7f4acc98 Query thread_id=9 exec_time=0 error_code=0
SET TIMESTAMP=1546419696/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=2, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=83/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 337
#190102 17:01:36 server id 1 end_log_pos 399 CRC32 0xf96aba00 Rows_query
# insert into table_gtid_test values (4)
# at 399
#190102 17:01:36 server id 1 end_log_pos 458 CRC32 0x57486632 Table_map: `flydb`.`table_gtid_test` mapped to number 178
# at 458
#190102 17:01:36 server id 1 end_log_pos 498 CRC32 0x54c17418 Write_rows: table id 178 flags: STMT_END_F
BINLOG '
8H0sXB0BAAAAPgAAAI8BAACAACZpbnNlcnQgaW50byB0YWJsZV9ndGlkX3Rlc3QgdmFsdWVzICg0
KQC6avk=
8H0sXBMBAAAAOwAAAMoBAAAAALIAAAAAAAEABWZseWRiAA90YWJsZV9ndGlkX3Rlc3QAAQMAATJm
SFc=
8H0sXB4BAAAAKAAAAPIBAAAAALIAAAAAAAEAAgAB//4EAAAAGHTBVA==
'/*!*/;
### INSERT INTO `flydb`.`table_gtid_test`
### SET
### @1=4 /* INT meta=0 nullable=1 is_null=0 */
# at 498
#190102 17:01:37 server id 1 end_log_pos 529 CRC32 0xe9a11a40 Xid = 254
COMMIT/*!*/;
# at 529
#190102 17:01:42 server id 1 end_log_pos 576 CRC32 0x46351bd1 Rotate to mysql-bin.000150 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@hostmysql-m mysql]# mysqlbinlog -vv mysql-bin.000150
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190102 17:01:42 server id 1 end_log_pos 123 CRC32 0x50a7ddba Start: binlog v 4, server v 5.7.23-log created 190102 17:01:42
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
9n0sXA8BAAAAdwAAAHsAAAABAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Abrdp1A=
'/*!*/;
# at 123
#190102 17:01:42 server id 1 end_log_pos 194 CRC32 0x47a4eb71 Previous-GTIDs
# 8100117b-a99d-11e8-b4d4-08002733c0ea:1-6
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
#从服务器relay log#
[root@hostmysql-s mysql]# mysqlbinlog -vv hostmysql-s-relay-bin.000011
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190102 14:52:01 server id 2 end_log_pos 123 CRC32 0x07aa3e59 Start: binlog v 4, server v 5.7.23-log created 190102 14:52:01
# This Format_description_event appears in a relay log and was generated by the slave thread.
# at 123
#190102 14:52:01 server id 2 end_log_pos 194 CRC32 0xd2ef1920 Previous-GTIDs
# 8100117b-a99d-11e8-b4d4-08002733c0ea:1-6
# at 194
#190102 17:01:42 server id 1 end_log_pos 576 CRC32 0x46351bd1 Rotate to mysql-bin.000150 pos: 4
# at 241
#190102 14:52:01 server id 2 end_log_pos 300 CRC32 0x5b62d980 Rotate to hostmysql-s-relay-bin.000012 pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@hostmysql-s mysql]# mysqlbinlog -vv hostmysql-s-relay-bin.000012
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190102 14:52:01 server id 2 end_log_pos 123 CRC32 0x07aa3e59 Start: binlog v 4, server v 5.7.23-log created 190102 14:52:01
# This Format_description_event appears in a relay log and was generated by the slave thread.
# at 123
#190102 14:52:01 server id 2 end_log_pos 194 CRC32 0xd2ef1920 Previous-GTIDs
# 8100117b-a99d-11e8-b4d4-08002733c0ea:1-6
# at 194
#190102 14:52:01 server id 1 end_log_pos 0 CRC32 0xecffe4b6 Rotate to mysql-bin.000150 pos: 4
# at 241
#190102 17:01:42 server id 1 end_log_pos 123 CRC32 0x50a7ddba Start: binlog v 4, server v 5.7.23-log created 190102 17:01:42
BINLOG '
9n0sXA8BAAAAdwAAAHsAAAAAAAQANS43LjIzLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
Abrdp1A=
'/*!*/;
# at 360
#190102 14:52:01 server id 0 end_log_pos 407 CRC32 0xa2b054e6 Rotate to mysql-bin.000150 pos: 194
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
--6、gtid的一些限制
#1)、基于事务的，同一事务不同的存储引擎产生多个gtid，主从的存储引擎不一致会导致数据不一致#
#2)、create table ...select.....不允许，因为是两个事务，测试如下：#
mysql> create table table_gtid_as_test as select * from table_gtid_test;
ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT.
#3)、temporary临时表的创建和销毁#
#autocommit关闭的情况下 不允许建立temporary表#
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> create temporary table test_tmp1(a 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.
mysql> set global autocommit=1;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@hostmysql-m ~]# mysql -uroot -pXXXXXX flydb -A
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.7.23-log MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
1 row in set (0.00 sec)
#autocommit开启的情况下 允许建立temporary表，但不写入binlog，所以从服务器没有记录#
mysql> create temporary table test_tmp1(a int);
Query OK, 0 rows affected (0.00 sec)
#从服务器无记录#
mysql> select * from test_tmp1;
ERROR 1146 (42S02): Unknown error 1146
mysql> system perror 1146
MySQL error code 1146 (ER_NO_SUCH_TABLE): Table '%-.192s.%-.192s' doesn't exist
参考：https://dev.mysql.com/doc/refman/5.7/en/replication-gtids.html
《MySQL运维内参MySQL、Galera、Inception核心原理与最佳实践》
《MySQL管理之道，性能调优，高可用与监控》
扫一扫
832
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
