MYSQL 5.7 数据库备份与恢复

最新推荐文章于 2024-08-06 00:15:00 发布
最新推荐文章于 2024-08-06 00:15:00 发布
阅读量4.1k 收藏 22
点赞数 3
分类专栏： mysql 文章标签： 数据库 mysql 数据库备份 数据库恢复 逻辑备份
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/shengjie87/article/details/108302127
版权

数据库备份与恢复

一、MySQL 数据库备份

1、概述

备份的主要目的是灾难恢复，备份还可以测试应用、回滚数据修改、查询历史数据、审 计等。

2、备份的重要性

在企业中数据的价值至关重要，数据保障了企业业务的正常运行。因此，数据的安全性 及数据的可靠性是运维的重中之重，任何数据的丢失都可能对企业产生严重的后果。通常情 况下造成数据丢失的原因有如下几种：
程序错误。
人为操作错误。
运算错误。
磁盘故障。
灾难（如火灾、地震）和盗窃。

3、备份类型

3.1 从物理与逻辑的角度分类

数据库备份可以分为物理备份和逻辑备份。物理备份是对数据库操作系统的物理文件 （如数据文件、日志文件等）的备份。这种类型的备份适用于在出现问题时需要快速恢复的 大型重要数据库。 物理备份又可以分为冷备份（脱机备份）、热备份（联机备份）和温备份。

  • 冷备份：在数据库关闭状态下进行备份操作。
  • 热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件。
  • 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。 逻辑备份是对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（CREATE DATABASE，CREATE TABLE 语句）和内容（INSERT 语句或分隔文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同的机器 体系结构上重新创建数据
3.2 从数据库的备份策略角度分类

从数据库的备份策略角度，数据库的备份可分为完全备份、差异备份和增量备份

  • 完全备份：每次对数据进行完整的备份，即对整个数据库、数据库结构和文件结构的备
    份，保存的是备份完成时刻的数据库，是差异备份与增量备份的基础。完全备份的备份与恢
    复操作都非常简单方便，但是数据存在大量的重复，并且会占用大量的磁盘空间，备份的时 间也很长。
  • 差异备份：备份那些自从上次完全备份之后被修改过的所有文件，备份的时间节点是从上次完整备份起，备份数据量会越来越大。恢复数据时，只需恢复上次的完全备份与最近的一次差异备份。
  • 增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次增量备份的时间为时间点，仅备份这之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复时，需要从上一次的完整备份开始到最后一次增量备份之的所有增量依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。

4、备份方法

MySQL 数据库的备份可以采用很多种方式，如直接打包数据库文件（物理冷备份）、 专用备份工具（mysqldump）、二进制日志增量备份、第三方工具备份等。

  1. 物理冷备份 物理冷备份时需要在数据库处于关闭状态下，能够较好地保证数据库的完整性。物理冷 备份一般用于非核心业务，这类业务一般都允许中断，物理冷备份的特点就是速度快，恢复 时也是最为简单的。通常通过直接打包数据库文件夹（本章中的数据库文件夹位于 /usr/local/mysql/data）来实现备份
  2. 专用备份工具 mysqldump 或 mysqlhotcopy
    mysqldump 程序和 mysqlhotcopy 都可以做备份。mysqldump 是客户端常用逻辑备份 程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的 SQL 语句。它可以转 储一个到多个 MySQL 数据库，对其进行备份或传输到远程 SQL 服务器。mysqldump 更为 通用，因为它可以备份各种表。mysqlhotcopy 仅适用于某些存储引擎。
  3. 通过启用二进制日志进行增量备份 MySQL 支持增量备份，进行增量备份时必须启用二进制日志。二进制日志文件为用户 提供复制，对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份（包 含自上次完全备份或增量备份以来发生的数据修改），需要刷新二进制日志。
  4. 通过第三方工具备份 Percona XtraBackup 是一个免费的 MySQL 热备份软件，支持在线热备份 Innodb 和 XtraDB，也可以支持 MySQL 表备份，不过 MyISAM 表的备份要在表锁的情况下进行。

5、备份操作

5.1 完全备份与恢复
5.1.1 物理冷备份与恢复

物理冷备份一般用 tar 命令直接打包数据库文件夹，而在进行备份之前需要使用 “systemctl stop mysqld”命令关闭 mysqld 服务。

  1. 备份数据库 创建一个/backup 目录作为备份数据存储路径，使用 tar 创建备份文件。整个数据库文 件夹备份属于完全备份。
[root@server ~]# systemctl stop mysqld
[root@server ~]# mkdir /backup

[root@server ~]# tar zcfP /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data

[root@server ~]# cd /backup/
[root@server backup]# ls
mysql_all-2020-08-30.tar.gz
  1. 恢复数据库 执行下面操作将数据库文件/usr/local/mysql/data/转移至 bak 目录下，模拟故障。
[root@server backup]# mkdir /bak

[root@server backup]# mv /usr/local/mysql/data/ /bak

[root@server backup]# tar zxvfP mysql_all-2020-08-30.tar.gz 

[root@server backup]# systemctl start mysqld
[root@server backup]# netstat -antp | grep 3306
tcp6       0      0 :::3306                 :::*                    LISTEN      8646/mysqld
5.1.2 mysqldump 备份与恢复

通过 mysqldump 命令可以将指定的库、表或全部的库导出为 SQL 脚本，便于该命令 在不同版本的 MySQL 服务器上使用。例如，当需要升级 MySQL 服务器时，可以先使用 mysqldump 命令将原有库信息导出，然后直接在升级后的 MySQL 服务器中导入即可

5.1.1.1备份数据库

使用 mysqldump 命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要 结合 Shell 的“>”重定向输出操作，命令格式如下所示

语法：
备份指定库中的部分表：
mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名

备份一个或多个完整的库（包括其中所有的表）
mysqldump [选项] 库名 [表名 1] [表名 2] … > /备份路径/备份文件名

备份 MySQL 服务器中所有的库。
mysqldump [选项] --all-databases > /备份路径/备份文件名

其中，常用的选项包括“-u”、“-p”，分别用于指定数据库用户名、密码

备份school库中的info表

[root@server backup]# mysqldump -uroot -p school info > mysqlinfo.sql
Enter password: 
[root@server backup]# ls
mysql_all-2020-08-30.tar.gz  mysqlinfo.sql

备份school库

[root@server backup]# mysqldump -uroot -p --databases school > school.sql
Enter password: 
[root@server backup]# ls
mysql_all-2020-08-30.tar.gz  mysqlinfo.sql  school.sql

若需要备份整个 MySQL 服务器中的所有库，应使用格式 3。当导出的数据量较大的时 候，可以添加“–opt”选项以优化执行速度。

备份所有数据库

[root@server backup]# mysqldump -uroot -p --opt --all-databases > all-database.sql
Enter password: 
[root@server backup]# ls
all-database.sql  mysql_all-2020-08-30.tar.gz  mysqlinfo.sql  school.sql

备份多个库，中间用空格分隔，例如备份school和class库：

mysqldump -uroot -p --databases school class > school.sql
5.1.1.2恢复数据库

使用 mysqldump 命令导出的 SQL 备份脚本，在需要恢复时可以通过 mysql 命令对其进行导入操作

语法：
mysql [选项] [库名] [表名] < /备份路径/备份文件名

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定 库名，且目标库必须存在

先创建schoolbak库，然后把原来school的info表恢复到schoolbak库中，-e表示在mysql里执行的语句，语句用引号引起来

mysql> create database schoolbak;
Query OK, 1 row affected (0.00 sec)

[root@server backup]# mysql -u root -p schoolbak < mysqlinfo.sql 
Enter password: 

[root@server backup]# mysql -u root -p -e 'show tables from schoolbak;'
Enter password: 
+---------------------+
| Tables_in_schoolbak |
+---------------------+
| info                |
+---------------------+

若备份文件中已经包括完整的库信息，则执行导入操作时无须指定库名
恢复school库

mysql> drop database school;
Query OK, 8 rows affected (0.02 sec)

[root@server backup]# mysql -uroot -p < school.sql 
Enter password: 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| school2            |
| schoolbak          |
| sys                |
+--------------------+
7 rows in set (0.00 sec)
5.2 增量备份与恢复

使用 mysqldump 进行完全备份，备份的数据中有重复数据，备份时间与恢复时间过长。 而增量备份就是自上一次备份之后增加或改变的内容

5.2.1 特点

与完全备份不同，增量备份没有重复数据，备份量不大，时间短；但其恢复麻烦，需 要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐 个反推恢复。MySQL 没有提供直接的增量备份办法，可以通过 MySQL 提供的二进制日志 （binary logs）间接实现增量备份

5.2.2 二进制日志

二进制日志保存了所有更新数据库的操作。二进制日志在启动 MySQL 服务器后开始记 录，并在文件达到二进制日志所设置的最大值或者接收到 flush logs 命令后重新创建新的日志文件，生成二进制文件序列，并及时把这些日志保存到安全的存储位置，即可完成一个时 间段的增量备份。使用 max_binlog_size 配置项可以设置二进制日志文件的最大值，如果二进制文件的大小超过了 max_binlog_size，它就会自动创建新的二进制文件。
要进行 MySQL 的增量备份，首先要开启二进制日志功能。开启 MySQL 的二进制日志 功能的实现方法有很多种，最常用的是在 MySQL 配置文件的 mysqld 项下加入“log-bin=/ 文件路径/文件名”前缀，如 log-bin=/usr/local/mysql/mysql-bin，然后重启 MySQL 服务就可以在指定路径下查看二进制日志文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如 mysql-bin.000001。

5.2.2.1 开启二进制日志

进入mysql配置文件，加入log-bin=master-bin开启二进制文件，名字的前缀自己定义，这里叫master-bin，配置完成后需要重启mysql服务，重启之后默认会产生第一个二进制日志：master-bin.000001

[root@server data]# vim /etc/my.cnf
[mysqld]
log-bin=master-bin
[root@server data]# systemctl restart mysqld
[root@server data]# ls
auto.cnf        ib_logfile1        mysql               schoolbak
ib_buffer_pool  ibtmp1             performance_schema  sys
ibdata1         master-bin.000001  school
ib_logfile0     master-bin.index   school2
5.2.3 配置操作

常用的增量恢复的方法有三种：一般恢复、基于位置的恢复、基于时间点的恢复

  • 一般恢复：将所有备份的二进制日志内容全部恢复
语法：
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p 密码
  • 基于位置的恢复：数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准
语法：
恢复数据到指定位置：
mysqlbinlog --stop-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码

从指定的位置开始恢复数据
mysqlbinlog --start-position='操作 id' 二进制日志 |mysql -u 用户名 -p 密码
  • 基于时间点的恢复：跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可 以分成三种情况。
语法：
从日志开头截止到某个时间点的恢复：
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 密码

查看info表的信息

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)
5.2.3.1 完全备份

先进行一次完全备份，以当前日期命名
一次完全备份之后刷新日志，表示切割日志，这时会出现第二个二进制日志master-bin.000002

[root@server data]# mysqldump -uroot -p school info > info-$(date +%F).sql
Enter password: 
[root@server data]# mysqladmin -uroot -p flush-logs <---刷新日志
Enter password: 
[root@server data]# ls
auto.cnf        ibdata1      ib_logfile1  info-2020-08-31.sql  master-bin.000002  mysql               school   schoolbak
ib_buffer_pool  ib_logfile0  ibtmp1       master-bin.000001    master-bin.index   performance_schema  school2  sys

在info表中插入两条记录学生chenqiang和hewei

mysql> insert into info (name,score,address,hobby) values('chenqiang',65,'nanjing',2);
Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,score,address,hobby) values('hewei',85,'shanghai',1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 12 | chenqiang | 65.00 | nanjing  |     2 |
| 13 | hewei     | 85.00 | shanghai |     1 |
+----+-----------+-------+----------+-------+
11 rows in set (0.00 sec)
5.2.3.2 增量备份

这个时候再次刷新日志，会产生一个新的日志master-bin.000003，而之前插入的两条记录保存在了master-bin.000002日志文件里

[root@server data]# mysqladmin -uroot -p flush-logs
Enter password: 
[root@server data]# ls
auto.cnf        ib_logfile0  info-2020-08-31.sql  master-bin.000003  performance_schema  schoolbak
ib_buffer_pool  ib_logfile1  master-bin.000001    master-bin.index   school              sys
ibdata1         ibtmp1       master-bin.000002    mysql              school2
5.2.3.3 完全备份恢复

首先删除表模拟故障

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

把之前完全备份的sql文件导入到数据库中，可以看到恢复到了插入学生chenqiang和hewei之前的记录

[root@server data]# mysql -uroot -p school < info-2020-08-31.sql 
Enter password: 

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)
5.2.3.4 增量备份恢复

进行增量备份恢复，学生chenqiang和何威恢复了

[root@server data]# mysqlbinlog --no-defaults /usr/local/mysql/data/master-bin.000002 | mysql -uroot -p
Enter password: 


mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 12 | chenqiang | 65.00 | nanjing  |     2 |
| 13 | hewei     | 85.00 | shanghai |     1 |
+----+-----------+-------+----------+-------+
11 rows in set (0.00 sec)
  • 基于位置恢复

通过查看二进制日志文件的具体内容可以发现，在每进行一个操作之前都会有一个独特的编号， 如“# at 293”。此编号随着操作数增多而变大，我们称之为操作 ID。在操作 ID 下面紧跟着的是时间标记。要实现基于位置或时间点恢复数据，需要分别依赖二进制日志文件中的操作 ID 或者时间标记。

但是master-bin.000002文件是二进制文件我们是读不懂的，需要使用以下命令进行解码再打开就可以看到具体数据库操作了

[root@server data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v master-bin.000002 > /bak/logbak
[root@server data]# vim /bak/logbak 

# at 293
#200831 15:40:58 server id 11  end_log_pos 352 CRC32 0x22e316cd         Table_map: `school`.`info` mapped to number 120
# at 352
#200831 15:40:58 server id 11  end_log_pos 417 CRC32 0x89368e75         Write_rows: table id 120 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=12
###   @2='chenqiang'
###   @3=65.00
###   @4='nanjing'
###   @5=2
# at 587
#200831 15:41:23 server id 11  end_log_pos 646 CRC32 0x30e4d1ca         Table_map: `school`.`info` mapped to number 120
# at 646
#200831 15:41:23 server id 11  end_log_pos 708 CRC32 0xbb3a1102         Write_rows: table id 120 flags: STMT_END_F
### INSERT INTO `school`.`info`
### SET
###   @1=13
###   @2='hewei'
###   @3=85.00
###   @4='shanghai'
###   @5=1

依旧删除表模拟故障，然后做一次完全备份恢复

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

[root@server data]# mysql -uroot -p school < info-2020-08-31.sql
Enter password: 
mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)

位置点587那里是创建学生hewei的位置点，所以定位到这个位置点就停止恢复，所以学生chenqiang恢复了，hewei没有恢复

[root@server bak]# mysqlbinlog --no-defaults --stop-position='587' /usr/local/mysql/data/master-bin.000002 | mysql -uroot -p
Enter password: 

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 12 | chenqiang | 65.00 | nanjing  |     2 |
+----+-----------+-------+----------+-------+
10 rows in set (0.00 sec)

依旧删除表模拟故障，然后做一次完全备份恢复

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

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

[root@server data]# mysql -uroot -p school < info-2020-08-31.sql
Enter password: 

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)

如果要恢复学生hewei的记录，需要定位开始的位置点为587，因为恢复从这里开始，所以前面的操作都不会被恢复

[root@server bak]# mysqlbinlog --no-defaults --start-position='587' /usr/local/mysql/data/master-bin.000002 | mysql -uroot -p 
Enter password: 

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 13 | hewei     | 85.00 | shanghai |     1 |
+----+-----------+-------+----------+-------+
10 rows in set (0.00 sec)
  • 基于时间点恢复

基于时间点的数据恢复所使用的选项是“–stop-datetime”，指定的时间同样也是查询二进制日志所得

依然是删除表模拟故障然后进行一次完全备份恢复

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

[root@server data]# mysql -uroot -p school < info-2020-08-31.sql
Enter password: 

mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)

设置停止时间点2020-08-31 15:41:23，注意时间的格式，跟之前按位置恢复的原理一样，只是这里换成了时间

[root@server bak]# mysqlbinlog --no-defaults --stop-datetime='2020-08-31 15:41:23' /usr/local/mysql/data/master-bin.000002 | mysql -uroot -p
Enter password: 
mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 12 | chenqiang | 65.00 | nanjing  |     2 |
+----+-----------+-------+----------+-------+
10 rows in set (0.00 sec)

删除表模拟故障，然后进行一次完全备份恢复

mysql> drop table info;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from info;
ERROR 1146 (42S02): Table 'school.info' doesn't exist

[root@server data]# mysql -uroot -p school < info-2020-08-31.sql
Enter password: 
mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
+----+-----------+-------+----------+-------+
9 rows in set (0.00 sec)

设置开始恢复的时间点2020-08-31 15:41:23

[root@server bak]# mysqlbinlog --no-defaults --start-datetime='2020-08-31 15:41:23' /usr/local/mysql/data/master-bin.000002 | mysql -uroot -p
Enter password: 
mysql> select * from info;
+----+-----------+-------+----------+-------+
| id | name      | score | address  | hobby |
+----+-----------+-------+----------+-------+
|  1 | shidapeng | 90.00 | nanjing  |     2 |
|  2 | shangzhen | 80.00 | beijing  |     1 |
|  3 | tangyan   | 98.00 | shanghai |     1 |
|  6 | chengu    | 88.00 | nanjing  |     1 |
|  7 | caicai    | 70.00 | hangzhou |     1 |
|  8 | zhaokun   | 80.00 | hangzhou |     2 |
|  9 | xiawenjie | 80.00 | hangzhou |     1 |
| 10 | zhaobin   |  NULL | shanghai |     1 |
| 11 | nannan    | 76.00 |          |     2 |
| 13 | hewei     | 85.00 | shanghai |     1 |
+----+-----------+-------+----------+-------+
10 rows in set (0.00 sec)
把倒立的世界腾空
关注
专栏目录
mysql5.7使用mysqldumper数据库导入导出备份迁移数据
qq_42554735的博客
07-17 1188
MySQL 5.7安装 一、下载MySQL的repo源 wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm 安装MySQL源 rpm -ivh mysql57-community-release-el7-8.noarch.rpm 使用yum安装mysql-server(这个过程可能要30分钟左右) yum -y install mysql-server 二、数据库配置 启动MySQL：systemctl start my
MySQL 5.7 数据库的备份与恢复
最新发布
thinking_chou的专栏
09-26 912
本文将详细介绍 MySQL 5.7 的备份与恢复方法，包括逻辑备份和物理备份的操作步骤、工具使用及最佳实践。`mysqlbackup` 是 MySQL Enterprise Edition 提供的工具，支持在线备份。`mysqldump` 是 MySQL 自带的备份工具，支持备份整个数据库或单个表。监控备份任务的执行状态，确保备份成功完成，及时处理备份失败的情况。对备份文件进行压缩，以节省存储空间。物理备份的恢复涉及将备份的数据目录复制回 MySQL 数据目录。恢复逻辑备份可以使用 `mysql` 命令。
MySQL数据库误删恢复--超详细
Linux运维老纪的博客
08-06 4321
MySQL误删数据库,造成了数据的丢失,这是非常尴尬的,但是有许多方案可以用来尝试恢复丢失的数据库,本章主要给大家介绍了关于MySQL数据库误删恢复的详细过程。
MySQL：常用备份方式（基于5.7）
weixin_34238633的博客
05-14 143
小生博客：http://xsboke.blog.51cto.com -------谢谢您的参考，如有疑问，欢迎交流一、 Mysqldump备份结合binlog日志恢复使用mysqldump进行全库备份，并使用binlog日志备份，还原时，可以使用binlog日志恢复到备份前的任意位置或时间 1. Bi...
MySQL5.7 --- mysqldump 备份与恢复
热门推荐
马帅的博客
10-24 1万+
MySQL5.7 — mysqldump 备份与恢复 MySQL 备份 冷备份： 停止服务进行备份，即停止数据库的写入 热备份： 不停止服务进行备份（在线） mysql 的 MyIsam 引擎只支持冷备份，InnoDB 支持热备份，原因： InnoDB引擎是事务性存储引擎，每一条语句都会写日志，并且每一条语句在日志里面都有时间点，那么在备份的时候，mysql可以根据这个日志来进行redo和...
MySQL-5.7 备份与恢复
weixin_30342827的博客
03-05 320
一、备份分类 按介质分类： 物理备份 指通过拷贝数据库文件方式完成备份，适用于数据库很大，数据重要且需要快速恢复的数据库。 逻辑备份 指通过备份数据库的逻辑结构和数据内容的方式完成备份，适用于数据库不是很大，或需要对导出文件做一定修改，或重建此库的情况。 优缺点： 物理备份速度快于逻辑备份，因为逻辑备份需要访问数据库并将内容转化成逻辑备份需要的格式 物理备份的备份恢复粒度范围是整个数据库或单...
mysql5.7数据的备份和恢复
龙行
10-05 2533
本次实验只适用一些简单的恢复，在一般的工作中恢复数据，不会用到这种，有想法可以聊聊！谢谢！
CentOs7下 mysql5.7自动备份数据库脚本
07-24
CentOs7下 mysql5.7自动备份数据库脚本
mysql_window环境下_数据库备份与恢复
04-23
用命令的形式 备份和恢复 mysql数据库 主要有一些备份和恢复中出现的注意事项！
MYSQL5.7数据库安装包
04-30
以下是对MySQL 5.7数据库安装包及其相关知识点的详细说明： 1. **MySQL概述**：MySQL是一款开源、免费的关系型数据库管理系统（RDBMS），由瑞典的MySQL AB公司开发，后被甲骨文公司收购。它以其高效、稳定和易于...
mysql5.7数据库备份
03-28
MySQL 5.7是一种常用的关系型数据库管理系统，下面是关于MySQL 5.7数据库备份的介绍： MySQL 5.7数据库备份可以通过多种方式进行，包括物理备份和逻辑备份。 1. 物理备份： 物理备份是将数据库的二进制文件直接...
MySQL5.7定时备份数据库
08-13
定时任务备份数据库并且保留几天的数据。
mysql-5.7 备份与恢复篇
weixin_47647077的博客
07-07 1184
1、索引优缺点 优点： 1）极大地加速了索引过程，减少IO次数 2）创建唯一索引，保证了数据库表中的唯一性 3）加速了表与表之间的连接 4）针对分组和排序检索时，能够显著减少查询查询中的分组和排序时间 缺点： 1）索引表占据物理空间 2）数据表中的数据增加、修改、删除的同时需要去动态维护索引表，降低了数据的维护速度 ...
mysql 5.7数据库备份_Mysql5.7数据库备份-恢复-2020年记录
weixin_39880615的博客
02-10 310
1·首先先创建个测试的数据库吧！create DATABASE test_mysql; #创建新数据库show databases; #查看有多少数据库use test_mysql; #使用此数据库create table ceshi(id int,name char(100));show tables; #查看有哪些数据表insert into ceshi values('1','xiaomi...
mysql5.7备份
weixin_30872157的博客
06-10 210
一、备份准备&备份测试 1、备份目录准备 #mysql专用目录 mkdir /mysql #mysql备份目录 mkdir /mysql/backup #mysql备份脚本 mkdir /mysql/backup/scripts #mysql备份文件 mkdir /mysql/backup/files #mysql备份日志 mkdir /mysql/backup/...
Mysql的备份和还原
lpfstudy的博客
04-26 1472
将一个Mysql服务器设置为主服务器，另一个Mysql服务器设置为从服务器，主服务器上的所有数据修改操作都会被同步到从服务器上，从服务器上的数据就成为了备份数据。热备可以保证Mysql数据库的高可用性和数据的安全性，同时也可以减少数据库的停机时间，提高数据库的可靠性和稳定性。数据库复制：在远程服务器上创建一个与本地服务器相同的数据库，并通过主从复制方式将本地数据库的数据实时同步到远程数据库中，达到备份的目的。数据恢复：如果数据库中的数据已经丢失，备份可以用来恢复数据，避免数据的永久丢失。
MySQL5.7 备份脚本
weixin_33795833的博客
05-27 750
脚本一：#!/bin/bashbackupdir=/root/mysqlbaktime=`date +%Y%m%d%H%M`if [ ! -e 备份目录 ];thenmkdir -p 备份目录fimysqldump -uroot -p密码 库 |bzip2 > $backupdir/ApolloConfigDB$time.sql.bz2find...
mysql5.7数据库的两种备份方式（mysqldump，XBK）超详细
关于自己学习笔记哟
12-11 1万+
mysqldump 时间错恢复 和GTID恢复 XBK 工具 物理备份
mysql5.7系列教程（五）备份恢复
青龙小码农
08-13 899
命令详解-binlog 查看数据库binlog列表 mysql> show binary logs; mysql> show master logs; 查看当前使用的binlog文件信息。 mysql> show binlog events \g; 查看指定的binlog文件信息。 mysql> show binlog events in 'mysql-bin....
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值