MySQL备份与恢复

备份

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;