016、MySQL主从复制(Replication)架构特殊从库之过滤复制部署实战

2024-3-09 12:15

|

Mysql

|

31

415 字

|

27 分钟

本文最后更新于 65 天前，其中的信息可能已经过时，如有错误请发送邮件到wuxianglongblog@163.com

MySQL主从复制(Replication)架构特殊从库之过滤复制部署实战

一.过滤复制概述

1.过滤复制的工作原理

    什么是过滤复制:
        所谓的过滤复制指的是从库对主库的某些数据库或者表进行过滤的复制的一种手段。

    过滤复制的工作原理:
        如下图所示，IO线程会将主库的二进制日志全部拉取到中继日志中，而后从库的SQL线程在"回放"中继日志的时候回过滤需要过滤的数据库或者表。

2.过滤复制的应用场景

    如下图所示，对于读多写少的数据库而言，我们也可以采取下面的方式实现读写分离。

二.从库过滤复制实现方案

1.在主库中实现过滤

    如下所示，我们可以查看当前正在写入的二进制文件:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock
        Welcome to the MySQL monitor.  Commands end with ; or \g.
        Your MySQL connection id is 24
        Server version: 5.7.31-log MySQL Community Server (GPL)

        Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

        Oracle is a registered trademark of Oracle Corporation and/or its
        affiliates. Other names may be trademarks of their respective
        owners.

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

        mysql> 
        mysql> SHOW MASTER STATUS;
        +------------------+----------+--------------+------------------+-------------------+
        | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
        +------------------+----------+--------------+------------------+-------------------+
        | mysql-bin.000002 |     2772 |              |                  |                   |
        +------------------+----------+--------------+------------------+-------------------+
        1 row in set (0.00 sec)

        mysql> 

    如上输出所示，我们可以基于"binlog_do_db"和"binlog_ignore_db"来设置要过滤的数据库，值得注意的是，我们在配置文件写参数时要全部字母小写，否则可能重启数据库时会报错不识别的参数:
        binlog_do_db:
            指定白名单，在白名单记录的数据库是可以进行复制的。
        binlog_ignore_db:
            指定黑名单，在黑名单记录的数据库是不可以进行复制的。

    值得注意的是，尽管MySQL提供了在主库进行过滤的方案，但很多公司并没选择该方案，因为大家普遍觉得这样做会给主库带来过多的资源消耗(比如CPU的消耗)，因此大多数人还是选择从库配置参数来过滤数据。但作为DBA你应该了解这种方案，以便于在工作中有人使用该方案时，你不会感到迷茫。

    温馨提示:
        (1)通常情况下，我们可以使用"binlog_do_db"和"binlog_ignore_db"中任意一个参数就可以实现主库的过滤复制功能;
        (2)如果有10个数据库，而我们只想复制其中1个数据库到从库，这个时候建议大家使用白名单，即"binlog_do_db"参数来控制;
        (3)如果有10个数据库，而我们只想排除1个数据库，其它数据库数据都需要复制到从库，这时候建议大家使用黑名单，即"binlog_ignore_db"参数来控制;

2.在从库实现过滤

    我们可以通过以下命令来查看MySQL从库是否有设置过滤的数据库或者表:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep Replicate | head -6
                      Replicate_Do_DB: 
                  Replicate_Ignore_DB: 
                   Replicate_Do_Table: 
               Replicate_Ignore_Table: 
              Replicate_Wild_Do_Table: 
          Replicate_Wild_Ignore_Table: 
        [root@docker201.oldboyedu.com ~]# 

    如上输出所示，我们可以基于以下几个参数来控制要过滤的数据库或者表，值得注意的是，我们在配置文件写参数时要全部字母小写，否则可能重启数据库时会报错不识别的参数:
        用于过滤"database"级别的参数:(精确匹配数据库)
            replicate_do_db:
                指定白名单，在白名单记录的数据库是可以让从库的SQL线程进行"回放"执行的。
            replicate_ignore_db:
                指定黑名单，在黑名单记录的数据库是不可以让从库的SQL线程进行"回放"执行的。
            温馨提示:
                要定制白名单数据库直接指定数据库名称即可，比如:"replicate_do_db=oldboyedu"，表示白名单指定"oldboyedu"数据库下的所有表的操作允许从库的SQL线程进行"回放"。

        用于过滤"table"级别的参数:(精确匹配表)
            replicate_do_table:
                指定白名单，在白名单记录的表是可以让从库的SQL线程进行"回放"执行的。
            replicate_ignore_table:
                指定黑名单，在黑名单记录的表是不可以让从库的SQL线程进行"回放"执行的。
            温馨提示:
                要指定具体的表必须写上该表对应的数据库名称，比如"replicate_do_table=oldboyedu.student"，表示白名单指定"oldboyedu"数据库下的"student"表的操作允许从库的SQL线程进行"回放"。

        用于模糊匹配过滤"table"级别的参数:(模糊匹配表)
            replicate_wild_do_table:
                指定白名单，在白名单记录的表是可以让从库的SQL线程进行"回放"执行的。
            replicate_wild_ignore_table:
                指定黑名单，在黑名单记录的表是不可以让从库的SQL线程进行"回放"执行的。
            温馨提示:
                我们可以指定一个比较模糊的表，比如"replicate_wild_do_table=oldboyedu.stu*"，表示白名单指定"oldboyedu"数据库下的以"stu*"开头的表操作都允许从库的SQL线程进行"回放"。

    综上所述，从库相比主库提供了更加细粒度的过滤级别，但通常我们使用的还是基于库级别进行过滤的场景比较多。

三.基于从库实现过滤复制实战案例

1.配置主从复制初始关系

    主库:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW DATABASES;"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 

    从库:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep Running:
                     Slave_IO_Running: Yes
                    Slave_SQL_Running: Yes
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES;"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | mysql              |
        | performance_schema |
        | sys                |
        +--------------------+
        [root@docker201.oldboyedu.com ~]#

2.从库指定只复制"oldboyedu"和"devops"这两个数据库的数据

[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep Replicate
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB: 
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# vim /oldboyedu/softwares/mysql3308/my.cnf 
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# tail -3 /oldboyedu/softwares/mysql3308/my.cnf 
# 指定"oldboyedu"和"devops"这两个数据库允许复制，多个数据库需要写多行即可
replicate_do_db=oldboyedu
replicate_do_db=devops
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# systemctl restart mysqld3308
[root@docker201.oldboyedu.com ~]# 
[root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep Replicate
              Replicate_Do_DB: oldboyedu,devops  # 重启后生效，不难发现，只允许复制"oldboyedu"数据库和"devops"数据库。
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
  Replicate_Ignore_Server_Ids: 
         Replicate_Rewrite_DB: 
[root@docker201.oldboyedu.com ~]#

3.测试从库配置的过滤复制是否生效

    主库创建数据库和表:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE hadoop DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE storm DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE spark DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE kafka DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE flink DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE devops DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "CREATE DATABASE oldboyedu DEFAULT CHARSET utf8mb4;"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "USE devops;CREATE TABLE cmdb (id int);"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "USE oldboyedu;CREATE TABLE student (id int);"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "USE hadoop;CREATE TABLE hdfs (id int);"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "USE oldboyedu;INSERT INTO student VALUES (10),(20),(30);"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "USE devops;INSERT INTO cmdb VALUES (11),(22),(33);"
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT * FROM devops.cmdb;"
        +------+
        | id   |
        +------+
        |   11 |
        |   22 |
        |   33 |
        +------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SELECT * FROM oldboyedu.student;"
        +------+
        | id   |
        +------+
        |   10 |
        |   20 |
        |   30 |
        +------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3307.sock -e "SHOW DATABASES;"
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | devops             |
        | flink              |
        | hadoop             |
        | kafka              |
        | mysql              |
        | performance_schema |
        | spark              |
        | storm              |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 

        温馨提示:
            请将"CREATE TABLE oldboyedu.student (id int);"换成"USE oldboyedu;CREATE TABLE student (id int);"，否则默认情况下从库是不会创建对应的表哟~

    从库验证:
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW SLAVE STATUS\G" | grep Replicate
                      Replicate_Do_DB: oldboyedu,devops
                  Replicate_Ignore_DB: 
                   Replicate_Do_Table: 
               Replicate_Ignore_Table: 
              Replicate_Wild_Do_Table: 
          Replicate_Wild_Ignore_Table: 
          Replicate_Ignore_Server_Ids: 
                 Replicate_Rewrite_DB: 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW DATABASES;" 
        +--------------------+
        | Database           |
        +--------------------+
        | information_schema |
        | devops             |
        | mysql              |
        | performance_schema |
        | sys                |
        | oldboyedu        |
        +--------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW TABLES FROM devops;" 
        +------------------+
        | Tables_in_devops |
        +------------------+
        | cmdb             |
        +------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SHOW TABLES FROM oldboyedu;" 
        +-----------------------+
        | Tables_in_oldboyedu |
        +-----------------------+
        | student               |
        +-----------------------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SELECT * FROM devops.cmdb;" 
        +------+
        | id   |
        +------+
        |   11 |
        |   22 |
        |   33 |
        +------+
        [root@docker201.oldboyedu.com ~]# 
        [root@docker201.oldboyedu.com ~]# mysql -S /tmp/mysql3308.sock -e "SELECT * FROM oldboyedu.student;" 
        +------+
        | id   |
        +------+
        |   10 |
        |   20 |
        |   30 |
        +------+
        [root@docker201.oldboyedu.com ~]#

谨此笔记，记录过往。凭君阅览，如能收益，莫大奢望。

暂无评论

发送评论编辑评论

Markdown

悄悄话

邮件提醒

|´・ω・)ノ

ヾ(≧∇≦*)ゝ

(☆ω☆)

（╯‵□′）╯︵┴─┴

￣﹃￣

(/ω＼)

∠( ᐛ 」∠)＿

(๑•̀ㅁ•́ฅ)

→_→

୧(๑•̀⌄•́๑)૭

٩(ˊᗜˋ*)و

(ノ°ο°)ノ

(´இ皿இ｀)

⌇●﹏●⌇

(ฅ´ω`ฅ)

(╯°A°)╯︵○○○

φ(￣∇￣o)

ヾ(´･･｀｡)ノ"

( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃

(ó﹏ò｡)

Σ(っ °Д °;)っ

( ,,´･ω･)ﾉ"(´っω･｀｡)

╮(╯▽╰)╭

o(*////▽////*)q

＞﹏＜

( ๑´•ω•) "(ㆆᴗㆆ)

颜文字

Emoji

小恐龙

花!