关于mysql主从同步之 sql_slave_skip_counter 和 指定库同步

分类:Mysql |

当我很饿的时候, 我总是想点很多的菜, 吃一会儿才发现自己很浪费, 而如果我细心去思考后再点菜时, 可能当我点完时, 别人早已吃完饭. 这就是正确处理事情的复杂性. 每一次的学习

一. 使用 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';
从库没有同步。