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;

1. 转载请保留原文链接：MySQL备份与恢复 https://aboss.top/post/662/
2. 本站所有资源文章出自互联网收集整理，本站不参与制作，如果侵犯了您的合法权益，请联系本站我们会及时删除。
3. 本站发布资源来源于互联网，可能存在水印或者引流等信息，请用户擦亮眼睛自行鉴别，做一个有主见和判断力的用户。
4. 本站资源仅供研究、学习交流之用，若使用商业用途，请购买正版授权，否则产生的一切后果将由下载用户自行承担。
5. 联系方式（#替换成@）：mail#aboss.top