关于mysql主从同步之 sql_slave_skip_counter 和 指定库同步
当我很饿的时候, 我总是想点很多的菜, 吃一会儿才发现自己很浪费, 而如果我细心去思考后再点菜时, 可能当我点完时, 别人早已吃完饭. 这就是正确处理事情的复杂性. 每一次的学习
一. 使用 sql_slave_skip_counter 时注意事项.
首先主从同步报错最多的情况:
类似:
Last_Error: Could not execute Update_rows event on table edusoho_e.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 1127 Last_Error: Could not execute Delete_rows event on table edusoho_e.t1; Can't find record in 't1', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000002, end_log_pos 7049
我们会用到 set global sql_slave_skip_counter=N; 来跳过这个错误, 以使数据库能正常同步. 大多数人对理解此的理解是跳过此sql语句. 事实上, 此参数调整并不那么简单的理解, 这也是我们可能出错的原因.
此参数设置是跳过N个events。注意:以event为单位,而不是以事务为单位,只有在由单条语句组成的事务时,两者才等价。如:一个事务由多个EVENT组成,BEGIN;INSERT;UPDATE;DELETE;COMMOIT; 此时, 两者就不会相等.
这里只做个总结, 如下:
1. 对于非事务表, 如数据表是myisam 则直接跳过此错误语句
2. 对于事务表, 此错误是在一个event 事务中, 则跳过整个事务event group, 会导致此sql后面的sql全部跳过.
3. 如果你使用了 N > 1 则很可能, 跳过了不少有效的sql语句.
从这个总结来看, 排除一个类似的错误并,而又希望主从数据完全一致并非易事. 最好的解决办法就是重做从库.
官方给的解决方法是设置另一个参数 slave_exec_mode, 默认值是 STRICT, 修改为IDEMPOTENT即可以只跳过错误sql:
set global slave_exec_mode='IDEMPOTENT';
此时虽然是可以只跳过此sql, 但如果从库已占用了此primary_key,就算是跳过错误, 数据还是不致的, 你可以核对数据并进行修正, 复杂度挺高的, 建议重做更安全.
二. 指定同步的数据需要配置的那些参数
场景: 指定同步数据库 shop 和 user , 其它数据库无需同步. 配置如下:
binlog_format=row # 可选模式 ROW replicate-do-db=shop replicate-do-db=user replicate_wild_do_table=shop.% replicate_wild_do_table=user.%
命令查看:
show slave status\G Replicate_Do_DB: shop,user Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: shop.%,user.% Replicate_Wild_Ignore_Table:
主要目的, 测试指定库同步时, 跨库操作的执行情况:
测试1:
Master: binlog_format=mixed
Master 操作: 未指定数据库, 即未使用 use shop 的情况下:
UPDATE `shop`.`bd_cart` SET `name` = '20110a1a11d' WHERE (`cart_id` = '1');
执行成功,
核对slave从库状态, 结果: 数据未同步
测试2:
Master: binlog_format=mixed
Master 操作: 指定数据库, 即使用 use shop 的情况下:
UPDATE `shop`.`bd_cart` SET `name` = '20110a1a11d' WHERE (`cart_id` = '1');
执行成功,
核对slave从库状态, 结果: 数据正确同步
测试3:
Master: binlog_format=row
Master 操作: 未指定数据库, 即未使用 use shop 的情况下:
UPDATE `shop`.`bd_cart` SET `name` = '20110a1a11d' WHERE (`cart_id` = '1');
执行成功,
核对slave从库状态, 结果: 数据正确同步
测试4:
Master: binlog_format=row
Master 操作: 指定数据库, 即使用 use user 的情况下:
UPDATE `shop`.`bd_cart` SET `name` = '20110a1a11d' WHERE (`cart_id` = '1');
执行成功,
核对slave从库状态, 结果: 数据正确同步
测试5:
Master: binlog_format=row
Master 操作: 未指定数据库, 即未使用 use shop 的情况下, 通过 create table xxx select 未同步的库.表 ... 的方式执行,
create table shop.a_axm SELECT * FROM xx.impl_channel_price
执行成功,
核对slave从库状态, 结果: 同步报错:
Last_SQL_Error: Error executing row event: 'Table 'shop.a_axm' doesn't exist'
测试6:
Master: binlog_format=row
Master 操作: 指定数据库, 即使用 use shop 的情况下, 通过 create table xxx select 未同步的库.表 ... 的方式执行,
create table shop.a_axm SELECT * FROM xx.impl_channel_price
执行成功,
核对slave从库状态, 结果: 数据正确同步
查看binlog, 每个binlog前都有`shop`. 即符合了 replicate_wild_do_table=shop.% 条件进行同步.
### INSERT INTO `shop`.`a_axm` ### SET ### @1=124 /* INT meta=0 nullable=0 is_null=0 */ ### @2=136 /* LONGINT meta=0 nullable=1 is_null=0 */ ### @3='赢销+' /* VARSTRING(600) meta=600 nullable=1 is_null=0 */ ### @4=16 /* INT meta=0 nullable=1 is_null=0 */ ### @5='本地商超' /* VARSTRING(150) meta=150 nullable=1 is_null=0 */ ### @6=673 /* INT meta=0 nullable=1 is_null=0 */ ### @7='50135' /* VARSTRING(90) meta=90 nullable=1 is_null=0 */ ### @8='魅夜热情卡布琳娜6度310ML 1x24纸箱听装' /* VARSTRING(300) meta=300 nullable=1 is_null=0 */ ### @9=88 /* INT meta=0 nullable=1 is_null=0 */ ### @10=NULL /* INT meta=0 nullable=1 is_null=1 */ ### @11=157.00 /* DECIMAL(10,2) meta=2562 nullable=1 is_null=0 */ ### @12=0.00 /* DECIMAL(10,2) meta=2562 nullable=0 is_null=0 */ ### @13=1554193412 /* INT meta=0 nullable=1 is_null=0 */ ### @14=1554193367 /* TIMESTAMP(0) meta=0 nullable=1 is_null=0 */ ### @15=0 /* TINYINT meta=0 nullable=1 is_null=0 */ ### INSERT INTO `shop`.`a_axm` ### SET
综上, 如果不能保证正确的使用sql执行, 建议还是完整同步, 以避免产生不必要的错误.
其它:
1.1 指定sql是否记录binlog , 可以使用
关闭: set session sql_log_bin=0;
开启: set session sql_log_bin=1;
session 是针对当关会话生效,
注意, 如果使用 global 那么只对新的会员生效, 你需要干掉已存在的所有链接.
1.2 如何将表从一个数据迁移到另一个数据库 https://dev.mysql.com/doc/refman/8.0/en/rename-table.html
rename table from a.xxx to b.xxx;
or
rename table a.xxx to b.xxx;
也可以一个sql语句重命名多个表;
RENAME TABLE old_table1 TO new_table1,
old_table2 TO new_table2,
old_table3 TO new_table3;
在同一数据库中:
rename table from table1 to table2
等效于
alter table table1 rename table2;
区别在于 rename table 可以在一个语句中重命名多张表.
1.3 如果指定不记binlog的数据库:
请不要记为 binlog_do_db=a,b,c
虽然不报错, 但也不会记录任何binlog, 因为此选项应该是指定一个数据库
正确写法, 三个数据库请写三条:
binlog_do_db=a
binlog_do_db=b
binlog_do_db=c
2. 关于 MySQL replicate-ignore-db 应该知道:
from: https://www.cnblogs.com/xiaoit/p/4599914.html
1:官方的解释是:在主从同步的环境中,replicate-ignore-db用来设置不需要同步的库。解释的太简单了,但是里面还有很多坑呢。
生产库上不建议设置过滤规则。如果非要设置,那就用Replicate_Wild_Ignore_Table: mysql.%吧。实验的很简单,如下
第一种情况 从库: replicate-ignore-db = mysql 主库: use mysql CREATE TABLE test.testrepl1( id int(5))ENGINE=INNODB DEFAULT CHARSET=UTF8; 从库不会同步。坑 第二种情况 从库: replicate-ignore-db = mysql 主库: DDL use test CREATE TABLE mysql.testrepl2( id int(5))ENGINE=INNODB DEFAULT CHARSET=UTF8; 从库不会同步。 第三种情况 DML use test update mysql.user set user = 'testuser5' where user = 'testuser1'; 从库会同步 第四种情况 grant all on *.* to testnowild@'%' identified by 'ge0513.hudie'; 从库会同步 第二大类: Replicate_Wild_Ignore_Table: mysql.% 第五种情况 主库: use test update mysql.user set user = 'testuser1' where user = 'testuser5'; 从库没有同步。 第六种情况 主库: grant all on *.* to testwild@'%' identified by 'ge0513.hudie'; 从库没有同步。