MySQL

理解备份恢复

MySQL使用环境中,基本都会搭建高可用架构最基本的主从,当主库发生故障导致无法使用的时,可以切换从节点提供服务。
那如果:

  • 删除操作:DROP TABLE操作 ,在Row模式下,可以通过binlog进行恢复,那再如果DROP DATABASE,那就无法恢复了。
  • 在一次迁移升级过程中,bug导致数据库无法启动
  • 需要找回前两天的数据
  • 云平台全面瘫痪,虽然出现概率很小

这时可以通过之前备份+binglog进行恢复数据。
备份的目的是发生灾难时进行恢复。
这里提供几个建议:
为了保证有效备份,需要考虑备份的扩展性以及用于备份有效性验证的服务器,还需要配合多种备份机制

  • 建设统一的备份服务器,备份服务器仅从本地机房实例进行数据备份
  • 备份异常时,需要有相应的处理机制保障下一次备份能够正常进行
  • 逻辑 物理备份 镜像结合进行备份

对于恢复,没有恢复的备份是无意义的,
所以需要

  • 建设统一的恢复验证服务器,用于定期验证备份有效性
  • 通过定期恢复演练,确保备份的有效性
  • 由于不可能所有备份都通过实际还原的方式进行校验,使用文件MD5对比方式进行每日基础校验

    备份恢复工具

    MySQL方面各种备份手段:
    备份恢复实现方式包含 物理 逻辑 商业软件 虚拟机的整体备份。
    MySQL备份&恢复.png
    常用的备份工具有三个:
    逻辑导出:mysqldump,msyqlpump,mydumper
    物理导出:xtrabackup。
    1.mysqldump - MySQL 自带的逻辑备份工具。
    备份原理是通过协议连接到 MySQL 数据库,将需要备份的数据查询出来,将查询出的数据转换成对应的SQL语句,当需要还原这些数据时,只要执行这些SQL语句,即可将对应的数据还原。
    2.xtrabackup是一款mysql开源备份(物理备份)工具,是由percona公司开发的
    3.mydumper是一个针对MySQL和Drizzle的高性能多线程备份和恢复工具,能多线程进行备份
    1.数据量少于10G以内使用mydumper,mysqldump 进行备份,其他备份建议xtrabackup
    2.除了以上场景单表备份,表结构等导出的时候,建议使用逻辑导出。
    3.mysqldump是单线程 mydumper是多线程,性能来说mydumper更优。
    性能方面:mysqlpump>mydumper>mysqldump 但mysqlpump存在一些bug
    4.除此之外MySQL8.0的clone功能确实非常不错的功能,需要脚本配合,应该比xtrabackup优秀,就是业界使用经验太少。

    备份策略

    对于不同的数据,可以采取不同的备份策略,结合全备和增量备份的方式,binlog也需要定期进行备份
    以下是常用备份策略:MySQL数据备份与恢复 - 图2
    MySQL数据备份与恢复 - 图3

    备份方面最佳实践

  • 使用xtrabackup进行物理备份

  • 使用mydumper进行逻辑备份(支持并行逻辑备份恢复)
  • 备份文件存储本地 或则 介质为NFS
  • 使用binlog2sql进行闪回恢复.
  • 对于重要系统,可以使用延迟从库进行备份恢复

image.png

  • 条件允许,系统级别每天额外每天进行镜像备份,之后再做去重处理。

    备份恢复注意

    常见的备份失败问题:
    image.png

    生产案例

    1. xtrabackup备份和恢复的GTID不一致:5.7和8.0都会存在

  • 备份恢复的实例显示已执行过的 GTID xtrabackup_binlog_info 文件记录的信息是 1-9969,
    而备份文件显示的是 1-473

  • 将该备份恢复至一个新实例并启动该实例,执行 show master status; 查看信息:MySQL数据备份与恢复 - 图6
  • 分析
    xtrabackup_binlog_info 文件中信息是有两种情况获取:全局系统变量 gtid_exected 或则 mysql.gtid_executed

解决方案:备份前 FLUSH LOGS;之后通过show master 和 select * from mysql.gtid_executed; 确认gtid 是否一致。
也可以忽略掉, 通过GTID_PURGED方式处理
也可以忽略掉, 通过GTID_PURGED方式处理

2. sql导致失败的案例

  • 堵塞语句kill(从开始执行FLUSH TABLES WITH READ LOCK):kill-long-query-type ,kill-long-queries-timeout
  • 长查询的语句:ftwrl-wait-query-type ,ftwrl-wait-timeout ,ftwrl-wait-threshold
  • no-lock:表示关闭FTWRL的表锁

    3. DDL语句导致失败案例

    1. InnoDB: Last flushed lsn: 3375345258517 load_index lsn 3379255303757
    2. InnoDB: An optimized (without redo logging) DDLoperation has been performed. All modified pages may not have been flushed to the disk yet.
    3. PXB will not be able take a consistent backup. Retry the backup operation

    xtrabackup在备份innoDB数据是,有2种线程:
    redo拷贝线程和ibd数据拷贝线程。
    xtrabackup进程开始执行后,会启动一个redo拷贝的线程,用于从最新的checkpoint点开始顺序拷贝redo.log;再启动ibd数据拷贝线程,进行拷贝ibd数据。
    但导致刷新redo 丢失的情况下,那备份就会失败
    刷新大量数据,或则 redo刷新跟不上
    导致刷新redo丢失的情况下,那备份就会失败

    4. 大量事务刷新日志&IO性能差

    1. xtrabackup: error: log block numbers mismatch:
    2. xtrabackup: error: expected log block no. 201901064, but got no. 208192508 from the log file.
    3. xtrabackup: error: it looks like InnoDB log has wrapped around before xtrabackup could process all records due to either log copying being too slow, or log files being too small.
    4. xtrabackup: Error: xtrabackup_copy_logfile() failed.

    Innodb产生日志的速度远超于Xtrabackup复制的速度,部分Innodb日志被截断,
    导致备份失败。

    5. 空间不足

    1. innobackupex: Error writing file '/tmp/xbtempevLQbf' (Errcode: 28 - No space left on device)
    2. xtrabackup: Error: write to logfile failed
    3. xtrabackup: Error: xtrabackup_copy_logfile() failed.

    所以备份的时候需要确认空间,mysql的tmp空间

    6. 备份软件,处理机制不太友好

    备份日志里已经报出错误,但xtrabackup线程一直存在。

    1. failed to execute query SET SESSION lock_wait_timeout=31536000,MySQL server has gone away.

    MySQL报gone away错误的常见因素
    1、MySQL连接超时(受参数wait_timeout和interactive_timeout控制)
    2、MySQL连接被KILL
    3、MySQL实例重启

    7. nfs挂载注意

    从数据库服务器备份到NFS挂载,然后使用另一台同样挂载该卷的服务器来准备备份(应用日志),那么其他服务器对数据的视图可能不一致。这是因为数据可能还没有被刷新到NFS服务器——它可能就在数据库服务器的缓存中。

    8. 恢复注意

  • mysql数据目录赋予权限

  • 必须清除原先的data目录和binglog信息,不清楚会导致无法正常启动 或则 启动之后的binlog和gtid不一致问题**