mysql 复制过滤器_MYSQL复制过滤器

复制过滤器可以让从节点复制指定的数据库或指定数据库的指定表

复制过滤器两种实现方式：

方法1.服务器选项：主服务器仅向二进制日志中记录与特定数据库相关的日志

注意：此方法只需要在主服务器上做即可，会影响具体的数据库的二进制日志生成，虽然实现了此功能，但后期通过二进制日志恢复数据库会收到一定的影响，慎用

实现方法：

基于一主两从的配置，修改MYSQL配置文件

vim /etc/my.cnf.d/mariadb-server.cnf

[mysqld]

binlog-do-db=db1      #白名单模式，仅允许主服务器上生成db1的二进制日志，此选项不支持一行指定多个参数，需要每个参数写一行

binlog-do-db=db2

重启服务

systemctl restart mariadb.service

主服务器上删除了非db1和db2的一些数据库，发现这些二进制日志已无法同步到从节点

MariaDB [(none)]> show databases;

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

| Database |

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

| bdb |

| db2 |

| db3 |

| db5 |

| db6 |

| hellodb |

| information_schema |

| mysql |

| performance_schema |

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

9 rows in set (0.001 sec)

MariaDB [(none)]> create database db1;

Query OK, 1 row affected (3.002 sec)

MariaDB [(none)]>

MariaDB [(none)]>

MariaDB [(none)]>

MariaDB [(none)]> drop database bdb;

Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> drop database db5;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> drop database db6;

Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> show databases;

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

| Database |

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

| db1 |

| db2 |

| db3 |

| hellodb |

| information_schema |

| mysql |

| performance_schema |

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

7 rows in set (0.000 sec)

从节点查看数据库，发现数据库并未改变，且slave正常

MariaDB [(none)]> show databases;

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

| Database |

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

| bdb |

| db1 |

| db2 |

| db3 |

| db5 |

| db6 |

| hellodb |

| information_schema |

| mysql |

| performance_schema |

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

10 rows in set (0.000 sec)

MariaDB [(none)]> show slave status\G;

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 10.0.0.211

Master_User: repluser

Master_Port: 3306

Connect_Retry: 10

Master_Log_File: mariadb-bin.000004

Read_Master_Log_Pos: 487

Relay_Log_File: mariadb-relay-bin.000006

Relay_Log_Pos: 788

Relay_Master_Log_File: mariadb-bin.000004

Slave_IO_Running: Yes                                     #此处显示状态正常

Slave_SQL_Running: Yes                                 #此处显示状态正常

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 487

Relay_Log_Space: 1549

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 211

Master_SSL_Crl:

Master_SSL_Crlpath:

Using_Gtid: No

Gtid_IO_Pos:

Replicate_Do_Domain_Ids:

Replicate_Ignore_Domain_Ids:

Parallel_Mode: conservative

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Slave_DDL_Groups: 11

Slave_Non_Transactional_Groups: 0

Slave_Transactional_Groups: 0

1 row in set (0.000 sec)

ERROR: No query specified

主服务器上修改db1数据库，增加表格

MariaDB [db1]> create table jinlei (id int,name varchar(20));

Query OK, 0 rows affected (0.004 sec)

从服务器上查看可以同步该库的表

MariaDB [(none)]> use db1

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db1]> show tables;

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

| Tables_in_db1 |

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

| jinlei |

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

1 row in set (0.000 sec)

主服务器在db3数据库内添加表

MariaDB [db1]> use db3

Database changed

MariaDB [db3]> create table jinlei (id int,name varchar(20));

Query OK, 0 rows affected (0.003 sec)

从服务器无法同步db3数据库内的表

MariaDB [db1]> use db3

Database changed

MariaDB [db3]> show tables;

Empty set (0.000 sec)

总结：在主服务器上配置文件中仅允许记录db1、db2的二进制文件后，主服务器上修改其他的数据库则无法同步到从服务器，只能在db1或db2上修改数据库才能同步到从服务器。

方法2服务器选项：从服务器SQL_THREAD在relay log中的事件时，仅读取与特定数据库或特定表相关的事件并应用于本地；

因为二进制日志是在从服务器本地被过滤，二进制日志还是通过主服务器发送过来了，所以此方法会造成网络及磁盘I/O的浪费

从服务器上修改系统变量

MariaDB [db3]> stop slave;

Query OK, 0 rows affected (0.002 sec)

MariaDB [db3]> set global replicate_do_db=‘db1,db2‘;

Query OK, 0 rows affected (0.000 sec)

主服务器上在db2中增加一个表

MariaDB [(none)]> use db2

Database changed

MariaDB [db2]> create table jiang (id int,name char(10));

Query OK, 0 rows affected (0.003 sec)

从服务器上db2内可以同步该表

MariaDB [(none)]> use db2;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db2]> show tables;

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

| Tables_in_db2 |

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

| jiang |

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

1 row in set (0.000 sec)

主服务器在db3中增加一张表

MariaDB [db2]> use db3

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

MariaDB [db3]> create table liang (id int,name char(10));

Query OK, 0 rows affected (0.003 sec)

从服务器上在db3中无法同步该表

MariaDB [db3]> show tables;

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

| Tables_in_db3 |

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

| liang |

| mao |

| zheng |

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

3 rows in set (0.000 sec)

总结：该方法相对于方法一更加的灵活。但这两种方法都不支持主服务器跨库操作。

2.

原文：https://www.cnblogs.com/jinlei92131/p/13588971.html