MySQL备份与恢复

编程 · 02-02 · 152 人浏览

备份

mysqldump

是逻辑备份工具,备份的是SQL语句。

备份方式:

  • InnoDB表可采取快照备份的方式,开启一个独立事务,获取当前最新的一致性快照。将快照数据,放在临时表中,转换成SQL,保存到sql文件中
  • 非InnoDB表需要锁表备份。触发FTWRL,全局锁表。转换成SQL,保存到sql文件中

备份参数:

  • -A 全备:mysqldump -A >/backup/full.sql
  • -B 备份1或多个库:mysqldump -B test1 test2 >/backup/db.sql

备份单表或多表:mysqldump database_name table1 table2 >/backup/tab.sql

加不加-B参数的区别:

  • 加-B参数,例如,-B test备份的是:create database test;use test;
  • 没加-B参数,备份的是表,在应用时如果test库不存在,需要手工创建,并且use到test库下再恢复

备份高级参数:

  • --master-data,功能:

    • 备份时自动记录binlog信息
    • 自动锁表和解锁
    • 配合--single-transaction可以减少锁表时间
    • --master-data=1会将change master语句写入dump文件中,--master-data=2会将change master 语句写入dump文件中,但是会被注释掉
  • -R:备份存储过程和函数
  • -E:备份事件
  • --triggers:备份触发器
  • --max_allowed_packet=128M:控制备份时传输数据包大小

每天全备:

mysqldump -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/backup/full_`date +%F`.sql

mysqldump小结:

  • 优点:可读性较强,压缩比,节省空间,不需要下载安装
  • 缺点:备份时间较长,恢复时间也长
  • 数据量较少100G以内,建议使用mysqldump

从mysqldump全备中获取库和表备份:

# 获得表结构
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' full.sql >createtable.sql
# 获得INSERT INTO语句
grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
# 获取单库备份
sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

Percona Xtrabackup(XBK)

是物理备份工具,支持全备和增量备份。有两个主要的工具:xtrabackup、innobackupex

  • xtrabackup只能备份InnoDB和XtraDB两种数据表
  • innobackupex则封装了xtrabackup,同时可以备份MyISAM数据表

InnoDB表:
热备份:业务正常发生时,影响较小的备份方式

  • checkpoint,将已提交数据页刷新到磁盘,记录一个LSN
  • 拷贝InnoDB表相关文件(ibdata1、frm、ibd...)
  • 备份期间产生新数据变化的redo也会被备份走

非InnoDB表:
温备份:锁表备份

  • FTWRL,触发全局锁
  • 拷贝非InnoDB表数据
  • 解锁

再次统计LSN,写入到专用文件,记录二进制日志位置,所有备份文件统一存放在一个目录下。

全量备份

命令:

innobackupex --no-timestamp /data/xbk/full_`date +%F`

--no-timestamp:表示不创建时间戳目录来存储备份,而是指定备份文件夹

全备恢复:

  • 准备备份:innobackupex --apply-log /data/xbk/full_*/,redo前滚,undo回滚,模拟CSR过程
  • 数据恢复:cp -a /data/xbk/full_*/* /data/3306/,前提是被恢复目录/data/3306/为空,被恢复数据库实例是关闭
  • 数据授权:chown -R mysql.mysql /data/*
  • 启动数据库

增量备份

增量备份无法单独恢复,必须依赖于全备进行恢复,所有增量必须要按顺序合并到全备中。

增量备份命令:innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1

--incremental-basedir:增量备份基目录(上次全量备份路径)

恢复过程,备份整理(apply-log)+ 合并备份(full+inc1+inc2):

  • 基础全备整理:innobackupex --apply-log --redo-only /backup/full
  • 合并inc1到full中:innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full
  • 合并inc2到full中(最后一个增量不用加--redo-only):innobackupex --apply-log --incremental-dir=/backup/inc2 /backup/full
  • 最后一次全备整理:innobackupex --apply-log /backup/full

截取binlog并恢复:

  • 找到起点:cat /backup/inc2/xtrabackup_binlog_info
  • 找到终点:文件末尾
  • position截取:mysqlbinlog --skip-gtids --start-position=219 binlog文件 >/tmp/binlog.sql
  • 临时关闭日志:set sql_log_bin=0;
  • 导入SQL语句:source /tmp/bin.sql;
  • 恢复日志:set sql_log_bin=1;

恢复

恢复全备:source /backup/full.sql

截取binlog:

  • 找到起点:grep "\-- CHANGE MASTER TO" /backup/tab.sql
  • 找到终点:show binlog events in 'binlog文件';
  • position截取:mysqlbinlog --skip-gtids --start-position=219 --stop-position=335 binlog文件 >/tmp/binlog.sql

恢复binlog:

  • 临时关闭日志:set sql_log_bin=0;
  • 导入SQL语句:source /tmp/bin.sql;
  • 恢复日志:set sql_log_bin=1;
MySQL
Theme Jasmine by Kent Liao