内容简介:记录用
- 在状态1,客户端的读写都是直接访问节点A,节点B是节点A的从库
- 只是将节点A的更新都同步过来,在节点B本地执行,保持一致
- 在状态1,虽然节点B没有被直接访问,但依然建议设置成
readonly
模式- 运营类的查询语句会在从库上执行,设置成
readonly
模式能够防止一些误操作 - 防止切换逻辑有Bug,例如出现 双写 ,造成主 从不一致
- 可以通过
readonly
状态来判断节点的 角色
- 运营类的查询语句会在从库上执行,设置成
- 在状态1,节点B设置为
readonly
模式,同样能与节点A保持同步更新-
readonly
设置对 超级权限用户 是无效的,而节点B中用于 同步更新 的线程,就拥有超级权限
-
主从同步
在节点A执行update语句,然后同步到节点B
- 从库B与主库A之间维持一个 长连接 ,主库A内部有一个专门用于服务于从库B长连接的线程
- 在从库B上执行
CHANGE MASTER
命令,设置主库A的信息-
IP
、PORT
、USER
、PASSWORD
- 从 哪个位置 ( 文件名 + 日志偏移量 )开始请求
binlog
-
- 在从库B上执行
START SLAVE
命令,这时从库B会启动两个线程:io_thread
+sql_thread
-
io_thread
:负责与主库A 建立连接
-
- 主库A校验完
USER
和PASSWORD
后,按照从库B传过来的 位置信息 ,从本地读取binlog
,发送给从库B - 从库B拿到
binlog
后,写到本地文件,即 中转日志 (relaylog
) -
sql_thread
读取relaylog
,解析出日志里的命令,然后执行
binlog
格式
- STATEMENT
- ROW
- MIXED = STATEMENT + ROW
表初始化
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
SET binlog_format='STATEMENT'; -- mysql -c启动 DELETE FROM t /*comment*/ WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1;
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 */
-
BEGIN
与COMMIT
对应,包装成一个事务,xid=73
是 事务ID -
use test;
是 自动添加 的,保证日志在从库上执行时,能找到正确的库 -
STATEMENT
格式的binlog
记录的是 SQL原文
SHOW WARNINGS
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 | +----+-------------+-------+------------+-------+---------------+------+---------+-------+------+----------+-------------+
-
Unsafe
的原因:DELETE
+LIMIT
,可能会导致 主从不一致 - 如果
DELETE
语句使用的是索引a
,那么删除的是a=4
这一行 - 如果
DELETE
语句使用的是索引t_modified
,那么删除的是t_modified='2018-11-09'
这一行,即a=5
这一行 - 当
binlog_format=STATEMENT
,binlog
记录的只是 SQL原文 ,可能会导致 主从不一致
ROW
SET binlog_format='ROW'; -- mysql -c启动 DELETE FROM t /*comment*/ WHERE a>=4 AND T_MODIFIED<='2018-11-10' LIMIT 1;
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 */
-
ROW
格式的binlog
并没有记录 SQL原文 ,而是替换成了两个Event
:Table_map
+Delete_rows
-
Table_map Event
:说明要操作的是test.t
-
Delete_rows Event
:定义 删除 行为
-
- 查看更详细的信息需要借助
mysqlbinlog
命令,从12010
开始解析日志
mysqlbinlog
$ 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*/;
-
server id 1
:事务在server_id=1
的库上执行 - 每个
Event
都有CRC32
值,控制参数binlog_checksum
-
Table_map Event
会map到一个数字,代表一张要打开的表- 如果要操作多张表,会有多个
Table_map Event
- 如果要操作多张表,会有多个
-
@1=4,@2=4,@3=1541779200
:详细记录 各个字段的值 -
binlog_row_image
-
FULL
:记录 所有字段 的值 -
MINIMAL
:记录 必要 的信息,这里只会记录id=4
-
- 当
binlog_format=ROW
,传到从库的执行时会删除id=4
的行,不会主从不一致
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
-
STATEMENT
:可能导致 主从不一致 -
ROW
:非常 占用空间 和 耗费IO资源 -
MIXED
是一个折中方案- MySQL自行判断 SQL 语句是否有可能导致 主从不一致
- 如果有可能则采用
ROW
格式,否则采用STATEMENT
格式
- 线上配置最少是
MIXED
,更严格是ROW
( 推荐 ,可用于 恢复数据 )
now
SET binlog_format='MIXED'; INSERT INTO t VALUES (10,10, NOW());
-- 采用的是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 */
$ 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
- 即便执行的是
DELETE
语句,ROW
格式(binlog_row_image=FULL
)的binlog
也会保存被删除的 整行数据 - 当发现误删数据后,可以直接将
binlog
中的DELETE
换成INSERT
即可
INSERT
- 与
DELETE
类似,能 精确定位 到误插入的数据 - 将
INSERT
换成DELETE
即可
UPDATE
- 针对
UPDATE
语句,ROW
格式的binlog
记录的是 修改前后的整行数据 - 如果是误更新,只需要将这个
EVENT
前后的两行信息 对调 一下,再到数据库执行即可
标准做法
用 mysqlbinlog
先 解析 出来,然后把 整个解析结果 发个 MySQL 执行
$ mysqlbinlog binlog.000014 --start-position=14314 --stop-position=14599 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;
循环复制
线上常用为 Master-Master
结构,节点A与节点B为 互为主从 关系(在切换过程中无需修改主从关系)
-
log_slave_updates=ON
,从库执行完relaylog
后也会生成binlog
- 从节点A更新的事务,
binlog
记录的都是节点A的server id
- 传到节点B执行以后,节点B生成的
binlog
的server id
依然是节点A的server id
- 再传回到节点A,节点A判断到这个
server id
与自己相同,就不会再处理该日志,解决 循环复制 的问题
mysql> SHOW VARIABLES LIKE '%log_slave%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | log_slave_updates | ON | +-------------------+-------+
参考资料
《MySQL实战45讲》
转载请注明出处:http://zhongmingmao.me/2019/02/22/mysql-master-slave-replication/
访问原文「MySQL -- 主从一致」获取最佳阅读体验并参与讨论
以上就是本文的全部内容,希望对大家的学习有所帮助,也希望大家多多支持 码农网
猜你喜欢:本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
Operating Systems
Remzi Arpaci-Dusseau、Andrea Arpaci-Dusseau / Arpaci-Dusseau Books / 2012-8-19 / USD 21.00
A book about modern operating systems. Topics are broken down into three major conceptual pieces: Virtualization, Concurrency, and Persistence. Includes all major components of modern systems includin......一起来看看 《Operating Systems》 这本书的介绍吧!