mysql避免回环复制_MySQL过滤复制

一、过滤复制

什么是过滤复制

# 出现原因

让从节点仅仅复制指定的数据库，或指定数据库的指定数据表。主服务器有10个数据库，而从节点只需要同步其中的一两个数据库。这个时候就需要复制过滤。

复制过滤器可以在主节点中实现，也可以在从节点中实现。

# 过滤复制选择：

主节点：

在主节点的二进制事件日志中仅记录与指定数据库(数据表)相关的事件日志，但是主节点的二进制日志不完整，没有记录所有对主节点的修改操作。(不推荐)

如果要使用该方式，则在主节点的配置文件中添加如下参数：

binlog_do_db=”XXX,XXX,XXX”; #数据库白名单列表

binlog_ingore_db=”XXX,XXX,XXX”; #数据库黑名单列表。

但这两个配置参数不要同时使用。

从节点：

从服务器的 SQL Thread在Replay中继日志中的事件时，仅读取于特定数据库(数据表)相关的事件，并应用于本地。(但是浪费I/O ,浪费带宽)推荐使用

从节点复制过滤相关设置项：

replicate_do_db =”“; #复制的白名单

replicate_ingore_db =”“; #复制的黑名单

replicate_do_table=”“;

relicate_ingore_table=”“;

replicate_wild_do_table=”“; #更高级别的应用，通配符，应用到哪一类表的。

1.过滤复制的方式

mysql> show master status;

+------------------+----------+--------------+------------------+-------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+----------+--------------+------------------+-------------------+

| mysql-bin.000002 | 305 | 白名单 | 黑名单 | |

+------------------+----------+--------------+------------------+-------------------+

1)白名单

#从库

replicate-do-db=test

replicate-do-table=test.t1

replicate-wild-do-table=test.t*

#主库

binlog-do-db=test

binlog-do-table=test.t1

binlog-wild-do-table=test.t*

2)黑名单

#从库

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;

Query OK, 1 row affected (1.00 sec)

mysql> create database lol;

Query OK, 1 row affected (0.00 sec)

2)第一台从库配置

[root@db02 data]# vim /etc/my.cnf

[mysqld]

server_id=2

replicate-do-db=wzry

[root@db02 data]# 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

Database changed

mysql> create table cikexintiao(id int);

Query OK, 0 rows affected (0.26 sec)

mysql> use lol

Database changed

mysql> create table fuleierzhuode(id int);

Query OK, 0 rows affected (0.01 sec)

#第一台从库查看

mysql> use wzry

Database changed

mysql> show tables;

+----------------+

| Tables_in_wzry |

+----------------+

| cikexintiao |

+----------------+

1 row in set (0.00 sec)

mysql> use lol

Database changed

mysql> show tables;

Empty set (0.00 sec)

#第二台从库查看

mysql> use wzry

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> use lol

Database changed

mysql> show tables;

+---------------+

| Tables_in_lol |

+---------------+

| fuleierzhuode |

+---------------+

1 row in set (0.00 sec)

3.配置过滤多个库

1)方法一：

[root@db02 data]# vim /etc/my.cnf

[mysqld]

server_id=2

replicate-do-db=wzry,lol

2)方法二：

[root@db02 data]# vim /etc/my.cnf

[mysqld]

server_id=2

replicate-do-db=wzry

replicate-do-db=lol

4.过滤复制配置在主库

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库的数据

5.过滤复制总结

#配置在从库时

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

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

#配置在主库时

1.配置白名单：binlog只记录白名单相关的sql语句

2.配置黑名单：binlog只不记录黑名单相关的sql语句