Mysql主從同步排除指定數據庫

主從同步時有時我們希望排除一些記錄不同步了，那麼這個要如何來配置了呢？下面小編來為各位介紹一篇Mysql主從同步忽略特定庫的方法吧．

一、主庫、從庫同步測試

[root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p
mysql> show processlist＼G
  State: Master has sent all binlog to slave; waiting for binlog to be updated
mysql> create database helloworld;
mysql> use hitest;
mysql> insert into test(id,name) values(3,'doit');
mysql> grant select,insert,update,delete on *.* to byrd@'192.168.199.%' identified by 'admin';
mysql> create user 'def'@'localhost' identified by 'admin';
mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| byrd | 192.168.199.% |
| def  | localhost     |
+------+---------------+
7 rows in set (0.00 sec)
#mysql> grant all on *.* to 'imbyrd'@'localhost' identified by 'admin';    #主庫建立一個用戶imbyrd，密碼為admin
############上面主庫############主庫從庫分隔符############下面從庫############
[root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p    #下面是從庫，上面是主庫哦！！！
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| helloworld         |
mysql> use hitest;
mysql> select * from test;
+----+--------+
| id | name   |
+----+--------+
|  1 | zy     |
|  2 | binghe |
|  3 | doit   |
+----+--------+
mysql> show grants for byrd@'192.168.199.%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> select user,host from mysql.user;
+------+---------------+
| user | host          |
+------+---------------+
| root | 127.0.0.1     |
| byrd | 192.168.199.% |
| root | ::1           |
| root | localhost     |
+------+---------------+
7 rows in set (0.00 sec)

結論：主庫、從庫同步正常！

二、主庫、從庫權限同步測試(此次只在從庫的my.cnf增加了replicate-wild-ignore-table=mysql.%)

mysql> create database hiworld;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hitest             |
| hiworld            |
+--------------------+
8 rows in set (0.00 sec)
mysql> grant all on *.* to byrd@'192.168.199.%' identified by 'admin';
mysql> show grants for byrd@'192.168.199.%';
+--------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
+--------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use hitest;
mysql> insert into test(id,name) values(6,'six');
mysql> select * from test;
+----+---------+
| id | name    |
+----+---------+
|  6 | six     |
+----+---------+
6 rows in set (0.02 sec)
############上面主庫############主庫從庫分隔符############下面從庫############
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| hitest             |
| hiworld            |
+--------------------+
12 rows in set (0.15 sec)
mysql> show grants for byrd@'192.168.199.%';
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].%                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> use hitest;
mysql> select * from test;
+----+---------+
| id | name    |
+----+---------+
|  6 | six     |
+----+---------+
6 rows in set (0.04 sec)

結論：從庫在my.cnf增加replicate-wild-ignore-table=mysql.%後權限未同步

主庫mysql-bin內容：

[root@Master-Mysql data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000016
create database hiworld
/*!*/;
GRANT ALL PRIVILEGES ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'
/*!*/;
use `hitest`/*!*/;
insert into test(id,name) values(6,'six')
/*!*/;
CREATE USER 'def'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
/*!*/;
從庫mysqld-relay-bin內容：

create database hiworld
/*!*/;
GRANT ALL PRIVILEGES ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C'
/*!*/;
use `hitest`/*!*/;
insert into test(id,name) values(6,'six')
/*!*/;
CREATE USER 'def'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441'
/*!*/;

結論

①：當從服務器注釋掉replicate-wild-ignore-table=mysql.%內容後(且mysql服務重新啟動)，之後的所有同步恢復正常，但是在注釋後的授權是無法恢復的，如果想要重新授權，需要在主服務器上重新執行授權命令；
②：從庫配置文件增加replicate-wild-ignore-table=mysql.%後，對授權、增加用戶、雖然記錄到mysqld-relay-bin中，但是會進行過濾，而對增加數據庫則進行同步；

備注：Prior to MySQL 5.5.32, this option caused any statements containing fully qualified table names not to be logged if there was no default database specified (that is, when SELECT DATABASE() returned NULL). In MySQL 5.5.32 and later, when there is no default database, no --binlog-ignore-db options are applied, and such statements are always logged. (Bug #11829838, Bug #60188)