MySQL备份与还原

MySQL数据库备份恢复

数据库备份类型

从物理与逻辑角度可以分为以下两种

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

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

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

常见的备份方法

物理冷备

• 被焚烧数据库处于关闭状态，直接打包数据库文件
• 备份速度快，恢复时是最简单的

专业备份工具mydump或mysqlhotcpoy

• mysqldump常用的逻辑备份工具
• mysqlhotcpoy仅用于备份MyISAM和ARCHIVE表

通过启用二进制日志进行增量备份

• 进行增量备份需要刷新二进制日志

第三方工具备份

• 免费的MySQL热备份软件Percona XtraBackup

数据库的完全备份操作

1.部署实验环境

create database auth;
use auth;
create table users (user_name char(20),user_passwd char(50));
insert into users values('zhangsan',password('123456'));
insert into users values('lisi',password('123456'));

 2.物理冷备份与恢复

（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;'

（3）恢复数据库

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# cd /backup/
[root@localhost backup]# tar zxvf mysql_2023-06-08.tar.gz

[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;'

..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 

增量备份与恢复

启用二进制日志功能

（1）修改主配置文件

[root@localhost ~]# vim /etc/my.cnf 
在[mysqld]模块中添加
log-bin=mysql-bin
server-id=1
binlog_format = MIXED

[root@localhost ~]# systemctl restart mysqld

 （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

向表中添加数据（逐个添加）

use auth;
insert into users values('aaa','123456');
insert into users values('bbb','123456');
insert into users values('ccc','123456');
insert into users values('ddd','123456');
insert into users values('eee','123456');
insert into users values('fff','123456');

[root@localhost data]# mysqladmin -u root -p flush-logs
mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

模拟误操作将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;'  ##查看表，已经不存在

常规恢复操作

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

再恢复增量备份
[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：不读取任何选项文件，其后需要直接跟日志文件或开始停止位置

基于位置的恢复

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

恢复完全备份
[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql 

查看日志内容
[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

恢复到指定的地方
[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;'   

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

恢复指定范围内的数据
[root@localhost mysql]# mysqlbinlog --no-defaults --start-position='521' --stop-position='729' mysql-bin.000002  | mysql -u root -p

基于时间点恢复

恢复到指定的时间
[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2019-03-08 21:01:55' mysql-bin.000002  | mysql -u root -p


从指定的时间开始恢复
[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2019-03-08 21:01:53' mysql-bin.000002  | mysql -u root -p


恢复指定时间范围内的数据
[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