内容简介：记录用
- 在状态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 -- 主从一致」获取最佳阅读体验并参与讨论
以上就是本文的全部内容，希望对大家的学习有所帮助，也希望大家多多支持 码农网
猜你喜欢:
本站部分资源来源于网络，本站转载出于传递更多信息之目的，版权归原作者或者来源机构所有，如转载稿涉及版权问题，请联系我们。
Algorithms + Data Structures = Programs
Niklaus Wirth / Prentice Hall / 1975-11-11 / GBP 84.95
It might seem completely dated with all its examples written in the now outmoded Pascal programming language (well, unless you are one of those Delphi zealot trying to resist to the Java/.NET dominanc......一起来看看 《Algorithms + Data Structures = Programs》 这本书的介绍吧!
