警惕！replicate_do_db 有坑！

寫在前面：
筆者采用傳統(tǒng)方式搭建的主從環(huán)境，主庫更新記錄后，從庫不能將數(shù)據(jù)同步過去，在從庫查看主從復(fù)制狀態(tài)，Read_Master_Log_Pos 和 Exec_Master_Log_Pos 一致，I/O、SQL線程都正常，沒有主從延遲發(fā)生，沒有人為的設(shè)置延遲更新參數(shù)，主庫binlog和從庫relay log都有相應(yīng)的更新記錄，從庫錯(cuò)誤日志沒有任何復(fù)制相關(guān)的error信息。如果你和筆者是同樣的情況，那么你可能和筆者一樣，遇到了復(fù)制過濾規(guī)則的 "坑"

環(huán)境：
MySQL5.6（MySQL5.7，MySQL8沒有親自測過）

場景復(fù)現(xiàn)：

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授權(quán)復(fù)制連接用戶：
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開啟數(shù)據(jù)復(fù)制：
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

主從復(fù)制狀態(tài)正常！

Master變更了數(shù)據(jù)：
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主機(jī)上看不到新建的表及其數(shù)據(jù)
Slave：
mysql> show databases;
+--------------------+
| Database?????????? |
+--------------------+
| information_schema |
| mysql????????????? |
| performance_schema |
| test?????????????? |
+--------------------+
4 rows in set (0.00 sec)

查看主從復(fù)制狀態(tài)：
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

你會發(fā)現(xiàn) I/O、SQL 線程正常；Read_Master_Log_Pos 和 Exec_Master_Log_Pos 值相同；Seconds_Behind_Master 值為0，說明沒有主從延遲發(fā)生；SQL_Delay 值為0，說明沒有主觀設(shè)置延遲插入；雖然設(shè)置了主從過濾規(guī)則，但也只是復(fù)制該庫的，難道是Slave的relay log出了問題，沒有記錄Master的日志？

到Slave去分析relay log日志，會發(fā)現(xiàn)也是有相應(yīng)的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的錯(cuò)誤日志，也沒有看到任何復(fù)制error相關(guān)的信息

那么問題來了，這可能是遭遇了BUG！ 筆者也忘記了，是某位大佬說過，還是在某博客中看到過，如果Slave配置了replicate_do_db 過濾規(guī)則，如果寫成了如下形式：
replicate_do_db=edusoho_e,statis 可能會遭遇BUG，需要分開來寫
replicate_do_db=edusoho_e
replicate_do_db=statis

重啟Slave以驗(yàn)證猜想

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)

你會發(fā)現(xiàn)新建的表和數(shù)據(jù)都同步過去了，說明確實(shí)是 replicate_do_db 過濾規(guī)則的 "坑"