Mysql主从同步排除指定数据库如何配置

2024-07-24 12:35:24

字体：大中小

来源：转载

供稿：网友

　　主从同步时有时我们希望排除一些记录不同步了,那么这个要如何来配置了呢?下面小编来为各位介绍一篇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) --phpfensi.com
　　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)。

（编辑：武林网）

上一篇：MYSQL数据库多实例配置流程详解

下一篇：备份MYSQL数据表结构与数据库结构教程步骤