MySQL数据备份和恢复

MySQL数据备份和恢复

备份原因

1.减少数据的丢失
2.让网站能7*24小时运行提供服务

备份类型

冷备
	需要停机维护，停止服务备份数据
	这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这种类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
温备
	不需要停机，也不需要停服务，但是在备份数据时会锁表
	这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
热备
	不需要停机，也不需要停服务，备份数据时不会锁表
	这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。

备份方式

逻辑备份(mysql的逻辑备份，不支持增备)
	mysqlbinlog
	replication(主从复制)
	into outfile
		在配置文件加入
			secure-file-priv=路径
		语法
			select * from 表 into outfile '路径';
	mysqldump
物理备份
	Xtrabackup

备份策略

全量备份
增量备份
差异备份

备份工具

1.mysqldump(逻辑)
	mysql原生自带的逻辑备份工具
2.mysqlbinlog(逻辑)
	实现binlog备份的原生态命令
3.Xtrabackup(物理)
	precona公司开发的性能很高的物理备份工具

备份工具-mysqldump

选项
	-A(--all-detabases)：备份所有库，全备
		mysqldump -A > 路径
	-B：备份指定数据库(单库备份)
		mysqldump -B 库 > 路径
	# 备份多个库
		mysqldump -B 库 库 > 路径
# 只备份表，不备份库
	mysqldump 库 > 路径
# 恢复时要指定一个数据库
	mysql 库名 < 路径
# 备份单表
	mysqldump 库 表 > 路径
	
	-F：备份时刷新binlog日志
		mysqldump -A -F > 路径
	-d：备份表结构
		mysqldump -A -d > 路径
	-t：仅备份数据
		mysqldump -A -t > 路径
	--master-data=[0|1|2]：打点备份
		mysqldump -A --master-data=0 > 路径：关闭打点备份(可以不写)
		mysqldump -A --master-data=1 > 路径：打点备份，可以用来将数据恢复到该库的从库上
		mysqldump -A --master-data=2 > 路径：打点备份
	
	--single-transaction > 路径：快照备份
		mysqldump -A --master-data=2 --single-transaction > 路径
	######### 特殊备份选项 ##########
	-R：备份存储过程与函数
	--triggers：备份触发器
	-x：锁表备份
# 完整的备份语句
	mysqldump -A -R --triggers --master-data=2 --single-transaction > 路径
# 优化：压缩备份
mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > 路径.gz
##  优化：路径加上日期
# 精确到天
	mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > 路径$(date +%F).gz
# 精确到小时
	mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > 路径$(date +%F-%H).gz
# 精确到分钟
	mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > 路径$(date +%F-%H-%m).gz
# 精确到秒
	mysqldump -A -R --triggers --master-data=2 --single-transaction|gzip > 路径$(date +%F-%T).gz
# 恢复数据
zcat 路径.gz|mysql

恢复数据操作

1.先临时关闭二进制日志
root@localhost:(none)>set sql_log_bin=0;
2.库内恢复操作
root@localhost:(none)>source 备份的sql文件
3.库外恢复操作
mysql < 备份的sql文件

## 注：
	1.mysqldump在备份和恢复时都需要MySQL实例启动为前提
	2.一般数据量级100G以内，大约15-30分钟可以恢复(PB、EB就需要考虑别的方式)
	3.mysqldump是以覆盖的形式恢复数据的

物理备份

Xtrabackup安装
1.下载
	wget http://test.driverzeng.com/MySQL_Package/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
2.安装
	yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

## 重点
1.对于非innodb表(如myisam)是直接锁表cp数据文件，属于一种温备
2.对于innodb的表(支持事务)，不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式
3.备份时读取配置文件/etc/my.cnf

## 老版本使用Xtrabackup会锁表属于温备
## 新版本，针对innodb有单独的备份命令innobackupex

全量备份

Xtrabackup的选项：
	--user：指定用户
	--password：指定密码
	--socker：指定socker文位置
	--apply-log：模拟CSR
	--copy-back：恢复数据
	--no-timestamp：备份时，自定义目录名，不使用时间戳
# 备份数据
	[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full
[root@db03 ~]# ls /backup/full/	
xtrabackup_binlog_info：binlog信息文件
	[root@db03 full]# cat xtrabackup_binlog_info 
	mysql-bin.000001	154
xtrabackup_checkpoints：备份信息文件
	[root@db03 full]# cat xtrabackup_checkpoints 
	backup_type = full-backuped
	from_lsn = 0
	to_lsn = 4251707
	last_lsn = 4251716
	compact = 0
	recover_binlog_info = 0
xtrabackup_info：备份的详细信息
xtrabackup_logfile：redo log

##  恢复全备数据
# 恢复数据前提
	1.被恢复的目录是空的
	2.被恢复的数据库的实例是关闭的
# 手动模拟CSR，重做redo，回滚undo
[root@db03 full]# innobackupex --apply-log /backup/full/
# 关闭数据库
[root@db03 full]# systemctl stop mysqld
# 删除之前的数据目录
[root@db03 full]# mv /var/lib/mysql /tmp/
# 恢复全备
[root@db03 full]# innobackupex --copy-back /backup/full/
# 修改权限
[root@db03 full]# chown -R mysql.mysql /var/lib/mysql

增量备份

1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按备份的顺序合并到全备中
选项：
	--incremental：开启增量备份
	--incremental-basedir：指定基于上一次备份的目录
	
# 全备
[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full
# 第一次增量备份
[root@db03 full]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full/ /backup/inc1
# 第二次增量备份
[root@db03 full]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1/ /backup/inc2

####### 恢复增备 ##########
--redo-only：只做redo
--incremental-dir：指定增量数据的目录(要把该目录合并到第一次全备目录里)
1.先把全备模拟CSR，只做redo，不做undo
[root@db03 full]# innobackupex --apply-log --redo-only /backup/full/
2.将inc1合并到全备上，只做redo，不做undo
[root@db03 full]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full/
3.将inc2合并到全备上，redo和undo都做
[root@db03 full]# innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full/
4.恢复全备
[root@db03 full]# innobackupex  --apply-log /backup/full/
5.关闭数据库
[root@db03 full]# systemctl stop mysqld
6.删除之前的数据目录
[root@db03 full]# mv /var/lib/mysql /tmp/
7.恢复全备
[root@db03 full]# innobackupex --copy-back /backup/full/
8.修改权限
[root@db03 full]# chown -R mysql.mysql /var/lib/mysql

差异备份

# 全备
[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full
# 第一次差异备份
[root@db03 full]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/chayi1
# 第二次差异备份
[root@db03 full]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/chayi2

######## 差异备份 #########
1.关闭数据库
[root@db03 full]# systemctl stop mysqld
2.删除数据目录
[root@db03 full]# mv /var/lib/mysql /tmp/
3.先把全备模拟CSR，只做redo，不做undo
[root@db03 full]# innobackupex --apply-log --redo-only /backup/full
4.合并最后一次差异备份，redo和undo都做
[root@db03 full]# innobackupex --apply-log  --incremental-dir=/backup/chayi2 /backup/full
5.将全备，redo和undo都做一次
[root@db03 full]# innobackupex --apply-log  /backup/full
6.恢复数据
[root@db03 full]# innobackupex --copy-back  /backup/full
7.修改权限
[root@db03 full]# chown -R mysql.mysql /var/lib/mysql

模拟用户写数据

[root@db03 ~]# vim /root/mysql.sh 
#!/bin/bash

num=1
while true;do
  mysql -uroot -p123 -e "insert into zls.chayi value($num);commit;"
  (( num++ ))
  sleep 1
done
# 全备
[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp /backup/full
# 第一次增备
[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/inc1
# 第二次增备
[root@db03 ~]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1 backup/inc2
######## 恢复步骤 ###########
1.先关闭数据库
[root@db03 full]# systemctl stop mysqld
2.准备新环境
3.删除数据目录
[root@db03 full]# mv /var/lib/mysql /tmp/
4.先把全备模拟CSR，只做redo，不做undo
[root@db03 full]# innobackupex --apply-log --redo-only /backup/full
5.将inc1合并到全备上，只做redo，不做undo
[root@db03 full]# innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1 /backup/full/
6.将inc2合并到全备上，redo和undo都做
[root@db03 full]# innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full
7.恢复全备
[root@db03 full]# innobackupex  --apply-log /backup/full/
8.恢复数据
[root@db03 full]# innobackupex --copy-back /backup/full2
9.授权
[root@db03 full]# chown -R mysql.mysql /var/lib/mysql
10.启动数据库
[root@db03 full]# systemctl start mysqld
11.截取binlog
12.恢复数据

作业：

1.企业故障恢复案例

背景：
正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

备份策略：
每天23：00，计划任务调用mysqldump执行全备脚本

故障时间点：
上午10点开发人员误删除一个核心业务表，如何恢复？

思路：

1）停业务避免数据的二次伤害
2）找一个临时的库，恢复前一天的全备
3）截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4）测试可用性和完整性
5）开启业务前的两种方式

a.直接使用临时库顶替原生产库，前端应用割接到新库
b.将误删除的表单独导出，然后导入到原生产环境

6）开启业务

1.准备新环境
/application/mysql-5.7.38/bin/mysqld --defaults-file=/etc/my.cnf  --initialize-insecure  --user=mysql --basedir=/application/mysql-5.7.38 --datadir=/application/mysql-5.7.38/data
2.将全备的数据发送到新环境中
scp /tmp/Mysql.sql.gz  172.16.1.51:/tmp/
3.恢复全备到新环境
[root@db01 /application/mysql]# zcat /tmp/Mysql.sql.gz |mysql
4.停掉连接数据库的程序和旧的数据库
[root@db03 mysql]# systemctl stop mysqld
5.查看全备文件，找到起始位置
[root@db03 mysql]# zcat /tmp/Mysql.sql.gz |head -25
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=15592;
6.查看binlog文件找到结束位置点
第一段结束点：41737
第二段起始点：41870
第二段结束点：47464
7.恢复数据
[root@db03 mysql]# mysqlbinlog --start-position=15592 --stop-position=41737 mysql-bin.000002 > /tmp/inc1.sql
[root@db03 mysql]# mysqlbinlog --start-position=41870 --stop-position=47464 mysql-bin.000002 > /tmp/inc2.sql
8.发送到新环境
[root@db03 mysql]# scp /tmp/inc* 172.16.1.51:/tmp
9.导入数据
[root@db01 /application/mysql]# mysql < /tmp/inc1.sql 
[root@db01 /application/mysql]# mysql < /tmp/inc2.sql
10.查询数据
mysql> select * from jl1.stu;
11.将数据库导出发送到旧数据库中
[root@db01 /application/mysql]# mysqldump -B jl1 > /opt/jl.sql
[root@db01 /application/mysql]# scp /opt/jl.sql 172.16.1.53:/tmp/
12.启动旧数据库
[root@db03 mysql]# systemctl start mysqld
12.在旧数据库中将数据导入
[root@db03 mysql]# mysql -uroot -p456 < /tmp/jl.sql

2.企业级增量恢复实战

背景：
某大型网站，mysql数据库，数据量500G，每日更新量100M-200M

备份策略：
xtrabackup，每周六0:00进行全备，周一到周五及周日00:00进行增量备份。

故障场景：
周三下午2点出现数据库意外删除表操作。

如何恢复？？？

1.周六全备数据
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp /backup/full
2.周一到周五进行增量备份
[root@db01 backup]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/full /backup/inc1

[root@db01 lib]# innobackupex --user=root --password=456 --no-timestamp --incremental --incremental-basedir /backup/inc1 /backup/inc2

3.移走data目录
[root@db01 backup]# mv arb/mysql /backup/

4.将全备只做redo不做undo
[root@db02 backup]# innobackupex --apply-log --redo-only /backup/full

5.将Inc1合并到full中，只做redo不做undo
[root@db01 backup]# innobackupex --apply-log --redo-only --incrementaldir=/backup/inc1 /backup/full

6.将inc2合并到full中，redo和undo都做
[root@db02 backup]# innobackupex --apply-log --incremental-dir=/backup/zls_inc2 /backup/full

7.将full2整体，redo和undo都做
[root@db02 backup]# innobackupex --apply-log /backup/full

8.恢复数据
[root@db02 backup]# innobackupex --copy-back /backup/full

9.授权
[root@db01 backup]# chown -R mysql.mysql arb/mysql

10.启动数据库
[root@db01 backup]# systemctl start mysqld

11.查询数据
mysql> show databases;

12.截取binlog
起始位置点：
[root@db02 full2]# cat /backup/full2/xtrabackup_binlog_pos_innodb
mysql-bin.000001 45318

结束位置点：
[root@db02 data]# mysqlbinlog -vvv --base64-output=decode-row mysql-bin.000001
54432

起始位置点：54524
结束位置点：61778

[root@db02 data]# mysqlbinlog --start-position=45318 --stop-position=54432 mysqlbin.000001 > /backup/binlog1.sql
[root@db02 data]# mysqlbinlog --start-position=54524 --stop-position=61778 mysqlbin.000001 > /backup/binlog2.sql