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 byrd@192.168.199.%                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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 byrd@192.168.199.%                                                                                            |
+--------------------------------------------------------------------------------------------------------------------------+
| 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 byrd@192.168.199.%                                                                                                                               |
+-------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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)