一、数据的备份
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
5.xtrabackup增量备份
1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
6.xtrabackup增量备份实践
1.首先进行全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full$(date +%F)
?
2.模拟新增数据
mysql> create database xtra;
mysql> use xtra
mysql> create table xtrabackup(id int);
mysql> insert xtrabackup values(1),(2),(3);
?
3.进行增量备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full2020-11-05 /backup/firstadd
参数说明:
--incremental：
--incremental-basedir：上一次备份的路径
?
4.确认增量备份是否准确
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
?
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #第一次增备的起始位置点是全备的结束位置点
to_lsn = 2327902069
?
5.再次新增数据
mysql> use xtra
mysql> insert xtrabackup values(4),(5),(6);
?
6.再次增备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/firstadd /backup/twoadd
?
7.再次查看是否衔接
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
?
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #全备的结束点
to_lsn = 2327902069
?
[root@db01 /backup]# cat twoadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327902069 #第一次增备的结束点
to_lsn = 2327903988
7.xtrabackup增量备份恢复实践
1.准备恢复
1）full + firstadd + twoadd
2）需要将 firstadd 和 twoadd 按顺序合并到full中
3）分步骤进行 --apply-log
?
2.第一步：在全备中apply-log时，只应用redo，不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full2020-11-05
?
3.第二步：将 firstadd 合并到 full 中，并且apply-log，只应用redo，不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/firstadd/ /backup/full2020-11-05
?
4.确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2327902069 #全备的值等于第一次增备的值
?
5.第三步：将 twoadd 合并到 full 中，redo和undo都应用
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/twoadd/ /backup/full2020-11-05
?
6.再次确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2327903988
?
7.第四步：整体full执行apply-log，redo和undo都应用
[root@db01 mysql]# innobackupex --apply-log /backup/full2020-11-05
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# mv /usr/local/mysql/data /usr/local/mysql/databack
[root@db01 ~]# innobackupex --copy-back /backup/full2020-11-05
[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@db01 ~]# systemctl start mysqld