MySQL的复制过滤器

最新推荐文章于 2024-11-20 03:45:00 发布

原创最新推荐文章于 2024-11-20 03:45:00 发布 · 458 阅读

0 ·

CC 4.0 BY-SA版权

MySQL学习专栏收录该内容

16 篇文章

订阅专栏

本文介绍了MySQL的复制过滤器，用于选择性地将主库的部分数据复制到从库。通常，过滤器配置在从库上以避免主库的日志信息过于局限。通过实验配置，展示了如何设置过滤器并验证其效果，确保从库只接收特定数据库的更新操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、过滤器概述
主数据库只复制给从数据库一部分数据给从节点，而不是复制全部内容；复制过滤器可以设置有哪些数据可以复制到从节点，类似于“白名单”，哪些数据是不可以进行复制，类似“黑名单”。
复制过滤器有两种实现机制：一种是在主库进行配置，一种是在从库上配置。然而第一种一般不会使用，因为如果在主库上配置过滤器，那么主库的二进制日志记录的便只有某一个数据库的信息，而不会记录其他库的信息，这样如果发生故障进行数据恢复时就没办法从二进制日志进行重放恢复。
所以一般会采用第二种方式，在从库上配置过滤器，但是也有缺点，就是增加IO读写的压力，因为主节点记录所有库的二进制日志信息依然会被从节点IO线程读取，并存储在从节点的中继日志中，由于设置了过滤，在从节点执行SQL线程时只会重放未被过滤掉的操作。

2、实验配置
1）先配置数据库主从
主库配置：

[root@www ~]# vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
log_bin=master-bin
server_id=1
[root@www ~]# systemctl restart mariadb.service
[root@www ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> grant  replication slave,replication client  on  *.* to 'jyy'@'192.168.126.135' identified by  'jyy';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]>

从库配置：

[root@www ~]#vim /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
server_id=3
relay_log=relay-log
[root@www ~]# systemctl restart mariadb.service
[root@www ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> change master to master_host='192.168.126.132',master_user='jyy',master_password='jyy',master_log_file='master-bin.000003',master_log_pos=495;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show slave status
    -> \G;
*************************** 1. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.126.132
                  Master_User: jyy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 495
               Relay_Log_File: relay-log.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: master-bin.000003
             Slave_IO_Running: No
            Slave_SQL_Running: No
              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: 495
              Relay_Log_Space: 245
              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: NULL
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: 0
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

2）测试：
主库创建数据库：

MariaDB [(none)]> create database aaa;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]>

从库上查看：

MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.126.132
                  Master_User: jyy
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000003
          Read_Master_Log_Pos: 736
               Relay_Log_File: relay-log.000002
                Relay_Log_Pos: 771
        Relay_Master_Log_File: master-bin.000003
             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: 736
              Relay_Log_Space: 1059
              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: 1
1 row in set (0.00 sec)

ERROR: No query specified

MariaDB [(none)]>

3）过滤器配置
从库：

MariaDB [(none)]> show global variables like 'replicate%';     //查看过滤器相关变量；
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| replicate_annotate_row_events    | OFF       |
| replicate_do_db                  |           |    //只复制哪个数据库；
| replicate_do_table               |           |    //只复制哪个表；
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db              |           |    //不复制哪个数据库
| replicate_ignore_table           |           |    //不复制哪个表；
| replicate_wild_do_table          |           |    //基于通配符来设置白名单；
| replicate_wild_ignore_table      |           |   //基于通配符来做黑名单；
+----------------------------------+-----------+
8 rows in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> stop slave;    //先关闭主从复制功能；
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> set global replicate_do_db=aaa;    //设置从库只复制主库的aaa库；
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> show global variables like "replicate%";
+----------------------------------+-----------+
| Variable_name                    | Value     |
+----------------------------------+-----------+
| replicate_annotate_row_events    | OFF       |
| replicate_do_db                  | aaa       |
| replicate_do_table               |           |
| replicate_events_marked_for_skip | replicate |
| replicate_ignore_db              |           |
| replicate_ignore_table           |           |
| replicate_wild_do_table          |           |
| replicate_wild_ignore_table      |           |
+----------------------------------+-----------+
8 rows in set (0.00 sec)

MariaDB [(none)]>

4）测试
在主库上创建另外一个数据库，并在aaa库上插入数据

MariaDB [(none)]> use aaa;
Database changed
MariaDB [aaa]> create table  student(id int(10),name varchar(20));
Query OK, 0 rows affected (0.00 sec)

MariaDB [aaa]> insert into student   values(1,'xiaoming'),(2,'xiaohong');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

MariaDB [aaa]> create database  bbb;
Query OK, 1 row affected (0.00 sec)

MariaDB [aaa]> 
MariaDB [aaa]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| bbb                |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)

MariaDB [aaa]>

在从库上查看

MariaDB [(none)]> show global variables like 'server_id';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 3     |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> 
MariaDB [(none)]> show databases;   
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |        //可以看到只有aaa库，主库中的bbb库并没有进行复制；
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [(none)]> select * from aaa.student;    
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoming |
|    2 | xiaohong |
+------+----------+      //有内容；
2 rows in set (0.00 sec)