MySQL系列:(十一)MySQL备份与恢复

admin 2021年01月11日 961次浏览

1、备份介绍

1.1、备份工具

  • mysqldump
  • percona Xtrabackup(XBK)

1.2、备份方式

  • 逻辑备份

    全备份:mysqldump
    增量备份:binlog (flush logs ,cp)

  • 物理备份
    全备份:XBK
    增量:XBK

1.3、备份类型

​ 热备 : 对于业务影响最小(InnoDB使用)
​ 温备 : 长时间锁表备份(MyISAM使用)
​ 冷备 : 业务关闭情况下备份(将数据库文件拷贝到其他地方,需要停业务操作)

1.4、检查备份可用性

  • 使用crontab -l查看备份相关定时任务
  • 通过备份脚本,查看备份路径及备份日志的相关信息,主要检查备份文件的大小和内容

2、mysqldump_逻辑备份

2.1、链接数据库

链接数据库的方法、参数与mysql链接相同

2.2、基础备份参数

  • 参数说明

    • -A:全部备份
    • -B:备份指定库
    • 不带参数:备份库下面的所有/指定表
  • 语法

    [root@localhost]# mysqldump -u[USER] -p[PASSWORD] [参数]  >/PATH/FILE
    
  • 示例

    # 全部备份
    [root@localhost]# mysqldump -uroot -p123 -A  > /backup/full.sql
    
    # 备份指定库
    ## 在 -B 参数后面的,全部是库名
    [root@localhost]# mysqldump -uroot -p123 -B world testdb > /backup/db.sql
    
    # 备份指定表
    ## 密码之后,第一个名称为库名,后面的全部为表名,如果不指定表名,则备份该库下面的所有表
    [root@localhost]# mysqldump -uroot -p123 world city country > /backup/tab.sql
    

2.3、特殊备份参数

  • -R:存储过程和函数

  • -E:事件

  • --triggers:触发器

  • --master-data=2

    • 以注释的形式记录备份时刻的binlog信息
    • 自动开启锁表功能
      不加--single-transaction参数 ,做温备份操作
      加上--single-transaction参数,对于InnoDB表不锁表备份(快照备份)
  • --single-transaction
    对于InnoDB的表进行一致性快照备份,不锁表

  • --set-gtid-purged

    该参数用于在做数据全备份时记录GTID值,如果设置为“AUTO/ON“时,记录GTID信息;设置为”OFF “时,不记录GTID信息,默认值为”AUTO “

    • 在构建主从时,使用--set-gtid-purged=AUTO或者--set-gtid-purged=ON

    • 做普通的本机备份恢复时,可以使用--set-gtid-purged=OFF

  • --max_allowed_packet

    控制备份时传输数据包的大小,例如:--max_allowed_packet=128M

2.4、数据恢复流程

  • 准备临时数据库

  • 准备最后一次正常全备的备份文件

  • 截取二进制日志(从最后一次正常全备后到数据库出问题之间的二进制日志)

  • 临时关闭二进制日志(binlog)

  • 恢复全备数据到临时库

  • 恢复binlog数据到临时库

  • 将故障表导出并恢复到生产

3、低版本迁移到高版本

  • 搭建高版本环境

  • 数据迁移

    • 低版本数据库全量备份
    • 导入低版本数据到高版本
      由于高低版本数据库结构有差异,迁移完数据后,需要执行mysql_upgrade命令对原有数据结构进行升级操作
    • binlog的持续追加
    • 停业务,恢复剩余的binlog
    • 业务割接