第十二章 MySQL的恢复与备份

一、数据的备份

1.备份的原因

1.数据重要
2.备份就是为了恢复
3.减少公司的损失

2.备份的类型

1.冷备份:先停止数据库的访问，然后备份

这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。

2.温备份:不停止数据库的访问，不阻止数据的读取，阻止数据修改的同时然后备份

这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。

3.热备份:不停止数据库的访问，不锁表，不阻止数据的读取和写入的同时进行备份

这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问

3.备份的形式

1）逻辑备份

#把数据导出成sql文件的形式进行备份
1.binlog 的方式
2.into outfile
	1）配置数据库
		[root@db01 ~]# vim /etc/my.cnf
		[mysqld]
		secure-file-priv=/tmp
	2）数据库执行
		mysql> select * from world.city into outfile '/tmp/world_city.data';
	3）查看导出文件
3.mysqldump
4.replication (数据库主从，属于逻辑备份，并没有是真正的做到备份)

2）物理备份

1.直接备份data目录
	1）将数据目录data打包
	2）将data打包文件推送到另一台数据库服务器（两台机器必须使用相同安装方式）
	3）到新机器解压data目录到数据库的数据目录进行替换
	4）登录数据库查看
	
2.Xtrabackup

3）备份的类型

1.全备：全部数据都进行备份
	1）比较浪费磁盘空间
	2）恢复数据方便

2.增备：对新增加的数据进行备份
	1）相对于全备比较节省磁盘空间
	2）恢复数据需要合并多次数据

3.差异备份：针对上一次全备进行备份
	1）也会出现数据的重复，比较浪费磁盘空间
	2）恢复数据，只需要与全备合并进行恢复即可

二、mysqldump 备份命令

1.mysql客户端

1.mysql
2.mysqladmin
3.mysqldump

2.mysqldump命令参数

0）语法

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help

1）不使用任何参数的时候

1.导出指定库
[root@db01 ~]# mysqldump -uroot -p world > /tmp/world.sql

2.导出指定库下面的指定表
[root@db01 ~]# mysqldump -uroot -p world city > /tmp/city.sql

3.导出指定库下面的多个表（第一个参数必须是库，后面的其他参数都只能是表名）
[root@db01 ~]# mysqldump -uroot -p world city country > /tmp/city2.sql

2） 加参数

1. -A 等于 --all-databases，导出所有库
	[root@db01 ~]# mysqldump -uroot -p -A > /tmp/full.sql

2. -B 指定库备份，只能指定库的名字
	[root@db01 ~]# mysqldump -uroot -p -B world row events > /tmp/1.sql
	
3. -F 备份数据的同时刷新binlog
	[root@db01 ~]# mysqldump -uroot -p -F world city > /tmp/city2.sql
	
4. --master-data=2 备份时进行打点，记录备份时的binlog位置点（必须开启binlog才能使用）
	[root@db01 ~]# mysqldump -uroot -p123 -B world --master-data=2 > /tmp/world.sql
		--master-data=0		不打点不记录位置
		--master-data=2		打点并注释（单纯的进行备份时）
		--master-data=1		打点不注释（主从数据库扩展从库时）

5. --single-transaction 快照备份

6. -d 仅表结构
7. -t 仅数据
8. -R --routines 备份存储过程和函数数据
9. --triggers 备份触发器数据

#备份的完整命令
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

3）扩展

1. gzip 压缩备份
	[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.tar.gz
	#压缩恢复数据时
	[root@db01 ~]# zcat /tmp/full.tar.gz | mysql -uroot -p

2. 备份同时加上时间戳
	[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full$(date +%F).sql

三、企业故障恢复案例

1.背景

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

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

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

2.数据库恢复思路

1.停止业务，避免数据的二次伤害
2.找一个临时的库，恢复前一天的全备
3.截取前一天23：00到第二天10点误删除之间的binlog，恢复到临时库
4.测试可用性和完整性
5.开启业务前的两种方式
	1）直接使用临时库顶替原生产库，前端应用割接到新库（核心业务表数据量多的时候）
	2）将误删除的表单独导出，然后导入到原生产环境（核心业务表数据量少的时候）
6.开启业务

3.故障模拟

1）准备数据

#建库
mysql> create database backup;
#切换库
mysql> use backup;
#建表
mysql> create table back111(id int);
#建表
mysql> create table back222 select * from world.city;

2）进行23:00全备

#定时任务备份
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/backup$(date +%F).sql

3）模拟23：00备份后到早上10点的操作

#切换库
mysql> use backup
#建表
mysql> create table back333 select * from world.city;
#修改数据
mysql> update back222 set countrycode='CHN' where 1=1;

4）模拟10:00开发人员删除核心表

#删除核心表
mysql> drop table back111;

4.恢复数据

1）停止业务

[root@db01 ~]# systemctl stop mysqld

2）准备新的数据库

3）找出binlog对应23:00到10点的位置点

1.找到起始位置点
	[root@db01 ~]# less /tmp/backup2020-11-04.sql
	#第22行
	-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=959935;
	
2.找到结束位置点
	[root@db01 ~]# mysqlbinlog --base64-output=decode-rows -vvv --start-position=959935 /usr/local/mysql/data/mysql-bin.000013 > 1.txt
	[root@db01 ~]# vim 1.txt
	# at 1339403
	... ...
	DROP TABLE `back111` /* generated by server */
	
3.截取binlog
	[root@db01 ~]# mysqlbinlog --start-position=959935 --stop-position=1339403 /usr/local/mysql/data/mysql-bin.000013 > /tmp/huifu.sql

4）将全备和恢复的sql推送到新库

[root@db01 ~]# scp /tmp/backup2020-11-04.sql 172.16.1.52:/tmp
[root@db01 ~]# scp /tmp/huifu.sql  172.16.1.52:/tmp

5）将sql导入新的数据库

[root@db02 ~]# mysql < /tmp/backup2020-11-04.sql 
[root@db02 ~]# mysql < /tmp/huifu.sql
#尽量使用source的方式恢复

6）确认数据

mysql> use backup
mysql> select * from back333;

四、Xtrabackup 物理备份

1.上传或者下载Xtrabackup工具

1.上传
[root@db01 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

#或者

2.下载
#下载epel源
wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://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

2.安装

[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

[root@db01 ~]# xtrabackup		#旧版本的命令
[root@db01 ~]# innobackupex		#新版本的命令

3.xtrabackup特性

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

4.xtrabackup全量备份

1.创建备份的目录
	[root@db01 ~]# mkdir /backup
	
2.全备
	[root@db01 ~]# innobackupex --user=root --password=123 /backup/full/
	
3.查看全备的文件
	[root@db01 ~]# ll /backup/full/
	总用量 0
	drwxr-x--- 9 root root 271 11月  4 17:50 2020-11-04_17-50-18
	drwxr-x--- 9 root root 271 11月  4 17:51 2020-11-04_17-51-34
	
4.去除时间戳进行备份
	[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full/

5.再次查看备份文件
	[root@db01 ~]# ll /backup/full/
    [root@db01 /backup/full]# vim xtrabackup_checkpoints   #备份的信息
	backup_type = full-backuped
	from_lsn = 0
	to_lsn = 2330991066
	
	xtrabackup_binlog_info		#binlog的信息
    xtrabackup_info				#备份工具的信息
    xtrabackup_logfile			#binlog

5.使用xtrabackup进行数据恢复

1.删库
mysql> drop database test;
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
+--------------------+
2 rows in set (0.00 sec)

2.停止数据库
[root@db01 ~]# systemctl stop mysqld

3.将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full

4.恢复数据
[root@db01 ~]# cd /usr/local/mysql
[root@db01 /usr/local/mysql]# mv data databack
#方式一：
[root@db01 /usr/local/mysql]# cp -r /backup/full ./data
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data
#方式二：
[root@db01 /usr/local/mysql]# innobackupex --copy-back /backup/full/
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data