MySQL备份与恢复 — 多种示例步骤

于 2024-07-27 17:09:24 发布
阅读量1.3k 收藏 18
点赞数 38
分类专栏： 数据库管理与高可用 文章标签： 运维 linux 服务器 sql mysql 数据库 数据库系统
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_65551023/article/details/140737806
版权

目录

备份的概念

重要性

备份分类

常见的备份方法

示例

冷备份

打包备份

模拟故障

恢复备份

热备份

导出

导出表

导出库

导出所有数据库

导入

导入表

导入库

导入所有数据库

增量备份与恢复

二进制日志

开启功能

生成日志

查看日志

查看加密内容

测试

恢复数据

模拟故障

恢复步骤

基于位置恢复

模拟故障

通过位置id来精确恢复数据

只指定开始id

只指定结束id

基于时间恢复

通过时间恢复数据

只指定开始时间

只指定结束时间

备份的概念

重要性

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

备份分类

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

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

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

  • 完全备份：每次对数据库进行完整的备份
  • 差异备份：只备份自从上次完全备份之后被修改过的文件
    • 如果要恢复数据，先恢复最近一次的完全备份，再恢复最近一次的差异备份
    • 如果做了多次差异备份，磁盘空间消耗大
  • 增量备份：只备份自从上次完全备份或增量备份后被修改的文件
    • 如果要恢复数据，先恢复最近一次的完全备份，再逐个恢复这个完全备份下一次的增量备份，直到最近一次的增量备份
    • 如果做了多次增量备份，磁盘空间消耗小

常见的备份方法

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

示例

本博客的实验示例，因为要模拟故障，需要多次恢复快照，所以在安装完MySQL后，创建一个快照以便恢复

冷备份

登入MySQL后，创建一个名为auth的数据库，进入到该数据库后，创建一个用于测试的表

create database auth;
use auth;
create table users (user_name char(20) not null,user_passwd char(50) default '');

添加两行数据，实现以下效果，tom的密码被加密，jerry的密码明文显示

mysql> insert into users values ('tom', password('123'));
mysql> insert into users values ('jerry', '123');
mysql> select * from users;
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| tom       | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| jerry     | 123                                       |
+-----------+-------------------------------------------+

使用quit退出

mysql> quit

打包备份

实现冷备份需要mysql处于关闭状态，所以这里关闭mysqld服务

并且创建出存放备份文件的目录

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

假如我们想要备份整个MySQL的程序和数据文件，那就备份MySQL的整个安装目录

使用tar命令将mysql整个安装目录打包成tar包，存放在/bak/ 目录下，命名为mysql_当天日期.tar.gz

+%F：表示以数字形式显示年月日

mysql/：表示需要打包的目录

[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@localhost local]# tar zcvf /bak/mysql_$(date +%F).tar.gz mysql/

然后cd到/bak/ 目录下就可以看到生成的tar包

[root@localhost local]# cd /bak/
[root@localhost bak]# ls
mysql_2024-07-26.tar.gz

模拟故障

这里模拟误操作

首先开启数据库服务

[root@localhost bak]# systemctl start mysqld

通过-e选项，将auth数据库删除，然后查询该库下的表，可以看到已经显示ERROR，查询不到了

到这里就完成模拟故障了

[root@localhost bak]# mysql -uroot -ppwd123 -e "drop database auth;"
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost bak]# mysql -uroot -ppwd123 -e "select * from auth.users;"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1146 (42S02) at line 1: Table 'auth.users' doesn't exist

恢复备份

因为备份做的是冷备份，所以恢复备份的时候也需要MySQL是关闭状态

[root@localhost bak]# systemctl stop mysqld

解压备份的tar包，将旧的mysql安装目录重命名备份起来，以防其中的数据可能还会使用

再将解压后的备份目录移动到安装mysql的目录下，实现恢复

[root@localhost bak]# tar zxvf mysql_2024-07-26.tar.gz
[root@localhost bak]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src
[root@localhost local]# mv mysql/ mysql.bak
[root@localhost local]# mv /bak/mysql /usr/local/mysql

恢复完成后，启动mysqld服务，测试恢复的效果

[root@localhost local]# systemctl start mysqld

临时登录mysql的执行查询语句，可以看到，故障之前的数据被查询出来了，完成恢复

[root@localhost local]# mysql -uroot -ppwd123 -e "select * from auth.users;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-----------+-------------------------------------------+
| user_name | user_passwd                               |
+-----------+-------------------------------------------+
| tom       | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |
| jerry     | 123                                       |
+-----------+-------------------------------------------+

热备份

在MySQL不停机的运行状态下进行备份操作

导出

mysqldump是mysql的一个导出工具，导出的文件实际上是一个脚本文件，运行这个脚本相当于在MySQL中输入了一系列的SQL语句，帮你把原本的对象创建出来

导出表

利用mysqldump来导出指定库中指定表的所有数据到指定的目录下

这里使用指定用户和密码，指定数据库和要在该库中导出的表 > 导出位置

[root@localhost ~]# mysqldump -uroot -ppwd123 auth users > /opt/auth-users.sql
导出库

这里直接去掉表名就是导出指定的数据库到指定位置

[root@localhost opt]# mysqldump -uroot -ppwd123 auth > /opt/auth.sql
导出所有数据库

如果要导出所有的数据库，不用再指定库或表，而是使用--all-databases选项，再指定导出位置就行了

  • --opt：优化执行速度
[root@localhost opt]# mysqldump -uroot --opt -ppwd123 --all-databases > /opt/all-data.sql

导入

导入表

导出的表可以导入到任何的数据库里，这里首先创建出一个名为"test"的数据库，用于导入的示例

直接使用mysql命令 指定要导入的数据库，然后使用重定向输入的符号，再指定要导入的文件绝对路径，就可以完成导入

[root@localhost opt]# mysql -uroot -ppwd123 -e "create database test;"
[root@localhost opt]# mysql -uroot -ppwd123 test < /opt/auth-users.sql
导入库

把指定的表换成库名就可以导入数据库了

  • 如果要导入的库已经存在，脚本文件会在该库内添加原有的表和其他各种信息
  • 如果要导入的库不存在，脚本文件会找不到该库，需要先创建出来该库，再执行
[root@localhost opt]# mysql -uroot -ppwd123 auth < /opt/auth.sql
导入所有数据库

使用mysql命令，不需要指定库或表，直接使用重定向输入，然后指定导入的sql文件即可

[root@localhost opt]# mysql -uroot -ppwd123 < /opt/all-data.sql

增量备份与恢复

增量备份（incremental backup）是备份的一个类型，是指在一次完全备份或上一次增量备份后，以后每次的备份只需备份与前一次相比增加或者被修改的文件。

二进制日志

开启功能

修改mysql配置文件，在[mysqld]模块中添加以下内容，然后重启mysql服务

[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
server-id=1
binlog_format=MIXED
[root@localhost ~]# systemctl restart mysqld
  • log-bin：日志生成的名称
  • server-id：指定服务器的标识，如果要构建一个主从架构的环境，两个设备的标识不能相同
  • binlog_format：指定复制方式
  • MIXED：混合复制方式
生成日志

此时我们cd进入mysql的data目录下

可以看到生成了两个文件：mysql-bin.000001、mysql-bin.index

如果我们此时使用mysqladmin命令刷新日志文件

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

会发现在data目录下又生成了一个mysql-bin.000002文件

  • flush-logs：把当前缓冲区的SQL语句写入到现有的日志文件中（000001），然后生成一个新的日志文件（000002），准备接受新的日志信息
查看日志

使用mysqlbinlog命令查看二进制日志文件，但是此时我们并没有在mysql中执行SQL语句，所以日志文件中也没有记录相关的信息

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000001
查看加密内容

在选项中使用base64来解码加密的行

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000001
测试

为了验证效果，逐个添加新的数据，并生成一个日志文件

先登录进mysql，然后添加一些数据，然后退出mysql，再刷新日志，此时日志信息就被记录到了000002的文件中

使用mysqlbinlog命令查看二进制日志文件（000002），就可以在日志文件中看到刚刚执行的SQL语句了

[root@localhost data]# mysql -uroot -ppwd123
mysql> use auth
mysql> insert into users values('aaa','123456');
mysql> insert into users values('bbb','123456');
mysql> insert into users values('ccc','123456');
mysql> insert into users values('ddd','123456');
mysql> insert into users values('eee','123456');
mysql> insert into users values('fff','123456');
mysql> quit
[root@localhost data]# mysqladmin -u root -ppwd123 flush-logs
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=DECODE-ROWS mysql-bin.000002

恢复数据

恢复数据的前提是出现了故障，所以我们先模拟出现了故障

模拟故障

将测试恢复的表先删除掉

[root@localhost data]# mysql -u root -ppwd123 -e 'drop table auth.users;'
恢复步骤

先恢复完整备份，然后再通过二进制日志文件逐个恢复增量备份

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

因为该类型日志文件是二进制的不能直接使用，所以先通过mysqlbinlog命令查询出来日志中的内容，通过管道符导入给mysql

然后可以使用select语句查询一下恢复的表，看看表中有没有被写入恢复的数据

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000002 | mysql -u root -ppwd123
[root@localhost data]# mysql -u root -ppwd123 -e 'select * from auth.users;'

基于位置恢复

假如在操作数据库时，误删除了一行数据，不要直接insert去添加，而是通过二进制日志文件的位置id来实现精确恢复

模拟故障

再次删除users表，然后先恢复出上一次的完全备份

[root@localhost data]# mysql -u root -ppwd123 -e 'drop table auth.users;'
[root@localhost mysql]# mysql -u root -p auth < /opt/auth.sql

再查看日志内容，从日志中分析哪些数据还没有恢复，找出想要恢复到哪个数据的操作id

日志中带# at 数字 的行表示数据操作的位置id，可以根据开始id和结束id恢来复单独的数据，但是结束id必须包含COMMIT的字段，代表数据存储到硬盘中，否则数据只是被存储在缓冲区内，并没有永久保存

在下方的内容中，开始id就是587，结束id就是732

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
# at 587
#240726 22:44:45 server id 1  end_log_pos 701 CRC32 0xae04e527     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1722005085/*!*/;
insert into users values('bbb','123456')
/*!*/;
# at 701
#240726 22:44:45 server id 1  end_log_pos 732 CRC32 0x5e9d9312     Xid = 11
COMMIT/*!*/;
# at 732

通过位置id来精确恢复数据

同时指定了开始id和结束id，mysqlbinlog会把两个id中间的操作全部执行一遍

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

只指定开始id

如果只指定了开始id，代表从开始id的位置，往后的指令全部执行一遍，直到文件末尾

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

只指定结束id

如果只指定了结束id，代表从日志开头到结束id的位置，中间的指令全部执行一遍

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

基于时间恢复

在二进制日志文件中，还可以根据记录的时间来恢复数据

比如下方内容中的#240726 22:44:45和#240726 22:45:20 是开始时间和结束时间

把两个时间点改为：2024-07-26 22:44:45和2024-07-26 22:45:20

利用这种格式，就可以使用mysqlbinlog命令进行基于时间的数据恢复

[root@localhost mysql]# mysqlbinlog --no-defaults mysql-bin.000002
# at 587
#240726 22:44:45 server id 1  end_log_pos 701 CRC32 0xae04e527     Query    thread_id=3    exec_time=0    error_code=0
SET TIMESTAMP=1722005085/*!*/;
insert into users values('bbb','123456')
/*!*/;
# at 701
#240726 22:44:45 server id 1  end_log_pos 732 CRC32 0x5e9d9312     Xid = 11
COMMIT/*!*/;
# at 732

通过时间恢复数据

同时指定了开始时间和结束时间，mysqlbinlog会把两个时间点中间的操作全部执行一遍

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2024-07-26 22:44:45' --stop-datetime='2024-07-26 22:45:20' mysql-bin.000002 | mysql -u root -p

只指定开始时间

如果只指定了开始时间，代表从开始时间的位置，往后的指令全部执行一遍，直到文件末尾

[root@localhost mysql]# mysqlbinlog --no-defaults --start-datetime='2024-07-26 22:44:45' mysql-bin.000002 | mysql -u root -p

只指定结束时间

如果只指定了结束时间，代表从日志开头到结束时间的位置，中间的指令全部执行一遍

[root@localhost mysql]# mysqlbinlog --no-defaults --stop-datetime='2024-07-26 22:45:20' mysql-bin.000002 | mysql -u root -p

变成我
关注
  • 38
    点赞
  • 踩
  • 18
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
MySQL备份详细步骤
DN金猿的博客
05-12 1025
information_schema库，performance_schema库，sys库和mysql.gtid_executed不会被备份，其他所有的库表都会备份。原文链接：https://blog.csdn.net/weixin_52951697/article/details/124717883。此时登录host2的数据库可以看到两个数据库中的内容相同。此时在/backup/MySQL下编写备份还原部分内容。此时在数据库中就可以查看被删除的数据库已经库表。将需要备份的数据库如下备份，后删除再导入即可。
mysql备份与恢复
微光
08-12 389
mysqlbinlog --no-defaults --start-position=‘1815’ --stop-position=‘2089’ mysql-bin.000003 |mysql -uroot -p123456 #恢复从位置为1815开始到位置为2089为止。mysqlbinlog [–no-defaults]–start-datetime=‘年-月-日 时:分:秒’–stop-datetime=‘年-月-日 时:分:秒’’ #查看ux01表。...
MySQL备份与恢复
qq_41270538的博客
03-06 1163
MySQL备份与恢复
mysql 按小时备份_MySQL备份与还原详细过程示例
weixin_27303545的博客
02-07 254
MySQL备份与还原详细过程示例一、MySQL备份类型1.热备份、温备份、冷备份 (根据服务器状态)热备份：读、写不受影响；温备份：仅可以执行读操作；冷备份：离线备份；读、写操作均中止；2.物理备份与逻辑备份 (从对象来分)物理备份：复制数据文件；逻辑备份：将数据导出至文本文件中；3.完全备份、增量备份、差异备份 (从数据收集来分)完全备份：备份全部数据；增量备份：仅备份上次完全备份或增量备份以后...
简述mysql完全备份过程_MySQL备份与还原详细过程示例
weixin_34226837的博客
01-19 299
MySQL备份与还原详细过程示例一、MySQL备份类型1.热备份、温备份、冷备份 (根据服务器状态)热备份：读、写不受影响；温备份：仅可以执行读操作；冷备份：离线备份；读、写操作均中止；2.物理备份与逻辑备份 (从对象来分)物理备份：复制数据文件；逻辑备份：将数据导出至文本文件中；3.完全备份、增量备份、差异备份 (从数据收集来分)完全备份：备份全部数据；增量备份：仅备份上次完全备份或增量备份以后...
MySQL数据库的备份与恢复
在熙丶的博客
06-19 3800
很细很细的步骤讲解，全是干货！！！
MySQL备份与恢复（一）
m0_59432158的博客
11-08 391
前言 备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审计等。 而备份、恢复中，日志起到了很重要的作用。 一、MySQL日志 MySQL 的日志默认保存位置为 /usr/local/mysql/data 1、配置文件 vim /etc/my.cnf [mysqld] ##错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启 log-error=/usr/local/mysql/data/mysql_...
Mysql运维篇（三） MySQL备份与恢复
weixin_41633010的博客
01-28 1317
路虽远行则将至，事虽难做则必成！ 滴水穿石，聚沙成塔！ 感谢CSDN各位大佬的经典博文，在迷茫时能够拨云见日,指点迷津，让我继续一路前行！ 如有侵权，请留言，我及时删除
Mysql 备份与还原
weixin_44473708的博客
07-17 512
③ 增量备份 *** 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份以上次完整备份或上次增量备份的时间为时间点，仅备份期间内的数据变化，因而备份的数据量小，占用空间小，备份速度快。① 完全备份 *** 每次对数据进行完整备份，即对整个数据库、数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础完全备份的备份与恢复操作都非常简单方便，但是数据存在大量的重复并且会占用大量的磁盘空间，备份的时间也很长。物理备份又可以成为冷备份（脱机备份）、热备份（连接备份）和温备份。
mysql数据库备份还原实战操作步骤-mysqldump备份还原
05-06
在本文中，我们将介绍 MySQL 数据库备份还原实战操作步骤，包括使用 mysqldump 工具进行备份和恢复数据库的详细指令。 一、备份数据库 mysqldump 是 MySQL 提供的一种备份工具，使用 mysqldump 可以将 MySQL ...
MySQL备份与恢复之保证数据一致性(5)
09-10
在MySQL备份与恢复过程中，保证数据一致性是一个复杂而关键的任务，涉及到多个层面的考虑，包括备份策略、事务处理和日志管理。理解这些概念并正确执行备份和恢复流程，可以有效地保护数据的完整性和可用性，减少因...
浅谈mysql 针对单张表的备份与还原
09-09
总的来说，MySQL针对单张表的备份与还原是一个涉及多个步骤的过程，包括选择合适的备份工具、配置用户权限、执行备份和验证，以及在必要时进行数据恢复。这个过程需要谨慎操作，确保数据的安全性和完整性。在实际...
C# 备份还原mysql数据库示例工具
01-19
本示例工具专注于C# Winform环境下对MySQL数据库的备份与还原功能，为用户提供了一个直观、简便的界面，非常适合初学者和软件集成。接下来，我们将详细讨论相关知识点。 首先，我们来看C#编程语言。C#是由微软开发...
Mysql自动备份与还原方法
09-11
### MySQL自动备份与还原方法详解 #### 一、MySQL自动备份方法 在现代数据库管理中，数据的安全性和完整性至关重要。为了防止数据丢失或损坏，定期进行数据库备份是必不可少的步骤。下面详细介绍如何设置MySQL...
使用易备数据备份软件，践行虚拟机最佳备份方案
sanyuan7783的博客
07-26 908
新的关键业务应用程序有助于为容器和容器化工作负载赋予更大动力。但是，在可预见的未来，虚拟机的应用仍会普遍存在。容器和虚拟机可以很好地协同工作，适用于大多数关键的 IT 基础设施。因此，备份虚拟机及其包含的数据、服务和应用程序仍然是企业至关重要的任务。本文以 VMware vSphere 虚拟机为例，介绍虚拟机备份的最佳实践方法。
AccessLog| 一款开源的日志分析系统
ClkLog的博客
07-26 458
系统采用Java语言、搭配OLAP数据库，涵盖基本web日志分析，同时提供性能分析，帮助高效运维
DNS反向解析、多域名解析、时间服务器相关配置
qq_74793290的博客
07-24 493
DNS反向解析、多域名解析、时间服务器
Linux 普通用户启动Nginx使用80端口，小于1024的端口
最新发布
y393016244的博客
07-26 122
在root用户下执行。
MySQL数据库备份恢复实战：导出与恢复操作详解
实验4 数据库备份与恢复实验着重于实践操作，旨在让学生掌握数据库管理和维护的关键技能。本实验分为多个部分，涵盖数据导出、备份与恢复的过程。 首先，实验通过MySQL的官方示例数据库employees-db-1（）来引导...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值