05-Mysql备份与恢复

物理备份：对数据库操作系统的物理文件（如数据库文件，日志文件等）的备份

物理备份方法：

冷备份（防脱备份）：是在关闭数据库的时候进行的

热备份（联机备份）：数据库处于运行状态，依赖于数据库的日志文件

温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作

逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份

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

完全备份：每次对数据库进行完整的备份

差异备份：备份自从上次完全备份之后被修改过的文件 缺点：对空间的消耗太大

增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

position：位置

1.1部署实验环境

1：物理冷备份与恢复

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# mkdir /backup

[root@localhost ~]# cd /usr/local/
[root@localhost local]# tar zcvf /backup/mysql_$(date +%F).tar.gz mysql/
[root@localhost local]# cd /backup/
[root@localhost backup]# ls
mysql_2023-06-08.tar.gz

（2）模拟故障

[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'drop database auth;'
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'
[root@localhost backup]# cd /usr/local/
[root@localhost local]# mv mysql/ mysql.bak

[root@localhost local]# cd /backup/
[root@localhost backup]# mv mysql /usr/local/
[root@localhost backup]# systemctl start mysqld
[root@localhost backup]# mysql -u root -p -e 'select * from auth.users;'

2.1：mysqldump备份与恢复

在服务启动状态下使用

（1）导出数据

[root@localhost ~]# mysqldump -u root -p auth users > /opt/auth-users.sql       \\导出数据表
[root@localhost ~]# mysqldump -u root -p auth > /opt/auth.sql         \\导出数据库
[root@localhost ~]# mysqldump -u root -p --opt --all-databases >/opt/all-data.sql     ##备份所有库

注释：
--opt：优化执行速度

（2）导入表

 如果已经存在数据库，可以将表导入到任何一个现有的数据库
[root@localhost ~]# mysql -u root -p -e 'create database test;'  
[root@localhost ~]# mysql -u root -p test < /opt/auth-users.sql   ##导入到其他库
[root@localhost ~]# mysql -u root -p auth < /opt/auth-users.sql   ##导入到原始所在的库
[root@localhost ~]# mysql -u root -p -e 'show tables from test;' 

（3）导入库

如果备份了完整的数据库，可以在mysql中没有该库信息的情况下直接导入该库
[root@localhost ~]# mysql -u root -p -e 'drop database auth;'  
[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -u root -p -e 'create database auth;' 
[root@localhost ~]# mysql -u root -p auth</opt/auth.sql

[root@localhost ~]# mysql -u root -p -e 'show databases;' 
[root@localhost ~]# mysql -uroot -p -e 'select * from auth.users;'

（4）全库还原

保证至少已经创建了这些库

[root@localhost ~]# mysql -u root -p </opt/all-data.sql 

3.1：增量备份与恢复

1：启用二进制日志功能

（1）修改主配置文件在[mysqld]模块中添加（随便那个位置） 添加成功后记得重启

log-bin=mysql-bin
server-id=1
binlog_format = MIXED

（2）生成新的日志文件

[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# mysqladmin -u root -p flush-logs

（3）查看日志内容

mysqlbinlog --no-defaults mysql-bin.000002
注意：如果sql语句被加密了，可以使用以下指令
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

2：向表添加数据（多添加一些数据）

3：模拟误操作将auth.users表删掉

[root@localhost data]# mysql -u root -p -e 'drop table auth.users;'
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'  ##查看表，已经不存在

4：常规恢复操作

（1）先恢复完全备份

[root@localhost data]# mysql -u root -p auth </opt/auth.sql 
[root@localhost data]# mysql -u root -p -e 'select * from auth.users;'   ##查看表，已经存在，但数据不全

（2）再恢复增量备份

[root@localhost mysql]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002
注意：要先查看日志内容，确定是需要恢复的数据

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -p
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'    ##查看表的内容

注释：
--no-defaults：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置

5：基于位置的恢复

（1）模拟误操作

[root@localhost mysql]# mysql -uroot -p -e 'drop table auth.users;'
[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'   ##没有了数据

（2）恢复完全备份

[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql 

（3）查看日志内容

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
从日志中分析哪些数据还没有恢复，找出想要恢复到哪个数据的操作ID
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p

（4）恢复到指定的地方

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-position='521' mysql-bin.000002 | mysql -u root -p      ##恢复到521之前的一个数据，不包含521

[root@localhost mysql]# mysql -u root -p -e 'select * from auth.users;'  

（5）从指定的位置向后开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' mysql-bin.000002 | mysql -u root -p     ##从521之后开始恢复，包含521

（6）恢复指定范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

6：基于时间点恢复

（1）恢复到指定的时间

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p

（2）从指定的时间开始恢复

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p

（3）恢复指定时间范围内的数据

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p