Mysql GTID 在主从复制中的一点研究
1、GTID 是多节点间事务的唯一标记，是使用 UUID+事务ID ，是在 mysql5.6 里新出现的功能。如需启用需要参数配置：gtid-mode=ON
enforce-gtid-consistency = ON
log-slave-updates = 1
2、mysql 复制使用gtid 时，在备机设置如下：
mysql> change master to
-> master_host=host_ip,
-> master_port=host_port,
-> master_user=masterhost_user,
-> master_password=masterhost_password,
-> master_AUTO_POSITION=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.04 sec)
备机 不需要指定主库的具体的 binlog file, binlog pos 位置，使用 master_AUTO_POSITION=1 就可以了。
使用 gtid 搭建备库时，必须主备库的server_id , uuid 不一致。
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 893
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 1103
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
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: 893
Relay_Log_Space: 1276
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: 2
Master_UUID: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1
Master_Info_File: /u01/mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1:1-3
Executed_Gtid_Set: 4a5c96c2-bd21-11e5-9ecf-000c295c61b1:1
Auto_Position: 1
3、当使用 gtid 同步报错时，无法正常使用 sql_slave_skip_counter 参数来跳过，解决方法是：
主库：
mysql> select * from aaa;
+-----+------+-------+------+
| id| name | score | age|
+-----+------+-------+------+
| 1 | aa | 100 | NULL |
| 2 | b | 22 | NULL |
|10 | e | 200 | NULL |
|22 | c | 34 | NULL |
| 100 | eee| 200 |100 |
+-----+------+-------+------+
5 rows in set (0.00 sec)
mysql> update aaa set name='ffff' where id=1;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1Changed: 1Warnings: 0
备库：
mysql> select * from aaa;
+-----+------+-------+------+
| id| name | score | age|
+-----+------+-------+------+
| 2 | b | 22 | NULL |
|10 | e | 200 | NULL |
|22 | c | 34 | NULL |
| 100 | eee| 200 |100 |
| 200 | aa | 100 | NULL |
+-----+------+-------+------+
5 rows in set (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1421
Relay_Log_File: mysql-relay-bin.000004
Relay_Log_Pos: 1367
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: test
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Could not execute Update_rows event on table test.aaa; Can't find record in 'aaa', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql3306.000001, end_log_pos 1390
Skip_Counter: 0
处理方法：
(1)、设置参数sql_slave_skip_counter=1 处理问题
mysql> set global sql_slave_skip_counter=1;
ERROR 1858 (HY000): sql_slave_skip_counter can not be set when the server is running with @@GLOBAL.GTID_MODE = ON. Instead, for each transaction that you want to skip, generate an empty transaction with the same GTID as the transaction
mysql>
可以看出 sql_slave_skip_counter 报错,无法解决。
(2)、设置参数slave_exec_mode='IDEMPOTENT' 处理问题
mysql> show variables like '%exec%';
+-----------------+--------+
| Variable_name | Value|
+-----------------+--------+
| gtid_executed | |
| slave_exec_mode | STRICT |
+-----------------+--------+
2 rows in set (0.00 sec)
mysql> set global slave_exec_mode='IDEMPOTENT';
Query OK, 0 rows affected (0.00 sec)
mysql> stop slave;
mysql> start slave;
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1421
Relay_Log_File: mysql-relay-bin.000005
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
可以看到可以正确解决问题。
(3)、设置 gtid_next 处理问题
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
mysql> set gtid_next='4a5c96c2-bd21-11e5-9ecf-000c295c61b1:6';
Query OK, 0 rows affected (0.00 sec)
mysql> begin;commit;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> set gtid_next="AUTOMATIC";
Query OK, 0 rows affected (0.00 sec)
mysql> start slave ;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.37.201
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql3306.000001
Read_Master_Log_Pos: 1672
Relay_Log_File: mysql-relay-bin.000007
Relay_Log_Pos: 448
Relay_Master_Log_File: mysql3306.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: test
也可以问题解决!
4、复制由 GTID 转成普通模式时，只需要执行：
mysql> change master to
-> MASTER_AUTO_POSITION=0;
然后使用正常的方法设置同步即可。
页:
[1]