MySQL备份和恢复

前言

●数据备份的重要性
在生产环境中，数据的安全性至关重要
任何数据的丢失都可能产生严重的后果
造成数据丢失的原因
程序错误
人为操作错误
运算错误
磁盘故障
灾难（如火灾，地震）和盗窃

一、数据库备份的分类

1.1从物理与逻辑的角度，备份可分为

●物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份
物理备份方法
冷备份（脱机备份）：是在关闭数据库的时候进行的
热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件
温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
●逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

1.2从数据库的备份策略角度，备份可分为

●完全备份：每次对数据库进行完整的备份
●差异备份：备份自从上次完全备份之后被修改过的文件
●增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

二、MySQL完全备份与恢复

●常见的备份方法
物理冷备
备份时数据库处于关闭状态，直接打包数据库文件
备份速度快，恢复时也是最简单的
专业备份工具mysqldump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
进行增量备份，需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup

2.1MySQL完全备份简介

●是对整个数据库、数据库结构和文件结构的备份
●保存的是备份完成时刻的数据库
●是差异备份与增量备份的基础
●每次对数据进行完整的备份，完全备份是增量备份的基础，完全备份保存的是备份完成时刻的数据库
●优点：
备份与恢复操作简单方便
●缺点
数据存在大量的重复
占用大量的备份空间
备份与恢复时间长

2.2数据库完全备份分类

●物理冷备份与恢复
关闭MySQL数据库
使用tar命令直接打包数据库文件夹
直接替换现有MySQL目录即可
●mysqldump备份与恢复
MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本（.sql结尾）
使用命令MySQL导入备份的数据

2.3物理冷备份与恢复步骤

1.在MySQL中先创建school库，再创建info表，写入一行记录（用于验证）
2.先关闭数据库服务，再打包备份

[root@localhost ~]# systemctl stop mysqld.service 
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
mysql_all-2020-08-20.tar.gz

3.将原来的数据移走到备份文件夹中，解压刚才备份的tar包到/opt目录下，再移动到mysql服务的文件夹中。

[root@localhost backup]# mv /usr/local/mysql/data/ /backup/
[root@localhost backup]# tar zxf mysql_all-2020-08-20.tar.gz -C /opt/
[root@localhost backup]# mv /opt/usr/local/mysql/data/ /usr/local/mysql/data

4.重启mysql服务，登录mysql，查看数据是否恢复

[root@localhost mysql]# systemctl start mysqld.service 

2.4mysqldump备份

●mysqldump命令对单个库进行完全备份

mysqldump -u 用户名 -p[密码] [选项] [数据库名] > /备份路径/备份文件名
单库备份的示例
mysqldump -u root -pabc123 info > /backup/info.sql

●mysqldump命令对多个库进行完全备份

mysqldump -u 用户名 -p[密码] [选项] --databases 库名1 [库名2] > /备份路径/备份文件名
多库备份的示例
mysqldump -u root -pabc123 --databases mysql school > /backup/mysql_school.sql

●对所有库进行完全备份

mysqldump -u 用户名 -p[密码] [选项] --all-databases > /备份路径/备份文件名
所有库备份的示例
mysqldump -u root -pabc123  --all-databases > /backup/all.sql

●对库内特定的表进行备份

mysqldump -u 用户名 -p [密码] [选项] 数据库名 表名 > /备份路径/备份文件名
备份表的示例 （和备份多个库不同之处：少了--databases）
mysqldump -u root -pabc123 school info > /backup/school-info.sql

2.5mysqldump恢复

source命令 （在mysql>中）
mysql命令 （在Linux中）
●source恢复的示例

mysql> use school;
mysql> drop table info; 
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /backup/school-info.sql;  ##source引用的必须是绝对路径
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
#恢复表中数据的时候可以恢复到新建的库中。（恢复库的时候，因为备份脚本里写的是先把表删掉，再新建表往里加数据）
mysql> create database stu; 
Query OK, 1 row affected (0.00 sec)
mysql> source /backup/school-info.sql;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+

●使用mysql命令恢复数据示例
格式：

mysql -u 用户名 -p [密码] < 库备份脚本的路径（必须是有--databases选项备份的）
mysql -uroot -pabc123 < /backup/school.sql

三、MySQL增量备份与恢复

###MySQL的日志文件记录了详细的操作过程，千万不能随便删###

3.1MySQL增量备份

●使用mysqldump进行完全备份存在的问题
备份数据中有重复数据
备份时间与恢复时间过长
●MySQL增量备份是自上一次备份后增加/变化的文件或者内容
●特点
没有重复数据，备份量不大，时间短
恢复需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

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

3.2MySQL数据库增量恢复

●一般恢复
将所有备份的二进制日志内容全部恢复
●断点恢复
基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作
可以基于精准的位置跳过错误的操作
基于时间点恢复
跳过某个发生错误的时间点实现数据恢复

3.3增量恢复的方法

●一般恢复
mysqlbinlog [–no-defaults] 增量备份文件 | mysql -u 用户名 -p
●基于位置的恢复
恢复数据到指定位置（到错误操作前的最后一次正确操作）
mysqlbinlog --stop-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码
从指定的位置开始恢复数据（跳过错误操作后的第一次正确操作）
mysqlbinlog --start-position=‘操作id’ 二进制日志 | mysql -u 用户名 -p密码

实验流程：

1.vim /etc/my.cnf ##添加开启二进制日志文件功能

2.重新启动mysql服务，到/usr/local/mysql/data/目录下查看初始二进制日志文件是否成功生成

[root@localhost ~]# systemctl restart mysqld.service 
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             performance_schema
ib_buffer_pool  ib_logfile1  mysql-bin.000001  sys
ibdata1         ibtmp1       mysql-bin.index

3.在school库中的info表中写入初始数据

mysql> insert into info(name,address) values ('yi','nj');
Query OK, 1 row affected (0.01 sec)
mysql> insert into info(name,address) values ('er','bj');
Query OK, 1 row affected (0.01 sec)
[root@localhost ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000001  ##对以base64编码显示的文件内容进行解码后查看

4.进行增量备份（接下来在mysql中输入的命令都在mysql-bin.000002文件中）

[root@localhost ~]# mysqladmin -u root -pabc123 flush-logs
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# ls
auto.cnf        ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema
ibdata1         ibtmp1       mysql-bin.000002  school

5.在mysql中进行三条操作，其中中间的为误操作，待会儿增量备份时需要跳过； 再flush-logs生成新的增量备份二进制日志文件mysql-bin.000003

[root@localhost ~]# mysqladmin -u root -pabc123 flush-logs
6.把二进制日志文件mysql-bin.000002进行转码后重定向到指定文件中，查看刚才的三条操作是否成功记录

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bk.txt

7.删除school库，先进行一般恢复，把完整备份的数据进行还原

mysql> drop database school;
[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001 | mysql -uroot -pabc123

8.进行基于位置的恢复
[root@localhost data]# vim /opt/bk.txt ##查看刚才解码重定向的文件

#at 345
#200821 12:06:37 server id 1  end_log_pos 389 CRC32 0x569220e7  Write_rows: tabl
e id 219 flags: STMT_END_F
###INSERT INTO `school`.`info`
###SET
###@1=3
###@2='san'
#at 389
#200821 12:06:37 server id 1  end_log_pos 420 CRC32 0x05845431  Xid = 14
COMMIT/*!*/;
...省略内容
BEGIN
/*!*/;
#at 559      ##--stop-position 恢复数据到指定位置559
#200821 12:06:54 server id 1  end_log_pos 611 CRC32 0x70f6b0c0  Table_map: `scho
ol`.`info` mapped to number 219
#at 611
#200821 12:06:54 server id 1  end_log_pos 654 CRC32 0x5de7d4b2  Delete_rows: tab
le id 219 flags: STMT_END_F
###DELETE FROM `school`.`info`
###WHERE
###@1=1
###@2='yi'
#at 654     ##--start-position 从指定的位置654开始恢复数据
#200821 12:06:54 server id 1  end_log_pos 685 CRC32 0x375241b7  Xid = 15
COMMIT/*!*/;
...省略内容
#at 876
#200821 12:07:05 server id 1  end_log_pos 919 CRC32 0xf854814d  Write_rows: tabl
e id 219 flags: STMT_END_F
###INSERT INTO `school`.`info`
###SET
###@1=4
###@2='si'
#at 919

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='559' mysql-bin.000002 | mysql -uroot -pabc123;   ##恢复数据到指定位置559
[root@localhost data]# mysqlbinlog --no-defaults --start-position='654' mysql-bin.000002 | mysql -uroot -pabc123;   ##从指定位置654开始恢复数据

●基于时间点的恢复
从日志开头截止到某个时间点的恢复

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密码

在刚才基于位置恢复实验的基础上把id=3、id=4的记录删除，再进行基于时间点的恢复实验

...省略内容
#at 559      
#200821 12:06:54 server id 1  end_log_pos 611 CRC32 0x70f6b0c0  Table_map: `scho
ol`.`info` mapped to number 219       
#at 611
#200821 12:06:54 server id 1  end_log_pos 654 CRC32 0x5de7d4b2  Delete_rows: tab
le id 219 flags: STMT_END_F        ##--stop-datetime 从日志开头截止到200821 12:06:54的恢复
###DELETE FROM `school`.`info`
###WHERE
###@1=1
###@2='yi'
#at 654    
#200821 12:06:54 server id 1  end_log_pos 685 CRC32 0x375241b7  Xid = 15
COMMIT/*!*/;
...省略内容
#at 876
#200821 12:07:05 server id 1  end_log_pos 919 CRC32 0xf854814d  Write_rows: tabl
e id 219 flags: STMT_END_F          ##--start-datetime 从200821 12:07:05到日志结尾的恢复
###INSERT INTO `school`.`info`
###SET
###@1=4
###@2='si'
#at 919

[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-08-21 12:06:54' mysql-bin.000002 | mysql -uroot -pabc123   
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-08-21 12:07:05' mysql-bin.000002 | mysql -uroot -pabc123