MySQL高级主从复制

最新推荐文章于 2023-12-21 20:10:35 发布

路～～～

最新推荐文章于 2023-12-21 20:10:35 发布

阅读量177

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/cxs_123/article/details/115327689

版权

延时从库

MySQL从5.6版本开始就支持主从延迟复制，这个功能主要解决的问题是，当主库有逻辑的数据删除或者错误更新时，所有的从库都会进行错误的更新，从而导致数据库的所有数据都异常，即使有定时的备份数据可以用于数据恢复，特别是数据库的数据量很大时，恢复时间也会很长，在恢复期间，数据库数据被删除或者出现错误数据都会影响正常的访问体验。而延迟复制就可以很好地解决这个问题。例如，可以设定某一个从库和主库的更新延迟1个小时，这样当主库数据出现问题以后，1个小时以内即可发现，可以对这个从库进行无害恢复处理，使之依然是正确的完整的数据，这样就省去了数据恢复占用的时间，用户体验也会有所提高。

延时从库即从库延迟一定的时间再对主库中新产生的数据进行同步。常规的主从复制，由于主库发生数据变动时，从库会很快对主库新产生的数据进行同步。

例如：主库误删除一张表，从库为了同步数据，也会在从库中执行相应的操作。所以常规的主从复制只能很好的避免数据的物理损坏，但是无法避免数据库中的数据逻辑损坏

主从复制的主要作用就是备份数据、恢复数据，所以就需要延时从库来尽可能地避免数据库中数据地逻辑损坏。

延时从库的配置

延时从库主要是通过 SQL线程延时回放 relaylog 来实现的。主库中新产生的 binlog 已经写入 relaylog 中了，SQL线程会在设定的时间到达后再去回放 relaylog 。

mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;  #设置延迟的时间（单位：秒）
mysql>start slave;
mysql> show slave status \G;
     ....
SQL_Delay: 300              #对应的参数
SQL_Remaining_Delay: NULL   #计时最近的一个事务执行还需要等待多长时间
     ....

延时从库处理逻辑故障

延时从库的恢复方法

（1）监控到主库出现故障时，停止从库 SQL线程，记录 relaylog 已经回放的日志点，作为截取 relaylog 中日志的起点

mysql> stop slave sql_thread ;    #可以单独启动一个线程，若是不指定启动某个线程，那就默认SQL、IO线程都启动。
mysql>show slave status \G;     #获取截取 relaylog 的起点
         ...
    Relay_Log_File: db01-relay-bin.000002     
    Relay_Log_Pos: 320
         ...

（2）截取 relaylog

获取截取日志的终点位置：
[root@db01 ~]# mysql -uroot -pxxxx
mysql> show relaylog events in 'db01-relay-bin.000002'  #找到误操作所对应的 position

[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=xxx --stop-position=xxx  db01-relay-bin.000002 >/backup/delay.sql   #截取日志

（3）模拟 SQL线程回放 relaylog

mysql> set sql_log_bin=0;
mysql> source /backup/delay.sql;
mysql> set sql_log_bin=1;

（4）恢复业务

情况一：若是就一个从库的话，让从库代替主库工作，然后再把主库中的数据进行恢复

情况二：从库将恢复好的库还原到主库中。

案例演练

1、数据准备

从库：设置延时从库，延时时间为300s
mysql> change master to master_delay=300;

主库：
mysql> create database delay charset utf8mb4;
mysql> use delay;
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;
mysql> drop database delay;

2、停止从库中 SQL线程，并获取 relaylog 的位置点

mysql> stop slave sql_thread;
mysql> show slave status \G；
          ...
   Relay_Log_File: db01-relay-bin.000002
   Relay_Log_Pos: 626
          ...

3、找到 relaylog 的截取终点

mysql> show relaylog events in 'db01-relay-bin.000002';
           ...
| db01-relay-bin.000002 | 1299 | Query          |         7 |        1228 | drop database delay

4、截取 relaylog 的日志

[root@db01 data]# mysqlbinlog --start-position=626 --stop-position=1299 db01-relay-bin.000002 >/tmp/relay.sql

5、恢复数据到从库

[root@db01 ~]# mysql -uroot -p -S /data/3308/mysql.sock 
    mysql> set sql_log_bin=0;
    mysql> source /tmp/relay.sql;
    mysql> set sql_log_bin=1;

过滤复制

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

主库方面：

binlog_do_db参数：类似白名单，在主库的配置文件中配置成功后，主库就只会记录指定的数据库的二进制信息。

例如：

[mysqld]

binlog_do_db=wordpress #记录wordpress数据库的二进制信息

binlog_do_db=world #记录world数据库的二进制信息

TIP：mysql> show master status; #使用此语句就可以查看"binlog_do_db"参数和"binlog_ignore_db"参数的配置情况。

从库方面：

Replicate_Do_DB: #基于库的白名单，在SQL线程中，只回放指定库的二进制数据，至于对IO线程从主库中复制多少到从库没有影响
Replicate_Ignore_DB: # 基于库的黑名单
Replicate_Do_Table: #基于表的白名单，在SQL线程中，只回放指定表的二进制数据，至于对IO线程从主库中复制多少到从库没有影响
Replicate_Ignore_Table: #基于表的黑名单
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: #这两个参数主要用于解决跨库更新的问题。

TIP：这里多说以下"Replicate_Wild_Do_Table"和"Replicate_Wild_Ignore_Table"这两个参数。
我们在从库中配置"Replicate_Do_DB"、"Replicate_Ignore_DB"时是会有隐患的，就是跨库更新的问题。

举个栗子：
假设我们在从库中配置：
[mysqld]
Replicate_Do_DB=world     #即基于库的白名单，在SQL线程中，只回放指定库的二进制数据，至于对IO线程从主库中复制多少到从库没有影响

主库中操作：
情况1：
mysql> use mysql;        #进入mysql这个数据库
mysql> update world.city set ...   
#跨库更新world这个数据库中的数据信息，从库中并不会进行相应的数据更新，原因是设置replicate_do_db或replicate_ignore_db后，MySQL执行sql前检查的是当前默认数据库，所以跨库更新语句在Slave上会被忽略。

情况2：
mysql> use world;     #进入到world数据库
mysql> drop table t1;    #删除t1表。此时若是从库中world数据库中没有t1这张表，那么主从复制的SQL线程就会报错：没有此表；同时主从复制也就停止工作了


针对以上的隐患，都可以通过使用"Replicate_Wild_Do_Table"和"Replicate_Wild_Ignore_Table"参数来避免。
例如从库中设置：
[mysqld]
Replicate_Wild_Do_Table=world.%      #就可以避免以上的两种情况。
#"%"的功能有点像like，模糊匹配。

GTID主从复制

使用GTID构建主从复制和普通主从复制的区别：

（1）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录，更方便 Failover

（2）额外的3个功能参数："gtid-mode=on"、"enforce-gtid-consistency=true"、"log-slave-updates=1"

（3）change master to 的时候不再需要 binlog 文件名和 position 号。只需要配置 "master_auto_position=1" 参数，从库就可以自动获取同步主库数据的起点

（4）在主从复制过程中，从库不再依赖 master.info 文件，而是直接读取最后一个 relaylog 文件中的GTID号

（5）构建主从时，由于可能主库已经存在数据，所以需要先备份，再恢复到从库。

若是逻辑备份：主库mysqldump备份时，由于"--set-gtid-purged"参数默认为AUTO/ON，所以在备份时会将事务操作的gtid号写

入备份文件，例如： "SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';"。

在将备份文件恢复到从库后，如果从库已经配置了"master_auto_position=1"参数，那么从库就会意识到自己已经拥有主库GTID号1-11的数据。所以开启主从复制后，从库就会直接从GTID号为12开始向主库请求新的数据。

tip：构建主从时，使用mysqldump进行逻辑备份时不要使用"--set-gtid-purged=off"，把此参数设置为off，那么“SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11'”这一行信息就会消失，那么从库就无法得知主库的gtid号，以后就会接着从1号的gtid号开始同步数据，从而操作构建主从的失败。

若是物理备份：使用xtrabackup进行物理备份时，备份文件中时没有

"SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11';" 这一行的信息的。

此时若还是配置 "master_auto_position=1"，从库就只会从gtid号为1的位置开始请求 binlog，构建主从也会失败。

对于这种用情况有两种方式解决：

1、物理备份的xtrabackup_binlog_info文件中也会记录主库的gtid信息，将主库gtid号加1，然后赋值给"master_auto_position"参数，这样就是告诉从库，接着从此gtid号开始请求binlog就可以了。

2、在xtrabackup_binlog_info文件中获取gtid号后，手工执行SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:gtid';然后设置"master_auto_position=1"。

GTID主从复制搭建

重要参数：

gtid-mode = on ：启用gtid，否则就是普通的主从复制架构

enforce-gtid-consistency = true ：强制gtid的一致性。开启gtid时，必须使用此参数

log-slave-updates = 1 ：强制刷新从库的 binlog

TIP：这里来详细说一下 "log-slave-updates" 参数。

看到 "log-slave-updates" 此参数的功能后，可能有些朋友就会懵掉了，就会想从库中的 binlog 功能不是由 "log-bin"参数控制的嘛？

确实，我们在MySQL数据库实例中配置文件中配置以下信息，就会开启二进制日志的功能，并将二进制日志写入指定的文件：

[mysqld]

log-bin=/data/binlog/mysql-bin

但是，在主从复制中，从库若是只配置了 "log-bin" 参数，不添加 "log-slave-updates" 参数，那么从库从主库中复制的数据就不会写入 "log-bin" 参数指定的日志文件中。只有在直接在从库中操作数据时，产生的数据才会写入"log-bin" 参数指定的日志文件中。

当开启"log-slave-updates" 参数后，从库从主库复制的数据会写入log-bin日志文件里。这也是该参数的功能。

搭建过程

1、准备三个MySQL数据库实例：db01、db02、db03

db01主库实例的配置文件：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql/
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=51
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db01 [\\d]>     “[\\d]”：用于在mysql中显示本数据库的名称，方便区别自己正在操作的数据库
EOF

db02从库实例的配置文件：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=52
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db02 [\\d]>
EOF

db03从库实例的配置文件：
cat > /etc/my.cnf <<EOF
[mysqld]
basedir=/application/mysql
datadir=/data/mysql/data
socket=/tmp/mysql.sock
server_id=53
port=3306
secure-file-priv=/tmp
autocommit=0
log_bin=/data/binlog/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[mysql]
prompt=db03 [\\d]>
EOF

2、构建主从

架构：
master:51
slave:52,53

从库中执行 "change master to" 来告诉从库主库的相关信息
例如：
mysql> change master to 
   master_host='10.0.0.51',
   master_user='repl',
   master_password='123' ,
   MASTER_AUTO_POSITION=1;
mysql> start slave;

3、其余步骤和普通主从复制一样，可参考此篇博文：传送门

路～～～

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL高级主从复制

延时从库延时从库即从库延迟一定的时间再对主库中新产生的数据进行同步。常规的主从复制，由于主库发生数据变动时，从库会很快对主库新产生的数据进行同步。例如：主库误删除一张表，从库为了同步数据，也会在从库中执行相应的操作。所以常规的主从复制只能很好的避免数据的物理损坏，但是无法避免数据库中的数据逻辑损坏主从复制的主要作用就是备份数据、恢复数据，所以就需要延时从库来尽可能地避免数据库中数据地逻辑损坏。延时从库的配置延时从库主要是通过 SQL线程延时回放 relaylog 来实现的。主.
复制链接

扫一扫