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;
当前页面是本站的「Google AMP」版。查看和发表评论请点击:完整版 »