mysql备份恢复两款常用工具

一、备份的类型
冷备份：关闭数据、停止业务
温备份：加锁备份
热备份：在先备份，不会影响到也正常运行
二、备份方式
2.1 逻辑备份：基于SQL语句的备份
mysqldump建库、建表、数据插入
基于二进制日志：数据库的所有变化类的操作
基于复制的备份：将二进制日志实时传送到另一台机器并且恢复
2.2 物理备份
xtrabackup进行物理备份
拷贝数据文件（冷备）
三、 备份工具
mysqldump （mysql原生自带很好用的逻辑备份工具）
mysqlbinlog （实现binlog备份的原生态命令）
xtrabackup （precona公司开发的性能很高的物理备份工具）
四、mysqldump备份工具使用
优点：逻辑备份工具，都是SQL语句，都是文本格式，便于查看和编辑，更便于压缩

缺点：备份效率较慢

mysqldump常用参数：

-u -p -h -S -P

五、备份案例
5.1 全库备份

-A --->全库备份     
例子：
mysqldump -uroot -p123 -A >/backup/full.sql

5.2 单库备份

例子：
 mysqldump -uroot -p123 -B lufei > /backup/lufei.sql  ---> -B 表示增加建库和use的语句
 mysqldump -uroot -p123  lufei > /backup/lufei1.sql

说明：-B,增加建库（create）及“use库”的语句，在将来恢复时，不需要手工进行建库和use 不加-B，需要恢复时，先创建库，use库下再进行恢复 另外，-B选项还可以实现，同时备份多个库，备份到同一个文件中 mysqldump -uroot -p123 -B lufei oldboy>/backup/lufei_oldboy.sql 以下例子，如果不加-B,去备份，他的功能是备份路飞数据库下的oldboy表 mysqldump -uroot -p123 lufei oldboy>/backup/lufei_oldboy.sql 语法：mysqldump 库1 表1 表2 表3 >库1.sql

• 注意：生产环境下，也要加的额外参数：

-R,       备份存储过程和函数数据
--triggers, 备份触发器数据
例如：
mysqldump -uroot -p123 -A  -R --triggers >/backup/full.sql

5.3 记录备份时刻binlog的位置

-F, --flush-logs   刷新binlog日志，为了方便将来二进制日志截取时的起点
mysqldump -uroot -p123 -A -F >/backup/full.sql

--master-data={1|2}     告诉你备份时刻的binlog位置，一般我们选择使用2，以注释的方式记录二进制日志位置

5.4 锁表备份和热备

锁表:适合所有引擎(myisam,innodb)
  -x, --lock-all-tables 
  -l, --lock-tables

--single-transaction  对innodb引擎进行热备
通过快照的方式实现热备

5.5 一个生产例子压缩备份:(全库备份)

mysqldump -uroot -p123 -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

六、mysqldump+binlog恢复案例
6.1 背景环境：

正在运行的网站系统，mysql数据库，数据量25G，日业务增量10-15M。
备份方式：
  每天23:00点，计划任务调用mysqldump执行全备脚本
故障时间点：
  上午10点，误删除了一个表
如何恢复？

6.2 思路

1、断开业务，防止对数据库二次伤害，挂出维护页面
  2、搭建备用库，恢复全备
  3、截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志
  4、恢复到备用库，验证数据可用性和完整性
  5、两种方案恢复前端应用
    5.1 备用库导出误删除的表，导入到生产库，开启业务
    5.2 直接将应用切割到备用库，替代生产库，开启业务

6.3 模拟故障并恢复：

1、原始数据：
mysql> create database oldboy;
mysql> use oldboy
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,'zhang3');
mysql> insert into t1 values (2,'li4');
mysql> insert into t1 values (3,'wang5');
mysql> commit;
2、模拟前一天晚上23:00全备
mysqldump  -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz
3、模拟白天（23:00-10:00）业务对数据的修改
mysql> insert into t1 values (4,'zhang33');
mysql> insert into t1 values (5,'li44');
mysql> insert into t1 values (6，'wang54');
mysql> commit;
4、模拟故障
drop table t1;
5、恢复
（1）准备全备，并获取到备份文件中的binlog的截取起点
gunzip all_2018-04-04.sql.gz
  -- CHANGE MASTER TO MASTER_LOG_FILE='my-bin.000004', MASTER_LOG_POS=731;
（2）截取二进制日志
mysqlbinlog --start-position=731  --stop-position=1126 /data/binlog/my-bin.000004 >/backup/binlog.sql
-----
 show binlog events in 'my-bin.000004';  ----》drop之前的position为1126 
-----
(3)恢复全备+binlog
set sql_log_Bin=0;
source /backup/all_2018-04-04.sql;
source /backup/binlog.sql

七、 Xtrabackup物理备份工具

7.1 Xtrabackup介绍

percona公司的备份工具，性能比较高。物理备份工具。

Xtrabackup的特点：

物理备份工具，在同级数据量基础上，都要比逻辑备份性能要好的多。
特别是在数据量比较大的时候，体现的更加明显。
备份方式：
1、拷贝数据文件
2、拷贝数据页
备份原理（innodb）：
1、对于innodb表，可以实现热备
（1）在数据还有修改操作的时刻，直接将数据文件中的数据页备份
此时，备份走的数据对于当前mysql来讲是不一致。
（2） 将备份过程中的redo和undo一并备走。
（3）为了恢复的时候，只要保证备份出来的数据页LSN能和redo LSN匹配，
将来恢复的就是一致的数据。redo应用和undo的应用。
2、对与myisam表，实现自动锁表拷贝文件。

7.2 Xtrabackup软件安装：
1、安装

wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo   //--->下载依赖包的yum源
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL    //---> 安装依赖包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm  
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm     //--->安装Xtraback

2、全备备份并恢复

mkdir /server/backup -p

常用参数

• --user=
• --password=
• --socket=
• --no-timestamp --不使用时间挫
  全备例子：

innobackupex    --user=root --password=123   /server/backup/
innobackupex    --user=root --password=123   --no-timestamp  /server/backup/

全备恢复例子：

1、恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)

• -apply-log：此选项作用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
• use-memory：该选项表示和--apply-log选项一起使用，prepare 备份的时候，xtrabackup做crash recovery分配的内存大小，单位字节。也可(1MB,1M,1G,1GB)，推荐1G

innobackupex --apply-log --use-memory=32M /server/backup/  # 必须做的准备操作

2、模拟故障

停库:
pkill mysqld
破坏数据：
cd /application/mysql/data
\rm -rf *

3、恢复

cp -a /server/backup/*  /application/mysql/data
或者
innobackupex --copy-back  /server/backup/full/

注意：恢复时，要确认数据路径是空的，并且数据库是停掉的

4、重新赋予权限

chown -R mysql.mysql /application/mysql/data

5、 启动并验证

/etc/init.d/mysqld start
mysql -e "select * from test.t1"

7.3 xtrabackup 实现增量备份及故障恢复案例
周日全备，周一到周六做增量

1、周日全备：

mkdir /backup/full
innobackupex --user=root --password=123 --no-timestamp /backup/full/

2、模拟数据变化（周一数据变化） 。 。 。

3、第一增量（周一晚上增量）：

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1

4、模拟数据变化（周二数据变化） 。 。 。

5、第二次增量（周二晚上增量）：

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2

6、周三上午做了n多的操作，10:00时刻,误删除t1表

7、在准备的备库中恢复数据：

innobackupex --apply-log  --redo-only /backup/full 
innobackupex --apply-log  --redo-only --incremental-dir=/backup/inc1 /backup/full
innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full 
innobackupex --apply-log  /backup/full

----准备工作，将日志进行合并

innobackupex --copy-back  /backup/full/   ---> cop备份到MySQL数据目录下

8、 截取第二次备份结束的position号误删除之前的position号

[root@test01 inc2]# cat xtrabackup_binlog_info 
my-bin.000042 1253
mysqlbinlog --start-position=1253  --stop-position=1881 /data/binlog/my-bin.000042 >/backup/binlog.sql
mysql> source /backup/binlog.sql

生产恢复案例：
背景：
1、xtrabackup备份策略每周日，full全备
2、xtrabackup周一到周六，inc1-inic6
3、总数据量200G
4、周三上午10点误删除表t1，数据量1G左右
5、周二晚上inc2备份完成之后到周三上午10点又做了很多操作
如何将数据库恢复到t1表误删除之前状态？
思路：
1、停业务，挂维护页
2、找备用库
3、合并full+inc1+inc2
4、截取周二晚上inc2备份后到周三上午10点，t1表删除之前的binlog日志
5、将合并后的full+截取的binlog恢复到备用库
6、验证数据可用性和完整性
7、使用备用库替代生产库使用或者将t1表导出并导入回生产库
8、业务恢复

---

以上恢复策略可以做如下优化

为了恢复1G表，需要将整个全备恢复，有必要吗？有什么好的解决办法？

当然有，就是通过导入表空间的方式 步骤：

创建一个和删前表结构一模一样的表
discard 掉他的表空间文件
将合并后的数据库对应的ibd文件cp到mysql数据目录下并修改相应的权限
最后导入表空间即可
通过二进制截取的方式截取误删除前最后一次备份后的position号，并恢复
具体操作如下：

登录后复制

drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;
cd /application/mysql/data/test
cp /backup/full/test/t1.ibd  ./
chown  -R mysql.mysql *
alter table t1 import tablespace;
select * from t1