文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

警惕!replicate_do_db 有坑!

2024-04-02 19:55

关注

写在前面:
笔者采用传统方式搭建的主从环境,主库更新记录后,从库不能将数据同步过去,在从库查看主从复制状态,Read_Master_Log_Pos 和 Exec_Master_Log_Pos 一致,I/O、SQL线程都正常,没有主从延迟发生,没有人为的设置延迟更新参数,主库binlog和从库relay log都有相应的更新记录,从库错误日志没有任何复制相关的error信息。如果你和笔者是同样的情况,那么你可能和笔者一样,遇到了复制过滤规则的 "坑"

环境:
MySQL5.6(MySQL5.7,MySQL8没有亲自测过)

场景复现:

Master配置:
[mysqld]
datadir = /home/data/mysql3306/
port = 3306
server_id = 1
binlog_format = row
log_bin = /home/data/mysql3306/binlog

SLave配置:
[mysqld]
datadir = /home/data/mysql3306/
port = 3306
binlog_format = row
server_id=2
relay_log = /home/data/mysql3306/relaylog
replicate_do_db=edusoho_e,statis

Master授权复制连接用户:
mysql> grant replication slave on *.*to repliter@'192.168.32.2' identified by PASSWORD ' *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9';
Query OK, 0 rows affected (0.01 sec)


mysql> flush logs;
Query OK, 0 rows affected (0.03 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      120 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Slave开启数据复制:
CHANGE MASTER TO MASTER_HOST='192.168.32.3',MASTER_USER='repliter',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='binlog.000004',MASTER_LOG_POS=120;
Query OK, 0 rows affected, 2 warnings (0.05 sec)

mysql> start slave;
Query OK, 0 rows affected (0.03 sec)

mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: edusoho_e,statis

主从复制状态正常!

Master变更了数据:
mysql> create database edusoho_e;
Query OK, 1 row affected (0.00 sec)

mysql> use edusoho_e;
Database changed

CREATE TABLE `t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

mysql> INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e', 'ldl', 'dba');
Query OK, 1 row affected (0.01 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000004 |      882 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Master的binlog日志是正常的

然而你在Slave主机上看不到新建的表及其数据
Slave:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

查看主从复制状态:
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.32.3
Master_User: repliter
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 882
Relay_Log_File: relaylog.000002
Relay_Log_Pos: 1042
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: edusoho_e,statis
Exec_Master_Log_Pos: 882
Seconds_Behind_Master: 0
SQL_Delay: 0

你会发现 I/O、SQL 线程正常;Read_Master_Log_Pos 和 Exec_Master_Log_Pos 值相同;Seconds_Behind_Master 值为0,说明没有主从延迟发生;SQL_Delay 值为0,说明没有主观设置延迟插入;虽然设置了主从过滤规则,但也只是复制该库的,难道是Slave的relay log出了问题,没有记录Master的日志?

到Slave去分析relay log日志,会发现也是有相应的Master的日志的
[root@slave mysql3306]# mysqlbinlog -v --base64-output=decode relaylog.000002
;
;
;
DELIMITER ;
# at 4
#190530  9:32:17 server id 2  end_log_pos 120 CRC32 0x35d47ba3  Start: binlog v 4, server v 5.6.16-log created 190530  9:32:17
# at 120
#700101  8:00:00 server id 1  end_log_pos 0 CRC32 0x0166516e    Rotate to binlog.000004  pos: 120
# at 164
#190530  9:29:02 server id 1  end_log_pos 0 CRC32 0xfea4f75a    Start: binlog v 4, server v 5.6.16-log created 190530  9:29:02
# at 280
#190530  9:35:18 server id 1  end_log_pos 229 CRC32 0x6b0d2047  Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1559180118;
SET @@session.pseudo_thread_id=2;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1;
SET @@session.sql_mode=1073741824;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1;
;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33;
SET @@session.lc_time_names=0;
SET @@session.collation_database=DEFAULT;
create database edusoho_e
;
# at 389
#190530  9:35:31 server id 1  end_log_pos 653 CRC32 0x1268f754  Query   thread_id=2 exec_time=0 error_code=0
use `edusoho_e`;
SET TIMESTAMP=1559180131;
CREATE TABLE `t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
;
# at 813
#190530  9:35:41 server id 1  end_log_pos 730 CRC32 0x20610ab1  Query   thread_id=2 exec_time=0 error_code=0
SET TIMESTAMP=1559180141;
BEGIN
;
# at 890
#190530  9:35:41 server id 1  end_log_pos 791 CRC32 0xc2edbad8  Table_map: `edusoho_e`.`t1` mapped to number 540
# at 951
#190530  9:35:41 server id 1  end_log_pos 851 CRC32 0xaa57d74f  Write_rows: table id 540 flags: STMT_END_F
### INSERT INTO `edusoho_e`.`t1`
### SET
###   @1=1
###   @2='edusoho_e'
###   @3='ldl'
###   @4=1
###   @5='dba'
###   @6=18
# at 1011
#190530  9:35:41 server id 1  end_log_pos 882 CRC32 0x7de64644  Xid = 1350
COMMIT;
DELIMITER ;
# End of log file
ROLLBACK ;
;
;

查看Slave的错误日志,也没有看到任何复制error相关的信息

那么问题来了,这可能是遭遇了BUG! 笔者也忘记了,是某位大佬说过,还是在某博客中看到过,如果Slave配置了replicate_do_db 过滤规则,如果写成了如下形式:
replicate_do_db=edusoho_e,statis 可能会遭遇BUG,需要分开来写
replicate_do_db=edusoho_e
replicate_do_db=statis

重启Slave以验证猜想

Master:
mysql> flush logs;
Query OK, 0 rows affected (0.44 sec)

mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000005 |      120 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

SET @@session.sql_log_bin=0;

DROP DATABASE `edusoho_e`;

mysql> create database edusoho_e;
Query OK, 1 row affected (0.00 sec)

mysql> use edusoho_e;
Database changed

CREATE TABLE `t1` (
`id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`xname` VARCHAR(20) NOT NULL DEFAULT '',
`address` CHAR(20) NOT NULL DEFAULT '',
`sex` TINYINT(1) NOT NULL DEFAULT '1',
`hobby` VARCHAR(30) NOT NULL DEFAULT '',
`age` TINYINT(2) DEFAULT '18',
PRIMARY KEY (`id`),
KEY `idx_name` (`xname`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

mysql> INSERT INTO `edusoho_e`.`t1` (`xname`, `address`, `hobby`) VALUES ('edusoho_e', 'ldl', 'dba');
Query OK, 1 row affected (0.01 sec)

Slave:
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| edusoho_e          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from edusoho_e.t1;
+----+-----------+---------+-----+-------+------+
| id | xname     | address | sex | hobby | age  |
+----+-----------+---------+-----+-------+------+
|  1 | edusoho_e | ldl     |   1 | dba   |   18 |
+----+-----------+---------+-----+-------+------+
1 row in set (0.00 sec)

你会发现新建的表和数据都同步过去了,说明确实是 replicate_do_db 过滤规则的 "坑"

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯