MySQL数据库备份与还原_mysql命令备份及还原-优快云博客

数据库备份与还原

数据库的备份和还原

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命令（打包）：

实现步骤：

停服务
```
systemctl stop mariadb
```

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实现数据库备份（工具）

特点：

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

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