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.% ，可以設定多個，這樣就可以解決跨庫更新的問題。