mysql的备份及恢复
前言：
我们试着想一想, 在生产环境中什么最重要？如果我们服务器的硬件坏了可以维修或者换新, 软件问题可以修复或重新安装, 但是如果数据没了呢？这可能是最恐怖的事情了吧, 我感觉在生产环境中应该没有什么比数据跟更为重要. 那么我们该如何保证数据不丢失、或者丢失后可以快速恢复呢？
为什么需要备份数据？
其实在前言中也大概说明了为什么要备份数据, 但是我们还是应该具体了解一下为什么要备份数据
在生产环境中我们数据库可能会遭遇各种各样的不测从而导致数据丢失, 大概分为以下几种.
- 硬件故障
- 软件故障
自然灾害
- 黑客攻击
- 误操作 (占比最大)
所以, 为了在数据丢失之后能够恢复数据, 我们就需要定期的备份数据, 备份数据的策略要根据不同的应用场景进行定制, 大致有几个参考数值, 我们可以根据这些数值从而定制符合特定环境中的数据备份策略
- 能够容忍丢失多少数据
- 恢复数据需要多长时间
- 需要恢复哪一些数据
数据的备份类型
数据的备份类型根据其自身的特性主要分为以下几组
完全备份
- 部分备份 完全备份指的是备份整个数据集( 即整个数据库 )、部分备份指的是备份部分数据集(例如: 只备份一个表)
而部分备份又分为以下两种
增量备份
- 差异备份 增量备份指的是备份自上一次备份以来(增量或完全)以来变化的数据; 特点: 节约空间、还原麻烦 差异备份指的是备份自上一次完全备份以来变化的数据 特点: 浪费空间、还原比增量备份简单
备份需要考虑的问题
定制备份策略前, 我们还需要考虑一些问题
我们要备份什么?
一般情况下, 我们需要备份的数据分为以下几种
数据
- 二进制日志, InnoDB事务日志
- 代码(存储过程、存储函数、触发器、事件调度器)
- 服务器配置文件
备份工具
这里我们列举出常用的几种备份工具
mysqldump : 逻辑备份工具, 适用于所有的存储引擎, 支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
cp, tar 等归档复制工具: 物理备份工具, 适用于所有的存储引擎, 冷备、完全备份、部分备份
lvm2 snapshot: 几乎热备, 借助文件系统管理工具进行备份
xtrabackup: 一款非常强大的InnoDB/XtraDB热备工具, 支持完全备份、增量备份, 由percona提供
以上的几种解决方案分别针对于不同的场景
1：如果数据量较小, 可以使用第一种方式, 直接复制数据库文件:
2：如果数据量还行, 可以使用第二种方式, 先使用mysqldump对数据库进行完全备份, 然后定期备份BINARY LOG达到增量备份的效果
3：如果数据量一般, 而又不过分影响业务运行, 可以使用第三种方式, 使用lvm2的快照对数据文件进行备份, 而后定期备份BINARY LOG达到增量备份的效果:
4：如果数据量很大, 而又不过分影响业务运行, 可以使用第四种方式, 使用xtrabackup进行完全备份后, 定期使用xtrabackup进行增量备份或差异备份
这里主要介绍两种备份：
1：使用mysqldump实现部分数据误删除的恢复
具体为：有hellodb数据库，在生产中，A员工对students表进行新增学生one，B员工误操作把students表给删除了，C员工再对teachers表进行新增老师two。我们要做的就是将B员工的误操作给撤回。
首先对数据库进行整体备份。
[root@centos7 ~]# mysqldump -A -F --single-transaction --master-data=2 > /mysql/backup/fullbak_$(date +%F_%T).sql
#-A --all-databases 备份所有数据库，含create database
#--master-date[=value]
1: 记录为CHANGE MASTER TO 语句、语句不被注释
2: 记录为注释的CHANGE MASTER TO语句
基于二进制还原只能全库还原
# -F, --flush-logs ：备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的
二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻
执行转储和日志刷新，可通过和--single-transaction或-x，--master-data 一起使
用实现，此时只刷新一次日志
[root@centos7 ~]# ls /mysql/backup/fullbak_2018-10-10_17\:25\:40.sql
/mysql/backup/fullbak_2018-10-10_17:25:40.sql2：产生新数据
MariaDB [hellodb]> insert into students values (26,'one',15,'F',null,null);
MariaDB [hellodb]> drop table students;
MariaDB [hellodb]> insert into teachers values (5,'two',18,'M');3.恢复
查看/mysql/backup/fullbak_2018-10-10_17\:25\:40.sql 获取二进制文件信息
里面注释行有二进制文件起始恢复位置
--
-- Position to start replication or point-in-time recovery from
--
-- CHANGE MASTER TO MASTER_LOG_FILE='bin.000006', MASTER_LOG_POS=245;[root@centos7 ~]# mysql < fullbak_2018-10-10_17\:25\:40.sql #恢复原始数据库[root@centos7 ~]# mysqlbinlog --start-position=245 mysql-bin.000014 > bin.sql编辑bin.sql，将其中错误的操作注释或删除，如
#DROP TABLE `students` /* generated by server */
保存退出后，导入bin.sql
[root@centos7 ~]#mysql < bin.sql 查看数据库，发现students数据库已找回
2：使用xtrabackup进行增量备份及还原
xtrabackup介绍
Xtrabackup是由percona提供的mysql数据库备份工具，据官方介绍，这也是世界上惟一一款开源的能够对innodb和xtradb数据库进行热备的工具。特点：
- 备份过程快速、可靠；
- 备份过程不会打断正在执行的事务；
- 能够基于压缩等功能节约磁盘空间和流量；
- 自动实现备份检验；
- 还原速度快；
一般情况, 备份完成后, 数据不能用于恢复操作, 因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此, 此时的数据文件仍不一致, 所以我们需要”准备”一个完全备份
1，备份
[root@centos7 ~]# mkdir /mysql/backup/{full,inc{1,2}} #创建备份目录先实现全备
[root@centos7 ~]# innobackupex /mysql/backup/full产生第一次的新数据
MariaDB [hellodb]> insert into students values (27,'wang',15,'F',null,null);第一次增量
[root@centos7 ~]# innobackupex --incremental /mysql/backup/inc1 --incremental-basedir=/mysql/backup/full/2018-10-12_11-14-47/产生第二次的新数据
MariaDB [hellodb]> insert into students values (28,'zhao ',15,'F',null,null);[root@centos7 ~]# innobackupex --incremental /mysql/backup/inc2 --incremental-basedir=/mysql/backup/inc1/2018-10-12_11-16-04/增量备份完成，备份后再产生一次新数据
MariaDB [hellodb]> insert into students values (29,'li ',15,'F',null,null);2.破坏
[root@centos7 ~]# systemctl stop mariadb.service
[root@centos7 ~]# rm /mysql/data/* -rf3：恢复
1，备完成备份
[root@centos7 ~]# innobackupex --apply-log --redo-only /backup/full/2018-10-12_11-14-47/
#此选项--apply-log-only阻止回滚未提完成的事务2，第1次增量备份到完全备份
[root@centos7 ~]# innobackupex --apply-log --redo-only /backup/full/2018-10-12_11-14-47/ --incremental-dir=/backup/inc1/2018-10-12_11-16-04/3，第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
[root@centos7 ~]# innobackupex --apply-log /backup/full/2018-10-12_11-14-47/ --incremental-dir=/backup/inc2/2018-10-12_11-17-00/4，到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
[root@centos7 ~]# innobackupex --copy-back /backup/full/2018-10-12_11-14-47/5，还原属性
[root@centos7 ~]# chown -R mysql.mysql /mysql/data6，启动服务
[root@centos7 ~]# systemctl restart mariadb.service4.通过二进制日志恢复 recovery 先读取最后一次增量备份目录下的xtrabackup_binlog_info，获取需要恢复的二进制日志的起始位置
[root@centos7 ~]# mysqlbinlog --start-position=8057 /mysql/logbin/mysql-bin.000003 > /root/bin.sql
[root@centos7 ~]# mysql < /root/bin.sql完成。