【MySQL数据库的恢复和备份】

最新推荐文章于 2024-03-22 20:27:03 发布
最新推荐文章于 2024-03-22 20:27:03 发布
阅读量263 收藏
点赞数
文章标签： 数据库 mysql java
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_43403889/article/details/129178915
版权

MySQL数据库的恢复和备份

一、数据备份的重要性

1.备份的主要目的是灾难恢复。

2.在生产环境中，数据的安全性至关重要。

3.任何数据的丢失都可能产生严重的后果。

4.造成数据丢失的原因：

  • 程序错误
  • 人为操作错误
  • 磁盘故障
  • 灾难（如火灾）和盗窃

5.注意：以下所讲的备份文件都是放在MySQL服务所在的主机上为例，但在实际应用中所有备份文件理应放在另一台主机上为好。

二、数据库备份的分类和备份策略

2.1 数据库备份的分类

1）物理备份
物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。
物理备份方法：
  • 冷备份(脱机备份) ：是在关闭数据库的时候进行的
  • 热备份(联机备份) ：数据库处于运行状态，依赖于数据库的日志文件
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作
2）逻辑备份
逻辑备份：对数据库逻辑组件（如：表等数据库对象）的备份。
  • 即以sql语句的形式，把库、表结构、表数据保存下来。

2.2 数据库的备份策略

  • 完全备份（全量备份）：每次对数据库进行完整的备份。
  • 差异备份：备份自从上次完全备份之后被修改过的文件。
  • 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。
    在这里插入图片描述

三、常见的备份方法

物理冷备： （完全备份）
  • 备份时数据库处于关闭状态，直接打包数据库文件
  • 备份速度快，恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy （完全备份，逻辑备份）
  • mysqldump常用的逻辑备份工具 （导出为sql脚本）
  • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份 （增量备份）
  • 进行增量备份，需要刷新二进制日志
第三方工具备份
  • 免费的MySQL热备份软件Percona XtraBackup（阿里云的工具：dts，支持热迁移）

四、MySQL完全备份介绍

4.1 什么是完全备份

  • 完全备份是对整个数据库、数据库结构和文件结构的备份
  • 保存的是备份完成时刻的数据库
  • 是差异备份与增量备份的基础

4.2 完全备份的优缺点

优点：
  • 备份与恢复操作简单方便
缺点：
  • 数据存在大量的重复
  • 占用大量的备份空间
  • 备份与恢复时间长

4.3 完全备份的方法

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

五、完全备份与恢复 操作演示

5.1 物理冷备份

5.1.1 完全备份
  • 先关闭数据库，之后打包备份
# 关闭数据库
service mysql stop
# 创建备份目录
mkdir /usr/local/mysql/backfile/
# 打包数据库文件。/usr/local/mysql/data 为数据库文件存放目录
tar -zcvf /usr/local/mysql/backfile/mysql_all_$(date +%F).tar /usr/local/mysql/data
#查看tar包内的文件
tar -tvf /usr/local/mysql/backfile/mysql_all_xxxx-xx-xx.tar
5.1.2 完全恢复
  • 将数据库迁移到另一台主机，测试完全恢复
  • 即将备份的文件替换另一主机data文件夹

5.2 逻辑备份（使用mysqldump工具）

  • mysqldump是常用的逻辑备份工具
  • mysqldump可以将指定的库、表导出为SQL脚本
5.2.1 mysqldump完全备份
1）完全备份一个或多个完整的库（包括库中所有的表）
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 导出的就是数据库脚本文件
./mysqldump -u root -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql
# 备份所有库
./mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
# 示例:
# 完全备份一个库test
./mysqldump -u root -p --databases test > /usr/local/mysql/backsql/test.sql
# 完全备份多个库sys库和mysql库
./mysqldump -u root -p --databases sys mysql > /usr/local/mysql/backsql/sys-mysql.sql
# 完全备份所有库
./mysqldump -u root -p --all-databases > /usr/local/mysql/backsql/all.sql

在这里插入图片描述

2) 完全备份指定库中的部分表
  • 注意：只备份表，sql语句中只有对表的操作，没有对库的操作。恢复时需要人为确认库存在。
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 使用“-d"选项，说明只保存数据库的表结构
# 不使用“-d”选项，说明表数据也进行备份
./mysqldump -u root -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql
# 示例：
# 完全备份test库中的user
./mysqldump -u root -p test user > /usr/local/mysql/backsql/test-user.sql
3）查看备份文件
  • 备份文件中保存的是sql语句。即以sql语句的形式，把库、表结构、表数据保存下来
    在这里插入图片描述
5.2.2 mysqldump完全恢复
  • 先启动mysql
service mysql start
1）恢复数据库
  • 先删除数据库，之后进行恢复。
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# "-e"选项，用于指定连接MySQL后执行的命令，命令执行完后自动退出
# 删除数据库test
./mysql -u root -p -e 'drop database test;'
# 查看当前有哪些数据库
./mysql -u root -p -e 'SHOW DATABASES;'
# 恢复数据库test
# 重定向输入库文件
./mysql -u root -p < /usr/local/mysql/backsql/test.sql
# 查看当前有哪些数据库
./mysql -u root -p -e 'SHOW DATABASES;'

在这里插入图片描述

2）恢复数据表
  • 当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 删除test库中的user表
./mysql -u root -p -e 'drop table test.user;'
# 查看test库中的表，已无user表
./mysql -u root -p -e 'show tables from test;'     
# 恢复test库中的user表
# 重定向导入备份文件，必须指定库名，且目标库必须存在
./mysql -u root -p test < /usr/local/mysql/backsql/test-user.sql

在这里插入图片描述

六、MySQL增量备份与恢复

6.1 MySQL增量备份介绍

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

6.2 MySQL增量备份的方法

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

6.3 MySQL数据库增量恢复

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

七、增量备份与恢复 操作演示

7.1 增量备份

1、开启二进制日志功能
vim /etc/my.cnf
[mysqld]
# 开启二进制日志。如果使用相对路径，则保存在/usr/local/mysql/data/目录下
log-bin=mysql-bin
# 可选，指定二进制日志(binlog)的记录格式为MIXED
binlog_format = MIXED
server-id = 1
 ​
# 进行mysql服务重启
service mysql restart
 ​
-------------- 以下是注释 -----------------
#二进制日志(binlog)有3种不同的记录格式: 
STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT
STATEMENT (基于SQL语句)：记录修改的sql语句。高并发的情况下，记录操作的sql语句时可能顺序会有错误，导致恢复数据时，数据丢失或有误差。效率高，但数据可能有误差。
ROW(基于行)：记录每一行数据，准确，但恢复时效率低。
MIXED(混合模式)：正常情况下使用STATEMENT，高并发的情况下会智能地切换到ROW。

在这里插入图片描述

2、可每周对数据库或表进行完全备份（参考以上所讲的完全备份）
3、可每天进行增量备份操作，生成新的二进制日志文件（例如mysql-bin.000002）
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 生成新的二进制日志
./mysqladmin -u root -p flush-logs

在这里插入图片描述

4、插入新数据，以模拟数据的增加或变更

在这里插入图片描述

5、再次生成新的二进制日志文件（例如mysql -bin.000003）

在这里插入图片描述

6、查看二进制日志文件的内容
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 生成的二进制日志保存至备份文件夹下
cp /usr/local/mysql/data/mysql-bin.000002 /usr/local/mysql/backbinlog/
./mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/backbinlog/mysql-bin.000002
# --base64-output=decode-rows：使用64位编码机制去解码并按行读取
# -v：显示详细内容

在这里插入图片描述

7.2 增量恢复

一般恢复
  • 将所有备份的二进制日志内容全部恢复
基于位置恢复
  • 数据库在某一时间点可能既有错误的操作也有正确的操作
  • 可以基于精准的位置跳过错误的操作
基于时间点恢复
  • 跳过某个发生错误的时间点实现数据恢复
7.2.1 一般恢复
将所有备份的二进制日志内容全部恢复
1、模拟丢失更改的数据的恢复步骤
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 登录mysql数据库
./mysql -u root -p
# 使用test库
use test;
# 删除今天新增加的两条数据
delete from user where id=2;
delete from user where id=3;
​# 增量恢复（今天新增加的两条数据记录保存在mysql-bin.000002日志中）
./mysqlbinlog --no-defaults /usr/local/mysql/backbinlog/mysql-bin.000002 | ./mysql -u root -p
​# 查看表中数据
./mysql -u root -p -e 'select * from test.user;'

在这里插入图片描述

2、模拟丢失表中所有数据的恢复步骤。
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 删除整个user表
./mysql -u root -p -e 'drop table test.user;'
# 查看test库中的表
./mysql -u root -p -e 'show tables from test;'
# 先完全恢复历史数据
./mysql -u root -p test < /usr/local/mysql/backsql/test-user.sql
# 再进行增量恢复今天新增的2条数据（mysql-bin.000002日志中保存了今天新增加的两条数据记录）
./mysqlbinlog --no-defaults /usr/local/mysql/backbinlog/mysql-bin.000002 | ./mysql -u root -p
# 查看表中数据
./mysql -u root -p -e 'select * from test.user;'

在这里插入图片描述

7.2.2 断点恢复
向user表中插入4条数据，之后刷新二进制日志，移动前一个日志：
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 刷新日志，生成新的二进制日志
./mysqladmin -u root -p flush-logs
#将前一个日志复制到/bak/目录下
cp /usr/local/mysql/data/mysql-bin.000003 /usr/local/mysql/backbinlog/
# 将二进制文件内容重定向到binlog.txt文件中 ​
./mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/backbinlog/mysql-bin.000003 > /usr/local/mysql/backbinlog/mysql-bin.000003.txt

在这里插入图片描述

1）基于位置的断点恢复
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 从某一个位置点开始恢复，一直到日志结尾
./mysqlbinlog --no-defaults --start-position='位置点' 文件名 | ./mysql -u root -p
# 从日志开头，一直恢复到某一个位置点前结束
./mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | ./mysql -u root -p
# 从某一个位置点开始恢复，一直到某一个位置点前结束
./mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | ./mysql -u root -p

  • 示例1：只想恢复"周五"和"吴六"（即恢复最后两行，第6-7行）
    在这里插入图片描述
    在这里插入图片描述
    在这里插入图片描述

  • 示例2：只想恢复"孙三"（即只恢复第4行）；首先还是删除4-7行数据然后查看位置点
    在这里插入图片描述
    在这里插入图片描述

  • 示例3：只想恢复"李四"和"周五"（即恢复第5-6行）；首先还是删除4-7行数据然后查看位置点
    在这里插入图片描述
    在这里插入图片描述

1）基于时间点的断点恢复
注意：日期必须是"yyyy-MM-dd HH:mm:ss"的格式。
# 先进入mysql的bin目录下
cd /usr/local/mysql/bin/
# 从某一个时间点开始恢复，一直到日志结尾
./mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | ./mysql -u root -p
# 从日志开头，一直恢复到某一个时间点前结束
./mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | ./mysql -u root -p
# 从某一个时间点开始恢复，一直到某一个位置点前结束
./mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime='结束时间点' 文件名 | ./mysql -u root -p
  • 示例：只想恢复"李四"和"周五"（即恢复第5-6行）
    在这里插入图片描述
    在这里插入图片描述

八、使用脚本备份

使用脚本每周六进行一次完全备份，之后每天进行增量备份。
  • 如果数据量很大，可以一周做2次完全备份，比如星期一和星期六各一次。
增量备份减少磁盘空间的占用，但恢复起来比较麻烦。需要一个一个恢复。
  • 一般使用crontab在凌晨进行备份
获取最新的二进制文件名称
  • 查看索引文件，可获取最新的二进制文件
tail -1 /usr/local/mysql/data/mysql-bin.index
完全备份脚本（每周六完全备份一次）：
# 编写脚本（此处以备份test库为例，也可所有库一起完全备份）
vim /home/root/back-sql.sh
#!/bin/bash
source /etc/profile
/usr/local/mysql/bin/mysqldump -u root -proot --databases test > /usr/local/mysql/backsql/test_$(date +%F).sql

# 为文件授权
chmod 777 /home/root/back-sql.sh
# 定时任务执行脚本
crontab -e
# 每周六凌晨1点备份一次
00 1 * * 6 /home/root/back-sql.sh
增量备份脚本（每天凌晨2点备份一次）：
# 编写脚本（此处以备份test库为例）
vim /home/root/back-binlog.sh
#!/bin/bash
source /etc/profile
# 使用sed命令打印二进制日志文件最后一行内容作为filename变量的值
filename=$(sed -n '$p' /usr/local/mysql/data/mysql-bin.index | awk -F '/' '{print $2}')
# 将二进制日志移动到备份目录下，并将移动后的文件名称加上日期
mv /usr/local/mysql/data/$filename  /usr/local/mysql/backbinlog/binlog_$(date +%F)
# 生成新的二进制日志文件
/usr/local/mysql/bin/mysqladmin -u root -proot flush-logs

# 为文件授权
chmod 777 /home/root/back-binlog.sh
# 定时任务执行脚本
crontab -e
# 每天凌晨2点备份一次
00 2 * * * /home/root/back-binlog.sh
就这样灬而已
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL数据库备份和恢复
04-10
MySQL 数据库备份 和 恢复
MySQL数据库备份恢复实现代码
09-08
本文将深入探讨MySQL数据库的备份和恢复实现，以及如何通过代码来完成这些操作。 首先，我们来看一下MySQL数据库的备份过程。备份是预防数据丢失的重要手段，它可以通过mysqldump工具来实现。mysqldump是一个实用...
MySQL8备份恢复（一）
贺浦力特的博客
04-05 1270
mysql 备份恢复 navicat mysqldump
MySQL-8-备份
qq_43297415的博客
05-30 897
MySQL多种备份方式冷备热备物理备份binlog备份
MYSQL8.0数据库备份——完整备份，增量备份，差异备份
最新发布
Huihuizaizi的博客
03-22 1519
MYSQL8.0数据库备份——完整备份，增量备份，差异备份
windows下误删数据库恢复（MySQL8.0）
Anlger的博客
02-28 1924
windows下误删数据库恢复
MySQL备份与恢复
lpx1249115962的博客
01-25 1484
*
MySQL数据库的备份恢复.md
06-17
本文详细描述了MySQL数据库的备份恢复操作，从模拟数据库丢失到备份到恢复数据库，都有详细步骤介绍，适合刚学习的小白
mysql数据库备份和恢复工具
11-23
mysql数据库备份和恢复工具以及源码 。
Mysql数据库备份恢复测试报告-xtrabackup.docx
03-29
通过已经备份的数据库文件对数据库进行恢复测试，并输出测试报告，本次测试对数据库的全量及增量备份进行恢复，并在恢复完成后验证数据是否正常，恢复时间是否与备份时间相一致。...
MySQL数据备份与恢复
Hard work results, technology is willing to dream
03-20 581
数据备份与恢复
数据库备份和还原bak文件
qq_54949432的博客
07-06 3615
sql server数据库，还原bak文件，数据库备份还原
MySQL Drop Database之后的恢复测试
weixin_33781606的博客
12-20 651
工具介绍工具的名字叫做: undrop-for-innodb代码地址: https://github.com/twindb/undrop-for-innodb官方文档： https://recovery.twindb.com/介绍工具的一篇ppt http://www.slideshare.net/akuzminsky/undrop-for-innodb当MySQL 执...
MySQL8.0 通过data文件和SQL语句恢复数据库
yhw912的博客
03-09 3755
MySQL 通过data文件恢复data里的数据库
31误删数据后除了跑路，还能怎么办？
sdaujsj1的博客
03-09 1072
文章目录31 | 误删数据后除了跑路，还能怎么办？误删行误删库 / 表延迟复制备库rm 删除数据小结上期问题时间误删行误删库 / 表延迟复制备库rm 删除数据小结上期问题时间 31 | 误删数据后除了跑路，还能怎么办？ 今天我要和你讨论的是一个沉重的话题：误删数据。 在前面几篇文章中，我们介绍了 MySQL 的高可用架构。当然，传统的高可用架构是不能预防误 删数据的，因为主库的一个 drop table 命令，会通过 binlog 传给所有从库和级联从库，进而导致整 个集群的实例都会执行这个命令。 虽然
Windows下实现Mysql8.0自动备份数据库
祖哥的博客
05-06 2375
相信大家在项目开发过程中，都会下意识地想要对数据库的数据进行备份，数据库的备份还是很重要的 ，可以防止数据丢失进行恢复，避免造成不必要的损失和麻烦，数据库的备份有很多种方法，小编在这里用mysqldump脚本来备份数据库 1. 首先我们创建一个文本文档，在文本文档里面复制以下内容 @echo off set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%" "C...
MYSQL mysqldump的实现原理
GOGOGO!
05-21 987
对于MySQL的备份，可分为以下两种： <font color=blue size=3>蓝色字体</font> 1. 冷备 2. 热备 其中，冷备，顾名思义，就是将数据库关掉，利用操作系统命令拷贝数据库相关文件。而热备指的是在线热备，即在不关闭数据库的情况下，对数据库进行备份。实际生产中基本上都是后者。 关于热备，也可分为两种方式： 1. 逻辑备份 2. 物理...
mysql8更换数据库还原_Mysql8-在mysql命令下数据库的备份与还原方法
weixin_36160268的博客
01-19 761
我们平时对只有几M或十几M的mysql数据库的备份与还原一般是直接通过管理工具phpmyadmin来完成，但若是数据库大到上百M或是几百M，那用phpmyadmin的备份和还原功能就不太可取了，不单是备份过程时间长，最大的问题可能就是备份存在不完整的可能性。那有啥办法即能超快的备份数据库又不会丢数据吗？答案是有的，那就是利用mysql的命令来备份数据，不光备份速度快，而且还不会丢数据，今天就简单的...
MySQL8.0数据库备份
qq_26069551的博客
09-28 4023
MySQL8.0数据库导出方法 数据库导出： 1.首先命令行模式进入mysql的bin目录 2.执行语句： mysqldump -uroot -p --databases jxshop >D:\jxshop.sql （mysqldump -u用户名 -p --databases 数据库名 > 保存的路径加上数据库名.sql） 回车，输入密码...
mysql数据库备份和恢复
06-01
MySQL数据库备份和恢复是非常重要的，以下是一些备份和恢复的方法： 1. 使用mysqldump备份和恢复数据：mysqldump是MySQL自带的备份工具，可以将整个数据库或者单个表备份成SQL文件，也可以通过SQL文件恢复数据。 备份命令：`mysqldump -u username -p database_name > backup_file.sql` 恢复命令：`mysql -u username -p database_name < backup_file.sql` 2. 使用MySQL Workbench备份和恢复数据：MySQL Workbench是MySQL官方提供的图形界面工具，可以通过它进行备份和恢复操作。 备份方法：在MySQL Workbench中选择要备份的数据库或表，右键点击选择“导出数据”，然后选择导出文件类型和路径即可。 恢复方法：在MySQL Workbench中选择要恢复的数据库或表，右键点击选择“导入数据”，然后选择导入文件类型和路径即可。 3. 使用第三方备份工具：可以使用第三方备份工具，如XtraBackup、mysqldumper、Percona等工具进行备份和恢复操作。 无论使用哪种备份和恢复方法，都需要注意备份的频率和备份文件的存储位置，以及恢复时要确保备份文件的完整性和可用性。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值