Mysql的备份和还原

最新推荐文章于 2024-07-25 16:50:23 发布

Claylpf

最新推荐文章于 2024-07-25 16:50:23 发布

阅读量1.4k

点赞数

文章标签：数据库大数据

本文链接：https://blog.csdn.net/lpfstudy/article/details/130370680

版权

本文详细介绍了Mysql的备份和恢复方法，包括热备、冷备、异地备份的概念和工具，如mysqldump、xtrabackup。讨论了完全备份、增量备份和差异备份的优缺点，并给出了使用mysqldump进行备份的实例。此外，还讲解了如何利用二进制日志进行点-in-time恢复，以及在数据丢失后的恢复步骤。

摘要由CSDN通过智能技术生成

Mysql的备份和还原

为什么需要备份呢？

Mysql备份的方式分为热备（online）和冷备（offline）

参考文档：完全备份、增量备份和差异备份 - 云无止境 - 博客园 (cnblogs.com)

接下列我们使用mysqldump对Mysql数据库进行备份处理（热备、全备）

参考文档：MySQL :: MySQL 5.7 Reference Manual :: 7.5.2 Point-in-Time Recovery Using Event Positions

根据时间点来恢复或者根据位置号来恢复

步骤：

1、产生一共全新的二进制日志文件，方便后面的操作

2、给数据库做一个全备

3、让数据发生变化（在emp表格中插入数据）

4、模拟出现故障，sc数据库被删除了

5、开始恢复数据

a、恢复全备

b、根据二进制日志进行恢复（可以根据时间点或者根据位置号来恢复）

Mysql的备份和还原

为什么需要备份呢？

数据丢失：由于各种原因，如硬盘故障、人为误操作、病毒攻击等，数据库中的数据可能会丢失。备份是一种防止数据丢失的重要手段。

数据恢复：如果数据库中的数据已经丢失，备份可以用来恢复数据，避免数据的永久丢失。

数据安全：备份还可以用于保护数据的安全性。备份可以在数据被损坏或被攻击时，提供一种恢复数据的方法。

数据迁移：备份可以用于将数据从一个服务器迁移到另一个服务器。这对于数据中心的重建、系统升级和迁移等情况非常有用。

数据分析：备份可以用于数据分析，例如用备份数据来分析历史趋势、性能分析等。

Mysql备份的方式分为热备（online）和冷备（offline）

热备（online）

Mysql的热备是指在Mysql数据库运行期间对数据库进行备份，备份过程中不会停止数据库的运行。热备可以保证Mysql数据库的高可用性和数据的安全性，同时也可以减少数据库的停机时间，提高数据库的可靠性和稳定性。

Mysql的热备可以采用以下几种方式：

基于二进制日志的备份：Mysql的二进制日志记录了所有数据库的修改操作，可以通过备份二进制日志来实现热备。该方法需要定期备份二进制日志，并将备份文件复制到备份服务器上。（使用mysqldump进行热备）

基于远程复制的备份：Mysql的主从复制功能可以实现热备。将一个Mysql服务器设置为主服务器，另一个Mysql服务器设置为从服务器，主服务器上的所有数据修改操作都会被同步到从服务器上，从服务器上的数据就成为了备份数据。

基于存储引擎的备份：Mysql的存储引擎支持热备功能，可以直接备份存储引擎的数据文件。该方法需要确保备份的数据文件和Mysql的数据文件完全一致。

总之，Mysql的热备可以通过多种方式实现，具体选择哪种方式需要根据实际情况进行选择。

我们在进行热备的时候可以使用哪些工具呢：

1、SQLyog

2、MYSQL workbench

3、Navicat

4、xtrabackup

如何使用xtrabackup：https://www.cnblogs.com/gomysql/p/3650645.htm

当然我们可以使用mysqldump进行备份

冷备（offline）

MySQL的冷备是指在数据库处于关闭状态下进行的备份操作。这种备份方式适用于数据量较小、备份频率较低的数据库。

冷备的步骤如下：

关闭MySQL数据库

备份MySQL数据文件

备份MySQL配置文件

备份MySQL日志文件

备份MySQL系统表空间文件

启动MySQL数据库

验证备份文件的完整性和可用性

冷备的优点是备份过程简单，对数据库的影响小，备份文件可靠性高。缺点是需要停止数据库服务，备份时间较长，备份频率低。

当我们进行冷备的时候常用的命令是：cp、tar、rsync、scp。

异地备份：

Mysql的异地备份是指将Mysql数据库备份数据存储在远程地点的备份方式。这种备份方式可以在本地服务器故障或数据丢失的情况下，快速恢复数据。异地备份可以采用以下两种方式：

数据库复制：在远程服务器上创建一个与本地服务器相同的数据库，并通过主从复制方式将本地数据库的数据实时同步到远程数据库中，达到备份的目的。

数据库导出：将本地数据库的数据导出为SQL文件，并将文件传输到远程服务器上进行存储。在需要恢复数据时，只需要将SQL文件导入到新的数据库中即可。

无论是哪种方式，都需要确保远程服务器的安全性和可靠性，以防止备份数据的丢失或泄露。同时，异地备份也需要定期进行检查和测试，以确保备份数据的完整性和可用性。

https://www.cnblogs.com/yanjieli/articles/10722087.html

示例脚本：

[root@wudang-mysql-2 mysql]# cat backup_db.sh 
#!/bin/bash

#得到时间
ctime=$(date +%F_%H%M%S)

#在本地新建存放目录/backup_db
mkdir  -p  /backup_db

#备份TENNIS库到/backup_db叫tennis.sql
mysqldump -uroot -pSanchuang1234#  TENNIS >/backup_db/${ctime}_tennis.sql

#备份mysql库里user表
mysqldump -uroot -pSanchuang1234#  mysql  user >/backup_db/${ctime}_mysql_user.sql

#备份服务器上新建文件夹/backup_db
ssh root@192.168.0.136  mkdir -p /backup_db
#上传当天备份的文件到备份服务器里192.168.0.136
scp /backup_db/${ctime}*.sql  root@192.168.0.136:/backup_db

#本地保留最近30天的备份文件
find  /backup_db  -mtime +30 -type f -name "*.sql" -exec rm -rf {} \;

#备份服务器上也保留最近30天的文件
#生成一个脚本文件
cat >del_30days_file.sh <<EOF
find  /backup_db  -mtime +30 -type f -name "*.sql" -exec rm -rf {} \;
EOF

#上传脚本文件到备份服务器
scp del_30days_file.sh root@192.168.0.136:/root

#远程执行脚本
ssh root@192.168.0.136 bash /root/del_30days_file.sh

Mysql的备份方式(方案)

1、完全备份（全备）

2、增量备份

3、差异备份

参考文档：完全备份、增量备份和差异备份 - 云无止境 - 博客园 (cnblogs.com)

完全备份：所有的内容全部备份

优点：恢复的时候方便快

缺点：消耗的空间大，时间长

增量备份：备份上一次备份后产生的数据，备份增加出来的数据

备份的好处是每次备份需要备份的数据较少，耗时较短，占用的空间较小

坏处是数据恢复比较麻烦

差异备份：每次备份都和完全备份进行差异比较，备份完全备份后多出来的部分

是完全备份和增量备份折中的方式。

图片解释：

接下列我们使用mysqldump对Mysql数据库进行备份处理（热备、全备）

如上图所示，我们使用mysqldump -uroot -p'123456' --all-databases > all_dump.sql命令对我们的Mysql数据库进行了备份，并将备份文件重定向到了all_dump.sql文件中

如果我们只备份部分库呢？

如上图所示，我们只备份了sc库。

如果我们只想备份某个表呢？

如上图所示，我们只备份了sc库里的emp表格。

mysqldump的用法：

[root@mysql backup]# mysqldump
Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]
For more options, use mysqldump --help
[root@mysql backup]#

示例：

下面我们将sc库删除，然后还原sc库。

删除sc库：

恢复sc库：

我们在bakeup文件夹下操作下面的命令：

导入sc库的备份文件（全备）

或者直接导入整个Mysql数据库的备份文件

检查是否恢复成功

全备 + 二进制日志恢复数据方式：

案例：

备份方案：
每天的下午16:50点做全备，刚好到了下午17点10分的时候，数据库被删除了，如何将数据恢复到17点10分的状态？（先恢复全备 + 二进制日志恢复）

参考文档：MySQL :: MySQL 5.7 Reference Manual :: 7.5.2 Point-in-Time Recovery Using Event Positions

根据时间点来恢复或者根据位置号来恢复

二进制日志文件可以根据时间点或者位置号来进行恢复。根据时间点恢复时，需要指定恢复的时间点（ --start-datetime and --stop-datetime），MySQL会将该时间点之后的所有操作进行恢复。根据位置号恢复时，需要指定恢复的位置号（ --start-position and --stop-position），MySQL会将该位置号之后的所有操作进行恢复。无论是根据时间点还是位置号进行恢复，都需要先将备份文件恢复到MySQL服务器上。

例如：

根据时间点来恢复

mysqlbinlog  --start-datetime="2020-08-13 11:50:07" --stop-datetime="2020-08-13 11:50:24" /data/mysql/zabbix-4-centos7-bin.000002 |mysql -uyangst -p'yang123#'

mysqlbinlog --start-datetime="2005-04-20 9:55:00" \
         --stop-datetime="2005-04-20 10:05:00" \
         /var/log/mysql/bin.123456 > /tmp/mysql_restore.sql

根据位置号来恢复

mysqlbinlog --start-position=291 --stop-position=697  /data/mysql/sc-mysql-bin.000005 |mysql -uroot -p'Sanchuang123#'

步骤：

1、产生一共全新的二进制日志文件，方便后面的操作

2、给数据库做一个全备

[root@mysql mysql]# mysqldump -uroot -p'123456' --databases sc > /backup/sc.sql

3、让数据发生变化（在emp表格中插入数据）

4、模拟出现故障，sc数据库被删除了

5、开始恢复数据

a、恢复全备

[root@mysql backup]# mysql -uroot -p'123456' < sc.sql

但是现在恢复的数据没有刚刚更改的数据，因此我们还需要通过二进制日志进行恢复。

b、根据二进制日志进行恢复（可以根据时间点或者根据位置号来恢复）

我们可以通过命令查找到删除库之前的（drop database命令之前）对应的位置号

mysqlbinlog -v mysql-bin.000005|egrep -C 20 "drop database"   --》读取drop database命令前后20行

我这里起始位置号为154，结束位置号为749

接下来我们开始恢复数据

[root@mysql mysql]# mysqlbinlog --start-position=154 --stop-position=749 mysql-bin.000005|mysql -uroot -p'123456'

查看是否恢复成功：

我们使用时间点去恢复也是一样的步骤，但是你必须知道起始时间和恢复时间。

Claylpf

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
Mysql的备份和还原

将一个Mysql服务器设置为主服务器，另一个Mysql服务器设置为从服务器，主服务器上的所有数据修改操作都会被同步到从服务器上，从服务器上的数据就成为了备份数据。热备可以保证Mysql数据库的高可用性和数据的安全性，同时也可以减少数据库的停机时间，提高数据库的可靠性和稳定性。数据库复制：在远程服务器上创建一个与本地服务器相同的数据库，并通过主从复制方式将本地数据库的数据实时同步到远程数据库中，达到备份的目的。数据恢复：如果数据库中的数据已经丢失，备份可以用来恢复数据，避免数据的永久丢失。
复制链接

扫一扫