主从切换

  1. 在状态1，客户端的读写都是直接访问节点A，节点B是节点A的从库
    • 只是将节点A的更新都同步过来，在节点B本地执行，保持一致
  2. 在状态1，虽然节点B没有被直接访问，但依然建议设置成readonly模式
    • 运营类的查询语句会在从库上执行，设置成readonly模式能够防止一些误操作
    • 防止切换逻辑有Bug，例如出现双写，造成主从不一致
    • 可以通过readonly状态来判断节点的角色
  3. 在状态1，节点B设置为readonly模式，同样能与节点A保持同步更新
    • readonly设置对超级权限用户是无效的，而节点B中用于同步更新的线程，就拥有超级权限

主从同步

在节点A执行update语句，然后同步到节点B

  1. 从库B与主库A之间维持一个长连接，主库A内部有一个专门用于服务于从库B长连接的线程
  2. 在从库B上执行CHANGE MASTER命令，设置主库A的信息
    • IP、PORT、USER、PASSWORD
    • 从哪个位置（文件名 + 日志偏移量）开始请求binlog
  3. 在从库B上执行START SLAVE命令，这时从库B会启动两个线程：io_thread + sql_thread
    • io_thread：负责与主库A建立连接
  4. 主库A校验完USER和PASSWORD后，按照从库B传过来的位置信息，从本地读取binlog，发送给从库B
  5. 从库B拿到binlog后，写到本地文件，即中转日志（relaylog）
  6. sql_thread读取relaylog，解析出日志里的命令，然后执行

binlog

格式

  1. STATEMENT
  2. ROW
  3. MIXED = STATEMENT + ROW

表初始化

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE `t` (
  `id` INT(11) NOT NULL,
  `a` INT(11) DEFAULT NULL,
  `t_modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `a` (`a`),
  KEY `t_modified`(`t_modified`)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1,1,'2018-11-13');
INSERT INTO t VALUES (2,2,'2018-11-12');
INSERT INTO t VALUES (3,3,'2018-11-11');
INSERT INTO t VALUES (4,4,'2018-11-10');
INSERT INTO t VALUES (5,5,'2018-11-09');

STATEMENT

1
2
3
SET binlog_format='STATEMENT';
-- mysql -c启动
DELETE FROM t /*comment*/  WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1;
1
2
3
4
5
mysql > SHOW BINLOG EVENTS IN 'binlog.000014';
| binlog.000014 | 6814 | Anonymous_Gtid |         1 |        6889 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.000014 | 6889 | Query          |         1 |        6979 | BEGIN
| binlog.000014 | 6979 | Query          |         1 |        7138 | use `test`; DELETE FROM t /*comment*/  WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1
| binlog.000014 | 7138 | Xid            |         1 |        7169 | COMMIT /* xid=73 */
  1. BEGIN与COMMIT对应，包装成一个事务，xid=73是事务ID
  2. use test;是自动添加的，保证日志在从库上执行时，能找到正确的库
  3. STATEMENT格式的binlog记录的是SQL原文

SHOW WARNINGS

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> SHOW WARNINGS;
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                        |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1592 | Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT.                             |
|       |      | The statement is unsafe because it uses a LIMIT clause. This is unsafe because the set of rows included cannot be predicted.   |
+-------+------+--------------------------------------------------------------------------------------------------------------------------------+

mysql> EXPLAIN DELETE FROM t /*comment*/  WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1;
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
|  1 | DELETE      | t     | NULL       | range | a,t_modified  | a    | 5       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
  1. Unsafe的原因：DELETE + LIMIT，可能会导致主从不一致
  2. 如果DELETE语句使用的是索引a，那么删除的是a=4这一行
  3. 如果DELETE语句使用的是索引t_modified，那么删除的是t_modified='2018-11-09'这一行，即a=5这一行
  4. 当binlog_format=STATEMENT，binlog记录的只是SQL原文，可能会导致主从不一致

ROW

1
2
3
SET binlog_format='ROW';
-- mysql -c启动
DELETE FROM t /*comment*/  WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1;
1
2
3
4
5
6
mysql > SHOW BINLOG EVENTS IN 'binlog.000014';
| binlog.000014 | 12010 | Anonymous_Gtid |         1 |       12085 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.000014 | 12085 | Query          |         1 |       12168 | BEGIN
| binlog.000014 | 12168 | Table_map      |         1 |       12218 | table_id: 72 (test.t)
| binlog.000014 | 12218 | Delete_rows    |         1 |       12266 | table_id: 72 flags: STMT_END_F
| binlog.000014 | 12266 | Xid            |         1 |       12297 | COMMIT /* xid=102 */
  1. ROW格式的binlog并没有记录SQL原文，而是替换成了两个Event：Table_map + Delete_rows
    • Table_map Event：说明要操作的是test.t
    • Delete_rows Event：定义删除行为
  2. 查看更详细的信息需要借助mysqlbinlog命令，从12010开始解析日志

mysqlbinlog

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
$ mysqlbinlog -vv ./binlog.000014 --start-position=12010;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190221 13:29:32 server id 1  end_log_pos 124 CRC32 0xe3d095e4     Start: binlog v 4, server v 8.0.12 created 190221 13:29:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PDduXA8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA8N25cEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgHkldDj
'/*!*/;
# at 12010
#190222 16:35:19 server id 1  end_log_pos 12085 CRC32 0xfab19774     Anonymous_GTID	last_committed=39	sequence_number=40	rbr_only=yes	original_committed_timestamp=1550824519718005	immediate_commit_timestamp=1550824519718005	transaction_length=287
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1550824519718005 (2019-02-22 16:35:19.718005 CST)
# immediate_commit_timestamp=1550824519718005 (2019-02-22 16:35:19.718005 CST)
/*!80001 SET @@session.original_commit_timestamp=1550824519718005*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 12085
#190222 16:35:19 server id 1  end_log_pos 12168 CRC32 0x322f1087     Query	thread_id=12	exec_time=0	error_code=0
SET TIMESTAMP=1550824519/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=33/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
BEGIN
/*!*/;
# at 12168
#190222 16:35:19 server id 1  end_log_pos 12218 CRC32 0x7cb9c355     Table_map: `test`.`t` mapped to number 72
# at 12218
#190222 16:35:19 server id 1  end_log_pos 12266 CRC32 0x155fe45e     Delete_rows: table id 72 flags: STMT_END_F

BINLOG '
R7RvXBMBAAAAMgAAALovAAAAAEgAAAAAAAEABHRlc3QAAXQAAwMDEQEAAgEBAFXDuXw=
R7RvXCABAAAAMAAAAOovAAAAAEgAAAAAAAEAAgAD/wAEAAAABAAAAFvlrwBe5F8V
'/*!*/;
### DELETE FROM `test`.`t`
### WHERE
###   @1=4 /* INT meta=0 nullable=0 is_null=0 */
###   @2=4 /* INT meta=0 nullable=1 is_null=0 */
###   @3=1541779200 /* TIMESTAMP(0) meta=0 nullable=0 is_null=0 */
# at 12266
#190222 16:35:19 server id 1  end_log_pos 12297 CRC32 0x4d1336cc     Xid = 102
COMMIT/*!*/;
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*/;
  1. server id 1：事务在server_id=1的库上执行
  2. 每个Event都有CRC32值，控制参数binlog_checksum
  3. Table_map Event会map到一个数字，代表一张要打开的表
    • 如果要操作多张表，会有多个Table_map Event
  4. @1=4,@2=4,@3=1541779200：详细记录各个字段的值
  5. binlog_row_image
    • FULL：记录所有字段的值
    • MINIMAL：记录必要的信息，这里只会记录id=4
  6. 当binlog_format=ROW，传到从库的执行时会删除id=4的行，不会主从不一致
1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SHOW VARIABLES LIKE '%binlog_checksum%';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| binlog_checksum | CRC32 |
+-----------------+-------+

mysql> SHOW VARIABLES LIKE '%binlog_row_image%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| binlog_row_image | FULL  |
+------------------+-------+

MIXED

  1. STATEMENT：可能导致主从不一致
  2. ROW：非常占用空间和耗费IO资源
  3. MIXED是一个折中方案
    • MySQL自行判断SQL语句是否有可能导致主从不一致
    • 如果有可能则采用ROW格式，否则采用STATEMENT格式
  4. 线上配置最少是MIXED，更严格是ROW（推荐，可用于恢复数据）

now

1
2
SET binlog_format='MIXED';
INSERT INTO t VALUES (10,10, NOW());
1
2
3
4
5
6
-- 采用的是STATEMENT格式
mysql > SHOW BINLOG EVENTS IN 'binlog.000014';
| binlog.000014 | 14314 | Anonymous_Gtid |         1 |       14389 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
| binlog.000014 | 14389 | Query          |         1 |       14479 | BEGIN
| binlog.000014 | 14479 | Query          |         1 |       14599 | use `test`; INSERT INTO t VALUES (10,10, NOW())
| binlog.000014 | 14599 | Xid            |         1 |       14630 | COMMIT /* xid=116 */
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
$ mysqlbinlog -vv ./binlog.000014 --start-position=14479 --stop-position=14599;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#190221 13:29:32 server id 1  end_log_pos 124 CRC32 0xe3d095e4     Start: binlog v 4, server v 8.0.12 created 190221 13:29:32 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
PDduXA8BAAAAeAAAAHwAAAABAAQAOC4wLjEyAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA8N25cEwANAAgAAAAABAAEAAAAYAAEGggAAAAICAgCAAAACgoKKioAEjQA
CgHkldDj
'/*!*/;
# at 14479
#190222 17:39:17 server id 1  end_log_pos 14599 CRC32 0xcbe6d9c4     Query	thread_id=12	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1550828357/*!*/;
SET @@session.pseudo_thread_id=12/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1168113696/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=33/*!*/;
SET @@session.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80005 SET @@session.default_collation_for_utf8mb4=255*//*!*/;
INSERT INTO t VALUES (10,10, NOW())
/*!*/;
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*/;

记录binlog时，执行了SET TIMESTAMP命令，约定了后续now()函数的返回值，因此确保了主从一致

恢复数据

DELETE

  1. 即便执行的是DELETE语句，ROW格式（binlog_row_image=FULL）的binlog也会保存被删除的整行数据
  2. 当发现误删数据后，可以直接将binlog中的DELETE换成INSERT即可

INSERT

  1. 与DELETE类似，能精确定位到误插入的数据
  2. 将INSERT换成DELETE即可

UPDATE

  1. 针对UPDATE语句，ROW格式的binlog记录的是修改前后的整行数据
  2. 如果是误更新，只需要将这个EVENT前后的两行信息对调一下，再到数据库执行即可

标准做法

用mysqlbinlog先解析出来，然后把整个解析结果发个MySQL执行

1
$ mysqlbinlog binlog.000014 --start-position=14314 --stop-position=14599 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

循环复制

线上常用为Master-Master结构，节点A与节点B为互为主从关系（在切换过程中无需修改主从关系）

  1. log_slave_updates=ON，从库执行完relaylog后也会生成binlog
  2. 从节点A更新的事务，binlog记录的都是节点A的server id
  3. 传到节点B执行以后，节点B生成的binlog的server id依然是节点A的server id
  4. 再传回到节点A，节点A判断到这个server id与自己相同，就不会再处理该日志，解决循环复制的问题
1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE '%log_slave%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| log_slave_updates | ON    |
+-------------------+-------+

参考资料

《MySQL实战45讲》