问题描述:
在主从复制的时候,设置了replicate-do-db,但是发生了报错,报错信息:
Last_Error: Error 'Unknown database 'test2'' on query. Default database: 'test'. Query: 'CREATE TABLE `test2`.`sometable` .....
分析过程 及 测试过程:
这是因为binlog_format设置成了row, 而执行的query涉及是否使用了 user db;
AWS 官方文档【1】和MySQL8官网文档【2】对于replicate-do-db以及binlog_format=row/statement 参数的使用进行了解释,测试如下:
1. 设置:binlog_format=statement,replicate-do-db=shiran1,shiran2
1.1
主库执行:
use shiran1; ----> 限制default database为shiran1
update shiran5.test5 set xxxx; ---> 这个指令依旧会被复制到从库。
因为binlog_format=statement会告诉复制SQL线程,将复制限制到default database(即USE选择的shiran1)的语句,而shiran1是在replicate-do-db设置下的,所以SQL线程依旧会读取update shiran5.test5这个sql.
1.2
如果主库不执行use shiran1,完全不执行任何use操作。或者主库执行 use shiran5;
update shiran5.test5 set xxxx; ---> 这个指令不会被复制到从库,因为这个语句的default database并没有在shiran1,shiran2之列。
2. 设置:binlog_format=row,replicate-do-db=shiran1,shiran2
2.1
主库执行:
use shiran1; ----> 限制default database为shiran1
update shiran5.test5 set xxxx; ---> 这个指令不会被复制到从库
因为binlog_format=row, 只会查看update这类DML语句更新的table所在的DB,不会被前面的use语句所限制。因此,因为DML语句中的table所在的DB为shiran5, 不在replicate-do-db=shiran1,shiran2之列,所以这个指令完全不会被复制到从库。
2.2
如果主库不执行use shiran1,完全不执行任何use操作。或者主库执行 use shiran5;
update shiran5.test5 set xxxx; ---> 这个结果跟1.2.1是一样的,不会管前面是否有use去限制default database, 只会管DML的table所用的DB是否在replicate-do-db之列。
3.
但是对于DDL,文档上的说法是:All data definition language (DDL) statements are replicated as statements, regardless of the binlog_format setting on the source DB instance.
所以,无论您的主库binlog_format设置为row还是statement, 您的create table都会按照binlog_format=statement的方式来复制到从库。
如果我们执行:
use shiran1;
create table shiran5.test6(a integer);
那么我们会遇到报错:Last_SQL_Error: Error 'Unknown database 'shiran5'' on query. Default database: 'shiran1'. Query: 'create table shiran5.test6(a integer)'
参考文档:
【1】AWS文档:使用 MySQL 只读副本 - Amazon Relational Database Service
【2】mysql官方文档:MySQL :: MySQL 8.0 Reference Manual :: 19.1.6.3 Replica Server Options and Variables