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自动审核工具
伪删表
- 删表之前将表改名,观察业务是否受影响
- 不直接删表,给表名加特殊后缀,用脚本删除
完备流程
- 上线之前备份数据
- 准备生产环境事故预案