MySQL数据库备份与还原

已于 2025-04-06 10:54:50 修改
阅读量550 收藏 3
点赞数 13
CC 4.0 BY-SA版权
分类专栏： MySQL数据库 文章标签： 数据库 mysql
于 2025-03-31 17:41:50 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/chenfengxiu/article/details/146874094

数据库备份与还原

数据库的备份和还原

mariadb版本的升级

配置yum下载源下载高版本的mariadb

vim /etc/yum.repos.d/MariaDB.repo
# 新增以下内容
[mariadb]
name = MariaDB
baseurl = http://mirrors.aliyun.com/mariadb/yum/10.6/centos7-amd64
gpgkey = http://mirrors.aliyun.com/mariadb/yum/RPM-GPG-KEY-MariaDB
gpgcheck=1
enabled=1

使用新的下载源更新mariadb

yum install mariadb-server -y
# 重新下载最新版本
mariadb --version
# 查看当前版本

数据备份的意义

  • 用户数据具有很大的价值
  • 磁盘损坏数据丢失
  • 机器故障导致数据丢失
  • 人为操作误删数据（主从/多节点）
  • 程序错误误删数据
  • 法律法规要求对数据进行备份

运维工作职责：7*24小时稳定服务 99.999%

RTO（恢复时间目标）：故障发生到业务恢复的时间点最大时间（多久可以恢复业务？越小越好）

RPO（恢复点目标）：故障发生之后，业务系统可容忍丢失的数据量

MySQL数据备份分类

按照是否停机可分

  • 冷备份：停服务
  • 热备份：不需要停服务

冷备：先停服务才能备份

优点：

  • 数据一致性高：不会有新的数据写入或删除、更新
  • 操作简单：简单的cp操作
  • 备份资源占用少：备份时不需要提供数据库的服务

缺点：

  • 业务中断：需要停止服务，业务不可用
  • 备份时间窗口受限：只能在指定的时间备份，选择业务的低峰期做备份、限制了备份的灵活性

适用场景：

  • 业务有固定的维护时间
  • 开发环境、测试环境、内部管理系统。。。

热备：不需要停服务

优点：

  • 业务连接性好：不需要停服
  • 灵活性高：随时可以备份
  • 数据及时性高：可以减少数据丢失（1小时、5分钟、近乎实时）

缺点：

  • 实现复杂：依赖数据库或第三方工具的支持
  • 数据一致性难保证：可能在某个瞬间可能会出现不一致，需要用到一些技术手段解决
  • 性能影响：在备份时占用一定系统资源，可能对数据库性能产生一定的影响

适用场景：

  • 不能容忍业务中断的关键系统（电商）
  • 绝大部分场景使用热备

按照备份结果分类

  • 物理备份：拷贝数据
  • 逻辑备份：SQL语句（非源文件数据），能够恢复源数据

物理备份

MariaDB [(none)]> show variables like 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+
1 row in set (0.002 sec)

优点：

  • 对文件的直接复制，简单直接，速度比较快
  • 恢复见到那
  • 对数据库结构依赖小（10.1 → 10.6）

缺点：

  • 通用性差（不同的操作系统不兼容）
  • 数据一致性不好（热备-备份过程中如果有数据的写入）

适用场景：

  • 大规模数据库看，对备份速度要求高

逻辑备份：SQL语句

mysqldump ⇒ *.sql

优点：

  • 可读性强：方便查看和修改
  • 通用性好（跨平台）：可以在不同的操作系统上恢复。MySQL ⇒ PostgreSQL
  • 数据筛选方便：可以备份部分数据或部分表

缺点：

  • 速度慢：对大型数据由于执行大量的SQL语句，备份和恢复的世家占用大量的CPU资源
  • 可能丢失物理存储信息：如存储引擎的特定信息。。。无法用SQL语句体现出来

适用场景：

  • 适用于大部分场景（企业中逻辑备份相对多）

根据备份类型分类

  • 全备：每次备份都将全部的数据做备份
  • 增备：基于上一个数据备份增加的数据
  • 差备：差异备份，以第一次的全备为基础每次备份多出来的数据

全备

优点：

数据完整，恢复简单

缺点：

占用空间多

备份时间长

备份频率受限

**适用场景：**数据量很小或首次备份

增备

优点：

  • 备份数量小，备份速度快

缺点：

  • 恢复操作复杂：按照备份先后顺序依次恢复

适用场景：

  • 对备份频率要求高
  • 变化频繁，变化量不大

差备

优点：

  • 备份数量较小，备份速度较快

缺点：

  • 恢复操作稍复杂：按照备份先后顺序依次恢复

适用场景：

  • 对备份频率要求高
  • 变化频繁，变化量不大

全备和差备组合使用最为常用

数据备份的步骤

1.制定主要备份策略（必做）

备份策略：

  • 每天全备（数据丢失不影响）
  • 每周全备+每日增备/差备
  • 每天全备+每小时增备/差备
  • 每周全备+每日增备/差备+实时备份

2.实施备份

3.恢复测试

  • 在测试机上进行恢复测试
  • 每个月测试一次备份数据，验证备份数据是否损坏（是否可以正常恢复）
  • 自动化脚本实现

4.验证备份数据完整性

  • 备份通常异机被备份（其他服务器）
  • 异地备份（其他机房或城市）
  • 在网络上传输的时候可能不完整（MD5码，现在一般用sha256）

5.数据加密

  • 传输加密（scp）
  • 存储空间（加密盘、GPG密钥加密）

6. 备份保留机制

  • 备份数据需要占用一定的空间
  • 本机：保留几天的备份（30天）
  • 异地：备份服务器保存180天。。。
  • S3网络存储1年

7. 备份通知

  • 备份完成之后，自动发送邮件/微信。。。通知

数据备份：

  • 不限于数据库备份
  • 日志备份（游戏日志、nginx日志、系统日志。。。）

数据备份的实现

全备：把所有的数据备份下来

热全备

**工具：**mysqldump

mysqldump的语法：

# 备份单个库
mysqldump -u<用户名> -p<密码> 数据库名 > 导出文件位置
# 恢复多个数据库时不需要指定所在的数据库（会删除同名的数据库）
# 备份多个数据库
mysqldump -u<用户名> -p<密码> --databases 数据库名1 数据库名2 > 导出文件位置
# 备份机器上所有库
mysqldump -u<用户名> -p<密码> --all-databases > 导出文件位置

案例1：备份test2数据库中所有的数据到/dbback/test2_full_backup_当前日期.sql

mysqldump -uroot -p123456 test1 > /dbbackup/test1_full_backup_$(date +%F_%T).sql

恢复测试

# 1. 模拟误操作（删除表）
drop table test1;
drop table test2;
# 现在的数据与原来的数据不相同

# 执行还原操作
use test2;
source /dbbackup/test2_full_backup_2025-01-12.sql
# 如果数据库被删除了需要先创建数据库并且进入该数据库中执行命令进行恢复

拓展：获取当前日期

data +%F   # 后面这些可以指定获得结果的格式

[root@localhost ~]# date +%F
2025-01-14
[root@localhost ~]# date +%F_%T
2025-01-14_17:23:26

冷全备

停服务、打包服务（tar、cp）、启动服务

tar命令（打包）：

实现步骤：

  1. 停服务

    systemctl stop mariadb

  2. tar打包

    tar czf /dbbackup/mariadb_all_full-日期.tar.gz /var/lib/mysql
# 前一个文件地址是路径，后面的是数据的目录

# 查看目录
SHOW VARIABLES LIKE 'datadir';

案例 - 将 所有的数据库打包并实现恢复

# 进行冷全备的完整流程
# 1.停服务
systemctl stop mariadb
# 2.tar打包
tar czf /dbbackup/mariadb_all_fullbackup-$(date +%F).tar.gz /var/lib/mysql
# 如果在打包的时候加上了路径，解压后的文件也是文件夹嵌套的

# 模拟误操作
drop database test2;
drop database test4;

# 进行恢复
# 1. 停服务
systemctl stop mariadb
# 2.移除原有数据
mv /var/lib/mysql /var/lib/mysql-bak-$(date +%F)
# 3.解压tar包
tar zxf /dbbackup/mariadb_all_fullbackup-2025-01-14.tar.gz -C /restore
# 4.将解压后的文件移动到应该在的位置
mv /restore/var/lib/mysql /var/lib/
# 5.重启服务

全备异地备份

scp：远程数据传输，基于ssh服务

使用scp将备份的数据传输到另外一台远程的机器上面实现异地备份

scp /dbbackup/mariadb_all_fullbackup-2025-01-14.tar.gz 192.168.10.137:/dbbackup/
# 语法格式
scp [选项 -r] 源文件 目标用户@目标主机:目标路径

ssh免密登录

对两台机器进行配置

# 生成公私钥
ssh-keygen
# 内容在/root/.ssh目录下 
[root@localhost .ssh]# ssh-keygen
Generating public/private rsa key pair.
Enter file in which to save the key (/root/.ssh/id_rsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
SHA256:6tbUcK+gIfxj+4IUdCcTn8h2ibOzNCTAf2VmBci424Y root@localhost.localdomain
The key's randomart image is:
+---[RSA 2048]----+
| ..  o.o.o.      |
|  ..o.*+*o       |
|   o.oBO=        |
|    ++.+. .      |
|   . *= S+ .     |
|    E.+=o . .    |
|   . =o= . .     |
|    ..B . .      |
|     oo=.        |
+----[SHA256]-----+
[root@localhost .ssh]# ll
总用量 12
-rw------- 1 root root 1679 1月  14 22:21 id_rsa
-rw-r--r-- 1 root root  408 1月  14 22:21 id_rsa.pub
-rw-r--r-- 1 root root  176 1月  14 22:15 known_hosts

# 将公钥传到目标主机上
# 公钥存储在目标主机/root/.ssh/authorized_keys
# 测试连接
ssh 192.168.10.137

配置成功的标志

# 目标机器是否存在下面的文件
/root/.ssh/authorized_keys
# 权限问题
# 1.当前机器的.ssh目录权限  -  700
# 2.目标机器的authorized_keys文件权限  -  600

相关文件的详细解释

known_hosts
# 当远程ssh/scp的时候，如果从来没有连接过机器，会有个交互式yes/no的提示
# 在连接过以后，known_hosts里面会存储目标机器的信息
ssh-keygen   
# 生成公钥和私钥的命令
id_rsa   # 私钥
id_rsa.pub  # 公钥
authorized_keys
# 在该文件中写的这些公钥的主机可以不需要密码直接连接

增备 ： 全备 + 增备

1. 二进制日志（binlog）实现增量备份

实现步骤

# 切割日志
flush logs;
MariaDB [(none)]> FLUSH LOGS;
Query OK, 0 rows affected (0.026 sec)

MariaDB [(none)]> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      371 |              |                  |
+------------------+----------+--------------+------------------+

# 查看当前日志记录patition
SHOW MASTER STATUS;
# 进行全备
mysqldump -uroot -p123456 --all-databases > /dbbackup/all_databases_$(date +%F).sql

恢复测试 ： 恢复全备 → 恢复增备

# 全备恢复
mysql -uroot -p123456 < /dbbackup/all_databases_2025-01-15.sql

# 增备恢复
# 查询binlog日志的操作
# 案例操作
[root@localhost mysqldata]# mysqlbinlog -v /mysqldata/mysql-bin.000002|grep -B 12 "drop database test1"
COMMIT/*!*/;
# at 1070502
#250115 15:07:31 server id 1  end_log_pos 1070544 CRC32 0xd8d47ef8 	GTID 0-1-397 ddl
/*M!100001 SET @@session.gtid_seq_no=397*//*!*/;
# at 1070544
#250115 15:07:31 server id 1  end_log_pos 1070640 CRC32 0x774dfbee 	Query	thread_id=9	exec_time=0	error_code=1805	xid=1835
SET TIMESTAMP=1736924851/*!*/;
SET @@session.pseudo_thread_id=9/*!*/;
SET @@session.foreign_key_checks=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=1411383296/*!*/;
/*!\C utf8mb3 *//*!*/;
SET @@session.character_set_client=utf8mb3,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
drop database test1

# 进行恢复操作
mysqlbinlog --start-position=371 --stop-position=1070502 /mysqldata/mysql-bin.000002 | mysql -uroot -p123456

2. xtrabackup实现数据库备份（工具）

特点：

  1. 备份速度快，物理备份可靠
  2. 备份过程中不会打断正在执行的事务
  3. 能够对数据进行压缩，节省磁盘空间和流量
  4. 自动备份校验
  5. 还原速度快

Mariadb 10.3 版本以上版本由于日志格式的改变，导致xtrabackuo不支持

Mariabackup工具是Mariadb提供的一个开源工具，基于xtrabackup实现对Mariadb数据库的备份

yum install MariaDB-server -y

实现步骤

# 首先进行全备
mariabackup --backup --target-dir=/dbbackup/full-vackup-$(date +%F) --user=root --password=123456
# 指定目标地址和用户名以及密码
# 备份完成后，灰生成一个全量备份的数据目录，包含数据文件、事务日志等

# 模拟数据变换
insert into product values(100,"海南之家",1000,'c001'),(200,"中国宝贝",10000,'c002');

# 第一次增备
mariabackup --backup --target-dir=/dbbackup/full-backup-2025-01-15-increment-$(date +%F_%H%M%S) --incremental-basedir=/dbbackup/full-backup-2025-01-15/ --user=root --password=123456

# 第二次数据变换
insert into product values(300,"海南之家",1000,'c001'),(400,"中国宝贝",10000,'c002');

# 第二次增备
mariabackup --backup --target-dir=/dbbackup/full-backup-2025-01-15-increment-$(date +%F_%H%M%S) --incremental-basedir=/dbbackup/full-backup-2025-01-15-increment-2025-01-15_154747 --user=root --password=123456

# 破坏数据
drop database test2;

# 数据恢复  停服（保证数据完整性）  ->  数据恢复  ->  启动服务
# 恢复全备
# 1. 停服
systemctl stop mariadb
# 2. 移除数据库文件datadir
mv /var/lib /tmp/mysql-$(date +%F)
# 3. 准备全备数据
mariabackup --prepare --target-dir=/dbbackup/full-backup-2025-01-15
# 4. 恢复全备数据
mariabackup --copy-back --target-dir=/dbbackup/full-backup-2025-01-15
# 5. 修改目录权限
chown mysql:mysql /var/lib/mysql -R
# 6. 启动服务
systemctl start mariadb 

# 恢复增备
# 1. 停服
systemctl stop mariadb
# 2. 移除数据库文件datadir
mv /var/lib /tmp/mysql-$(date +%F)
# 3. 准备全备数据和增备数据
mariabackup --prepare --target-dir=/dbbackup/full-backup-2025-01-15
# 在全备基础上恢复准备增备数据（target-dir不发生变化）
mariabackup --prepare --target-dir=/dbbackup/full-backup-2025-01-15 --incremental-dir=/dbbackup/full-backup-2025-01-15-increment-2025-01-15_155408/
# 4. 恢复数据
mariabackup --copy-back --target-dir=/dbbackup/full-backup-2025-01-15
# 5. 修改目录权限
chown mysql:mysql /var/lib/mysql -R
# 6. 启动服务
systemctl start mariadb

差备

在使用mariabackup这个工具时，与增备很相似，只需要注意incremental-dir的设置

每次都是相对于全备做的备份，在恢复时也只需要两步就能恢复

脚本实践环节

任务要求 - 备份所有数据库到/backup/ip_名字.tar.gz

  1. 每天凌晨3点创建一个全备
  2. 每隔30分钟创建一个差备
  3. 本地备份完成后，上传到备份服务器/backup/ip_名字.tar.gz
  4. 本地保留最近30天的备份数据（备份服务器保留180天的数据）
  5. 如果今天下午3点05分数据库的test2表被删除了，该如何进行恢复

脚本内容

  1. 全备和差备
  2. scp备份服务器
  3. 清除本地备份数据

crontab → 定时任务

  • 全备脚本 → 每天凌晨三点 - full_back.ssh
  • 差备脚本 → 每隔三十分钟 - diff_back.ssh

定时任务

MySQL数据库备份与还原三种常用方式介绍
dvlinker的技术专栏
07-26 263
MySQL数据库备份与还原三种常用方式介绍
mysql数据库的备份和还原
watermelon1135的博客
11-21 574
MYSQL的备份和还原第一种方式通过命令行的备份和还原1.首先通过win+r进入cmd窗口第二种方式使用navicat for mysql工具备份还原 第一种方式 通过命令行的备份和还原 1.首先通过win+r进入cmd窗口 执行命令： mysqldump -u用户名 -p密码 要备份的数据名&gt;备份文件存储位置及其名字.sql 举个栗子： 执行结果： 好了，备份完成！接下要做的就是...
MySQL数据库备份和还原
weixin_33728708的博客
10-07 136
备份MySQL数据库的命令 mysqldump -hhostname -uusername -ppassword databasename &gt; backupfile%date:~0,10%.sql     ---以当前时间命名 备份MySQL数据库为带删除表的格式 　　备份MySQL数据库为带删除表的格式，能够让该备份覆盖已有数据库而不需要手动删除原有数据库。 mysqldu...
【MySQL基础】数据库的备份与还原
最新发布
2301_80220607的博客
06-06 3822
【MySQL基础】数据库备份与还原：安全的进行数据库删除的必要步骤！！
MySQL 数据库备份与还原
青苹果工作室
08-05 2122
MySQL 数据库备份 第一步：选中需要备份数据库下面的“备份”=>右侧点击“新建备份”按钮 第二步：点击“开始”按钮 第三步：选中当前备份的文件=>复制后=>粘贴到自己备份的路径； 粘贴到指定的路径后，可以自己修改名字等： MySQL 数据库还原 第一步：找到需要还原的备份文件如：“210805083812.psc” 第二步：新建一个数据库，命名为“test” 选中服务=>右击=>左键单击“新建数据库“ 2. 输...
MySQL数据库的备份与还原。
weixin_38710804的博客
05-15 439
是对整个数据库的数据进行备份和还原。 1、备份：一般先进入MySQL安装目录的bin目录，然后运行：mysqldump -u root -p aa > tt.sql，回车输入root用户的密码。 aa是要备份的数据库名，tt.sql备份出的数据文件。tt.sql文件将保存在MySQL安装目录的bin目录里。 2、还原： 先进入MySQL系统:mysql -u root -p ...
mysql数据库备份与还原
04-25
### MySQL数据库备份与还原知识点详解 #### 一、MySQL数据库备份的重要性 在现代数据库管理中，备份是一项至关重要的任务。它不仅可以帮助我们防止因意外事件（如硬件故障、人为错误或恶意攻击）而导致的数据丢失...
mysql数据库备份与还原图解.docx
01-08
MySQL 数据库备份与还原图解 MySQL 数据库备份是指将 MySQL 数据库中的数据导出到文件中，以便在需要时将数据恢复到数据库中。反之，MySQL 数据库还原是指将备份的数据恢复到数据库中。下面将详细介绍 MySQL ...
MySQL数据库备份
2303_78135757的博客
02-04 1546
详细的数据库备份知识
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值