mysql 学习记录05-复制特性

概述

MySQL的复制特性很灵活,可以实现整个服务(all database)级别,单个数据库级别,数据库中某些表对象的复制,即能从A复制到B(主从单项复制),B再复制到C，也可以实现A直接复制到B和C(单主多从复制),甚至A的数据库复制给B,B的数据也复制会A(双主复制)。

一般来说MySQL的复制特性一版用于以下场景

  • 提高性能。通过一主多从(甚至多主多从)的部署方案,将涉及数据写的操作放在Master端操作,而将数据读的操作分散到众多的slave端。这样一方面能够降低Master负载,提高数据写入请求的响应效率;另一方面众多的Slave节点同时提供读操作,有一个负载均衡的效果
  • 数据安全。由于数据被复制到Slave节点,即使Master节点宕机,Slave节点还保存着一份数据,这相当于实现了数据的冗余;在日常工作中可以将备份任务放在Slave端执行,以避免执行备份操作时对Master产生影响
  • 数据分析。将数据分析和挖掘等较占资源的操作放在Slave节点执行,这样就可以降低对Master节点可能造成的性能影响
  • 数据分布。基于MySQL复制特性的实现原理,Master和Slave并不需要实时链接,因此可以将Slave和Master放在不同的物理位置,而基本不用担心网络中断等因素对同步造成影响

MySQ复制的过程默认是异步的,也就是说Master端的修改不会马上同步到Slave端,另一方面,Slave不需要时刻都链接Master,只有需要同步数据时,才连接到Master。只要Master节点依然保留了自上次同步后所生成的二进制日志,就可以继续同步。

对于某些场景,这种方式就不一定适用了。如果说对于读写并发和实时性要求都很高的场景,用户可能希望插入的数据库,能够立刻就被查询出来,如果查询是从Slave端获取数据,那么异步的数据同步方案就满足不了需求。针对这种情况,MySQL复制在处理数据时,有两种不同的模式:

  • 基于语句复制([S]tatement [B]ased [R]eplication) : 基于实际执行的SQL语句的模式方案,简称SBR
  • 基于记录复制([R]wo [B]ased [R]eplication) : 基于修改的列的复制模式,简称RBR
  • 基于上述两种方式的混合的复制模式(Mix Based Replication, MBR)

数据复制模式跟日志文件记录格式强相关

创建复制环境

1. 禁止主库写入

两种方式,如下

  1. 可以关闭MySQL服务
/etc/init.d/mysqld stop
  1. 锁定表
flush tables with read lock;

2. 复制数据文件

随便找方式复制即可

3. 设置Master Server ID并启动Master(如果已经设置过可以跳过这一步)

  1. 修改Master配置文件
[mysqld]
# 设置server id
server_id = 888888
# 设置binlog存放路径
log-bin = mysql-bin

4. 获得Master当前的binlog文件和Position(用户后面设置Slave)

# 查看Master信息用于之后配置Slave
show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

5. 恢复Master的正常服务

如果Master服务停止了,要启动Master

/etc/init.d/mysqld start

如果Master是锁定的,需要释放锁

unlock tables;

6. 创建复制专用用户

在MySQL中,Slave如果想要获取二进制日志,需要主动链接Master节点,创建一个用户专门用于Slave从Master同步数据,更安全和方便管理。

# 创建一个用户运行从192.168.23网段链接服务器,拥有读取二进制日志的权限(如果已经设置过可以跳过这一步)
grant replication slave on *.* to repl@'192.168.23.%' identified by 'Zj123!@#';

7. 配置Slave

  1. 修改Slave配置文件
[mysqld]
# 设置server id
server_id = 666666
# 设置binlog存放路径,Slave可以不配置这一项
# log-bin = mysql-bin
  1. 删除auto.cnf文件
    这个文件里保存了server-uuid的参数,他拥有唯一标识MySQL服务,如果Slave端是直接复制的Master端的文件,需要把这个文件删除掉,避免和Master相同,MySQL在启动时会自动生成一个
/etc/init.d/mysqld start

8. 启动Slave

/etc/init.d/mysqld start

9. 在Slave上配置Master的连接信息

# 配置连接信息,在第四步中用`show master status`
change master to master_host = '192.168.23.8', master_port = 3306, master_user = 'repl', master_password = 'Zj123!@#', master_log_file = 'mysql-bin.000002', master_log_pos = 154;
# 启动slave
start slave

10. 测试复制是否配置成功

  1. 做变更数据的操作看数据是否能同步
  2. 如果没有同步,用show slave status查看错误信息

复制环境常用管理命令

1. 检查Slave节点的状态

show slave status

参数

下面罗列一些比较重要的参数

  • Slave_IO_State : 显示Slave的当前状态
  • Slave_IO_Running : 显示I/O线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Slave_IO_Running : 显示SQL线程是否在运行,正常情况下应该在运行,除非DBA手动将其停止,或者出现错误
  • Last_IO_Erroe/Last_SQL_Error : 正常情况下应该是控制,如果遇到了错误,那么这里会输出错误信息,根据错误信息可以找到相应的错误
  • Seconds_Behind_Master : 显示房钱Slave节点与Matser节点的同步延迟。注意,这个参数有一定的迷惑性,表现在两方面。第一,看起来像是时间,而且确实有时间因素,但其实不是时间。其实是Slave节点接收到的Master的日志文件和已经应用的日志文件之间的差距。比如I/O线程接收到的日志文件写入位置为34560,而SQL线程才应用到34000,这两个位置之间时间上的差距是一小时,那么Seconds_Behind_Master就会显示3600秒。而有可能此时Master已经写到34660的位置了,只是还没有被I/O线程读取到Slave几点。这个参数显示的值不是完全精确的主从之间的延迟时间,而只是Slave节点本地日志接收和应用上的差异。
  • Master_Log_File/Read_Master_Log_Pos : 显示当前读取的Matser节点二进制日志文件和文件位置。
  • Relay_Master_Log_File/Exec_Master_Log_Pos : 显示当前Slave节点正在应用的日志文件位置。
  • Relay_Log_File/Relay_Log_Pos : 显示当前Slave节点正在处理的中继日志文件和位置

show slave status是最为直接简单全面的查询Slave节点状态的方法,此外show processlist也能在一定程度上起到复制了解复制状态的作用

2. 启动停止slave线程

出现意外情况,可以手动控制Slave界定啊的启动和停止,主要通过stop slave和start slave实现
Slave服务由两个线程组成

  • IO_THREAD : 负责读取Master端的二进制日志,并写入到本地的中继日志(relay-log)中
  • SQL_THREAD : 负责从本地中继日志中读取时间并执行

这是两个独立的线程,他们的启动和停止,也可以分开进行,相互之间没有依赖性。
灵活的利用这个两个线程的启停,可以使复制的应用场景更加灵活。如果在备份时,为了保持备份数据的一致性,很多人往往选择停止整个Slave服务,单仔细一想,IO_THREAD并不需要停止,特可以继续从Master读取二进制日志保存在在本地的中继日志中,这样还可以起到对Master节点数据冗余保护的作用。我们只需要停止SQL_THREAD,记录下当前应用到的日志文件名和位置,就可以开始备份任务。因为此时Slave不会有数据更新,相当于处于只读状态,这样创建出来的就是一致性的备份了。当然数据再备份期间与Master是不同步的(如果这期间Master有数据写入的话)。

怎么单独启停IO_THREAD/SQL_THREAD呢?只需要在语句后面机上要操作的线程名就行了,如下

start slave sql_thread;
stop slave sql_thread

复制特性的实施原理和关键因素

复制特性的核心就是基于Master节点对数据库中各项变更的处理机制,MySQL数据库启用二进制日志后,该文件中就会记录MySQL服务自启动以来,所有对数据库结构或内容(数据)的变更事件(即增删改操作),当然select语句由于不会触发结构上变更所以select行为不会被记录。

Slave节点只要连接到Master节点,请求这些二进制日志。拿到二进制日志后,Slave节点就能解析这些二进制日志,并在本地执行,这样就相当于将修改操作在Slave节点重演。日志应用完之后,Slave就和Master一样了

复制特性就是基于二进制日志

复制格式

二进制日志在记录事件时,支持多种格式,由binlog_format参数控制

  • 基于语句记录(Statement-Based Logging, SBL),对应的参数值为statement
  • 基于行格式记录(Row-Based Logging,RBL),对应的参数为row
  • 混合模式记录(Mixed-Based Logging,MBL),对应的参数为mixed

复制特性中,也有一个被称为复制格式的(Replication Formats)的概念,复制格式和二进制日志格式其实是同一个东西,不过是有两个名字,也就是不同场景下的称谓是不同的。

考虑到复制格式和二进制日志格式之间的关系,其格式的种类和逻辑上,目前也分为3中

  • 基于语句复制(Statement-Based Replication,SBR)。在MySQL中的复制功能,最初就是靠传播SQL语句到目标端执行的,这就是所谓的基于语句复制,也就是说二进制日志中保存的就是SQL语句。
  • 基于行复制(Row-Based Replication,RBR)。后来(5.1.4),MySQL又引入了全新的基于行粒度的日志格式,这种模式下,二进制日志文件写入事件时,记录的是变更的记录行的信息
  • 混合记录模式(Mixed-Based Replication)。再后来(5.1.8),MySQL又进了一步,在记录事件到二进制日志时,MySQL服务根据需要,动态修改日志格式,就是所谓的混合模式。注意,混合记录是中模式,而不是一种格式。在这种模式下,默认还是选择基于行的格式记录。具体选择的是什么格式,要看当前执行的语句,以及操作对象所使用的存储引擎而定。

在服务运行期间可以对服务进行修改,但是注意最好不要随便改,因为有可能会导致复制环境报错

SBR与RBR的特点及优势

SBR的优点
  • 技术成熟,自3.23版本即开始提供对这种记录格式的支持
  • 生成日志少,特备是对于大量更新及删除的操作
  • 由于能够记录下数据库做过的所有变更操作,日志可用于行为审计
SBR的缺点
  • 存在安全隐患。这个安全并不是说会被攻击,而是说数据安全。Master节点中产生的修改操作(INSERT,DELETE,UPDATE,REPLACE),并不是都能通过基于语句方式完整的复制到Slave节点,对于不去确定的行为在基于语句复制时,很难确保Slave节点会执行并获得正确的数据,这点从逻辑上证明了主从出现不一致的合理性。比如说Master节点和Slave节点分别执行FOUND_ROWS(),SYSDATE(),UUID()这类函数,可能返回不同的结果;如果使用了这些函数,那么语句执行时会抛出下列警告信息(客户端使用show warnings查看)
Statement is not safe to log in statement format
  • 执行INSERT ... SELECT语句时需要更多的行锁(相对RBR而言)
  • UPDATE要扫表(无索引可用的情况下)时需要持有更多的行锁(相对RBR而言)
  • 对于InnoDB引擎,INSERT语句使用AUTO_INCREMENT会阻塞其他INSERT语句
  • 对于复杂的语句,Slave节点执行时语句必须先被评估,而对于基于row格式复制,则Slave节点只需要修改具体的记录即可(不必执行跟Master端相同的SQL语句,这既是有点也是缺点)
  • 如果语句在Slave节点执行时操作失败,基于statement格式复制会增加主从不一致的概率
  • 单条语句中执行的函数中调用NOW()返回日志相同,但是存储过程就不一定了
  • 对象定义必须拥有唯一键,主要是为了避免冲突
RBR的优点
  • 所有修改都能被安全的复制到Slave节点
  • 5.1.14版本开始,mysql数据库不再被复制,RDB方式的复制不能支持mysql库中的表对象。对于像GRANT/REVOKE这类操作,以及trigger/stored procedures/views等对象的维护操作,会被使用SBR方式复制到Slave
  • 对于CREATE TABLE ... SELECT这类DDL+DML的操作,CREATE创建对象部分使用SBR模式复制,其他部分则使用RBR模式复制
  • 与RDBMS实现的技术类似,其他数据库软件管理和维护方面的经验也可以继承使用
  • Master端执行修改操作时,仅需持有极少的锁,因此可以获得更高的并发性能
  • Slave节点执行INSERT/UPDATE/DELETE时也仅需持有少量锁
RBR的缺点
  • RBR可能会生成更多的日志。比如DML语句,基于statement格式记录日志的话,只需记录所执行的SQL语句,相比之下,基于row格式记录日志,会记录所有变化了的行到二进制日志文件,如果语句触发的记录变更特别多,那么生成的二进制日志自然非常多,即使执行的操作随后被回滚。这也意味着创建备份和回复需要更多时间,以及二进制日志会被更长时间加锁以写数据,也可能带来额外的并发性能上的文件。
  • UDFS生成BLOB值需要花费比基于statement格式日志更长的时间,这是因为BLOB值是被记录的,而不是语句生成的
  • 不能通过分析日志来获取曾经执行过的语句。不过还是可以通过mysqlbinlog命令看有哪些数据被修改了
  • 对于非事务引擎,比如MyISAM表对象,Slave节点应用INSERT操作时,使用RBR模式要比使用SBR模式持有更强的锁定,也就是说使用RBR模式在Slave上并没有并行插入的概念

中继日志文件和状态文件

IO_THREAD将接受到二进制日志文件保存在中继日志文件中。中继日志用来保存其他及诶点的事件(也是二进制格式),可以使用如下参数配置

relay-log = relay-bin
relay-log-index = relay-bin-index

Slave节点会在满足下列条件时,触发生成新的中继文件,并更新相关的索引文件

  • 启动Slaves节点I/O线程时
  • 执行日志刷新命令,如FLUSH LOGS或mysqladmin flush-logs等

  • 中继文件达到指定的最大值有如下两种情况

    1. 如果max_relay_log_size参数值大于0,则日志文件超过该值后即会重建。
    2. 如果max_relay_log_size参数值为0,则通过max_binlog_size确定单个Relay日志的最大值

中继日志文件的维护可以交由SQL_THREAD来处理,他会自动删除无用的中继日志文件

除了中继日志文件外,复制环境中Slave节点还会创建两个复制环境的状态文件,即master.info和relay-log.info,这两个文件都默认保存在mysql的data目录下,也可以通过--master-info-file和--relay-log-info-file参数来自定义文件的名称和路径

  • master.info : 用于保存复制环境中连接Master节点的配置信息,比如用户名,端口,IP,密码等,5.6之后也可以选择将这些信息保存到mysql.slave_master_info表对象
  • relay-log-info : 保存处理进度及中继日志文件的位置,与前面的配置类型5.6之前保存在data路径下的relay-log-info文件中,5.6之后可以将这些信息保存到mysql.slave_relay_log_info表对象中

注意不要手动去修改这两个文件,不然可能会出现无法预知的错误。

复制过滤规则

注意,就算设置了过滤规则,Slave也会接受所有的二进制日志,等到应用时再过滤。

MySQL提供了一系列以--replicate-*开头的参数,用于复制环境中Slave节点定义过滤规则

replicate-rewrite-db=from_name->to_name用于将A库的对象保存到B库中

1. 库级过滤规则

--replicate-do-db或--replicate-ignore-db用于控制是否应用指定数据库的变更事件,注意这两个参数执行,并不是过滤指定数据的操作,而是过滤当前默认数据(USE)所做的操作，如下
Slave设置了--replicate-ignore-db = a
在master上执行

use a;
drop table table1;
drop table b.table2;

上面的操作,Salve设置了不引用数据库a的事件,SQL操作中因为默认数据库是a,所以对table2的操作会被过滤掉,因为默认数据库为a(使用use a;设置了默认数据库)

--replicate-do-db某些情况也可以用于忽略其他数据库。例如,当指定了--replicate-do-db=sales,那么所有不在默认数据(use sales)下发生的变更,都不会记录日志

2. 表级复制选项

跟传统粒度越细,优化级别越高的规则不同,MySQL复制特性中的过滤规则,是先检查数据库级别的设置,当数据库级参数无有效匹配时,Slave节点才检查并评估表级过滤参数。

首先,作为一个准备工序,Slave节点要检查是否是基于语句复制(SBR),如果是的话,并且语句是在存储过程中触发,那么Slaves节点执行语句并退出,如果基于RBR的话,Slave节点并不知道Master端执行的语句做了什么,因此这种情况下什么都不会应用。

基于SBR场景,复制事件以语句形式体现(所有的修改时间都可以定位为SQL语句),而基于RBR,所有的事件均以具体修改的表的列的数据体现(即使一条简单的update tbl set col = 1也可能触发大量的row修改事件)

如果没有指定任何表级过滤规则,那么检查到这一步时,Slave节点就直接执行所有修改事件了,这是默认场景时的表现。这是默认场景时的表现。如果指定了--replicate-do-table或--replicate-wild-do-table参数,则只执行参数中指定对象的修改事件;如果指定了--replicate-ignore-table或--replicate-wild-ignore-table参数,则除了参数中指定对象的修改事件不执行外,其他变更事件均要在本地执行。

对于--replicate-*这类参数,如果需要指定多个对象。

不同场景下复制过滤规则的应用

条件结果
没有任何--replicate-*参数Slave执行所有接收到的时间
指定了--replicate-*-db参数,未指定表级参数只执行(或忽略)指定数据库的事件
指定了--replicate-*-table参数,未指定库级参数只执行(或忽略)指定表对象的事件
既有库级参数,也有表级参数Slave节点首先执行(或忽略)指定数据的事件,而后再处理表级过滤过滤选项。需要注意日志记录格式对复制的影响

使用XtraBackup

。。。

半同步复制环境

在半同步机制下,Master节点只要确认有至少一个Slave节点接收到了事务,即可向发起请求的客户端返回操作成功信息,Master节点甚至不需要等待Slave节点也成功执行完这个事务,只要至少有一个Slave节点接收到这个事务,并且将之成功写入到本地中继日志文件,就算成功。应该是因为相比同步机制,工作只完成了一半左右,所以就叫半同步。

相比异步复制,半同步数据在数据完整性方面有显著提升。每个成功提交的事务,都代表这份数据至少存在两个节点上。

1. 配置半同步复制环境

找到插件目录

1. 找到插件目录
mysql> show variables like 'plugin_dir';
+---------------+--------------------------+
| Variable_name | Value                    |
+---------------+--------------------------+
| plugin_dir    | /usr/lib64/mysql/plugin/ |
+---------------+--------------------------+
1 row in set (0.00 sec)
2. 查看路径
[root@jin plugin]# ls | grep sem
semisync_master.so
semisync_slave.so
3. 安装插件
# 在master安装
install plugin rpl_semi_sync_master SONAME 'semisync_master.so';
# 在slave安装
install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';
4. 插件设置

设置master

set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_timeout = 3000;

设置slave

set global rpl_semi_sync_slave_enabled = 1;
配置项说明
  • rpl_semi_sync_master_enabled : 用于控制是否在Master节点启用半同步复制,默认值为1即启用状态
  • rpl_semi_sync_master_timeout : 用于指定Master节点等待Slave响应的时间,单位是毫秒,默认是10000即10秒钟,这里设置为3秒。若超出指定时间Slave节点仍无响应,那么当前复制环境就临时被转换为异步复制。
  • rpl_semi_sync_slave_enabled : 跟第一个参数看起来很像,唯一的区别,他是用来控制Slave节点是否启用半同步复制
  • 前面配置的3个变量尽管可以动态修改,但强烈建议将所有配置的变量,都保存在初始化参数文件中,否则需要在每次启动MySQL服务时再手动进行配置
  • rpl_semi_sync_*开头的变量还有几个,这里只配置了最重要的3项,更多参数可以参考官方文档中的内容。
5. 重启Slave节点的IO_THREAD线程

这一步时为了让Slave节点重新连接Master节点,注册成半同步Slave身份,如果不重启IO_THREAD,那么Slave就会一直保持异步复制模式。

stop SLAVE IO_THREAD;
start SLAVE IO_THREAD;

监控半同步环境

可以使用show status查看状态变量,安装半同步插件后,MySQL中就会多出若干个以Rpl_semi_sync_*开头的状态变量,其中在Slave节点,与半同步复制相关的状态变量只有一项:Rpl_semi_sync_slave_status : 标识当前Slave是否启用了半同步模式。
Master节点中与半同步复制相关的状态变量要多一些,其中最值得关注的有下列几项:

  • Rol_semi_sync_master_clients : 显示当前处理半同步模式的Slave节点数量
  • Rol_semi_sync_master_status : 标识当前Master节点是否启用了半同步模式
  • Rol_semi_sync_master_no_tx : 当前未成功发送到Slave的事务数量
  • Rol_semi_sync_master_yes_tx : 当前成功发送到Slave的事务数量

语句如下

show variables like 'rpl_semi_sync%'

复制环境中的故障切换

...

延迟复制

MySQL数据库的复制环境,可以通过配置,实现Slave节点的延迟复制,就是说,将Slave节点与Master节点保持指定时间的间隔。这个实现起来比较简单,只需要指定Slave节点中的MASTER_DELAY,通过CHANGE MASTER TO语句即可进行设定,语法如下:

CHANGE MASTER TO MASTER_DELAY = n;

单位为秒

设置完之后,执行START SLAVE就可以使之生效,而无需重启MySQL。这样设置之后,Slave节点接收到Master的节点生成的日志之后,不会马上应用,而是等待,知道时间符合设定的延迟条件后才开始使用。

延迟复制一版用于以下场景

  • 若Master节点出现误改,误删等操作,造成数据都是的情况,由于Slaves节点有延迟因素的存在,那么DBA可以通过Slave节点仍然保存的数据,快速地将数据恢复回去。不过延迟时间不会太长如果发现误删操作,而且Slave节点恰好尚未应用这些事件,那就必须争分夺秒恢复才行
  • 测试复制环境出现延迟时,对系统可能造成的影响。
  • 无需通过恢复M就可以查看之前版本的数据库,某些场景下,这也能简化DBA的工作

可以用SHOW SLAVE STATUS查看Slaves节点复制信息时,有3个列值与此有关

  • SQL_Delay : 显示当前设定的延迟时间,以秒为单位。
  • SQL_Remaining_Delay : 当Slave_SQL_Running_State列的状态是"Waiting until MASTER_DELAY seconds after master executed event"时,本列显示的值就是距离延迟阈值的时间,换个说法就是还有多长时间才能开始应用,否则的话本列值应该是NULL
  • SQL_SQL_Running_State : 显示当前SQL_THREAD的状态

当SQL_THREAD处于延迟等待阶段,SHOW PROCESSLIST显示该进程的状态时,将会显示为"Waiting until MASTER_DELAY seconds after master exexuted event"。这些信息都说明,当前环境配置了延迟复制

标签: mysql

添加新评论