主從同步時(shí)有時(shí)我們希望排除一些記錄不同步了,那么這個(gè)要如何來配置了呢?下面小編來為各位介紹一篇Mysql主從同步忽略特定庫的方法吧.

一、主庫、從庫同步測試

1. [root@Master-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p 
2. mysql> show processlist＼G 
3.   State: Master has sent all binlog to slave; waiting for binlog to be updated 
4. mysql> create database helloworld; 
5. mysql> use hitest; 
6. mysql> insert into test(id,name) values(3,'doit'); 
7. mysql> grant select,insert,update,delete on *.* to byrd@'192.168.199.%' identified by 'admin'; 
8. mysql> create user 'def'@'localhost' identified by 'admin'; 
9. mysql> select user,host from mysql.user; 
10. +------+---------------+ 
11. | user | host          | 
12. +------+---------------+ 
13. | byrd | 192.168.199.% | 
14. | def  | localhost     | 
15. +------+---------------+ 
16. 7 rows in set (0.00 sec) 
17. #mysql> grant all on *.* to 'imbyrd'@'localhost' identified by 'admin';    #主庫建立一個(gè)用戶imbyrd，密碼為admin 
18. ############上面主庫############主庫從庫分隔符############下面從庫############ 
19. [root@Slave-Mysql ~]# /usr/local/mysql/bin/mysql -uroot -p    #下面是從庫，上面是主庫哦！！！ 
20. mysql> show databases; 
21. +--------------------+ 
22. | Database           | 
23. +--------------------+ 
24. | helloworld         | 
25. mysql> use hitest; 
26. mysql> select * from test; 
27. +----+--------+ 
28. | id | name   | 
29. +----+--------+ 
30. |  1 | zy     | 
31. |  2 | binghe | 
32. |  3 | doit   | 
33. +----+--------+ 
34. mysql> show grants for byrd@'192.168.199.%'; 
35. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
36. | Grants for byrd@192.168.199.%                                                                                                                               | 
37. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
38. | GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
39. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
40. 1 row in set (0.03 sec)  --Vevb.com 
41. mysql> select user,host from mysql.user; 
42. +------+---------------+ 
43. | user | host          | 
44. +------+---------------+ 
45. | root | 127.0.0.1     | 
46. | byrd | 192.168.199.% | 
47. | root | ::1           | 
48. | root | localhost     | 
49. +------+---------------+ 
50. 7 rows in set (0.00 sec) 

結(jié)論:主庫、從庫同步正常.

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

1. mysql> create database hiworld; 
2. mysql> show databases; 
3. +--------------------+ 
4. | Database           | 
5. +--------------------+ 
6. | hitest             | 
7. | hiworld            | 
8. +--------------------+ 
9. 8 rows in set (0.00 sec) 
10. mysql> grant all on *.* to byrd@'192.168.199.%' identified by 'admin'; 
11. mysql> show grants for byrd@'192.168.199.%'; 
12. +--------------------------------------------------------------------------------------------------------------------------+ 
13. | Grants for byrd@192.168.199.%                                                                                            | 
14. +--------------------------------------------------------------------------------------------------------------------------+ 
15. | GRANT ALL PRIVILEGES ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
16. +--------------------------------------------------------------------------------------------------------------------------+ 
17. 1 row in set (0.00 sec) 
18. mysql> use hitest; 
19. mysql> insert into test(id,name) values(6,'six'); 
20. mysql> select * from test; 
21. +----+---------+ 
22. | id | name    | 
23. +----+---------+ 
24. |  6 | six     | 
25. +----+---------+ 
26. 6 rows in set (0.02 sec) 
27. ############上面主庫############主庫從庫分隔符############下面從庫############ 
28. mysql> show databases; 
29. +--------------------+ 
30. | Database           | 
31. +--------------------+ 
32. | hitest             | 
33. | hiworld            | 
34. +--------------------+ 
35. 12 rows in set (0.15 sec) 
36. mysql> show grants for byrd@'192.168.199.%'; 
37. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
38. | Grants for byrd@192.168.199.%                                                                                                                               | 
39. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
40. | GRANT SELECT, INSERT, UPDATE, DELETE, REPLICATION SLAVE ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' | 
41. +-------------------------------------------------------------------------------------------------------------------------------------------------------------+ 
42. 1 row in set (0.00 sec) 
43. mysql> use hitest; 
44. mysql> select * from test; 
45. +----+---------+ 
46. | id | name    | 
47. +----+---------+ 
48. |  6 | six     | 
49. +----+---------+ 
50. 6 rows in set (0.04 sec) 

結(jié)論:從庫在my.cnf增加replicate-wild-ignore-table=mysql.%后權(quán)限未同步.

主庫mysql-bin內(nèi)容:

1. [root@Master-Mysql data]# /usr/local/mysql/bin/mysqlbinlog mysql-bin.000016 
2. create database hiworld 
3. /*!*/; 
4. GRANT ALL PRIVILEGES ON *.* TO 'byrd'@'192.168.199.%' IDENTIFIED BY PASSWORD '*01A6717B58FF5C7EAFFF6CB7C96F7428EA65FE4C' 
5. /*!*/; 
6. use `hitest`/*!*/; 
7. insert into test(id,name) values(6,'six') 
8. /*!*/; 
9. CREATE USER 'def'@'localhost' IDENTIFIED BY PASSWORD '*4ACFE3202A5FF5CF467898FC58AAB1D615029441' 
10. /*!*/; 

從庫mysqld-relay-bin內(nèi)容:

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

結(jié)論:

①:當(dāng)從服務(wù)器注釋掉replicate-wild-ignore-table=mysql.%內(nèi)容后(且mysql服務(wù)重新啟動(dòng)),之后的所有同步恢復(fù)正常,但是在注釋后的授權(quán)是無法恢復(fù)的,如果想要重新授權(quán),需要在主服務(wù)器上重新執(zhí)行授權(quán)命令.

②:從庫配置文件增加replicate-wild-ignore-table=mysql.%后,對授權(quán)、增加用戶、雖然記錄到mysqld-relay-bin中,但是會(huì)進(jìn)行過濾,而對增加數(shù)據(jù)庫則進(jìn)行同步；

備注: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)