mysql数据库备份与恢复（增量，全量，mysqldump，xtrabackup）

mysql备份概述
1.关于备份你要知道的
备份和冗余有什么区别？
备份：能够防止由于机械故障一季认为误操作带来的数据丢失，例如将数据库文件保存在其他位置
冗余：数据有多份，但不等于备份，只能防止机械故障带来的数据丢失，例如主备模式，数据库集群
2.备份什么
数据库：一堆物理文件的集合；日志文件+数据文件+配置文件
3。备份过程中须考虑的因素
必须定制详细的备份计划（策略）（备份频率，时间点，周期）
备份数据应该放在非数据库本地，并加以有多份副本
必须做好数据恢复演练（每隔一段时间，对备份的数据在测试环境中进行模拟恢复，保证当出现数据灾难的时候能够及时恢复数据）
根据数据应用的场合，特点选择正确的备份工具
数据的一致性
服务的可用性

4.备份类型
逻辑备份
备份的是建表，建库，插入等操作所执行的sql语句
适用于中小型数据库，效率相对较低。一般数据库正常提供服务的前提下进行，如：mysqldump ，mydumper,into outfile
物理备份
直接复制数据文件
适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的mysql版本
一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行备份；如：tar ,cp,xtrabackup(数据库可以正常提供服务），lvm snapshot,rsync
在线热备（冗余）
实时在线备份
mysql架构，主从等

5.备份工具
社区版
mysqldump(逻辑备份，只能全量)
企业和社区都有
本质上使用sql语句描述数据库及数据并导出
在myisam引擎上锁表，innodb引擎上锁行
数据量很大时不推荐使用（100G），主要导入时费劲
mysqlhotcopy（物理备份工具）
企业和社区都有
perl写的一个脚本，本质上是使用锁表语句后再拷贝数据
只支持myisam引擎

企业版
mysqlbackup
在线
增量
部分
某个特定时间的一致性状态备份

第三方备份工具
XtraBackup和innobackupex（物理备份）
Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备（备份时不影响数据读写），时商业备份工具InnoDB Hotbackup的一个很好的替代品。
Xtrabackup有两个主要工具：xtrabackup，innobackupex
xtrabackup只能备份innoDB和Xtradb两种数据表，不能备份myisam类型的表
innobackupex是将Xtrabackup进行封装的perl脚本，所以能同时备份处理innodb和myisam的存储引擎，但在处理myisam时需要加一个读锁。

mydumper（逻辑备份，备份sql语句）目前不更新了

生产主要使用：mysqldump，XtraBackup

6.备份方法
完全备份（全备）
增量备份（增量备份基于全量备份）

二mysql的逻辑备份
1.mysqldump
本质：导出的是sql语句文件
有点：无论什么引擎，都可以用mysqldump备份成sql语句
缺点：速度慢，导入时可能会出现格式不兼容的突发状况，无法直接做增量备份
提供三种级别的备份，表级，库级，和全库级
基本语法：
表级别备份
mysqldump [OPTIONS] database [tables]

库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3…]

全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

   常用参数

–flush-logs,-F 开始备份前刷新日志 ，重新生成一个二进制日志文件
–flush-privileges 备份包含mysql数据库时刷新授权表
–lock-all-tables, -x myisam一致性，服务可用性，（针对所有库所有表）
–lock-tables, -l 备份前锁表（针对要备份的库）
–single-transaction 适用InnoDB引擎，保证一致性，服务可用性
–master-data=2 表示将二进制日志位置和文件名写入到备份文件并在dump文件注释掉这一行
–master-data=1 表示将二进制日志文件和文件名写入到备份文件，在dump文件中不注释这一行
–master-data参数其他说明：
1）恢复时会执行，默认是1
2）需要RELOAD privilege 并必须打开二进制文件
3）这个选项会自动打开–lock-all-tables,关闭–lock-tables

表级备份
mysqldump -p123 db01 emp > /tmp/emp.sql
mysqldump -p123 db01 emp dept > /tmp/emp.sql

表级恢复
mysql -p123 db01 </tmp/emp.sql
或者mysql数据库内使用source命令来执行外部的sql文件
mysql> source /tmp/mysqlbackup/emp.sql

库级备份
mysqldump --databases db01 -p > /tmp/mysqlbak/db01.sql 备份单个库
mysqldump --databases db01 db02 -p > /tmp/mysqlbak/db01.sql 备份多个库

库级恢复
mysql -p < /tmp/mysqlbak/db01.sql
mysql > source /tmp/mysqlbak/db01.sql

全库级备份
mysqldump --databases --master-data --single-transaction > /tmp/all.sql

总结：
1.mysqldump工具备份时sql语句，故备份不需要停服务
2.使用备份文件时，要保证数据库处于运行状态
3.只能实现全库，指定库，表级别的某一时刻的备份，本身不能增量备份
4.适用于中小型数据库

结合二进制日志增量备份
核心思路：
1.备份的目的为了数据出问题进行恢复
2.二进制日志文件默认会记录下所有对数据库数据变化的操作（增，删，改）
3.二进制文件同时还会记录具体sql执行时的环境，时间以及起始位置和结束位置（pos值）
4.通过获取二进制文件里的sql操作来进行数据恢复

（一）mysqldump+binlog增量备份
思路：
1.先做全量备份
2.对数据库做更改操作
3.备份二进制日志文件
4.模拟故障进行
5.数据恢复
6.测试验证

步骤：
1.备份全库
mysqldump --single-transaction --flush-logs --master-data=2 --all-databases > /tmp/all_db.sql -p

2.更改数据库
mysql> select * from t1;
±-----±------+
| ID | name |
±-----±------+
| 6 | zhang |
| 2 | qian |
| 3 | sun |
| 4 | li |
| 5 | zhou |
±-----±------+
5 rows in set (0.00 sec)

mysql> delete from t1 where ID=5;
Query OK, 1 row affected (0.08 sec)

mysql> select * from t1;
±-----±------+
| ID | name |
±-----±------+
| 6 | zhang |
| 2 | qian |
| 3 | sun |
| 4 | li |
±-----±------+
4 rows in set (0.00 sec)

mysql> insert into t1 values(9,‘zhou’);
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
±-----±------+
| ID | name |
±-----±------+
| 6 | zhang |
| 2 | qian |
| 3 | sun |
| 4 | li |
| 9 | zhou |
±-----±------+
5 rows in set (0.00 sec)
mysql> update t1 set name=‘wang’ where ID=4;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

3.备份二进制文件
方法1：直接拷贝物理文件
cp binlog.000002 /opt

方法2：备份改变过的数据（SQL）
mysqlbinlog --start-position=120 binlog.000002 > /tmp/binlog.sql

注意，此处的pos值，通过/tmp/all_db.sql中查看
– CHANGE MASTER TO MASTER_LOG_FILE=‘binlog.000002’, MASTER_LOG_POS=120;

4.故障模拟
mysql> drop database db2;
Query OK, 2 rows affected (0.14 sec)

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
±-------------------+
4 rows in set (0.00 sec)

5.全量恢复
1.通过刚刚全量备份进行恢复
mysql < /tmp/all_db.sql -p

2.进入数据库查看结果
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db1 |
| db2 |
| mysql |
| performance_schema |
±-------------------+
5 rows in set (0.00 sec)
说明：以上数据不是最新数据

6.二进制日志恢复到最新状态
方法1：进入到数据库中,source binglog日志
mysql> source /opt/binlog.000002

方法2：命令行用mysqlbinlog恢复
mysqlbinlog --start-position=120 /opt/binlog.000002 |mysql -uroot -p456
注意：如果想要恢复指定的sql，到binglog日志中确定pos值，使用以下方法恢复
mysqlbinlog --start-position=402 --stop-position=505 /opt/binlog.000002 |mysql -uroot -p456

（二）总结
1.mysqldump工具只能恢复到备份那一时刻
2.通过二进制日志来恢复到最新数据
3.二进制日志也可以跳过sql恢复
4.二进制日志恢复数据的重点就是找到相应的position值或者时间点，建议通过pos值进行恢复。

逻辑导入导出
1.导出数据：
select * into outfile ‘/opt/t1’ from db2.t1;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
vim /usr/local/mysql/my.cnf
[mysqld]
secure-file-priv=/opt/
mysql> select * into outfile ‘/opt/t1’ from db2.t1;
Query OK, 6 rows affected (0.00 sec)

2，。恢复数据
方法1：（说明：该方法要求在编译数据库时要加上–enable-local-infile参数才可以使用
mysql> load data local infile ‘/opt/t1’ into table db2.t1;
Query OK, 6 rows affected (0.00 sec)
Records: 6 Deleted: 0 Skipped: 0 Warnings: 0

方法2：
mysqlimport dbname /path/file

三msyql的物理备份
1.xtrabackup备份介绍
优点：
备份过程快速，可靠（因为是物理备份）
支持增量备份，更为灵活
备份过程中不会大点正在执行的事务
能够基于压缩等功能节约磁盘空间和流量
自动实现备份检验
还原速度快
缺点：
只对innidb表增量备份，myisam表增量备份时是全备
innobackupex备份myisam表之前要对全库进行read lock，阻塞写操作，若备份是在从库上进行的话会影响主从同步，造成延迟。对innodb表备份不会阻塞读写

2.xtrabackup备份原理（不影响数据库使用）
innobackupex首先会启动一个xtrabackup_log后台检测得进程，实时检测mysql得redo log得变化，一旦发现redo有新的日志写入，立刻将日志写入到日志文件xtrabackup_log中
物理拷贝innodb的数据文件和系统表空间文件idbdata1到对应的以默认时间戳为备份目录的地方。
复制结束后，执行flush table with read lock 操作进行全库锁表准备备份非innodb文件
物理复制.frm .myd .myi等非InnoDB引擎文件到备份目录
查看二进制日志的位置
解锁表unlock tables
停止xtrabackup_log进程

3.xtrabackup备份恢复原理
启动xtrabackup_log进程，检测mysql的redo log日志，将变化写入到自己的xtrabackup日志中
拷贝物理文件到备份目录
拷贝完成后停止xtrabackup_log进程
注意：此时的备份数据不能直接用于恢复，因为没有备份过程中的数据
预备阶段（将备份期间xtrabackup_log日志中记录的数据更改操作应用到开始备份的集中里）
预备结束后的备份集的数据是备份结束点的数据，此时就恶意用于恢复了
2.xtrabackup全被及恢复
思路：
innobackupex工具安装
innobackupex进行全备，备份集不能直接用于恢复
预备阶段，备份过程中产生的xtrabackup_log应用到全量备份集
模拟故障（删除数据）
进行全库恢复
测试验证

安装工具

全库备份
创建测试表
mysql> create database db03;
mysql> use db03;
mysql> create table t1(id int,name varchar(10)) engine=myisam;
mysql> indert into t1 values(1,‘mona’);
mysql> insert into t1 values(1,‘mona’);
mysql> create table t2(id int,name varchar(10)) engine=innodb;
mysql> insert into t2 values(2,‘tom’);

创建备份用户并授权
创建admin用户并授予相应权限
mysql> grant reload,process,lock tables,replication client on . to ‘admin’@‘localhost’ identified by ‘456’;
mysql> flush privileges;

说明：
在数据库中需要以下权限
RELOAD和LOCK TABLES权限：为了执行FLUSH TABLES WITH READ LOCK
REPLICATION CLIENT权限：为了获取binary log位置
PROCESS权限：显示有关在服务器中执行的线程信息（即有关回话执行的语句），允许使用SHOW ENGINE

全库备份
[root@localhost ~]# innobackupex -S /tmp/mysql.sock --user=admin --password=456 /full_xtraback

说明：备份目录默认会自动创建，也可以手动创建
[root@localhost ~]# ls /full_xtraback/
2020-03-29_23-24-26 2020-03-29_23-26-11 2020-03-29_23-31-13

了解相关文件：
[root@localhost ~]# ll /full_xtraback/2020-03-29_23-31-13/
backup-my.cnf：备份命令用到的配置选项信息
db03
db1
db2
ibdata1
mysql
xtrabackup_binlog_info：mysql服务器当前正在使用的二进制日志文件及备份这一刻为止二进制日志事件的位置
xtrabackup_checkpoints：备份类型（如完全或增量），备份状态（如是否已经为prepared状态）和LSN（日志序列号）范围信息；每个InnoDB页（通常为16k大小）都会包含一个日志 序列号，即LSN
LSN：是整个数据库系统的系统版本号，每个页面相关的LSN能够表名粗页面最近是如何发生改变的
xtrabackup_info：xtrabackup工具在备份时记录的使用工具及数据库信息
xtrabackup_logfile：xtrabackup记录innodb事务日志的信息

应用日志到备份集（预备）
–apply-log 表示应用日志到备份集
[root@localhost ~]# innobackupex --apply-log --user=admin --password=456 /full_xtraback/2020-03-29_23-31-13

说明：在实现准备过程中，innobackupex通常还可以使用–user-memory选项来指定其可以使用的内存的大小，默认通常为100M，如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

故障模拟
删除数据目录文件
1.停掉前应用
2.停mysqld进程
[root@localhost data]# pkill -9 mysqld

数据恢复(恢复时不需要连接数据库）
–copy-back：表示从备份集拷贝文件到数据目录（数据目录为空）
[root@localhost data]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=admin --password=456 --copy-back /full_xtraback/2020-03-30_11-08-40/

说明：
1.innobackupex工具是物理拷贝文件，所以在恢复时不需要连接数据库，这样就不需要初始化数据库并启动服务
2.数据目录必须是空得，innobackupex --copy-back不会覆盖已存在的文件，除非指定–force-non-empty-directories参数
3.还要注意，还原时需要先关闭服务
恢复过程中如果出现报错
innobackupex version 2.4.10 based on MySQL server 5.7.19 Linux (x86_64) (revision id: 3198bce)
Error: datadir must be specified.
原因：innobackupex工具不知道数据目录在哪里
解决：指定配置文件位置–defaults-file=/path/my.cnf
另外需要在my.cnf中指定basedir和datadir

恢复之后文件权限为root，需要修改为mysql

[root@localhost data]# chown -R mysql.mysql .

启动服务
[root@localhost data]# service mysqld start

xtrabackup增量备份恢复原理
增量备份恢复原理：

思路：
1.全量备份（增量基于全量)
2,应用日志到全量备份集中（预备）
3.更改数据
4.增量备份
5.应用增量的备份集到全量备份集中
6.模拟故障
7.数据恢复
8测试验证

执行：
1.全量备份
[root@localhost data]# innobackupex -S /tmp/mysql.sock --user=admin --password=456 /full_db

2.应用日志到全量备份集
–apply-log 表示应用日志
–redo-only 表示只应用到已经提交的事务，不会滚未提交的事务
注意：如果已经回滚了未提交事务，那么就无法再应用增量备份
[root@localhost data]# innobackupex --user=admin --password=456 --apply-log --redo-only /full_db/2020-03-30_13-53-24/

3.修改数据
mysql> insert into db01.t1 values(2,‘tom’);

4.xtrabackup增量备份
语法：
innobackupex --incremental /incre_backup --incremental-basedir=BASEDIR
说明：
1.BASEDIR指的是完全备份所在的目录；/incre_backup目录中所产生以时间命名的目录以存放所有的增量备份数据。
2.在执行过增量备份之后再一次进行增量备份时，其–incremental-basedir应该指向上一次的增量备份所在的目录

[root@localhost ~]# innobackupex -S /tmp/mysql.sock --user=admin --password=456 --incremental /incre_backup --incremental-basedir=/full_back/2020-03-30_14-15-41/

5.应用增量备份集到全量备份集
语法：
innobackupex --apply-log /全量备份集目录 --incrementa-dir=/增量备份集
[root@localhost ~]# innobackupex --user=admin --password=456 --apply-log /full_back/2020-03-30_14-15-41/ --incremental-dir=/incre_backup/2020-03-30_14-19-47

说明：
1.–redo-only除了最后一个不用加之外，其他的增量应用都要加，最后一个应用的时候可以直接进入回滚未提交事务阶段；如果加了也没事，服务启动得到时候会进入recovery过程，来回滚
2.应用增量备份的时候只能按照备份的顺序来应用。如果应用顺序错误，那么备份就不可用。如果无法确定顺序，可以使用xtrabackup-checkpoints来应用顺序
6.模拟故障
删库
[root@localhost data]# mv * /haha

7.数据恢复
注意：在准备步骤完成后，还原时只需要还原完全备份即可
[root@localhost data]# innobackupex --defaults-file=/usr/local/mysql/my.cnf --user=admin --password=456 --copy-back /full_back/2020-03-30_14-15-41/

修改数据库目录权限
[root@localhost data]# chown -R mysql.mysql .

启动服务
[root@localhost data]# service mysqld start

4.xtrabackup总结
如果数据库在第一次增量备份后发生故障，那么数据恢复时只能使用xtrabackup全量备份加xtrabackup增量备份的时间点，即恢复到第一次增量备份结束的点
如果要将数据库恢复到最新状态，需要结合binglog日志恢复
以上全量和增量的备份集是不能用的，需要将xtrabackup_log(事务日志，在备份过程中数据库的更改）应用到全量的备份集中才有效（应用日志必须按照顺序去应用：全备----》增备1------》增备2.。）
应用所有日志全量备份集中时，需注意：
除了最后一次怎量备份应用日志可以不加–redo-only外，其他都要加。；
只应用已经提交的事务，不会滚未提交的事务
应用日志到全量备份集中时一定要严格按照时间顺序执行，否则无效
在恢复数据时，确保目录为空，数据库服务状态是停止状态。