一、Mysql数据库备份的相关概述
备份的目的：
用于灾难恢复、测试应用、回滚数据修改、查询历史数据、审计等
数据丢失的原因：
（1）程序错误
（2）人为操作失误
（3）运算错误
（4）磁盘故障
（5）灾难和盗窃等

二、数据库备份的类型
- 从物理和逻辑角度划分
物理备份： 对数据库操作系统的物理文件（数据文件、日志文件等）进行的备份（针对出现问题时需要快速恢复大型数据库）
划分： 冷备份、热备份、温备份
（1）冷备份： 数据库关闭的状态下进行的备份
（优点：备份速度快，恢复时简单，通过直接打包数据库文件）
（2）热备份： 数据库处于运行状态时进行的备份
（备份依赖于数据库的日志文件）
（3）温备份： 数据库锁定表格的状态下进行的备份
（不可往表格中写入但是可以读取的状态）

逻辑备份： 对数据库的逻辑组件进行的备份
（对create database、create table、insert等语句信息进行的备份，适用于可编辑数据值或者表结构较小的数据量）

- 备份策略的三种角度
（1）完全备份： 又叫完整备份，对整个数据库进行备份，保存的是备份完成时刻的数据库（是差异备份和增量备份的前提）
特点： 备份恢复简单，占用磁盘空间大，备份时间较长
（2）差异备份： 备份上一次完全备份后被修改过的所有文件
特点： 备份数据量大，恢复时需要恢复上次的完全备份 + 最近一次的差异备份
（3）增量备份： 以上一次完全备份或者上一次增量备份的时间为节点，只备份时间节点数据发生变化的部分（通过日志文件备份）
特点： 备份数据量小，占用空间小，备份速度快,恢复时需要一次完整备份 + 所有增量备份依次恢复

- 数据备份的方法
（1）物理冷备份： （使用tar工具直接打包）
（2）专用备份工具： （mysqldump或者mysqlhotcopy）
（3）通过二进制日志进行增量备份
（4）通过第三方工具进行备份

三、mysql数据库的备份操作
（1）物理冷备份与恢复
（使用tar命令直接打包数据库文件夹）

步骤：
1.systemctl stop mysqld （停止数据库服务）
2.创建备份目录：mkdir /backup
3.备份：tar zcvf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data (把/usr/local/mysql/data目录打包)
注意：$(date +%F） 是一个环境变量，date显示当前时间，这个是自动写成当前时间
4.模拟故障：mkdir /bak  
           mv /usr/local/mysql/data/ /bak 
（移走数据库目录，我的数据库目录是这个位置，做的时候要看一下自己的数据库主目录位置在哪里，一般安装的时候会指定主目录）
5.启动数据库：systemctl start mysqld (发现数据库无法正常启动)
6.恢复数据库：mkdir /restore
             tar zxvf /backup/mysql_all-2020-12-08.tar.gz -C /restore/ （解压备份的数据库文件）
             mv /restore/usr/local/mysql/data/ /usr/local/mysql/ （恢复数据库到原来位置）
             systemctl start mysql （发现数据库已经可以正常启动）

 

做的时候要注意自己的mysql主目录的存放位置

（2）使用mysqldump实现备份恢复
作用： 可以将指定的库、表或者全部的库导出为SQL脚本

格式1：备份指定库中的某个库
基本语法结构： mysqldump 选项 库名 表名 > /备份路径/文件名
示例：

先创建一个库，库名叫cless，再在库里创建一个表叫aaa
mysql> create database cless;
Query OK, 1 row affected (0.00 sec)
mysql> use cless;    （切换到库里）
Database changed
mysql> create table aaa(姓名 char(12),年龄 int(12),性别 char(4));
Query OK, 0 rows affected (0.10 sec)
然后退出数据库，进行备份
mysql> exit
Bye
[root@mysql ~]# mkdir /beifen  (先创建备份目录)
[root@mysql ~]# mysqldump -u root -p cless aaa > /beifen/cless-aaa.sql  （一般备份文件都是sql结尾）
Enter password:     （输入数据库密码）
*****把cless库下的aaa表进行备份
[root@mysql ~]# ll /beifen/    （查看备份目录，发现已经有了备份文件）
总用量 4
-rw-r--r-- 1 root root 1807 12月  8 18:25 cless-aaa.sql

格式2：备份一个或者多个完整的库 （包含库中的所有表）
语法结构： mysqldump 选项 --databases 库名1 库名2… > /备份路径/备份文件名
示例：

之前已经创建了备份目录，这里就不进行创建了
[root@mysql ~]# mysqldump -u root -p --databases cless >/beifen/cless.sql
Enter password:     （输入数据库密码）
*****直接把整个cless库都进行备份
[root@mysql ~]# ll /beifen/
总用量 8
-rw-r--r-- 1 root root 1807 12月  8 18:31 cless-aaa.sql
-rw-r--r-- 1 root root 1947 12月  8 18:30 cless.sql

格式3：备份所有的库
语法结构： mysqldump 选项 --all-databases > /备份路径/备份文件名
示例：

[root@mysql ~]# mysqldump -u root -p --all-databases > /beifen/all.sql
Enter password:    （输入数据库密码）
*****把整个mysql数据库中的库全部备份
[root@mysql ~]# ll /beifen/
总用量 652
-rw-r--r-- 1 root root 658319 12月  8 18:33 all.sql
-rw-r--r-- 1 root root   1807 12月  8 18:31 cless-aaa.sql
-rw-r--r-- 1 root root   1947 12月  8 18:30 cless.sql

（3）恢复数据库
基本语法： mysql 选项 库名 表名 < /备份路径/备份文件名

格式1： 恢复库中的删除的某个表
示例：

先进入数据库删除指定的表
mysql> use cless;    （进入cless库）
Database changed
mysql> drop table aaa;    （删除aaa表）
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;     （查看cless库中的所有表，发现已经没有了）
Empty set (0.00 sec)
mysql> exit
Bye
[root@mysql ~]# mysql -u root -p cless  < /beifen/cless-aaa.sql 
Enter password: 
[root@mysql ~]# mysql -u root -p123.com   (再次进入数据库)
mysql> use cless;    （进入cless库中）
Database changed
mysql> show tables;   （查看cless库中所有表，发现aaa表已经恢复）
+-----------------+
| Tables_in_cless |
+-----------------+
| aaa             |
+-----------------+
1 row in set (0.00 sec)

格式2：恢复删除的库
示例：

[root@mysql ~]# mysql -u root -p -e 'drop database cless;'   
（-e 可以直接修改数据库，命令是一样的，这里是删除数据库cless）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'show databases;'   （查看数据库，发现cless没有了）
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| mysql              |
| performance_schema |
| test               |
+--------------------
[root@mysql ~]# mysql -u root -p < /beifen/cless.sql   （恢复库cless）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'show databases;'  （再次查看发现cless库已经恢复了）
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| aaa                |
| cless              |
| mysql              |
| performance_schema |
| test               |
+--------------------+

四、mysql数据库的增量备份与恢复
定义： 备份上一次备份后增量或改变的文件或内容

特点：
（1）备份量小
（2）备份时间短
（3）恢复麻烦（缺点）
（4）需要进行上次的完全备份恢复和完全备份后所有的增量备份的恢复
（5）通过mysql提供的二进制日志间接实现增量备份（实现备份的方法）
Mysql二进制日志对备份的意义（为什么要使用二进制日志进行备份）：
（1）二进制日志保存了所有的更新或者更新数据库的操作
（2）Mysql服务一启动，二进制日志就开始记录
（3）二进制日志达到峰值时或者接收到flush logs 命令后会重新创建新的日志文件，生成二进制文件序列，并且及时把日志存储到安全的位置，形成一个时间段的增量备份
（4）二进制日志是通过max_binlog_size项来设置文件的最大值，只要日志文件超过该设置的大小，就会自动创建新文件
开启二进制日志的步骤：

[root@mysql ~]# cat <<A>> /etc/my.cnf  （往/etc/my.cnf下插入）
> log-bin=/usr/local/mysql/mysql-bin
> A
[root@mysql ~]# tail -3 /etc/my.cnf
# read_rnd_buffer_size = 2M 
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES 
log-bin=/usr/local/mysql/mysql-bin
[root@mysql ~]#  systemctl restart mysqld   （重启mysql服务）
[root@mysql ~]# ls -l /usr/local/mysql/mysql-bin*   （查看日志，日志是以mysql-bin开头的）
-rw-rw---- 1 mysql mysql 120 12月  8 21:31 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  34 12月  8 21:31 /usr/local/mysql/mysql-bin.index
（二进制日志文件的扩展名是一个6 位数字）

五、Mysql数据库备份与恢复案例
（1）一般恢复
（将所有备份的二进制日志内容全部恢复,完整备份和增量备份以及恢复）

[root@mysql ~]# mysql -u root -p123.com  (登录数据库)
mysql> create database client;   （创建一个新的库）
Query OK, 1 row affected (0.00 sec)
mysql> use client;  （切换库）
Database changed
mysql> create table user_info(身份证 char(18) not null,姓名 char(12)not null,性别 char(2),用户id号 char(12) not null,资费 int(10));    （创建表）
Query OK, 0 rows affected (0.00 sec)
mysql> insert into user_info values('000001','张三','男','001','100'); （依次插入数据）
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values('000002','李四','男','002','80');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('000003','王五','女','003','90');
Query OK, 1 row affected (0.00 sec)
mysql> describe user_info;   （查看表的结构）
+-------------+----------+------+-----+---------+-------+
| Field       | Type     | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| 身份证      | char(18) | NO   |     | NULL    |       |
| 姓名        | char(12) | NO   |     | NULL    |       |
| 性别        | char(2)  | YES  |     | NULL    |       |
| 用户id号    | char(12) | NO   |     | NULL    |       |
| 资费        | int(10)  | YES  |     | NULL    |       |
+-------------+----------+------+-----+---------+-------+
5 rows in set (0.00 sec)
mysql> select * from user_info;   （查看表的信息）
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)
mysql> exit   （退出数据库，准备开始完整备份）
Bye
[root@mysql ~]# mkdir /mysql_beifen      （创建备份目录）
[root@mysql ~]# mysqldump -u root -p client user_info > /mysql_beifen/client-user_info-$(date +%F).sql    （使用mysqldump工具进行完整备份）
Enter password: 
[root@mysql ~]# mysqladmin -u root -p flush-logs  （生成二进制日志文件）
Enter password: 
[root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*   （查看二进制日志文件，发现多了一个002的文件）
-rw-rw---- 1 mysql mysql 1249 12月  9 00:28 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  120 12月  9 00:28 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql   68 12月  9 00:28 /usr/local/mysql/mysql-bin.index
[root@mysql ~]# mysql -u root -p123.com   （再次登录数据库）
mysql> use client;   （切换到指定库）
Database changed
mysql> insert into user_info values('000004','流星','男','004','55');  （再次往表里插入数据）
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('000005','飞翔','女','005','66');
Query OK, 1 row affected (0.00 sec)
mysql> exit
Bye
[root@mysql ~]# mysqladmin -u root -p flush-logs  （再次生成二进制日志文件）
Enter password: 
[root@mysql ~]# ls -l /usr/local/mysql/mysql-bin.*  （再次查看发现又多了一个003的日志文件）
-rw-rw---- 1 mysql mysql 1249 12月  9 00:28 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql  679 12月  9 00:34 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  120 12月  9 00:34 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql  102 12月  9 00:34 /usr/local/mysql/mysql-bin.index
[root@mysql ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_beifen/ 
（对第二次备份的002文件复制到创建的mysql备份目录）
（因为只要是开启二进制文件功能就会产生001的日志文件，002是写了五行数据后的日志文件，再次生成的003日志文件什么都没有，也就是后面写的两行数据是写在002上了，003就是又开启了一个新的日志文件）
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （模拟误操作删除表）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看表中信息）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist （因为已经删除直接报错）
下面先恢复完整备份再恢复增量备份
[root@mysql ~]# mysql -u root -p client </mysql_beifen/client-user_info-2020-12-09.sql （进行完整备份）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看，发现已经有了三行数据记录，说明完整备份成功）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
+-----------+--------+--------+-------------+--------+
恢复增量备份
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 |mysql -u root -p  
（使用二进制日志进行恢复，这里使用的是备份目录，也可以直接使用二进制日志的目录进行备份）
（mysqlbinlog  --no-defaults /usr/local/mysql/mysql-bin.000002 | mysql -u root -p）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （发现五行数据都恢复了）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
| 000004    | 流星   | 男     | 004         |     55 |
| 000005    | 飞翔   | 女     | 005         |     66 |
+-----------+--------+--------+-------------+--------+

（2）基于位置的恢复
（用二进制日志内容去恢复到指定的位置）

[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （再次删除表）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表，发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
**********第一种情况：只想恢复到“流星” 的数据，不恢复“飞翔” 的数据
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql  （只恢复一次完整备份，直接恢复到王五的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表发现恢复到了王五的数据）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
+-----------+--------+--------+-------------+--------+
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制日志文件）
（查看二进制日志文件时发现每进行一个操作都有独特的编号，随着操作的增加，编号数在不断增加，这个编号称为“操作ID”）
。。。。。。 （找到插入‘流星’时的数据，发现操作id是203）
# at 203
#201209  0:32:59 server id 1  end_log_pos 345 CRC32 0x322611bc 	Query	thread_id=5	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1607445179/*!*/;
insert into user_info values('000004','流星','男','004','55')
/*!*/;
。。。。。。
[root@mysql ~]# mysqlbinlog  --no-defaults --stop-position='459' /mysql_beifen/mysql-bin.000002 |mysql -u root -p  
（只恢复编号id是459之前的，不包括459，--stop-position='459' ： 表示恢复到这个ID后结束恢复）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
| 000004    | 流星   | 男     | 004         |     55 |
+-----------+--------+--------+-------------+--------+
**********第二种情况： 只想恢复“飞翔”；不恢复“流星”
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （再次删除表)
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查看表发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql （先恢复完整备份）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查询表，发现恢复了三行数据）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
+-----------+--------+--------+-------------+--------+
[root@mysql ~]# mysqlbinlog --no-defaults --start-position='459' /mysql_l_beifen/mysql-bin.000002 |mysql -u root  （恢复从操作id459开始的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （再次查看发现恢复成功）
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
| 000005    | 飞翔   | 女     | 005         |     66 |
+-----------+--------+--------+-------------+--------+

--stop-position： 表示恢复时间节点之前的数据也就是恢复到指定id结束
--start-position：表示恢复时间节点之后的数据也就是从指定id开始恢复

（3）基于时间点的恢复
--stop-datetime： 表示恢复时间节点之前的数据也就是恢复到指定时间节点结束
--start-datetime：表示恢复时间节点之后的数据也就是从指定时间节点开始恢复
查看日志获取时间节点

基于时间点的恢复，只恢复“飞翔”不恢复“流星”
 

基于时间点的恢复，只恢复“飞翔”不恢复“流星”
[root@mysql ~]# mysql -u root -p -e 'drop table client.user_info;' （删除表)
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;' （查看表发现报错）
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
[root@mysql ~]# mysql -u root -p client < /mysql_beifen/client-user_info-2020-12-09.sql   （恢复完整备份）
Enter password: 
[root@mysql ~]# mysqlbinlog --no-defaults /mysql_beifen/mysql-bin.000002 （查看二进制文件获取时间节点）
。。。。。
# at 459
#201209  0:33:14 server id 1  end_log_pos 601 CRC32 0xe7d6d2ad 	Query	thread_id=5	exec_time=0	error_code=0
SET TIMESTAMP=1607445194/*!*/;
insert into user_info values('000005','飞翔','女','005','66')
/*!*/;
。。。。。
[root@mysql ~]# mysqlbinlog --no-defaults --start-datetime='2020-12-09  0:33:14' /mysql_beifen/mysql-bin.000002 | mysql -u root -p  
（恢复从2020年12月9日零点三十三分十四秒开始的数据）
Enter password: 
[root@mysql ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password: 
+-----------+--------+--------+-------------+--------+
| 身份证    | 姓名   | 性别   | 用户id号    | 资费   |
+-----------+--------+--------+-------------+--------+
| 000001    | 张三   | 男     | 001         |    100 |
| 000002    | 李四   | 男     | 002         |     80 |
| 000003    | 王五   | 女     | 003         |     90 |
| 000005    | 飞翔   | 女     | 005         |     66 |
+-----------+--------+--------+-------------+--------+

 at 后面跟的就是操作id
第二行的201209 0:33:14 就是2020年的12月九日 零点的三十山分十四秒
————————————————