深圳营销型网站建设-龙华信科,cnzz网站代做,齐河网站建设公司,做网站代码审计哪个工具比较好MySQL 主备一致主备切换binlog 格式statementrowmixed生产格式循环复制问题主备切换
MySQL 主备切换流程 :
状态 1 : 客户端的读写都直接访问节点 A#xff0c;而节点 B 是 A 的备库#xff0c;只将 A 的更新都同步过来 , 并本地执行。来保持节点 B 和 A 的数据是相同当切换…
MySQL 主备一致主备切换binlog 格式statementrowmixed生产格式循环复制问题主备切换
MySQL 主备切换流程 :
状态 1 : 客户端的读写都直接访问节点 A而节点 B 是 A 的备库只将 A 的更新都同步过来 , 并本地执行。来保持节点 B 和 A 的数据是相同当切换成状态 2 : 客户端读写访问的都是节点 B而节点 A 是 B的备库 建议将备库设为只读 (readonly) 模式
一般查询语句会被放备库上查设置为只读 , 能防止误操作防止切换逻辑有 bug如 : 切换过程中出现双写造成主备不一致readonly 状态能判断节点的角色readonly 对超级 (super) 权限用户是无效而同步更新的线程 (有超级权限)
主备流程图 :
备库 B 通过 change master 设置主库 A 的 IP、端口、用户名、密码从哪个位置 (文件名 , 日志偏移量) 开始请求 binlog备库 B 执行 start slave 备库会启动两个线程 : io_thread (与主库建立连接 ) , sql_thread主库 A 校验完用户名、密码后开始按备库 B 传过来的位置从本地读取 binlog发给 B备库 B 拿到 binlog 后写到本地文件 (中转日志 (relay log) )sql_thread 读取中转日志解析出日志里的命令并执行 binlog 格式
binlog 有两种格式 :
statementrowmixed : 前两种格式的混合
测试表/数据 :
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)
) ENGINEInnoDB;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);查看 binlog 记录 delete 语句
delete from t /*comment*/ where a4 and t_modified2018-11-10 limit 1;statement
statement : binlog 记录的 SQL 原语句
show binlog events in master.000001;警告信息 :
statement 格式delete 中带 limit会认为该命令是 unsafe
show warnings;statement : delete 带 limit可能出现主备数据不一致 :
delete 用索引 a就会删除 a4 该行用索引 t_modified那就删除 t_modified2018-11-09’ ( a5 该行 )当主库用索引 a而备库用索引 t_modified 。就会出现主备不一致
row
row 格式 binlog :
row 的 binlog 没有 SQL 原语句只能换成 Table_map (操作表) , Delete_rows (删除的行为) 解析 binlog 内容 : 从 8900 位置开始
server id 1 : 该事务在 server_id1 该库Table_map : 显示打开的表map 226 : 用于区分不同表binlog_row_image 默认配置是 FULLDelete_rows 会有删掉的行的所有字段的值 ; binlog_row_image MINIMAL : 记录必要的信息只记录 id4 信息最后 Xid : 事务被正确提交
mysqlbinlog -vv data/master.000001 --start-position8900;binlog_formatrow : binlog 记录真实删除行的主键 idbinlog 传到备库去就肯定会删除 id4 行不会造成主备不一致问题
mixed
mixed : 即利用 statment 优点又避免了数据不一致问题 MySQL 会判断该 SQL 是否可能引发主备不一致可能就用 row 格式否则就用 statement 格式 row 的缺点 : 很占存储。如 : delete 删掉 10 万行数据 , 0 万条记录都写到 binlog statement : SQL 记录到 binlog 仅占用几十个字节
mixed 格式的特别点 : insert into t values(10, 10, now());该 SQL 用 statement 格式 : 炸一看会引发主备不一致 用 mysqlbinlog 查看 :
其中 : SETTIMESTAMP1546103491 : 约定 now() 的返回时间依然能保障主备数据的一致性 生产格式
生产环境依然用 row 格式 , 其原因 : 利于恢复数据
各CUD 的恢复角度 :
delete : 会把删掉的整行信息保存起来。所以当执行完一条 delete 后发现删错数据了就在 binlog 中记录的 delete 转成 insert把错删的数据插入就可以恢复insert : 会记录所有的字段信息能精准确定刚插入的那行数据。只要将 insert 转成 delete 删除掉被误插入数据就行update : 会记录修改前整行的数据和修改后的整行数据。只要将 event 前后的信息对调下再去进行更新恢复
循环复制问题
生产环境使用比较多的是双 M 结构 : 节点 A 和 B 之间总是互为主备关系 双 M 结构有个循环复制问题 :
建议参数 : log_slave_updates on : 备库执行 relay log 后生成 binlog节点 A 更新一条语句再生成 binlog 发给节点 B节点 B 执行完更新语句 , 也会生成 binlog而节点 A 同时又是节点 B 的备库 : 会把节点 B 新生成的 binlog 拿过来执行一次
解决循环复制问题 :
两个节点的 server id 必须不同 (或者不能成为主备关系)binlog 中记录该命令第一次执行时 , 所在实例的 server id备库接到 binlog 并重放时生成原 binlog 的 server id 相同的新 binlog备库 binlog 后会先判断 server id当与自己相同表示该 binlog 是自己生成就直接丢弃该 binlog
具体流程 :
节点 A 更新数据binlog 记录 A 的 server id传到节点 B 执行后节点 B 生成的 binlog 的 server id 就是 A 的 server id再传给节点 AA 判断该 server id 是否与自己的相同当相同就不执行该日志死循环在这里就断掉
