MySQL5.7 以后都基本用 GTID 方式复制了, 相对于 binlog 和 position 号方式, 在 failover 时候减少很多人工切换操作
GTID,global transaction identitifiers, 基于全局事务的复制方式, 由 server_uuid:transaction_id 组成, server_uuid 在数据库启动过程生成, 在 / data/auto.cnf 中
复制过程: master 事务提交时 GTID, 记录到 binlog; 然后 master 的 binlog 传送到 slave 的 relaylog,slave 读取 GTID 生成 gtid_next 系统参数; slave 校验 GTID 是否在 binlog 并进一步应用事务 (5.7 后是存放在 gtid_executed 系统表, 这样不用开启 log_slave_updates 参数, 从而不用把 relaylog 记录再记录到 binlog, 减少 slave 压力)
下面操作下:
首先主从都配置 gtid_mode,enforce_gtid_consistency 参数, 其中备库多加个 log_slave_updates=1
- [root@localhost /usr/local/MySQL/data]$ cat /etc/my.cnf
- [mysqld]
- datadir=/usr/local/MySQL/data
- log_bin=MySQL-bin
- server_id=1
- gtid_mode=on
- enforce_gtid_consistency=on
- [root@localhost /usr/local/MySQL/data]$
- MySQL> show variables like '%gtid%';
- +----------------------------------+-----------+
- | Variable_name | Value |
- +----------------------------------+-----------+
- | binlog_gtid_simple_recovery | ON |
- | enforce_gtid_consistency | ON |
- | gtid_executed_compression_period | 1000 |
- | gtid_mode | ON |
- | gtid_next | AUTOMATIC |
- | gtid_owned | |
- | gtid_purged | |
- | session_track_gtids | OFF |
- +----------------------------------+-----------+
- 8 rows in set (0.00 sec)
- MySQL>
然后将之前异步复制的配置去掉, 重新配置 slave 中主库信息即可
- MySQL> show variables like '%log_slave_updates%';
- +-------------------+-------+
- | Variable_name | Value |
- +-------------------+-------+
- | log_slave_updates | ON |
- +-------------------+-------+
- 1 row in set (0.00 sec)
- MySQL> stop slave;
- Query OK, 0 rows affected (0.01 sec)
- MySQL> reset slave all;
- Query OK, 0 rows affected (0.02 sec)
- MySQL> change master to
- -> master_host='192.0.1.10',
- -> master_user='scott',
- -> master_password='tiger',
- -> master_port=3306,
- -> master_auto_position=1;
- Query OK, 0 rows affected, 2 warnings (0.02 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.0.1.10
- Master_User: scott
- Master_Port: 3306
- Connect_Retry: 60
- Master_Log_File: MySQL-bin.000004
- Read_Master_Log_Pos: 573
- Relay_Log_File: localhost-relay-bin.000002
- Relay_Log_Pos: 786
- Relay_Master_Log_File: MySQL-bin.000004
- 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: 573
- Relay_Log_Space: 997
- 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: 531fa6d1-627f-11e9-8dc7-000c297887a1
- Master_Info_File: /data/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: 531fa6d1-627f-11e9-8dc7-000c297887a1:1-2
- Executed_Gtid_Set: 531fa6d1-627f-11e9-8dc7-000c297887a1:1-2
- Auto_Position: 1
- Replicate_Rewrite_DB:
- Channel_Name:
- Master_TLS_Version:
- 1 row in set (0.00 sec)
- MySQL>
测试一下, ok 的, 主库状态可以看到
- MySQL> show master status;
- +------------------+----------+--------------+------------------+------------------------------------------+
- | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
- +------------------+----------+--------------+------------------+------------------------------------------+
- | MySQL-bin.000004 | 573 | | | 531fa6d1-627f-11e9-8dc7-000c297887a1:1-2 |
- +------------------+----------+--------------+------------------+------------------------------------------+
- 1 row in set (0.00 sec)
- MySQL>
将 slave 中 log_slave_updates 关掉, 就能看到 MySQL.gtid_executed 表中记录已执行 gtid 信息了
- MySQL> select * from MySQL.gtid_executed;
- +--------------------------------------+----------------+--------------+
- | source_uuid | interval_start | interval_end |
- +--------------------------------------+----------------+--------------+
- | 531fa6d1-627f-11e9-8dc7-000c297887a1 | 1 | 2 |
- | 531fa6d1-627f-11e9-8dc7-000c297887a1 | 3 | 3 |
- +--------------------------------------+----------------+--------------+
- 2 rows in set (0.00 sec)
由于是基于事务的, 所以就有了限制条件: create table select 的方式在基于行复制的情况下会被拆分为创建表和 insert 数据两个事件, 某些情况下这两个事件会被分配相同 GTID 导致后面 insert 数据部分被忽略而产生错误; 一个事务中既包含 InnoDB 表又包含 MyISAM 表会导致可能产生多个 gtid, 或者表在主从库中存储引擎不一致都会产生 gtid 复制异常
来源: http://www.linuxidc.com/Linux/2019-08/160154.htm