[toc]

主从复制简介

案例

image-20230625185306351

2015年5月28日11时，12小时后恢复，损失：平均每小时106.48W$

规避风险的方案

  1. 高可用
  2. 辅助备份
  3. 分担负载

功能简介

复制是 MySQL 的一项功能，允许服务器将一个实例的更改复制到另一个实例。

  1. 主服务器将所有数据和结构更改记录到二进制日志中。
  2. 从属服务器从主服务器请求该二进制日志并在本地应用其内容。
  3. IO：请求主库，获取上一次执行过的新的事件，并存放到relaylog
  4. SQL：从relaylog中将sql语句翻译给从库执行



主从复制原理

主从复制的前提

  1. 两台或两台以上的数据库实例
  2. 主库要开启二进制日志
  3. 主库要有复制用户
  4. 主库的server_id和从库不同
  5. 从库需要在开启复制功能前，要获取到主库之前的数据（主库备份，并且记录binlog当时位置）
  6. 从库在第一次开启主从复制时，时必须获知主库：ip，port，user，password，logfile，pos
    • IP：10.0.0.51
    • Port：3306
    • User：rep
    • Password：Help9090
    • logFile：mysql-bin.000002
    • Pos：120
  7. 从库要开启相关线程：IO、SQL
  8. 从库需要记录复制相关用户信息，还应该记录到上次已经从主库请求到哪个二进制日志
  9. 从库请求过来的binlog，首先要存下来，并且执行binlog，执行过的信息保存下来

主从复制的原理结构

主从复制涉及3线程4文件

  • 从库：
    • IO线程
    • SQL线程
    • master.info
    • relay-log.info
    • db03-relay-bin.000004
  • 主库：
    • dump线程
    • mysql-bin.000001

image-20230419193908831

  • 总结：
    1. binlog：仅主库开启
    2. 主从复制用户：仅主库要创建
    3. server_id：主库和从库都需要设置，不能相同，即标识符作用
  • 注意：
    • 做主从复制之前，一定要保证两台数据库的数据是一致的

主从复制涉及到的文件和线程

  • 主库：
    1. 主库binlog：记录主库发生过的修改事件
    2. dump thread：给从库传送（TP）二进制日志线程
  • 从库：
    1. relay-log（中继日志）：存储所有主库TP过来的binlog事件
    2. master.info：存储复制用户信息，上次请求到的主库binlog位置点
    3. IO thread：接收主库发来的binlog日志，也是从库请求主库的线程
    4. SQL thread：执行主库TP过来的日志
  • 原理
    1. 通过change master to语句告诉从库主库的ip，port，user，password，file，pos
    2. 从库通过start slave命令开启复制必要的IO线程和SQL线程
    3. 从库通过IO线程拿着change master to用户密码相关信息，连接主库，验证合法性
    4. 从库连接成功后，会根据binlog的pos问主库，有没有比这个新的数据
    5. 主库接收到从库请求后，比较一下binlog信息，如果有就将最新数据通过dump线程给从库IO线程
    6. 从库通过IO线程接收到主库发来的binlog事件，存储到TCP/IP缓存中，并返回ACK更新master.info位置点
    7. 将TCP/IP缓存中的内容存到relay-log中
    8. SQL线程读取relay-log.info，读取到上次已经执行过的relay-log位置点，继续执行后续的relay-log日志，执行完成后，更新relay-log.info

搭建主从

新环境搭建主从

  • 主库操作

    • 修改配置文件

      1
      2
      3
      4
      5
      # 编辑mysql配置文件
      vim /etc/my.cnf
      [mysqld]             # 在mysqld标签下配置
      server_id=1          # 标识符，主库和从库server-id不相同即可
      log_bin=mysql-bin    # 开启binlog日志

    • 创建主从复制用户

      1
      2
      3
      4
      5
      6
      7
      8
      # 登录数据库
      mysql -uroot -pHelp9090

      #创建rep用户
      mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'Help9090';

      # 记录主库binlog及位置点
      mysql> show master status;

  • 从库操作

    • 修改配置文件

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      # 修改db02配置文件
      vim /etc/my.cnf
      [mysqld]          # 在mysqld标签下配置
      server_id =5      # 标识符，主库和从库server-id不相同即可

      # 重启mysql
      /etc/init.d/mysqld restart

      # 登陆数据库
      mysql -uroot -pHelp9090

      # 执行change master to 语句
      mysql> change master to
      master_host='172.16.1.51',
      master_user='rep',
      master_password='Help9090',
      master_log_file='mysql-bin.000001',
      master_log_pos=125337;

      # 开启主从复制
      mysql> start slave;

      # 查看从库状态
      mysql> show slave status\G

生产环境搭建主从

  • 主库操作

    • 修改配置文件

      1
      2
      3
      4
      5
      6
      7
      8
      # 编辑mysql配置文件
      vim /etc/my.cnf
      [mysqld]
      # 在mysqld标签下配置
      server_id=1
      # 标识符，主库和从库server-id不相同即可
      log_bin=mysql-bin
      # 开启binlog日志

    • 创建主从复制用户

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      # 1.登录数据库
      mysql -uroot -pHelp9090

      # 2.创建rep用户
      mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'Help9090';

      # 3.做打点全备
      mysqldump -uroot -pHelp9090 -A -R --triggers --master-data=1 --single-transaction > /tmp/master.sql

      # 4.发送到从库
      scp /tmp/master.sql 172.16.1.51:/tmp/

  • 从库操作

    • 修改配置文件

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      # 1.修改配置文件
      vim /etc/my.cnf
      [mysqld]
      # 在mysqld标签下配置
      basedir=/app/mysql
      datadir=/app/mysql/data
      server_id =2
      # 标识符，主库和从库server-id不相同即可
      skip-name-resolve
      log_bin=mysql-bin

      # 2.重启mysql
      /etc/init.d/mysqld restart

      # 3.恢复全备数据
      mysql -uroot -pHelp9090 < /tmp/master.sql

      # 4.登陆数据库
      mysql -uroot -pHelp9090

      # 5.执行change master to 语句
      mysql> change master to
      master_host='10.0.0.54',
      master_user='rep',
      master_password='Help9090';

      # 6.开启主从复制
      mysql> start slave;

      # 7.查看从库状态
      mysql> show slave status\G

MySQL主从复制基本故障

  • 从库IO线程故障

    1. IP不通
      • ping
    2. 端口不通
      • telnet
    3. 用户名密码问题
      • 在从库上尝试连接主从复制用户：mysql -urep -pHelp0909 -h10.0.0.54
    4. server_id
      • 检查是否相同
    5. uuid
      • 检查是否相同
    6. binlog
      • 检查是否正确

  • 从库SQL线程故障

    • 主库和从库数据不一致

      • 方案一(有风险)

        1
        2
        3
        4
        5
        6
        7
        8
        # 临时停止同步
        mysql> stop slave;

        # 将同步指针向下移动一个（可重复操作）
        mysql> set global sql_slave_skip_counter=1;

        # 开启同步
        mysql> start slave;

      • 方案二(有风险)

        1
        2
        3
        4
        5
        # 编辑配置文件
        [root@db01 ~]# vim /etc/my.cnf

        # 在[mysqld]标签下添加以下参数
        slave-skip-errors=1032,1062,1007

      • 方案三(推荐)

        1
        # 重新备份数据库，恢复到从库

      • 规避风险

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        # 给从库设置为只读

        # 在命令行临时设置
        set global read_only=1;

        # 在配置文件中永久生效
        read_only=1

        # 1.保证两个数据库的数据完全一致
        # 2.从库无法写入数据

延时从库

延时从库的概述

普通的主从复制可能存在不足

  1. 逻辑损坏怎么办？
  2. 不能保证主库的操作，从库一定能做
  3. 高可用？自动failover？
  4. 过滤复制

PS：企业中一般会延时3-6小时

延时从库的原理

image-20230422205313978

延时从库的优缺点

  • 优点：
    • 可以备份，恢复数据
    • 安全性高
  • 缺点：
    • 延时从库不能对外提供服务
    • 浪费资源

配置延时从库

  • 延时从库配置方法

    • 没有做过主从复制的从库

      • 部署延时从库

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        18
        19
        20
        21
        22
        23
        24
        25
        26
        # 停止主从复制
        mysql>stop slave;

        # 查看主库的binlog名字和位置点
        mysql> show master status;
        +------------------+----------+
        | File             | Position |
        +------------------+----------+
        | mysql-bin.000002 | 310      |
        +------------------+----------+

        # 配置从库中主库的信息并设置延时时间
        change master to
        master_user='rep',
        master_password='123@qqdianCOM',
        master_host='172.16.1.51',
        master_log_file='mysql-bin.000002',
        master_log_pos=310,
        master_delay=180;

        # 开启主从复制
        mysql> start slave;

        # 查看状态
        mysql> show slave status \G
        SQL_Delay: 180

      • 取消从库身份的两种方式

        1
        2
        3
        4
        5
        # 重置从库所有主库信息，重新开始配置
        mysql> reset slave all;

        # 重置从库主库信息，更新配置
        mysql> reset slave;

    • 做过主从复制的从库

      • 部署延时从库

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        # 停止主从复制
        mysql> stop slave;

        # 重置从库主库信息，更新配置
        mysql> reset slave;

        # 更新从库中主库的信息
        mysql> change master to master_delay=180;

        # 开启主从复制
        mysql> start slave;

        # 查看状态
        mysql> show slave status \G
        SQL_Delay: 180

      • 取消延时从库身份

        1
        2
        3
        4
        5
        6
        7
        8
        # 停止主从复制
        mysql> stop slave;

        # 修改延时时间为0
        mysql> change master to master_delay=0;

        # # 开启主从复制
        mysql> start slave;

利用延时从库恢复数据案例

故障环境

总数据量级500G，正常备份去恢复需要1.5-2小时

  1. 配置延时3600秒

    1
    mysql>CHANGE MASTER TO MASTER_DELAY = 3600;

  2. 主库

    1
    drop database db;

  3. 怎么利用延时从库，恢复数据？

模拟环境

  • 搭建主从库并配置延时从库

    • 主库

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      # 1.登录数据库
      mysql -uroot -pHelp9090

      # 2.创建rep用户
      mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'Help9090';

      # 3.做打点全备
      mysqldump -uroot -pHelp9090 -A -R --triggers --master-data=1 --single-transaction > /tmp/master.sql

      # 4.发送到从库
      scp /tmp/master.sql 172.16.1.52:/tmp/

    • 从库

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      # 1.修改配置文件
      vim /etc/my.cnf
      [mysqld]          # 在mysqld标签下配置
      basedir=/app/mysql
      datadir=/app/mysql/data
      server_id =2      # 标识符，主库和从库server-id不相同即可
      skip-name-resolve
      log_bin=mysql-bin

      # 2.重启mysql
      /etc/init.d/mysqld restart

      # 3.恢复全备数据
      mysql -uroot -pHelp9090 < /tmp/master.sql

      # 4.登陆数据库
      mysql -uroot -pHelp9090

      # 5.执行change master to 语句
      mysql> change master to
      master_host='172.16.1.51',
      master_user='rep',
      master_password='Help9090',
      master_delay=300;

      # 6.开启主从复制
      mysql> start slave;

      # 7.查看从库状态
      mysql> show slave status\G

  • 模拟数据

    • 主库创建库

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      # 创建db库
      create database db;

      # 执行脚本模拟写入数据
      vim a.sh
      #!/bin/bash
      mysql -uroot -pHelp9090 -e 'drop database db;'
      mysql -uroot -pHelp9090 -e 'create database if not exists db;'
      mysql -uroot -pHelp9090 -e 'use db;create table if not exists db1(id int);'
      num=0
      while true;do
              ((num ++))
              mysql -uroot -pHelp9090 -e "insert into db.db1
      values($num);commit;"
              sleep 1
      done

    • 模拟误删除数据

      1
      drop database db;

恢复数据

  • 从库操作

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    # 停止SQL线程
    mysql> stop slave sql_thread;

    # 准备新环境

    # 停止连接数据库的所有程序
    systemctl stop tomcat
    systemctl stop nginx
    systemctl stop php-fpm

    # 给延时从库做一次全备
    [root@db03 data]# mysqldump -uroot -p123@qqdianCOM -A -R --triggers|gzip > /tmp/full.sql.gz

    # 将全备恢复到新环境(注意：不要关闭二进制日志记录)
    [root@db03 data]# scp /tmp/full.sql.gz 172.16.1.51:/tmp

    # 查看relay-log.info中SQL线程执行进度
    [root@db03 data]# cat relay-log.info
    ./db03-relay-bin.000002
    72130

    # 截取relaylog结束位置码
    mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002 |grep drop -C 5
    at 564297

    # 第一段截取内容
    起始位置点：497244
    结束位置点：564297

    # 截取relaylog起始位置码
    mysqlbinlog --base64-output=decode-rows -vvv db02-relay-bin.000002 |grep drop -C 5
    at 564383

    # 截取relaylog结束位置码
    [root@db03 data]# mysqlbinlog --base64-output=decode-rows -vvv
    db03-relay-bin.000002|tail
    at 992801

    # 截取relay-log
    [root@db03 data]# mysqlbinlog --start-position=497244 --stopposition=564297 db03-relay-bin.000002 > /tmp/inc1.sql
    [root@db03 data]# mysqlbinlog --start-position=564383 --stopposition=992801 db03-relay-bin.000002 > /tmp/inc2.sql

    # 将截取的relay-log发送到新环境
    [root@db03 data]# scp /tmp/inc1.sql /tmp/inc2.sql 172.16.1.51:/opt

    # 延时从库开启sql线程
    mysql> start slave sql_thread;

  • 新环境操作

    1
    2
    3
    4
    5
    6
    7
    8
    # 恢复全备数据
    [root@db01 ~]# zcat /tmp/full.sql.gz |mysql -uroot -pHelp9090

    # 恢复第一段截取内容
    [root@db01 ~]# mysql -uroot -pHelp9090 < /opt/inc1.sql

    # 恢复第二段截取内容
    [root@db01 ~]# mysql -uroot -pHelp9090 < /opt/inc2.sql

半同步复制

半同步复制的概述

从MySQL5.5开始，支持半自动复制。
之前版本的MySQL Replication都是异步（asynchronous）的，主库在执行完一些事务后，是不会管备库的进度的。
如果备库不幸落后，而更不幸的是主库此时又出现Crash（例如宕机），这时备库中的数据就是不完整的。
简而言之，在主库发生故障的时候，我们无法使用备库来继续提供数据一致的服务了。

半同步复制（Semi synchronous Replication）则一定程度上保证提交的事务已经传给了至少一个备库。
出发点是保证主从数据一致性问题，安全的考虑。

半同步复制的原理

image-20230422210106163

各版本半同步复制功能的介绍

  • 5.5 出现概念，但是不建议使用，性能太差
  • 5.6出现group commit 组提交功能，来提升开启半同步复制的性能
  • 5.7更加完善了，在group commit基础上出现了MGR
  • 5.7的增强半同步复制的新特性：after commit； after sync；

配置半同步复制

  • 配置半同步复制

    • 主库配置半同步复制

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      35
      36
      37
      38
      39
      40
      41
      42
      43
      44
      45
      46
      47
      48
      49
      50
      51
      52
      53
      54
      55
      56
      57
      58
      59
      60
      61
      # 先查看是否动态支持半同步复制
      mysql> show global variables like 'have_dynamic_loading';
      +----------------------+-------+
      | Variable_name        | Value |
      +----------------------+-------+
      | have_dynamic_loading | YES   |
      +----------------------+-------+

      # 查看本地半同步插件
      [root@db01 ~]# ll /usr/lib64/mysql/plugin/
      -rwxr-xr-x 1 root root 937952 Dec 8 02:37 semisync_master.so
      -rwxr-xr-x 1 root root 160888 Dec 8 02:37 semisync_slave.so

      # 主库安装半同步插件
      mysql> install plugin rpl_semi_sync_master soname'semisync_master.so';

      # 启动插件
      mysql> set global rpl_semi_sync_master_enabled = 1;

      # 设置sql线程执行超时时间
      mysql> set global rpl_semi_sync_master_timeout = 1000;

      # 永久开启和设置超时时间(不用重启)
      vim /etc/my.cnf
      rpl_semi_sync_master_enabled = 1
      rpl_semi_sync_master_timeout = 1000

      # 查看配置信息
      mysql> show variables like'rpl%';
      +-------------------------------------------+------------+
      | Variable_name                             | Value      |
      +-------------------------------------------+------------+
      | rpl_semi_sync_master_enabled              | ON         |
      | rpl_semi_sync_master_timeout              | 1000       |
      | rpl_semi_sync_master_trace_level          | 32         |
      | rpl_semi_sync_master_wait_for_slave_count | 1          |
      | rpl_semi_sync_master_wait_no_slave        | ON         |
      | rpl_semi_sync_master_wait_point           | AFTER_SYNC |
      | rpl_stop_slave_timeout                    | 31536000   |
      +-------------------------------------------+------------+

      # 查看半自动同步执行信息
      mysql> show global status like 'rpl_semi%';
      +--------------------------------------------+-------+
      | Variable_name                              | Value |
      +--------------------------------------------+-------+
      | Rpl_semi_sync_master_clients               | 0 |
      | Rpl_semi_sync_master_net_avg_wait_time | 0 |
      | Rpl_semi_sync_master_net_wait_time     | 0 |
      | Rpl_semi_sync_master_net_waits         | 0 |
      | Rpl_semi_sync_master_no_times          | 0 |
      | Rpl_semi_sync_master_no_tx             | 0 |
      | Rpl_semi_sync_master_status            | ON |
      | Rpl_semi_sync_master_timefunc_failures | 0 |
      | Rpl_semi_sync_master_tx_avg_wait_time | 0 |
      | Rpl_semi_sync_master_tx_wait_time     | 0 |
      | Rpl_semi_sync_master_tx_waits         | 0 |
      | Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
      | Rpl_semi_sync_master_wait_sessions | 0 |
      | Rpl_semi_sync_master_yes_tx | 0 |
      +--------------------------------------------+-------+

    • 从库配置半同步复制

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      33
      34
      # 从库安装半同步插件
      mysql> install plugin rpl_semi_sync_slave soname'semisync_slave.so';

      # 开启插件
      mysql> set global rpl_semi_sync_slave_enabled = 1;

      # 永久开启插件(不用重启)
      vim /etc/my.cnf
      rpl_semi_sync_slave_enabled = 1

      # 重启IO线程，使其生效
      mysql> stop slave io_thread;
      mysql> start slave io_thread;

      # 查看半自动同步执行信息
      mysql> show global status like 'rpl_semi%';
      +--------------------------------------------+-------+
      | Variable_name | Value |
      +--------------------------------------------+-------+
      | Rpl_semi_sync_master_clients | 1 |
      | Rpl_semi_sync_master_net_avg_wait_time | 0 |
      | Rpl_semi_sync_master_net_wait_time | 0 |
      | Rpl_semi_sync_master_net_waits | 0 |
      | Rpl_semi_sync_master_no_times | 0 |
      | Rpl_semi_sync_master_no_tx | 0 |
      | Rpl_semi_sync_master_status | ON |
      | Rpl_semi_sync_master_timefunc_failures | 0 |
      | Rpl_semi_sync_master_tx_avg_wait_time | 0 |
      | Rpl_semi_sync_master_tx_wait_time | 0 |
      | Rpl_semi_sync_master_tx_waits | 0 |
      | Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
      | Rpl_semi_sync_master_wait_sessions | 0 |
      | Rpl_semi_sync_master_yes_tx | 0 |
      +--------------------------------------------+-------+

  • 关闭半同步复制

    • 主库关闭半同步复制

      1
      2
      # 关闭半同步（1:开启 0:关闭）
      mysql> set global rpl_semi_sync_master_enabled = 0;

    • 从库关闭半同步复制

      1
      2
      3
      4
      5
      6
      # 关闭半同步（1:开启 0:关闭）
      mysql> set global rpl_semi_sync_slave_enabled = 0;

      # 重启IO线程
      mysql> stop slave io_thread;
      mysql> start slave io_thread;

PS：

  • 在开启半同步复制状态时，查询会有延迟时间，关闭之后则没有
  • 相关参数说明
    • rpl_semi_sync_master_timeout=milliseconds
      设置此参数值（ms）,为了防止半同步复制在没有收到确认的情况下发生堵塞，如果Master在超时之前没有收到任何确认，将恢复到正常的异步复制，并继续执行没有半同步的复制操作。
    • rpl_semi_sync_master_wait_no_slave={ON|OFF}
      如果一个事务被提交,但Master没有任何Slave的连接，这时不可能将事务发送到其它地方保护起来。
      默认情况下，Master会在时间限制范围内继续等待Slave的连接，并确认该事务已经被正确的写到磁盘上。
      可以使用此参数选项关闭这种行为，在这种情况下，如果没有Slave连接，Master就会恢复到异步复制。

过滤复制

过滤复制的概述及原理

image-20230422212852878

  • 主库：
    • 白名单：只记录白名单中列出的库的二进制日志
      • binlog-do-db
    • 黑名单：不记录黑名单列出的库的二进制日志
      • binlog-ignore-db
  • 从库：
    • 白名单：只执行白名单中列出的库或者表的中继日志
      • –replicate-do-db=test
      • –replicate-do-table=test.t1
      • –replicate-wild-do-table=test.t2
    • 黑名单：不执行黑名单中列出的库或者表的中继日志
      • –replicate-ignore-db
      • –replicate-ignore-table
      • –replicate-wild-ignore-table

复制过滤配置

  • 主库：在binlog做的操作

    • 黑名单

      • binlog-ignore-db

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        # 主库设置黑名单
        vim /etc/my.cnf
        [mysqld]
        binlog-ignore-db=mysql
        binlog-ignore-db=wzry

        # 重启MySQL
        [root@db01 ~]# systemctl restart mysqld
        黑名单指定的数据库中执行的sql语句都不记录到binlog中，其他库正常记录

        # 查看binlog状态
        mysql> show master status;
        +------------------+----------+--------------+------------------+
        |      File        | Position | Binlog_Do_DB | Binlog_Ignore_DB |
        +------------------+----------+--------------+------------------+
        | mysql-bin.000008 | 154      |              | mysql,wzry       |
        +------------------+----------+--------------+------------------+

    • 白名单

      • binlog-do-db

        1
        2
        3
        4
        5
        6
        7
        8
        9
        10
        11
        12
        13
        14
        15
        16
        17
        # 主库设置白名单
        vim /etc/my.cnf
        [mysqld]
        binlog-do-db=lol
        binlog-do-db=dnf

        # 重启MySQL
        [root@db01 ~]# systemctl restart mysqld
        白名单只记录指定库的binlog，其他库一律不记录

        # 查看binlog状态
        mysql> show master status;
        +------------------+----------+--------------+
        |      File        | Position | Binlog_Do_DB |
        +------------------+----------+--------------+
        | mysql-bin.000006 | 154      | lol,dnf      |
        +------------------+----------+--------------+

  • 从库：在relay-log做的操作，IO线程正常接收数据，SQL线程处判断是否执行

    • 黑名单

      • replicate-ignore-db：设置只读取哪个库的数据

        1
        replicate-ignore-db=库名

      • replicate-ignore-table：设置只读取哪个库中的哪个表的数据

        1
        replicate-ignore-table=库名.表名

      • replicate-wild-ignore-table：设置只读取哪个库中的通配符指定的表

        1
        replicate-wild-ignore-table=库名.x*

    • 白名单

      • replicate-do-db

        1
        replicate-do-db=库名

      • replicate-do-table

        1
        replicate-do-table=库名.表名

      • replicate-wild-do-table

        1
        replicate-wild-do-table=库名.x*

需求示例

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
# 主库列表
db01：172.16.1.51
王者荣耀
和平精英
英雄联盟
地下城与勇士

# 王者荣耀库
db02：172.16.1.52
# vim /etc/my.cnf
[mysqld]
replicate-do-db=wzry

# 和平精英库
db03：172.16.1.53
# vim /etc/my.cnf
[mysqld]
replicate-do-db=hpjy

# 英雄联盟库
db04：172.16.1.54
# vim /etc/my.cnf
[mysqld]
replicate-do-db=lol

# 地下城与勇士库
db05：172.16.1.55
# vim /etc/my.cnf
[mysqld]
replicate-do-db=dnf

实现一主三从，过滤复制

  • 搭建主从库并配置延时从库

    • 主库(db01)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      # 修改配置文件
      vim /etc/my.cnf
      [mysqld]
      # 在mysqld标签下配置
      basedir=/app/mysql
      datadir=/app/mysql/data
      server_id =1
      # 标识符，主库和从库server-id不相同即可
      skip-name-resolve
      log_bin=mysql-bin

      # 登录数据库
      mysql -uroot -pHelp9090

      # 创建rep用户
      mysql> grant replication slave on *.* to rep@'172.16.1.%' identified by 'Help9090';

      # 做打点全备
      mysqldump -uroot -pHelp9090 -A -R --triggers --master-data=1 --single-transaction > /tmp/master.sql

      # 发送到从库
      scp /tmp/master.sql 172.16.1.52:/tmp/ 172.16.1.53:/tmp/ 172.16.1.54:/tmp/

    • 从库(db02、db03、db04)

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      11
      12
      13
      14
      15
      16
      17
      18
      19
      20
      21
      22
      23
      24
      25
      26
      27
      28
      29
      30
      31
      32
      # 1.修改配置文件
      vim /etc/my.cnf
      [mysqld]
      # 在mysqld标签下配置
      basedir=/app/mysql
      datadir=/app/mysql/data
      server_id =2
      # 标识符，主库和从库server-id不相同即可
      skip-name-resolve
      log_bin=mysql-bin
      replicate-do-db=test1

      # 2.重启mysql
      /etc/init.d/mysqld restart

      # 3.恢复全备数据
      mysql -uroot -pHelp9090 < /tmp/master.sql

      # 4.登陆数据库
      mysql -uroot -pHelp9090

      # 5.执行change master to 语句
      mysql> change master to
      master_host='10.0.0.51',
      master_user='rep',
      master_password='Help9090',

      # 6.开启主从复制
      mysql> start slave;

      # 7.查看从库状态
      mysql> show slave status\G