数据库学习之十一:mysql 备份恢复

十一、mysql 备份恢复

课程大纲

1、备份的原因

2、备份的方式

3、备份的工具

4、mysqldump备份工具的详解

5、mysqldump+mysqlbinlog实现增量备份

6、企业级备份策略及恢复案例

7、xtrabackup备份恢复实战

运维工作的核心简单概括就两件事:

第一个是保护公司的数据。

第二个是让网站能7*24小时提供服务(用户体验)。

1、备份的类型

冷备份:关闭数据、停止业务

温备份:枷锁备份

热备份:在线备份,不会影响业务。

2、备份方式

逻辑备份:

基于sql语句的备份:

①mysqldump--》建库,建表,数据插入

②基于二进制日志:数据库的所有变化类的操作。

③基于复制的备份:将二进制日志实时传递到另一台机器并且恢复。

物理备份:

①xtrabackup 进行物理备份

②拷贝数据文件(冷备)

3、备份工具:

①mysqldump

mysql原生自带很好用的逻辑备份工具

②mysqlbinlog(根据始末position位置进行截取备份)

实现binlog备份的原生态命令

③xtrabackup

percona公司开发的性能很高的物理备份工具

mysqldump备份工具优缺点:

优点:逻辑备份工具,都是sql语句,都是文本格式,便于查看和编辑,便于压缩。

缺点:备份较慢,效率低。

mysqldump参数介绍:

-u -p -S -h -P

-A, 全库备份

例子:

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A >/backup/mysqlfull.sql

单库备份:

-B,增加建库(create)及’use库‘的语句,在将来恢复时,不需要手工建库和use库。

-B 选项还可以实现,同时备份多个库,备份到同一个文件中。(空格隔开)

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -B lufei >/backup/mysqllufei.sql

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 lufei >/backup/mysqllufei1.sql

不加-B,恢复时候需要先创建库,use库下再进行恢复。

不加-B ,去备份他库下的一个单表的意思。

生成环境下,也要加的额外参数:

-R:备份存储过程和函数数据。

--triggers :备份触发器数据。

-F,--flush-logs :刷新binlog日志,为了方便将来二进制日志截取时的起点。

mysqldump -uroot -poldboy -A -F >/backup/mysqlfull.sql

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

mysql> show master status;

锁表:适合所有引擎(myisam,innodb)

-x,--lock-all-tables

-l,--lock-tables

--single-transaction 对innodb引擎进行热备

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A --master-data=2 --single-transaction >/backup/mysqlfull.sql

通过快照的方式实现热备。

压缩备份:

[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A -R --triggers --master-data=2 --single-transaction | gzip >/backup/mysqlfull_$(date +%F).sql

4、mysqldump+binlog企业恢复实战

使用source 命令进行恢复
set sql_log_bin=0;(临时关闭二进制日志,防止恢复操作记录到二进制日志中)
source /opt/xxx.sql;
企业实例:
背景环境:
正在运行的网站系统,mysql数据库,数据量25G,日业务增量10-15M。
备份方式:
每天23:00点,计划任务调用mysqldump执行全备脚本。
故障时间点:
上午10点,误删除了一个表。
如何恢复?
思路:
①断开业务,防止对数据库二次伤害,挂出维护页面。
②搭建备用库,恢复全备。
③截取昨天晚上23:00之后到上午10点误删除操作之前的二进制日志。
④恢复到备用库,验证数据可用性和完整性。
⑤两种恢复前端应用。
	5.1备用库导出误删除的表,导入到生产库,开启业务。
	5.2直接将应用切割刀备用库,替代生产库,开启业务。
模拟故障并恢复:
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

5、xtrabackup 介绍

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

Xtrabackup软件安装:

1、安装
wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo
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

2、全备备份并恢复
mkdir /server/backup  -p

--user=
--password=
--socket=
--no-timestamp 

全备例子:
innobackupex     /server/backup/

innobackupex     --no-timestamp  /server/backup/full

全备恢复例子:

1、恢复数据前的准备(合并xtabackup_log_file和备份的物理文件)
innobackupex --apply-log --use-memory=32M /server/backup/full/

2、模拟故障
停库:
pkill mysqld
破坏数据:
cd /application/mysql/data
\rm -rf *

3、恢复
cp -a /server/backup/full/*  /application/mysql/data

或者

innobackupex --copy-back  /server/backup/full/   

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

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

启动:
/etc/init.d/mysqld start

mysql -e "select * from oldboy.t1"

6、xtrabackup 实现增量备份及故障恢复

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

7、xtrabackup生产恢复案例:

背景:
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恢复到备用库
	( 根据备份日志最后的position,再根据show binlog events in ‘my-bin.000004‘;获取最后的position)
	6、验证数据可用性和完整性
	7、使用备用库替代生产库使用或者将t1表导出并导入回生产库
	8、业务恢复

-----------------
-----------------
思考:以上恢复策略是否可以优化?
为了恢复1G表,需要将整个全备恢复,有必要吗?有什么好的解决办法?
单独恢复一个表:

drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;

cd /application/mysql/data/oldboy
cp /backup/full/oldboy/t1.ibd  ./

chown  -R mysql.mysql *
alter table t1 import tablespace;

数据库学习之十一:mysql 备份恢复

上一篇:ORACLE 误删除数据和表的恢复办法包括truncate


下一篇:分布式之数据库和缓存双写一致性方案解析