MySQL选择性同步
强烈建议:尽量不要使用本文中的几个参数用作MySQL选择性同步。本文仅是测试,极不推荐生产环境中用以下参数。
测试版本:MySQL 5.7.26, 注意:MySQL5.6 可能不一样!!
说明:图中最后的都是从库的结论。比如a库的表不执行binlog指的是从库中a库中不会执行主库中binlog中的xx记录。
1 binlog-do-db
1.1 binlog_format = ROW
主库配置文件my.cnf设置:
binlog-do-db=a;
实验如下:
1.1.1 DDL语句
主库上DDL操作:
(1)创建库b,创建库a
主库执行:
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00sec)
mysql> create databasea;
Query OK,1 row affected (0.00sec)
mysql> create databaseb;
Query OK,1 row affected (0.00sec)
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| a |
| b |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00sec)
mysql>show master status\G*************************** 1. row ***************************
File: binlog.000004Position:462Binlog_Do_DB: a
Binlog_Ignore_DB:
Executed_Gtid_Set:1 row in set (0.00sec)
mysql># mysqlbinlog--base64-output=decode-rows -v -v binlog.000004 >> ~/db_01.txt# at372#200224 14:18:12 server id 1611343306 end_log_pos 462 CRC32 0x738926e7 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582525092/*!*/;create databasea/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;
DELIMITER ;
#End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从库执行:
mysql>show databases;+--------------------+
| Database |
+--------------------+
| information_schema |
| a |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00sec)
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event
Master_Host:172.16.161.134Master_User: repl
Master_Port:3306Connect_Retry:60Master_Log_File: binlog.000004Read_Master_Log_Pos:462Relay_Log_File: relaylog.000006Relay_Log_Pos:669Relay_Master_Log_File: binlog.000004Slave_IO_Running: Yes
Slave_SQL_Running: Yes
结论:主库创建的库,设置了binlog-do-db的库会记录在binlog日志里,而未指定的不会记录在binlog中。
(2)在a库中创建库a的a表,在库a中创建库b的b表。
主库:
mysql> usea;Databasechanged
mysql> create table a (id int);
Query OK,0 rows affected (0.01sec)
mysql> create table b.b (id int);
Query OK,0 rows affected (0.01sec)
mysql>主库的binlog日志:use `a`/*!*/;SET TIMESTAMP=1582525459/*!*/;create table a (id int)/*!*/;
# at623#200224 14:24:29 server id 1611343306 end_log_pos 688 CRC32 0x0ca2b45e Anonymous_GTID last_committed=3 sequence_number=4 rbr_only=noSET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at688#200224 14:24:29 server id 1611343306 end_log_pos 786 CRC32 0x15eeef53 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582525469/*!*/;create table b.b (id int)/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;
DELIMITER ;
#End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从库:
mysql> desca.a;+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00sec)
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event
Master_Host:172.16.161.134Master_User: repl
Master_Port:3306Connect_Retry:60Master_Log_File: binlog.000004Read_Master_Log_Pos:786Relay_Log_File: relaylog.000006Relay_Log_Pos:830Relay_Master_Log_File: binlog.000004Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno:1049Last_Error: Error'Unknown database'b'' on query. Default database: 'a'. Query: 'create table b.b (id int)'
由于a库中的记录都同步到了binlog,而从库中没有b库,所以同步异常。
结论:在a库中的DDL操作都会记录在binlog中。
(3) 在b库中对a库的a表进行DDL
先在从库中创建b库,解决上面同步的报错,这样从库中也有了b库和b库的b表。
mysql> useb;Databasechanged
mysql> alter table a.a add column name varchar(10);
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql> desca.a;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
查看主库的binlog日志,发现这个操作并没有记录在binlog中。
结论:在b库中进行的DDL操作并不会记录在binlog中。因此从库中也不会执行到这一操作。
(4)
在a库中对b库的表进行DDL
主库:
mysql> usea;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> alter table b.b add column name varchar(10);
Query OK,0 rows affected (0.02sec)
Records:0 Duplicates: 0 Warnings: 0mysql>主库binlog日志:
# at851#200224 15:31:20 server id 1611343306 end_log_pos 967 CRC32 0x13c14a93 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582529480/*!*/;alter table b.b add column name varchar(10)/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;
DELIMITER ;
#End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从库:
mysql> descb.b;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01sec)
mysql>
同步正常!
小结论:
(1) 使用binlog-do-db参数,指定了什么库,那么在这个库上(use xxx)的DDL操作都会记录到binlog中。
(2) 使用binlog-do-db参数,在未指定的库上执行的DDL语句, 都不会记录在binlog中,尽管是操作执行binlog-do-db执行数据库的DDL。
1.1.2 DML 语句
主库DML操作:
(1) 在a库中对a库的表进行DML
让保持从库同步主库a库a表。
主库:
mysql> usea;Databasechanged
mysql> drop tablea;
Query OK,0 rows affected (0.01sec)
mysql> create table a (id int);
Query OK,0 rows affected (0.01sec)
mysql> insert into a values(1);
Query OK,1 row affected (0.01sec)
从库:
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00 sec)
结论:在a库中操作a表会记录在binlog。
(2) 在a库中对b库的表进行DML
主库:
mysql> descb.b;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01sec)
mysql> usea;Databasechanged
mysql> insert into b.b values(1,'b');
Query OK,1 row affected (0.01sec)
mysql> select * fromb.b;+------+------+
| id | name |
+------+------+
| 1 | b |
+------+------+
1 row in set (0.00sec)
mysql>从库:
mysql> select * fromb.b;
Emptyset (0.00sec)
mysql>
结论:在binlog-do-db指定的库中操作其它库的DML语句,不会记录在binlog中。
(3) 在b库中对a库的表进行DML
主库:
mysql> desca.a;+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.00sec)
mysql> useb;Databasechanged
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
+------+
1 row in set (0.00sec)
mysql> insert into a.a values(2);
Query OK,1 row affected (0.00sec)
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00sec)
mysql>主库binlog日志:
# at1622#200224 16:23:48 server id 1611343306 end_log_pos 1696 CRC32 0x99c73a44 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1582532628/*!*/;BEGIN
/*!*/;
# at1696#200224 16:23:48 server id 1611343306 end_log_pos 1737 CRC32 0xfef48115 Table_map: `a`.`a` mapped to number 113# at1737#200224 16:23:48 server id 1611343306 end_log_pos 1777 CRC32 0x5e85445b Write_rows: table id 113flags: STMT_END_F
###INSERT INTO`a`.`a`
###SET###@1=2 /*INT meta=0 nullable=1 is_null=0*/# at1777#200224 16:23:48 server id 1611343306 end_log_pos 1808 CRC32 0x602e2c5c Xid = 78
COMMIT/*!*/;SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /*added by mysqlbinlog*/ /*!*/;
DELIMITER ;
#End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
从库:
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)
结论:binlog中没有use b的记录,操作binlog-do-db指定库的DML语句会被记录到binglog中。
2 binlog-ignore-db
主库配置文件my.cnf设置:
binlog-ignore-db=a;
2.1 binlog_format = ROW
2.1.1 DDL语句
(1)在b库中创建a库中的表
主库:
mysql> useb;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> create table a.a2 (id int);
Query OK,0 rows affected (0.02sec)
从库:
mysql>show tables;+-------------+
| Tables_in_a |
+-------------+
| a |
| a2 |
+-------------+
结论:在非binlog-ignore-db指定的库中执行的DDL语句会记录在binlog中。
(2)在a库中创建b库中的表
主库:
mysql> usea;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> create table b.b2 (id int);
Query OK,0 rows affected (0.01sec)
mysql>从库:
mysql> useb;Databasechanged
mysql>show tables;+-------------+
| Tables_in_b |
+-------------+
| b |
+-------------+
1 row in set (0.01 sec)
结论:在binlog-ignore-db指定的库中执行的DDL语句都不记录在binlog中。
2.1.2 DML语句
(1)在b库中对a库的表进行DML操作
主库:
mysql> useb;Databasechanged
mysql> insert into a.a2 values(10);
Query OK,1 row affected (0.00sec)
mysql> select * froma.a2;+------+
| id |
+------+
| 10 |
+------+
1 row in set (0.00sec)
mysql>从库:
mysql>show slave status\G*************************** 1. row ***************************Slave_IO_State: Waitingfor master tosend event
Master_Host:172.16.161.134Master_User: repl
Master_Port:3306Connect_Retry:60Master_Log_File: binlog.000005Read_Master_Log_Pos:482Relay_Log_File: relaylog.000009Relay_Log_Pos:689Relay_Master_Log_File: binlog.000005Slave_IO_Running: Yes
Slave_SQL_Running: Yes
mysql> select * froma.a2;
Emptyset (0.00 sec)
结论:在非binlog-ignore-db指定的库中执行操作binlog-ignore-db指定的库的表DML语句都不记录在binlog中。
(2)在a库中对b库的表进行DML操作
主库:
mysql> usea;Databasechanged
mysql> select * fromb.b;+------+------+
| id | name |
-----+------+
| 1 | b |
+------+------+
1 row in set (0.00sec)
mysql> insert into b.b values(2,'b1');
Query OK,1 row affected (0.01sec)
mysql> select * fromb.b;+------+------+
| id | name |
+------+------+
| 1 | b |
| 2 | b1 |
+------+------+
2 rows in set (0.00sec)
从库:
mysql> select * fromb.b;+------+------+
| id | name |
+------+------+
| 2 | b1 |
+------+------+
1 row in set (0.00sec)
mysql>
结论:在binlog-ignore-db指定的库中执行操作非binlog-ignore-db指定的库的表DML语句都会记录在binlog中。
3 replicate-do-db
这个是对从库起作用的参数,在从库中配置:
replicate-do-db=a;
3.1 binlog_format = ROW
3.1.1 DDL语句
(1)在a库中对b库的表进行DDL
主库:
mysql> usea;Databasechanged
mysql> create table b.b6 (id int);
Query OK,0 rows affected (0.01sec)
mysql>从库:
mysql> useb;Databasechanged
mysql>show tables;+-------------+
| Tables_in_b |
+-------------+
| b6 |
+-------------+
5 rows in set (0.00 sec)
结论:从库会将主库的所有binlog记录同步到relay日志,从库对于在指定replicate-do-db库中执行的DDL语句都会执行。
(2)在b库中对a库的表进行DDL
主库:
mysql> useb;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> create table a.a6 (id int);
Query OK,0 rows affected (0.01sec)
从库:
mysql> usea;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql>show tables;+-------------+
| Tables_in_a |
+------------+
| a |
| a1 |
| a2 |
| a4 |
+-------------+
结论:从库会将主库的所有binlog记录同步到relay日志,从库对于在非指定replicate-do-db库中执行的DDL语句都不会执行。
3.1.2 DML语句
(1)在a库中对b库的表进行DML
主库:
mysql> usea;Databasechanged
mysql> insert into b.b6 values(1);
Query OK,1 row affected (0.01sec)
从库:
mysql> select * fromb.b6;
Emptyset (0.00 sec)
结论:从库会将主库的所有binlog记录同步到relay日志,对于非指定replicate-do-db库的DML语句都不会执行。
(2)在b库中对a库的表进行DML
主库:
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00sec)
mysql> useb;Databasechanged
mysql> insert into a.a values(3);
Query OK,1 row affected (0.00sec)
mysql> select * froma.a;+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00sec)
从库:
mysql>mysql> select * froma.a;+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
结论:从库会将主库的所有binlog记录同步到relay日志,从库对于指定replicate-do-db库的DML语句都会执行。对于非指定replicate-do-db库的DML语句都不执行。
4 replicate-ignore-db
这个是对从库起作用的参数,在从库中配置:
replicate-ignore-db=a;
4.1 binlog_format = ROW
4.1.1 DDL语句
(1)在a库中对b库的表进行DDL
主库:
mysql> usea;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> create table b.b7 (id int);
Query OK,0 rows affected (0.01sec)
mysql> alter table b.b add column (age int);
Query OK,0 rows affected (0.05sec)
Records:0 Duplicates: 0 Warnings: 0从库:
mysql> useb;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql>show tables;+-------------+
| Tables_in_b |
+-------------+
| b |
| b1 |
| b2 |
| b3 |
| b6 |
+-------------+
5 rows in set (0.00sec)
mysql> descb;+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
结论:从库会将主库的所有binlog记录同步到relay日志,对于在replicate-ignore-db库执行的的DDL语句都不会执行。
(2)在b库中对a库的表进行DDL
主库:
mysql> useb;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> create table a.a7 (id int);
Query OK,0 rows affected (0.02sec)
mysql> alter table a.a add column (age int);
Query OK,0 rows affected (0.05sec)
Records:0 Duplicates: 0 Warnings: 0从库:
mysql> usea;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> desca;+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| age | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
2 rows in set (0.00sec)
mysql>show tables;+-------------+
| Tables_in_a |
+-------------+
| a |
| a1 |
| a2 |
| a4 |
| a7 |
+-------------+
5 rows in set (0.00sec)
mysql>
结论:从库会将主库的所有binlog记录同步到relay日志,对于非replicate-ignore-db库执行的的DDL语句都会执行。
4.1.2 DML语句
(1)在a库中对b库的表进行DML
主库:
mysql> usea;Databasechanged
mysql> insert into b.b (id) values(21);
Query OK,1 row affected (0.00sec)
从库:
mysql> select * fromb.b;+------+------+
| id | name |
+------+------+
| 1 | b |
| 2 | b1 |
| 21 | NULL |
+------+------+
3 rows in set (0.00 sec)
结论:从库会将主库的所有binlog记录同步到relay日志,对于在replicate-ignore-db库中执行的非replicate-ignore-db指定库表的DML语句都会执行。
(2)在b库中对a库的表进行DML
主库:
mysql> useb;
Readingtable information for completion of table and columnnames
You can turnoff this feature to get a quicker startup with -ADatabasechanged
mysql> insert into a.a (id) values(22);
Query OK,1 row affected (0.01sec)
从库:
mysql> select * froma.a;+------+------+
| id | age |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 1 | NULL |
| 4 | NULL |
| 100 | NULL |
| 400 | NULL |
| 600 | NULL |
+------+------+
8 rows in set (0.00 sec)
结论:从库会将主库的所有binlog记录同步到relay日志,对于在非replicate-ignore-db库执行的非replicate-ignore-db库的表的DML语句都会执行,replicate-ignore-db库的表的DML都不会执行。
5 总结
请尽量不要在生产环境中使用以上参数!请尽量不要在生产环境中使用以上参数!请尽量不要在生产环境中使用以上参数!