使用 Xtrabackup 在线对MySQL做主从复制

2019独角兽企业重金招聘Python工程师标准>>>

使用 Xtrabackup 在线对MySQL做主从复制

xtrabackup

mysqldump对于导出10G以下的数据库或几个表，还是适用的，而且更快捷。一旦数据量达到100-500G，无论是对原库的压力还是导出的性能，mysqldump就力不从心了。Percona-Xtrabackup备份工具，是实现MySQL在线热备工作的不二选择，可进行全量、增量、单表备份和还原。（但当数据量更大时，可能需要考虑分库分表，或使用 LVM 快照来加快备份速度了）

2.2版本 xtrabackup 能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份，innobackupex通过perl封装了一层xtrabackup，对MyISAM的备份通过加表读锁的方式实现。2.3版本 xtrabackup 命令直接支持MyISAM引擎。

XtraBackup优势 ：

1. 无需停止数据库进行InnoDB热备
2. 增量备份MySQL
3. 流压缩到传输到其它服务器
4. 能比较容易地创建主从同步
5. 备份MySQL时不会增大服务器负载

replication

为什么要做主从复制？
我想这是要在实施以前要想清楚的问题。是为了实现读写分离，减轻主库负载或数据分析？ 为了数据安全，做备份恢复？主从切换做高可用？
大部分场景下，以上三个问号一主一从都能够解决，而且任何生产环境都建议你至少要有一个从库，假如你的读操作压力特别大，甚至要做一主多从，还可以不同的slave扮演不同的角色，例如使用不同的索引，或者不同的存储引擎，或使用一个小内存server做slave只用于备份。（当然slave太多也会对master的负载和网络带宽造成压力，此时可以考虑级联复制，即 A->B->C ）

还有需要考虑的是，一主一从，一旦做了主从切换，不通过其它HA手段干预的话，业务访问的还是原IP，而且原主库很容易就作废了。于是 主-主 复制就产生了，凭借各自不同的 server-id ，可以避免 “A的变化同步到B，B应用变化又同步到A” 这样循环复制的问题。但建议是，主主复制，其中一个主库强制设置为只读，主从切换后架构依然是可用的。

复制过程是slave主动向master拉取，而不是master去推的，所以理想情况下做搭建主从时不需要master做出任何改变甚至停服，slave失败也不影响主库。

复制类型

mysql系统库mysql库里面表的日志记录格式需要说明：在通过如INSERT、UPDATE、DELETE、TRUNCATE等方式直接修改数据的语句，使用binlog_format指定的方式记录，但使用GRANT、ALTER、CREATE、RENAME等改动的mysql库里数据的，会强制使用statement-based方式记录binlog。

可以在线修改二进制日志类型，如 SET SESSION binlog_format=MIXED; ，需要SUPER权限。

复制类型还可以分为 异步复制和半同步复制。
通常没说明指的都是异步，即主库执行完Commit后，在主库写入Binlog日志后即可成功返回客户端，无需等等Binlog日志传送给从库，一旦主库宕机，有可能会丢失日志。而半同步复制，是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后，才返回Commit操作成功给客户端；如此半同步就保证了事务成功提交后至少有两份日志记录，一份在主库Binlog上，另一份在从库的Relay Log上，从而进一步保证数据完整性；半同步复制很大程度取决于主从网络RTT（往返时延），以插件 semisync_master/semisync_slave 形式存在。

• 基于语句的复制：STATEMENT，在主服务器上执行的SQL语句，在从服务器上执行同样的语句，有可能会由于SQL执行上下文环境不同而是数据不一致，例如调用NOW()函数。MySQL在5.7.7以前默认采用基于语句的复制，在 5.7.7 及以后版本默认改用 row-based。
• 基于行的复制：ROW，把改变的内容复制过去，而不是把命令在从服务器上执行一遍。从mysql5.0开始支持，能够严格保证数据完全一致，但此时用mysqlbinlog去分析日志就没啥意义。因为任何一条update语句，都会把涉及到的行数据全部set值，所以binlog文件会比较大。
  （遇到的一个坑是，迁移时，从库改正了字段默认值定义，但数据在主库更改后，即使产生的新数据默认值是正确的，但基于行的复制依然用不正确的值字段全部更新了）
• 混合类型的复制:MIXED，默认采用基于语句的复制，一旦发现基于语句的无法精确的复制时，就会采用基于行的复制。

原理

1. (1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
   (2) slave将master的binary log events拷贝到它的中继日志(relay log)；
   (3) slave重做中继日志中的事件，将改变反映它自己的数据。

• 该过程的第一部分就是master记录二进制日志。在每个事务更新数据完成之前，master在二进制日志记录这些改变。MySQL将事务串行的写入二进制日志，即使事务中的语句都是交叉执行的。在事件写入二进制日志完成后，master通知存储引擎提交事务。
• 下一步将master的binary log拷贝到它自己的中继日志。首先，slave开始一个工作线程——I/O线程。I/O线程在master上打开一个普通的连接，请求从指定日志文件的指定位置之后的日志内容，然后开始binlog dump process。Binlog dump process从master的二进制日志中读取事件，如果已经跟上master，它会睡眠并等待master产生新的事件。I/O线程将这些事件写入中继日志。

• SQL slave thread（SQL从线程）处理该过程的最后一步。SQL线程从中继日志读取事件，并重放其中的事件而更新slave的数据，使其与master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

  此外，在master中也有一个工作线程：和其它MySQL的连接一样，slave在master中打开一个连接也会使得master开始一个线程。复制过程有一个很重要的限制——复制在slave上是串行化的，也就是说master上的并行更新操作不能在slave上并行操作。

补充

• mysql 5.7开始加入了多源复制，这个特性对同时有很多个mysql实例是很有用的，阿里云RDS（迁移）实现了类似的方式。
• 从MySQL 5.6.2开始，mysql binlog支持checksum校验，并且5.6.6默认启用（CRC32），这对自己模拟实现mysql复制的场景有影响。

下面开始配置主从：

　　主从版本一致—>主库授权复制帐号—>确保开启binlog及主从server_id唯一—>xtrabackup恢复到从库—>记录xtrabackup_binlog_info中binlog名称及偏移量—>从库change master to —>slave start—>检查两个yes

查看mysql主从复制延迟和数据中断

查看mySQL延迟的方法，查看了多个案例，大家众说纷纭，意见差不多一致。如下做的一些测试，希望能检测到mysql复制延迟、数据中断。

方法一、查看Seconds_Behind_Master

该参数有如下值：NULL表示io_thread或sql_thread有一个发生故障，就是说该线程的Running状态时No，而非Yes，N0表示主从复制良好，没有lag存在正值  表示主从已出现延时，数字越大表示从库落后主库越多负值很罕见，是一个BUG，按理说不应该出现。

该方法是使用命令show slave status,通过比较SQL THREAD接受events时间的时间戳与IO THREAD执行事件events时间戳的差值--秒数，来确定slave落后于master多少，如主从时间不同，改时间的计算不受影响。

众所周知备库relay-log和主库的bin-log里的内容一样，真正和主库有关两的是io_thread，当主库I/O负载很大或网络阻塞时，io_thread不能及时复制binlog，而sql_thread一直能跟上io_thread的脚步，这时seconds_behind_master的值是0，实际上却不是，这时用该值作为延迟参考则不准。change master to master_host='192.168.2.7',master_user='tongbu',master_password='123456',master_log_file='mysql-bin.000008',master_log_pos=291263843;

方法二、使用pt-heartbeat工具

该工具可以计算出MySQL复制或者是PostgreSQL,它可以更新master或者监控复制。它还可以从f 读取配置。它借助timestmp的比较实现的，首先需要保证主从服务器时间必须要保持一致，通过与相同的一个NTP server同步时钟。它需要在主库上创建一个heartbeat的表，里面的时间戳ts就是当前的时间戳 now()，该结构也会被复制到从库上。表建好以后，会在主库上以后台进程的模式去执行一行更新操作的命令，定期去向表中的插入数据，这 个周期默认为1 秒，同时从库也会在后台执行一个监控命令，与主库保持一致的周期+0.5S（默认0.5S延迟检查）去比较，复制过来记录的ts值与主库上的同一条ts值，差值为0表示无延时，差值越大表示 延时的秒数越多。

主从同步和主从延时

背景

用户画像功能上线后，线上 MySQL 监控显示

主从延迟现象严重：平均 6s 左右，需要剖析一下原因，找到改进的措施。

当前 blog 要解决的问题：

• 监控：MySQL 的主从延迟
• 影响：
  • MySQL 主从延迟的影响
  • 多少的延迟，可以接受
• 原因：MySQL 主从延迟的产生原因

MySQL 主从复制

原点之问：MySQL 主从集群的作用，要解决什么问题

场景：

• 高并发情况况下，单台 MySQL 数据库承载的连接数多、读写压力大，MySQL系统瓶颈凸显
• 大部分互联网场景，数据模型[一写多读]
  • 读次数（read_num） 一般是写次数（write_num）的 10 倍以上
  • 补充：数据分析、商业智能等场景，read_num和 write_num基本相当，同一量级

MySQL 集群方式，能够分散单个节点的访问压力。

MySQL 集群，常见方式：主从集群

• Master 节点，负责所有的「写请求」
• Slave 节点，负责大部分的「读请求」

MySQL 主从集群的作用：

MySQL 主从集群，分散访问压力，提升整个系统的可用性，降低大访问量引发的故障率。

常见的主从架构：

• 一主一从：一个 Master，一个 Slave
• 一主多从：一个 Master，多个 Slave

具体，参考下图：

实现细节

MySQL 在主从同步时，其底层实现细节又是什么？为此后分析主从延迟原因以及优化方案，做好理论准备。

总结来说，MySQL 的主从复制：异步单线程。

• Master上1 个IO线程，负责向Slave传输binary log （bin_log ）
• Slave上 2 个线程：IO线程和执行SQL的线程，其中：
  • IO线程：将获取的日志信息，追加到relay log上；
  • 执行SQL的线程：检测到relay log中内容有更新，则在Slave上执行sql；

特别说明：MySQL 5.6.3 开始支持「 多线程的主从复制 」，一个数据库一个线程，多个数据库可多个线程。

完整的 Master & Slave 之间主从复制过程：

主从延时时间：Master 执行成功，到 Slave 执行成功，时间差。

上述过程：

• 主从延迟：「步骤2」开始，到「步骤7」执行结束。
• 步骤 2：存储引擎处理，时间极短
• 步骤 3：文件更新通知，磁盘读取延迟
• 步骤 4：Bin Log 文件更新的传输延迟，单线程
• 步骤 5：磁盘写入延迟
• 步骤 6：文件更新通知，磁盘读取延迟
• 步骤 7：SQL 执行时长

通过上面分析，MySQL 主从复制是典型的生产者-消费者模型：整体耗时，分为几类

• 磁盘的读写耗时：步骤 3、步骤 5、步骤6
• 网络传输耗时：步骤 4
• SQL 执行耗时：步骤 7 (地点：Slave 上 relay log 执行过程)
• 排队耗时：步骤 3（地点：Master 上 bin log 中排队，生产者-消费者）

客观认识：主从架构

Master 数据写入后， Slave 一定要及时写入数据，这个本质是：主从架构下的强一致性。

Master 与 Slave 之间的延迟，是客观存在的。

一般对主从架构的定位：

• 提升系统的可用性：Master 宕机后，数据不丢失，可以使用 Slave 临时替换 Master
• 不要求 Slave 跟 Master 的强一致，而只要求最终一致
• 通常，对数据一致性要求很高的场景下，并不建议采用：主从结构，分担高并访问压力 。

 同步复制

如果要满足主从架构的强一致性，采取「同步复制」的 2PC 策略即可：

• 第一阶段：Master 收到 Client 的写入数据请求，在本地写入数据；
• 第二阶段：Master 收到 Slave 写入成功的消息，再向 Client返回数据写入成功；

主流数据库均支持这种完全的同步模式，MySQL的Semi-sync功能（从MySQL 5.6开始官方支持），就是基于这种原理。

「 同步复制」对数据库的写性能影响很大，适用场景：

银行等严格要求强一致性的应用，对于写入延迟一般没什么要求（延迟几个小时都可以接受，数据不出错就行）。

异步复制

异步复制：Master 数据写入成功后，Slave 上异步进行数据写入，只要保证数据最终一致性即可。

主从延迟

如何监控

监控主从延迟的方法有多种：

1. Slave 使用本机当前时间，跟 Master 上 binlog 的时间戳比较
2. pt-heartbeat、mt-heartbeat

本质：同一条 SQL，Master上执行结束的时间 vs.Slave上执行结束的时间。

主从延迟的影响

Slave 延迟的影响：

• 异常情况下，HA无法切换： HA 软件需要检查数据的一致性，延迟时，主备不一致。
• 备库 Hang 会引发备份失败：flush tables with read lock 会 900s 超时（什么含义？）
• 以 Slave 为基准进行的备份，数据不是最新的，而是延迟的。

简单来说，恶化的主从延迟，将丧失 MySQL 集群带来的优势：

• 读写分离失效：读写分离，降低单机压力，提升系统瓶颈上限，如果延迟恶化，则失效。
• 主备容灾失效：主备切换，提升系统可用性，如果延迟恶化（1h以上），则失效。

产生原因

常见的主从延迟原因：

• Master 上，大事务，耗时长：优化业务，拆分为小事务
  • Master 上，SQL 执行速度慢：优化索引，提升索引区分度（事务内部有查询操作）
  • Master 上，批量 DML 操作：建议延迟至业务低峰期操作
• Master 上，多线程写入频繁 ， Slave 单线程速度跟不上：提升 Slave 硬件性能、借助中间件，改善主从复制的单线程模式

如何解决

整体上 2 个策略，齐头并进：

• 内部解决：减弱主从复制的延迟
• 外部解决：缓存层，在前端访问和数据库之间，添加缓存，优先从缓存读取，减弱数据库的并发压力，Slave 只作为数据备份，不分担访问流量；

减弱主从延迟，采取措施：

• 细化事务：将大事务拆为小事务，不必要的地方移除事务
  • 提升SQL执行速度：优化索引
  • 减少批量操作：批量DML的耗时较多，减少不必要的批量DML
• 降低多线程大事务并发的概率：优化业务逻辑

转载于:https://my.oschina.net/u/3708406/blog/1612739