MYSQL之过滤复制

1.过滤复制的方式

1.查看master status

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      305 |   白名单      |    黑名单        |                   |
+------------------+----------+--------------+------------------+-------------------+

2.配置白名单

# 从库
replicate-do-db=test  # test库
replicate-do-table=test.t1  # test库下的t1表
replicate-wild-do-table=test.t*  # test库下t开头的表

# 三种只能选一种，多个库(表)配置可以多写一行配置，或者','。

# 主库
binlog-do-db=test
binlog-do-table=test.t1
binlog-wild-do-table=test.t*

3.配置黑名单

# 从库
replicate-ignore-db=test
replicate-ignore-table=test.t1
replicate-wild-ignore-table=test.t*

# 主库
binlog-ignore-db=test
binlog-ignore-table=test.t1
binlog-wild-ignore-table=test.t*

2.配置过滤复制

1.主库创建两个库
mysql> create database wzry;

mysql> create database lol;
2.第一台从库配置
[root@db02 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=wzry

[root@db02 ~]# systemctl restart mysqld

# 查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: wzry
3.配置第二台从库
[root@db03 ~]# vim /etc/my.cnf
[mysqld]
server_id=2
replicate-do-db=lol

[root@db03 ~]# systemctl restart mysqld

# 查看主从状态
mysql> show slave status\G
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: lol
4.验证过滤复制
# 1.主库操作
mysql> use wzry

mysql> create table cikexintiao(id int);

mysql> use lol

mysql> create table fuleierzhuode(id int);

# 第一台从库查看
mysql> use wzry

mysql> show tables;
+----------------+
| Tables_in_wzry |
+----------------+
| cikexintiao    |
+----------------+

mysql> use lol

mysql> show tables;

# 第二台从库查看
mysql> use wzry

mysql> show tables;


mysql> use lol

mysql> show tables;
+---------------+
| Tables_in_lol |
+---------------+
| fuleierzhuode |
+---------------+
1 row in set (0.00 sec)

3.过滤复制配置在主库

1.配置
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
server_id=1
log_bin=/usr/local/mysql/data/mysql-bin
binlog-do-db=wzry

2.查看主库状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      120 | wzry         |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

3.在主库的wzry库和lol库添加数据

4.从库查看数据，只能看到wzry库的数据

4.过滤复制总结

# 配置在从库时
1.配置白名单：IO线程将主库的数据拿到了relay-log，但是sql线程只执行白名单配置的数据库相关语句
1.配置黑名单：IO线程将主库的数据拿到了relay-log，但是sql线程只不执行黑名单配置的数据库相关语句

# 配置在主库时
1.配置白名单：binlog只记录白名单相关的sql语句
2.配置黑名单：binlog只不记录黑名单相关的sql语句

# 建议配置在从库