MySQL master-slave synchronization excludes the specified database

First, the main library, from the Library synchronization test

[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 is 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, Password is admin
########### #上面主库 ########### #主库从库分隔符 ########### #下面从库 ############
[Root@slave-mysql ~]#/usr/local/mysql/bin/mysql-uroot-p #下面是从库, above is the main library Oh!!!
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 ' *01a6717b 58ff5c7eafff6cb7c96f7428ea65fe4c ' |
+-------------------------------------------------------------------------------------------------------------- -----------------------------------------------+
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)

Conclusion: The main library, synchronization from the library is normal!

Second, the main library, from the Library Permissions synchronization test (this time only in the MY.CNF from the library added 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 |
+--------------------+
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 ' *01a6717b 58ff5c7eafff6cb7c96f7428ea65fe4c ' |
+-------------------------------------------------------------------------------------------------------------- -----------------------------------------------+
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)

Conclusion: Permissions are not synchronized since the library has increased replicate-wild-ignore-table=mysql.% from the MY.CNF

Main Library Mysql-bin content:

[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 Content from Library:

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 '
/*!*/;

Conclusion

①: When the replicate-wild-ignore-table=mysql.% content is commented out from the server (and the MySQL service restarts), all subsequent synchronizations are back to normal, but the authorization after the comment cannot be restored, and if you want to authorize again, The authorization command needs to be re executed on the primary server;
②: After adding replicate-wild-ignore-table=mysql.% from the library configuration file, the authorization, the increase user, although records to Mysqld-relay-bin, but will filter, but to increase the database is synchronized;

Remarks: Prior to MySQL 5.5.32, this option caused any statements containing fully table name S is logged if there is no default database specified (which is, when SELECT database () returned NULL). In MySQL 5.5.32 and later, where there is no default database, no--BINLOG-IGNORE-DB options are applied, and such statemen TS are always logged. (Bug #11829838, bug #60188)