mysql 主从库_mysql主从选择性同步某几个库实现方法

我们知道mysql主从同步可以选择性同步某几个数据库，并且要想实现这个目的可以借助如下几个参数：

binlog-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

binlog-ignore-db=不需要复制的数据库苦命，如果复制多个数据库，重复设置这个选项即可

replicate-do-db=需要复制的数据库名，如果复制多个数据库，重复设置这个选项即可

replicate-ignore-db=不需要复制的数据库名，如果要忽略多个数据库，重复设置这个选项即可

注意这些参数只能通过show master STATUS或者show slave STATUS查看，不能通过show variables like 'binlog%'查看。

mysql> show master STATUS;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000178 | 120 | liuhe | | |

+------------------+----------+--------------+------------------+-------------------+

1 row in set (0.00 sec)

1.binlog-do-db：在master主库设置，作用是让主库pump进程选择性的去给slave库发送binlog,实现选择性同步某些库，例如：

binlog-do-db=liuhe

binlog-do-db=lliuwenhe

下面是针对binlog-do-db所做的一些测试：

测试1：

首先设置binlog-do-db=liuhe

mysql>use liuhe

mysql>create table llll ( id int)

mysql>inser into llll values(1);

接着执行：

mysql>use test;

mysql>create table 2llll ( id int);

mysql>inser into 2llll values(1);

然后格式化当前使用的binloglog

[root@server02 mysql]# mysqlbinlog --base64-output=decode-rows -v -v mysql-bin.001513 >llll

[root@server02 mysql]# cat llll | grep liuhe

use `liuhe`/*!*/;

#170511 16:44:59 server id 1 end_log_pos 344 CRC32 0xd7d04588 Table_map: `liuhe`.`llll` mapped to number 70

### INSERT INTO `liuhe`.`llll`

[root@server02 mysql]# cat llll | grep test

use `test`/*!*/;

#170511 16:45:31 server id 1 end_log_pos 637 CRC32 0x2f9e6e0e Table_map: `test`.`2llll` mapped to number 99

### INSERT INTO `test`.`2llll`

总结：上面测试结果表明：当binlog-do-db参数设置有值时，在主库binlog依旧会记录不需要同步的库的相关操作，仅仅是主库pump进程选择性的给slave库发送需要同步的库的相关binlog.

测试2：

主库：

binlog-do-db=test

binlog-do-db=liuhe

root@[mysql]>show variables like '%binlog_format';

+---------------+-----------+

| Variable_name | Value |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

1 row in set (0.00 sec)

root@[mysql]>use mysql;

Database changed

root@[mysql]>create table test.hehe (id int);

Query OK, 0 rows affected (0.00 sec)

root@[mysql]>select * from test.hehe;

Empty set (0.02 sec)

从库：

(testing)root@localhost [test]> use test;

Database changed

(testing)root@localhost [test]> show tables;

Empty set (0.01 sec)

主库：

root@[mysql]>use liuhe;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

root@[liuhe]>create table test.hehe2 (id int);

Query OK, 0 rows affected (0.02 sec)

从库：

(testing)root@localhost [test]> show tables;

+----------------+

| Tables_in_test |

+----------------+

| hehe2 |

+----------------+

1 row in set (0.00 sec)

结论：在binlog_format=STATEMENT时，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL和DML语句相关操作的binlog都不会传给slave。即使指定具体的test.dd；

主库：

root@[(none)]>show variables like '%binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | ROW |

+---------------+-------+

1 row in set (0.00 sec)

root@[(none)]>use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

root@[mysql]>create table test.hehe (id int);

Query OK, 0 rows affected (0.01 sec)

从库查看，没有同步过来：

(testing)root@localhost [test]> show tables;

Empty set (0.02 sec)

---------

主库：

root@[mysql]>insert into test.hh values(11);

Query OK, 1 row affected (0.00 sec)

root@[mysql]>commit;

Query OK, 0 rows affected (0.01 sec)

从库：

(testing)root@localhost [test]>show tables ##hh这个表存在于主和从库之中

+-----------------+

| Tables_in_test |

+-----------------+

| hh |

+-----------------+

2 rows in set (0.00 sec)

(testing)root@localhost [test]> select * from hh;

+------+

| id |

+------+

| 11 |

+------+

1 row in set (0.00 sec)

结论：在row模式下，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL语句相关操作的binlog不会传个slave库。即使指定具体的test.dd；但是DML语句相关操作的binlog会传个slave库，也就是ddl不会同步，dml会同步。

主库：

root@[(none)]>show variables like '%binlog_format';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| binlog_format | MIXED |

+---------------+-------+

1 row in set (0.00 sec)

root@[(none)]>use mysql

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

root@[mysql]>create table test.hehe (id int);

Query OK, 0 rows affected (0.01 sec)

从库：

(testing)root@localhost [test]> show tables;

Empty set (0.02 sec)

---------

主库：

root@[mysql]>insert into test.hh values(11);

Query OK, 1 row affected (0.00 sec)

root@[mysql]>commit;

Query OK, 0 rows affected (0.01 sec)

从库：

(testing)root@localhost [test]> select * from hh;

Empty set (0.01 sec)

结论：在mixed模式下，在用use dbname的格式下，如果dbname没有在binlog-do-db里，DDL、DML语句相关操作的binlog不会传个slave库。即使指定具体的test.hehe；

整个测试2总结起来就是：当主库的binliog格式为statement的时候，如果dbname没有在binlog-do-db里，DDL、DML语句相关操作都不会同步给slave，即使指定具体的test.hehe。当等于row模式下，在用use dbname的格式下，如果dbname没有在binlog-do-db里，ddl不会同步，dml会同步，至于等于mixed的时候，我没有都测到，因为mixed是以上statement和row的混合，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种，所以他的同步情况也应该是不确定的，我上面测试的那种是情况是正好采用了statement模式，所以和statement的情况一样。

另外其余三个参数和binlog-do-db类似，所以这四个参数一般不适用，非常危险，可能会导致主从不一致，慎用！！！

那么怎么实现这个选择性同步某几个库的目的呢？？？

答案就是：在从库设置参数replicate-wild-do-table或者Replicate-Ignore-Table

slave上配置过滤, 使用基于查询中真正涉及到的表的选项, 避免复制 liuhe数据库中的数据的安全的方案是 配置:  replicate-wild-ignore-table=liuhe.%. 这样做仍然有一些特殊的情况, 不能正常工作,但可以在更多的情况下正常工作,并且会遇到更少的意外。

例如replicate-wild-do-table=liuhe.% ，可以设置多个，这样就可以解决跨库更新的问题。