MySQL备份与恢复

共计 2727 个字符，预计需要花费 7 分钟才能阅读完成。

备份

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 命令执行 sql 脚本优点：可支持大文件。

恢复

恢复全备：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;