MySQL---备份与恢复

备份与恢复

物理冷备

  物理备份指对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份有可分为脱机备份（冷备份）和联机备份（热备份）。

• 冷备份
  在关闭数据库时进行的备份操作，能够较好的保证数据库的完整性
• 热备份
  在数据库运行状态中的进行的操作，这种备份方式依赖于数据库的日志文件

逻辑备份与恢复

  逻辑备份是指数据库的逻辑组件（如表等数据库对象）的备份。
  从数据库的备份策略角度，备份可以分为完全备份、差异备份和增量备份。

• 完整性备份
  所有数据均备份
• 增量备份
  在完整性备份的基础上，备份相对于前一次备份被修改过的备份
• 差异备份
  也是依赖完整性备份，备份相对完整备份后被修改过的部分

mysqldump完全备份

语法：
##单数据库备份
mysqldump -u 用户名 -p [密码] [选项] [数据库名] > /备份路径/备份文件名
##多数据库备份
mysqldump -u 用户名 -p [密码] [选项] --databases 数据库名1 ... > /备份路径/备份文件名
##数据库表备份
mysqldump -u 用户名 -p [密码] [选项]  数据库名 表名 > /备份路径/备份文件名
##数据库完全备份
mysqldump -u 用户名 -p [密码] [选项] --all-databases > /备份路径/备份文件名
--opt 规范提醒说明 表示要完整备份数据库了 可以不要

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mask               |
| mysql              |
| performance_schema |
| sys                |
| wxy                |
+--------------------+
6 rows in set (0.00 sec)
##开始备份
##单数据库备份
[root@localhost opt]# mysqldump -u root -p mask > /opt/mask.sql
Enter password: 
[root@localhost opt]# mysqldump -u root -p wxy > /opt/wxy.sql
Enter password: 
[root@localhost opt]# ls
 mask.sql  wxy.sql  rh
##多数据库备份
[root@localhost opt]# mysqldump -u root -p --databases mask wxy > /opt/mask_wxy.sql
Enter password: 
[root@localhost opt]# ls
mask.sql  mask_wxy.sql  rh  wxy.sql
##完整备份
[root@localhost opt]# mysqldump -u root -p --opt --all-databases > /opt/all-data.sql
Enter password: 
[root@localhost opt]# ls
all-data.sql  mask.sql  mask_wxy.sql   rh  wxy.sql
##数据库表备份
[root@localhost opt]# mysqldump -u root -p mask score > /opt/mask--score.sql
Enter password: 
[root@localhost opt]# ls
all-data.sql  mask--score.sql  mask.sql  mask_wxy.sql    rh  wxy.sql

source方式恢复

语法
mysql> source 库备份脚本绝对路径

[root@localhost opt]# mysql -u root -p
Enter password: 
mysql> create database maskbak;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mask               |
| maskbak            |
| mysql              |
| performance_schema |
| sys                |
| wxy                |
+--------------------+
7 rows in set (0.00 sec)

mysql> use maskbak;
Database changed
##把刚刚单数据库备份的数据恢复到随意一个数据库中
mysql> source /opt/mask.sql
mysql> use maskbak;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_maskbak |
+-------------------+
| address           |
| car               |
| score             |
| temp              |
| temp2             |
| user              |
| wuliao            |
| www               |
+-------------------+
8 rows in set (0.00 sec)
##多数据备份数据库的恢复，会创建原来备份的数据库恢复原来表中的数据或创建的数据库名已存在会覆盖现有的同名表中的数据
mysql> source /opt/mask_wxy.sql

mysql方式恢复

语法：
mysql -u 用户名 -p [密码] < 库备份脚本文件的路径

##删除两个之前备份过的库
mysql> drop database mask;
Query OK, 8 rows affected (0.02 sec)

mysql> drop database wxy;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| maskbak            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> exit
Bye
##恢复
[root@localhost ~]# mysql -u root -p < /opt/mask_wxy.sql
Enter password: 
[root@localhost ~]# mysql -u root -p 
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mask               |
| maskbak            |
| mysql              |
| performance_schema |
| sys                |
| wxy                |
+--------------------+
7 rows in set (0.00 sec)

增量备份与启用二进制文件进行恢复

增量备份

  使用mysqldump进行完全备份存在的问题，如：备份数据中有重复数据、备份时间与恢复时间过长。
  增量备份是自上一次备份后增加/变化的文件或者内容，特点如下：

• 没有重复数据，备份量不大，时间短
• 恢复需要上次完全备份及完全备份之后所有的增量备份才
  能恢复，而且要对所有增量备份进行逐个反推恢复

  MySQL没有提供直接的增量备份方法可通过MySQL提供的二进制日志间接实现增量备份,MySQL二进制日志对备份的意义在于：

• 二进制日志保存了所有更新或者可能更新数据库的操作
• 二进制日志在启动MySQL服务器后开始记录，并在文件达到max_binlog_size所设置的大小或者接收到flush logs命令后重新创建新的日志文件
• 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间段的增量备份

增量恢复方法

• 一般恢复

mysqlbinlog [--no-defaults] 增量备份文件（二进制文件） |mysql -u 用户名 -p

• 基于位置的恢复

恢复数据到指定位置
msyqlbinlog --stop-position=’操作 id’ 二进制文件日志 |mysql -u 用户名 -p 密码
从指定的位置开始恢复数据
msyqlbinlog --start-position=’操作 id’ 二进制文件日志 |mysql -u 用户名 -p 密码

• 基于时间点的恢复

从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime=’年-月-日 小时：分钟：秒’ 二进制文件日志 |mysql -u 用户名 -p 密码
从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时：分钟：秒’ 二进制文件日志 |mysql -u 用户名 -p 密码
从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime=’年-月-日 小时：分钟：秒’  ----stop-datetime=’年-月-日 小时：分钟：秒’ 二进制文件日志|mysql -u 用户名 -p 密码

需要开启二进制日志文件

##在mysql配置文件中开启二进制日志文件功能
[root@localhost data]# vim /etc/my.cnf
##在[mysqld]下添加这条，表示开启二进制文件功能，mysql-bin表示文件名以mysql-bin 开头
[mysqld]
....
 log-bin=mysql-bin
...
[root@localhost data]# systemctl restart mysqld
[root@localhost data]# ls /usr/local/mysql/data
 ...   mysql-bin.000001

一般恢复

mysqlbinlog [--no-defaults] 增量备份文件（二进制文件） |mysql -u 用户名 -p

基于位置和时间点的恢复

  在有些情况下一般恢复无法恢复数据库到我们希望恢复的状态，我们将使用基于位置和时间点的恢复，这样可以恢复到更具体的位置，以达到我们期望的状态。
  下面将模拟误操作删除一些数据后，再进行数据的恢复。

mysql> insert into score values (14,'zhaoliu',68,'wufa');
Query OK, 1 row affected (0.00 sec)
##误操作删除了lisi
mysql> delete from score where name='lisi';
Query OK, 1 row affected (0.00 sec)
mysql> insert into score values (15,'heiba',55,'hongkong');
Query OK, 1 row affected (0.01 sec)

mysql> select * from score;
+----+----------+-------+----------+
| id | name     | score | address  |
+----+----------+-------+----------+
|  1 | zhangsan |  90.5 | nanjing  |
| 13 | wangwu   |  60.0 | xiamen   |
| 14 | zhaoliu  |  68.0 | wufa     |
| 15 | heiba    |  55.0 | hongkong |
+----+----------+-------+----------+
4 rows in set (0.00 sec)
##刚好进行了一次增量备份
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)

###进行基于位置的二进制日志文件恢复
[root@localhost data]# cd /usr/local/mysql/data/
##源二进制文件为完全看不懂的乱码，为了能看到相关操作使用mysqlbinlog后重定向到opt下
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 > /opt/bk01.txt    ##现在为看的懂部分的文件，再进行下面的转换就可以看懂更多
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001 > /opt/bk01.txt 
##经过转换之后，我们就可以看到里面的操作了
[root@localhost data]# vim /opt/bk01.txt      以下内容就为我们刚刚做的操作，at后面的为位置，下面的为时间
...
# at 348
#200819 19:09:16 server id 1  end_log_pos 404 CRC32 0xaa30b995  Write_rows: table id 101 flags: STMT_END_F
### INSERT INTO `mask`.`score`
### SET
###   @1=14
###   @2='zhaoliu'
###   @3=68.0
###   @4='wufa'
....
# at 629
#200819 19:10:29 server id 1  end_log_pos 685 CRC32 0xc1132899  Delete_rows: table id 101 flags: STMT_END_F
### DELETE FROM `mask`.`score`
### WHERE
###   @1=2
###   @2='lisi'
###   @3=59.0
###   @4='fujiang'
....
# at 910
#200819 19:12:44 server id 1  end_log_pos 968 CRC32 0x080f4e1e  Write_rows: table id 101 flags: STMT_END_F
### INSERT INTO `mask`.`score`
### SET
###   @1=15
###   @2='heiba'
###   @3=55.0
###   @4='hongkong'
##基于位置，我使用这条语句报错，最后就基于时间的方法恢复吧
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='348' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p

[root@localhost data]# mysqlbinlog --no-defaults --start-position='910' /usr/local/mysql/data/mysql-bin.000001 | mysql -uroot -p
Enter password: 

##基于日期
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-08-19 19:10:29' /usr/local/mysql/data/mysql-bin.000001 |mysql -u root -p
Enter password: 
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-08-19 19:12:44' /usr/local/mysql/data/mysql-bin.000001 |mysql -u root -p
Enter password: 

总结

  在基于位置点的恢复中，stop-position是误操作上一条正确操作的位置点；而在基于时间点的恢复中，stop-datetime是误操作的那条的时间点。