MySQL -- 主从切换

一主多从

虚线箭头为主从关系，A和A'互为主从，B、C、D指向主库A
一主多从的设置，一般用于读写分离，主库负责所有的写入和一部分读，其它读请求由从库分担

主库故障切换

A'成为新的主库，B、C、D指向主库A'

基于位点的切换

B原先是A的从库，本地记录的也是A的位点，但相同的日志，A的位点与A'的位点是不同的

-- 节点B设置为节点A'的从库
CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
MASTER_LOG_FILE=$master_log_name
MASTER_LOG_POS=$master_log_pos

寻找位点

很难精确，只能大概获取一个位置
由于在切换过程中不能丢数据，在寻找位点的时候，总是找一个稍微往前的位点，跳过那些已经在B执行过的事务

常规步骤

等待新主库A'将所有relaylog全部执行完
在A'上执行SHOW MASTER STATUS，得到A'上最新的File和Position
获取原主库A发生故障的时刻T
使用mysqlbinlog解析A'的File，得到时刻T的位点

1
2
3

-- end_log_pos=123，表示在时刻T，A'写入新binlog的位置，作为B的CHANGE MASTER TO命令的MASTER_LOG_POS参数
$ mysqlbinlog /var/lib/mysql/slave-bin.000009 --start-datetime='2019-02-26 17:44:00' --stop-datetime='2019-02-26 17:45:00' | grep end_log_pos
#190226 17:42:01 server id 2  end_log_pos 123 CRC32 0x5b852e9b     Start: binlog v 4, server v 5.7.25-log created 190226 17:42:01 at startup

位点不精确

假设在时刻T，原主库A已经执行完成了一个INSERT语句，插入一行记录R
- 并且已经将binlog传给A'和B，然后原主库A掉电
在B上，由于已经同步了binlog，R这一行是已经存在的
在新主库A'上，R这一行也是存在的，日志写在了123这个位置之后
在B上执行CHANGE MASTER TO，执行A'的File文件的123位置
- 就会把插入R这一行数据的binlog又同步到B去执行
- B的同步线程会报重复主键错误，然后停止同步

跳过错误

方式1：主动跳过一个事务，需要持续观察，每次碰到这些错误，就执行一次跳过命令

1 2	SET GLOBAL sql_slave_skip_counter=1; START SLAVE;

方式1：设置slave_skip_errors=1032,1062，1032错误是删除数据时找不到行，1062错误是插入数据时报唯一键冲突
在主从切换过程中，直接跳过1032和1062是无损的，等主从间的同步关系建立完成后，需要将slave_skip_errors恢复为OFF

mysql> SHOW VARIABLES LIKE '%slave_skip_errors%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| slave_skip_errors | OFF   |
+-------------------+-------+

基于GTID的切换

GTID: Global Transaction Identifier，全局事务ID
在事务提交时生成，是事务的唯一标识，组成GTID = server_uuid:gno
- server_uuid是实例第一次启动时自动生成的，是一个全局唯一 的值
- gno是一个整数，初始值为1，每次提交事务时分配，+1
官方定义：GTID = source_id:transaction_id
- source_id即server_uuid
- transaction_id容易造成误解
  - transaction_id一般指事务ID，是在事务执行过程中分配的，即使事务回滚了，事务ID也会递增
  - 而gno只有在事务提交时才会分配，因此GTID往往是连续的
开启GTID模式，添加启动参数gtid_mode=ON和enforce_gtid_consistency=ON
在GTID模式下，每个事务都会跟一个GTID一一对应，生成GTID的方式由参数gtid_next（Session）控制
每个MySQL实例都维护了一个GTID集合，用于表示：_实例执行过的所有事务_

gtid_next

gtid_next=AUTOMATIC，MySQL会将server_uuid:gno分配给该事务
- 记录binlog时，会先记录一行SET @@SESSION.GTID_NEXT=server_uuid:gno，将该GTID加入到本实例的GTID集合
gtid_next=UUID:NUMBER，通过SET @@SESSION.GTID_NEXT=current_gtid执行
- 如果current_gtid已经存在于实例的GTID集合中，那么接下来执行的这个事务会直接被系统忽略
- 如果current_gtid并没有存在于实例的GTID集合中，那么接下来执行的这个事务会被分配为current_gtid
- current_gtid只能给一个事务使用，如果执行下一个事务，需要把gtid_next设置成另一个GTID或者AUTOMATIC

-- gtid_next=AUTOMATIC
mysql> SHOW BINLOG EVENTS IN 'master-bin.000003';
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                                                       |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+
| master-bin.000003 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4                                                                                                      |
| master-bin.000003 | 123 | Previous_gtids |         1 |         194 | b8502fe3-3b4a-11e9-9562-0242ac110002:1-5                                                                                                   |
| master-bin.000003 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'b8502fe3-3b4a-11e9-9562-0242ac110002:6'                                                                          |
| master-bin.000003 | 259 | Query          |         1 |         484 | GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| master-bin.000003 | 484 | Gtid           |         1 |         549 | SET @@SESSION.GTID_NEXT= 'b8502fe3-3b4a-11e9-9562-0242ac110002:7'                                                                          |
| master-bin.000003 | 549 | Query          |         1 |         643 | CREATE DATABASE test                                                                                                                       |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------------------------------------------------------------------------------+

表初始化

CREATE TABLE `t` (
  `id` INT(11) NOT NULL,
  `c` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1,1);

对应的binlog

mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+-------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                         |
+-------------------+----------+--------------+------------------+-------------------------------------------+
| master-bin.000004 |      877 |              |                  | b8502fe3-3b4a-11e9-9562-0242ac110002:1-12 |
+-------------------+----------+--------------+------------------+-------------------------------------------+

mysql> SHOW BINLOG EVENTS IN 'master-bin.000004';
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name          | Pos | Event_type     | Server_id | End_log_pos | Info                                                               |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| master-bin.000004 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.25-log, Binlog ver: 4                              |
| master-bin.000004 | 123 | Previous_gtids |         1 |         194 | b8502fe3-3b4a-11e9-9562-0242ac110002:1-9                           |
| master-bin.000004 | 194 | Gtid           |         1 |         259 | SET @@SESSION.GTID_NEXT= 'b8502fe3-3b4a-11e9-9562-0242ac110002:10' |
| master-bin.000004 | 259 | Query          |         1 |         373 | use `test`; DROP TABLE `t` /* generated by server */               |
| master-bin.000004 | 373 | Gtid           |         1 |         438 | SET @@SESSION.GTID_NEXT= 'b8502fe3-3b4a-11e9-9562-0242ac110002:11' |
| master-bin.000004 | 438 | Query          |         1 |         620 | use `test`; CREATE TABLE `t` (
  `id` INT(11) NOT NULL,
  `c` INT(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB |
| master-bin.000004 | 620 | Gtid           |         1 |         685 | SET @@SESSION.GTID_NEXT= 'b8502fe3-3b4a-11e9-9562-0242ac110002:12' |
| master-bin.000004 | 685 | Query          |         1 |         757 | BEGIN                                                              |
| master-bin.000004 | 757 | Table_map      |         1 |         802 | table_id: 109 (test.t)                                             |
| master-bin.000004 | 802 | Write_rows     |         1 |         846 | table_id: 109 flags: STMT_END_F                                    |
| master-bin.000004 | 846 | Xid            |         1 |         877 | COMMIT /* xid=27 */                                                |
+-------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+

事务BEGIN之前有一条SET @@SESSION.GTID_NEXT
如果实例X有从库Z，那么将CREATE TABLE和INSERT语句的binlog同步到从库Z执行
- 执行事务之前，会先执行两个SET命令，这样两个GTID就会被加入到从库Z的GTID集合

主键冲突

如果实例X是实例Y的从库，之前实例Y上执行INSERT INTO t VALUES (1,1)
- 对应的GTID为aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12
- 实例X需要同步该事务过来执行，会报主键冲突的错误，实例X的同步线程停止，处理方法如下

-- 实例X提交一个空事务，将该GTID加到实例X的GTID集合中
SET gtid_next='aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12';
BEGIN;
COMMIT;
-- 实例X的Executed_Gtid_Set已经包含了aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12
mysql> SHOW MASTER STATUS;
+-------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                                                                  |
+-------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+
| master-bin.000004 |     1087 |              |                  | aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12,b8502fe3-3b4a-11e9-9562-0242ac110002:1-12  |
+-------------------+----------+--------------+------------------+------------------------------------------------------------------------------------+

-- 恢复GTID的默认分配行为
SET gtid_next=AUTOMATIC;

-- 实例X还是会继续执行实例Y传过来的事务
-- 但由于实例X的GTID集合已经包含了aaaaaaaa-bbbb-cccc-dddd-eeeeeeeeeeee:12，因此实例X会直接跳过该事务
START SLAVE;

主从切换

CHANGE MASTER TO
MASTER_HOST=$host_name
MASTER_PORT=$port
MASTER_USER=$user_name
MASTER_PASSWORD=$password
master_auto_position=1

master_auto_position=1：主从关系使用的是GTID协议，不再需要指定MASTER_LOG_FILE和MASTER_LOG_POS
实例A'的GTID集合记为set_a，实例B的GTID集合记为set_b
实例B执行START SLAVE，取binlog的逻辑如下

START SLAVE

实例B指定新主库A'，基于主从协议建立连接
实例B把set_b发送给A'
实例A'计算出seb_a和set_b的GTID差集（存在于set_a，但不存在于set_b的GTID集合）
- 判断实例A'本地是否包含了差集需要的所有binlog事务
- 如果没有全部包含，说明实例A'已经把实例B所需要的binlog删除掉了，直接返回错误
- 如果全部包含，实例A'从自己的binlog文件里面，找到第1个不在set_b的事务，发送给实例B
  - 然后从该事务开始，往后读文件，按顺序读取binlog，发给实例B去执行