警惕！replicate_do_db有坑！

写在前面：
笔者采用传统方式搭建的主从环境，主库更新记录后，从库不能将数据同步过去，在从库查看主从复制状态，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
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
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/*!*/;
/*!\C utf8 *//*!*/;
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 /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

查看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 过滤规则的 "坑"

网站标题：警惕！replicate_do_db有坑！
文章分享：http://azwzsj.com/article/ipgeog.html

警惕！replicate_do_db有坑！

其他资讯