警惕! replicate_do_db有坑!

<强>

<强>
MySQL5.6 (MySQL5.7, MySQL8没有亲自测过)



主配置:
(mysqld)
datadir=/home/data/mysql3306/
=3306港
server_id=1
binlog_format=行
log_bin=/home/data/mysql3306/binlog

奴隶配置:
(mysqld)
datadir=/home/data/mysql3306/
=3306港

server_id binlog_format=行=2
relay_log=/home/data/mysql3306/relaylog


主授权复制连接用户:
mysql>格兰特复制奴隶*。* repliter@ 192.168.32.2的确认密码* 6 bb4837eb74329105ee4568dda7dc67ed2ca2ad9的;



主变更了数据:
mysql>创建数据库edusoho_e;
查询好了,1行影响(0.00秒)

mysql>使用edusoho_e;
数据库改变

CREATE TABLE t1的(
' id ' INT(11)无符号不是零AUTO_INCREMENT,
' xname VARCHAR (20) NOT NULL违约”,
'地址' CHAR (20) NOT NULL默认”,
'性'非常小的整数(1)NOT NULL默认' 1 ',
“爱好”VARCHAR (30) NOT NULL默认”,
的年龄非常小的整数(2)默认“18”,
主键(id),
键“idx_name”(“xname”)
)引擎=INNODB默认字符集=utf8;

mysql>插入“edusoho_e”。“t1”(“xname”、“地址”、“爱好”)值(“edusoho_e”、“低密度脂蛋白”,dba);
查询好了,1行影响(0.01秒)

mysql>显示主状态;
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - +文件
| | |位置Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - +
| binlog。000004 | 882 | | | |
+ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - +
1行组(0.00秒)



然而你在奴隶主机上看不到新建的表及其数据
奴隶:
mysql>显示数据库;
+ - - - - - - - - - - - - - - - - - - - - - -数据库+
| |
+ - - - - - - - - - - - - - - - - - - - - - - +
| information_schema |
| mysql |
| performance_schema |
|测试|
+ - - - - - - - - - - - - - - - - - - - - - - +
4行组(0.00秒)

查看主从复制状态:
mysql>显示奴隶状态\ G;
* * * * * * * * * * * * * * * * * * * * * * * * * * * 1。行* * * * * * * * * * * * * * * * * * * * * * * * * * *
Slave_IO_State:等待主人发送事件
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:是的
Slave_SQL_Running:是的
Replicate_Do_DB: edusoho_e,统计学的
Exec_Master_Log_Pos: 882
Seconds_Behind_Master: 0
SQL_Delay: 0



到奴隶去分析中继日志日志,会发现也是有相应的主人的日志的
[root@slave mysql3306] # mysqlbinlog - v - base64-output=relaylog解码。000002
/* !50530集@@SESSION.PSEUDO_SLAVE_MODE=1 */;
/* !40019集@@session.max_insert_delayed_threads=0 */;
/* !50003集@OLD_COMPLETION_TYPE=@@COMPLETION_TYPE COMPLETION_TYPE=0 */;
分隔符/* ! */;
# 4
# 190530 9:32:17服务器id 2 end_log_pos 120 CRC32 0 x35d47ba3开始:binlog 4 v, v服务器5.6.16-log创造了190530个9:32:17
120
# 700101 # 8:00:00服务器id 1 end_log_pos 0 CRC32 0 x0166516e binlog旋转。000004 pos: 120
164
# 190530 # 9:29:02服务器id 1 end_log_pos 0 CRC32 0 xfea4f75a开始:binlog 4 v, v服务器5.6.16-log创造了190530个9:29:02
280
# 190530 # 9:35:18服务器id 229 end_log_pos CRC32 x6b0d2047查询thread_id=2 exec_time=0 error_code=0
设置时间戳=1559180118/* ! */;
设置@@session.pseudo_thread_id=2/* ! */;
@@session设置。@@session foreign_key_checks=1。@@session sql_auto_is_null=0。unique_checks=1, @@session.autocommit=1/* ! */;
设置@@session.sql_mode=1073741824/* ! */;
@@session设置。auto_increment_increment=1, @@session.auto_increment_offset=1/* ! */;
/* !\ C utf8 *//* ! */;
设置@@session.character_set_client=33岁@@session.collation_connection=33, @@session.collation_server=33/* ! */;
设置@@session.lc_time_names=0/* ! */;
设置@@session.collation_database=默认/* ! */;

/* ! */;
389
# 190530 # 9:35:31服务器id 653 end_log_pos CRC32 x1268f754查询thread_id=2 exec_time=0 error_code=0
/* ! */;
设置时间戳=1559180131/* ! */;

/* ! */;
813

警惕! replicate_do_db有坑!