MySQL备份笔记

MySQL备份笔记

备份的分类维度

• 备份时数据库的状态
  • Hot Backup(热备)：正常运行中直接备份
  • Cold Backup(冷备)：完全停止后备份
  • Warm Backup(温备)：数据库只读
• 备份文件的格式
  • 逻辑备份：输出文本或SQL语句
  • 物理备份(裸文件)：备份数据库底层文件，如对于InnoDB来说，一般就是.idb文件以及其他元数据的文件
• 备份的内容
  • 完全备份：备份完整数据
  • 增量备份：备份数据差异
  • 日志备份：备份Binlog(Binlog代表这一段时间内所有的数据差异，即数据变化的操作)

工具举例

• mysqldump：逻辑、热、全量备份
• xtrabackup：物理、热、全量+增量备份

使用OUTFILE命令进行备份

OUTFILE

• MySQL原生的SQL指令
• 最原始的逻辑备份方式
• 备份的功能和效果取决于如何写SQL语句

使用方式

• 首先查出MySQL的导出路径

  show variables like '%secure%';
  

• 使用into outfile指令将查询结果导出至文件

  select * into outfile '/var/lib/mysql-files/out_file_test' from table_xxx;
  

注意事项

• 在InnoDB事务下，可以做到一致性视图，即在可重复读的隔离级别下，开启事务后，因为MVCC的存在，此时所导的每一张表都处于同一时刻
• 修改分隔符：fields terminated by
• 修改换行符：lines terminated by

缺陷

• 输出的文本比较简略
• 很难进行还原，现在往往用来简单导出数据

OUTFILE如何改进？

• 自动发SELECT语句，不需要手动发送
• 自动开始事务
• 输出INSERT语句，可以直接用于还原

mysqldump

• 非常常用的MySQL逻辑备份工具
• MySQL Server自带
• 输出的备份内容为sql语句，平衡了阅读和还原
• sql语句占空间较小

原理

• mysqldump使用以下语句对数据进行备份

  SELECT SQL_NO_CACHE FROM `table_xxx`;
  

• SQL_NO_CACHE查询出的数据不会进入SQL缓存

使用方法

• 使用以下语句对数据进行备份：

  mysqldump -uroot -p111111 --databases d1 --single-transaction > xxx.sql;
  

• 直接执行导出的sql文件即可进行还原

  source xxx.sql;
  

注意事项

• –single-transaction：在RR级别下进行(InnoDB)
• –lock-all-tables：使用FTWRL锁所有表(MyISAM)
• –lock-tables：使用READ LOCAL锁当前库的表(MyISAM)
• –all-databases：备份所有数据库

缺点

• 导出逻辑数据，备份较慢
• 还原需要执行sql，速度也较慢

增量备份

mysqldump是不能做增量备份的，因为它是向库中每一个表发送select * 语句，那么要怎么实现增量备份呢？

思路：

• binlog忠实记录了MySQL数据的变化
• mysqldump全量备份之后，可以用binlog作为增量
• mysqldump全量备份时，切换新的binlog文件
• 从零还原时，采用全量还原+binlog还原

步骤

一、mysqldump全量备份

• mysqldump使用以下语句对数据进行全量备份：

  mysqldump -uroot -p111111 --database d1 --single-transaction --flush-logs --master-data=2 > xxx.sql
  

• –flush-logs：备份后切换binlog文件

• –master-data=2：记录切换后的binlog文件名

二、binlog增量备份

• 需要增量备份时，切换binlog文件

  mysqladmin -uroot -p111111 flush-logs
  

• 将所有新增的binlog文件备份

三、还原

• 首先恢复旧的全量备份

  source xxx.sql;
  

• 然后将binlog增量还原至数据库

  mysqlbinlog MySQL-bin.000002 ...(多个binlog文件用空格分开) | mysql -u root -p 111111
  

XtraBackup物理备份

为什么需要物理备份

• 直接备份InnoDB底层数据文件
• 导出不需要转换，速度快
• 工作时对数据库的压力较小
• 更容易实现增量备份

直接拷贝裸文件可行吗？

• 理论上可行，但有很多问题：
• 要同时备份frm文件、ibd文件、binlog文件、redo log文件等
• 在不同版本的数据库和操作系统上还原可能有兼容问题
• 必须冷备份，影响业务

实现物理 + 热 + 全量备份

• 思路：利用redo log，备份ibd文件 + 备份期间的redo log
• 1、启动redo log监听线程，开始收集redo log
• 2、拷贝ibd数据文件
• 3、停止收集redo log
• 4、加FTWRL锁拷贝元数据frm

实现物理 + 热 + 增量备份

• 思路：与全量级别相同
• 如何确定增量：根据每个页的LSN号，确定变化的页

实现物理还原

• 思路：mysqld crash崩溃恢复流程相似
• 还原ibd文件，重放redo log

ibbackup

• 现名MySQL Enterprise Backup，InnoDB官方出品
• 实现了上述的功能，性能优秀

XtraBackup

• Percona公司开发的开源版本，实现ibbackup所有功能
• XtraBackup 8.0 -> MySQL 8.0
• XtraBackup 2.4 -> MySQL 5.1，5.5，5.6，5.7

XtraBackup全量使用方法

• 备份：

  innobackupex --user=root --password=111111 backdir/    # backdir：备份文件夹
  

• 数据还原：(停掉mysqld)

  innobackupex --copy-back backdir/xxxx-xx-xx/
  

XtraBackup增量使用方法

• 增量备份：

  innobackupex --user=root --password=111111 --incremental backdir/ --incremental-basedir='/backdir/xxxx-xx-xx'
  

• 增量备份合并至全量备份

  innobackupex --apply-log backdir/xxxx-xx-xx/ --incremental-dir=backdir/yyyy-yy-yy/
  

如何防患于未然

权限隔离

• 给业务应用分配的账号只给DML权限
• 开发同学使用只读账号
• DBA平时使用时使用只读账号，特殊操作时切换账号

SQL审计

• DBA在开发环境审计即将上线的SQL语句
• 开发同学修改在线数据库，提交给DBA执行
• Inception自动审核工具

伪删表

• 删表之前将表改名，观察业务是否受影响
• 不直接删表，给表名加特殊后缀，用脚本删除

完备流程

• 上线之前备份数据
• 准备生产环境事故预案