本文档风哥主要介绍MariaDB的备份恢复与PITR（Point-In-Time Recovery）时间点恢复方法，包括各种备份方法、恢复流程和PITR实施步骤。风哥教程参考MariaDB官方文档Backup and Restore内容，适合数据库管理员学习。
Part01-基础概念与理论知识
1.1 备份概述
备份是数据库管理的重要组成部分，包括：
- 物理备份：直接复制数据文件
- 逻辑备份：使用mysqldump等工具导出数据
- 增量备份：只备份变化的数据
- 全量备份：备份所有数据
1.2 恢复概述
恢复是指从备份中还原数据，包括：
- 完全恢复：从全量备份中恢复所有数据
- 部分恢复：只恢复部分数据
- 时间点恢复：恢复到指定时间点的数据
1.3 PITR概述
PITR（Point-In-Time Recovery）是指将数据库恢复到指定时间点的状态，需要：
- 全量备份
- 二进制日志（binlog）
- 准确的时间点或二进制日志位置
更多视频教程www.fgedu.net.cn
Part02-生产环境规划与建议
2.1 备份策略
风哥提示：生产环境应制定完善的备份策略，包括备份频率、备份方式、备份存储等。
2.2 恢复策略
恢复策略建议：
- 定期测试恢复流程
- 建立恢复预案
- 记录恢复步骤
- 确保备份的完整性
2.3 PITR策略
PITR策略建议：
- 启用二进制日志
- 定期进行全量备份
- 保存二进制日志
- 记录关键时间点
学习交流加群风哥微信: itpux-com
Part03-生产环境项目实施方案
3.1 备份方法
更多学习教程公众号风哥教程itpux_com
# 备份方法
# 1. 使用mysqldump进行逻辑备份
# 备份所有数据库
mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/all_databases.sql
# 备份单个数据库
mysqldump –databases fgedudb –single-transaction –flush-logs –master-data=2 > /mariadb/backup/fgedudb.sql
# 备份单个表
mysqldump fgedudb fgedu_users –single-transaction > /mariadb/backup/fgedu_users.sql
# 2. 使用xtrabackup进行物理备份
# 全量备份
xtrabackup –backup –target-dir=/mariadb/backup/full_backup
# 增量备份
xtrabackup –backup –target-dir=/mariadb/backup/incremental_backup –incremental-basedir=/mariadb/backup/full_backup
# 3. 使用LVM快照备份
# 创建快照
lvcreate –snapshot –size=10G –name mariadb_snap /dev/vg0/mariadb
# 挂载快照
mount /dev/vg0/mariadb_snap /mnt/snap
# 复制数据
cp -r /mnt/snap/* /mariadb/backup/lvm_backup
# 卸载并删除快照
umount /mnt/snap
lvremove /dev/vg0/mariadb_snap
# 1. 使用mysqldump进行逻辑备份
# 备份所有数据库
mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/all_databases.sql
# 备份单个数据库
mysqldump –databases fgedudb –single-transaction –flush-logs –master-data=2 > /mariadb/backup/fgedudb.sql
# 备份单个表
mysqldump fgedudb fgedu_users –single-transaction > /mariadb/backup/fgedu_users.sql
# 2. 使用xtrabackup进行物理备份
# 全量备份
xtrabackup –backup –target-dir=/mariadb/backup/full_backup
# 增量备份
xtrabackup –backup –target-dir=/mariadb/backup/incremental_backup –incremental-basedir=/mariadb/backup/full_backup
# 3. 使用LVM快照备份
# 创建快照
lvcreate –snapshot –size=10G –name mariadb_snap /dev/vg0/mariadb
# 挂载快照
mount /dev/vg0/mariadb_snap /mnt/snap
# 复制数据
cp -r /mnt/snap/* /mariadb/backup/lvm_backup
# 卸载并删除快照
umount /mnt/snap
lvremove /dev/vg0/mariadb_snap
3.2 恢复方法
# 恢复方法
# 1. 使用mysqldump恢复
# 恢复所有数据库
mysql < /mariadb/backup/all_databases.sql
# 恢复单个数据库
mysql fgedudb < /mariadb/backup/fgedudb.sql
# 恢复单个表
mysql fgedudb < /mariadb/backup/fgedu_users.sql
# 2. 使用xtrabackup恢复
# 准备备份
xtrabackup –prepare –target-dir=/mariadb/backup/full_backup
# 恢复备份
xtrabackup –copy-back –target-dir=/mariadb/backup/full_backup
# 调整权限
chown -R mysql:mysql /mariadb/fgdata
# 3. 使用LVM快照恢复
# 停止服务
systemctl stop mariadb
# 恢复数据
cp -r /mariadb/backup/lvm_backup/* /mariadb/fgdata
# 调整权限
chown -R mysql:mysql /mariadb/fgdata
# 启动服务
systemctl start mariadb
# 1. 使用mysqldump恢复
# 恢复所有数据库
mysql < /mariadb/backup/all_databases.sql
# 恢复单个数据库
mysql fgedudb < /mariadb/backup/fgedudb.sql
# 恢复单个表
mysql fgedudb < /mariadb/backup/fgedu_users.sql
# 2. 使用xtrabackup恢复
# 准备备份
xtrabackup –prepare –target-dir=/mariadb/backup/full_backup
# 恢复备份
xtrabackup –copy-back –target-dir=/mariadb/backup/full_backup
# 调整权限
chown -R mysql:mysql /mariadb/fgdata
# 3. 使用LVM快照恢复
# 停止服务
systemctl stop mariadb
# 恢复数据
cp -r /mariadb/backup/lvm_backup/* /mariadb/fgdata
# 调整权限
chown -R mysql:mysql /mariadb/fgdata
# 启动服务
systemctl start mariadb
3.3 PITR实施
# PITR实施
# 1. 启用二进制日志
# 在my.cnf中添加
[mysqld]
log-bin=/mariadb/logs/mariadb-bin
server-id=1
# 2. 进行全量备份
mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/full_backup.sql
# 3. 记录二进制日志位置
# 查看备份文件中的二进制日志位置
head -20 /mariadb/backup/full_backup.sql
# 4. 模拟数据损坏
# 假设在某个时间点数据被损坏
# 5. 恢复全量备份
mysql < /mariadb/backup/full_backup.sql
# 6. 应用二进制日志到指定时间点
# 查看二进制日志文件
ls -la /mariadb/logs/
# 应用二进制日志
mysqlbinlog –start-position=12345 –stop-datetime=”2026-04-07 12:00:00″ /mariadb/logs/mariadb-bin.000001 | mysql
# 或者应用到指定位置
mysqlbinlog –start-position=12345 –stop-position=67890 /mariadb/logs/mariadb-bin.000001 | mysql
# 1. 启用二进制日志
# 在my.cnf中添加
[mysqld]
log-bin=/mariadb/logs/mariadb-bin
server-id=1
# 2. 进行全量备份
mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/full_backup.sql
# 3. 记录二进制日志位置
# 查看备份文件中的二进制日志位置
head -20 /mariadb/backup/full_backup.sql
# 4. 模拟数据损坏
# 假设在某个时间点数据被损坏
# 5. 恢复全量备份
mysql < /mariadb/backup/full_backup.sql
# 6. 应用二进制日志到指定时间点
# 查看二进制日志文件
ls -la /mariadb/logs/
# 应用二进制日志
mysqlbinlog –start-position=12345 –stop-datetime=”2026-04-07 12:00:00″ /mariadb/logs/mariadb-bin.000001 | mysql
# 或者应用到指定位置
mysqlbinlog –start-position=12345 –stop-position=67890 /mariadb/logs/mariadb-bin.000001 | mysql
学习交流加群风哥QQ113257174
Part04-生产案例与实战讲解
4.1 备份实战
# 实战示例：使用mysqldump进行备份
# 1. 准备环境
[root@fgedu.net.cn ~]# mkdir -p /mariadb/backup
[root@fgedu.net.cn ~]# chown -R mysql:mysql /mariadb/backup
# 2. 备份所有数据库
[root@fgedu.net.cn ~]# mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/all_databases.sql
# 3. 查看备份文件
[root@fgedu.net.cn ~]# ls -la /mariadb/backup/
-rw-r–r– 1 root root 123456 Apr 7 19:00 all_databases.sql
# 4. 备份单个数据库
[root@fgedu.net.cn ~]# mysqldump –databases fgedudb –single-transaction –flush-logs –master-data=2 > /mariadb/backup/fgedudb.sql
# 5. 备份单个表
[root@fgedu.net.cn ~]# mysqldump fgedudb fgedu_users –single-transaction > /mariadb/backup/fgedu_users.sql
# 6. 使用xtrabackup进行备份
[root@fgedu.net.cn ~]# xtrabackup –backup –target-dir=/mariadb/backup/xtrabackup_full
# 7. 查看备份目录
[root@fgedu.net.cn ~]# ls -la /mariadb/backup/xtrabackup_full/
# 1. 准备环境
[root@fgedu.net.cn ~]# mkdir -p /mariadb/backup
[root@fgedu.net.cn ~]# chown -R mysql:mysql /mariadb/backup
# 2. 备份所有数据库
[root@fgedu.net.cn ~]# mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/all_databases.sql
# 3. 查看备份文件
[root@fgedu.net.cn ~]# ls -la /mariadb/backup/
-rw-r–r– 1 root root 123456 Apr 7 19:00 all_databases.sql
# 4. 备份单个数据库
[root@fgedu.net.cn ~]# mysqldump –databases fgedudb –single-transaction –flush-logs –master-data=2 > /mariadb/backup/fgedudb.sql
# 5. 备份单个表
[root@fgedu.net.cn ~]# mysqldump fgedudb fgedu_users –single-transaction > /mariadb/backup/fgedu_users.sql
# 6. 使用xtrabackup进行备份
[root@fgedu.net.cn ~]# xtrabackup –backup –target-dir=/mariadb/backup/xtrabackup_full
# 7. 查看备份目录
[root@fgedu.net.cn ~]# ls -la /mariadb/backup/xtrabackup_full/
4.2 恢复实战
# 实战示例：使用mysqldump进行恢复
# 1. 模拟数据丢失
[root@fgedu.net.cn ~]# mysql -u root -p -e “DROP DATABASE fgedudb;
”
# 2. 恢复数据库
[root@fgedu.net.cn ~]# mysql -u root -p < /mariadb/backup/fgedudb.sql
# 3. 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SHOW DATABASES;
”
+——————–+
| Database |
+——————–+
| information_schema |
| fgedudb |
| mysql |
| performance_schema |
| test |
+——————–+
# 4. 使用xtrabackup进行恢复
# 停止服务
[root@fgedu.net.cn ~]# systemctl stop mariadb
# 准备备份
[root@fgedu.net.cn ~]# xtrabackup –prepare –target-dir=/mariadb/backup/xtrabackup_full
# 清空数据目录
[root@fgedu.net.cn ~]# rm -rf /mariadb/fgdata/*
# 恢复备份
[root@fgedu.net.cn ~]# xtrabackup –copy-back –target-dir=/mariadb/backup/xtrabackup_full
# 调整权限
[root@fgedu.net.cn ~]# chown -R mysql:mysql /mariadb/fgdata
# 启动服务
[root@fgedu.net.cn ~]# systemctl start mariadb
# 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SHOW DATABASES;
”
# 1. 模拟数据丢失
[root@fgedu.net.cn ~]# mysql -u root -p -e “DROP DATABASE fgedudb;
”
# 2. 恢复数据库
[root@fgedu.net.cn ~]# mysql -u root -p < /mariadb/backup/fgedudb.sql
# 3. 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SHOW DATABASES;
”
+——————–+
| Database |
+——————–+
| information_schema |
| fgedudb |
| mysql |
| performance_schema |
| test |
+——————–+
# 4. 使用xtrabackup进行恢复
# 停止服务
[root@fgedu.net.cn ~]# systemctl stop mariadb
# 准备备份
[root@fgedu.net.cn ~]# xtrabackup –prepare –target-dir=/mariadb/backup/xtrabackup_full
# 清空数据目录
[root@fgedu.net.cn ~]# rm -rf /mariadb/fgdata/*
# 恢复备份
[root@fgedu.net.cn ~]# xtrabackup –copy-back –target-dir=/mariadb/backup/xtrabackup_full
# 调整权限
[root@fgedu.net.cn ~]# chown -R mysql:mysql /mariadb/fgdata
# 启动服务
[root@fgedu.net.cn ~]# systemctl start mariadb
# 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SHOW DATABASES;
”
4.3 PITR实战
# 实战示例：PITR时间点恢复
# 1. 启用二进制日志
[root@fgedu.net.cn ~]# vi /etc/my.cnf
# 添加以下内容
[mysqld]
log-bin=/mariadb/logs/mariadb-bin
server-id=1
# 重启服务
[root@fgedu.net.cn ~]# systemctl restart mariadb
# 2. 进行全量备份
[root@fgedu.net.cn ~]# mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/pitr_full.sql
# 3. 查看二进制日志位置
[root@fgedu.net.cn ~]# head -20 /mariadb/backup/pitr_full.sql
— CHANGE MASTER TO MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=12345;
# 4. 模拟数据操作
[root@fgedu.net.cn ~]# mysql -u root -p fgedudb
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu06’, ‘fgedu06@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu07’, ‘fgedu07@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu08’, ‘fgedu08@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
# 5. 模拟误操作
MariaDB [fgedudb]> DELETE FROM fgedu_users WHERE username LIKE ‘fgedu%’;
Query OK, 8 rows affected (0.00 sec)
# 6. 恢复全量备份
[root@fgedu.net.cn ~]# mysql -u root -p < /mariadb/backup/pitr_full.sql
# 7. 查看二进制日志
[root@fgedu.net.cn ~]# ls -la /mariadb/logs/
-rw-rw—- 1 mysql mysql 123456 Apr 7 19:00 mariadb-bin.000001
# 8. 应用二进制日志到误操作之前
# 查看二进制日志内容
[root@fgedu.net.cn ~]# mysqlbinlog /mariadb/logs/mariadb-bin.000001 | grep -A 5 -B 5 “DELETE FROM fgedu_users”
# 应用二进制日志到指定位置
[root@fgedu.net.cn ~]# mysqlbinlog –start-position=12345 –stop-position=67890 /mariadb/logs/mariadb-bin.000001 | mysql -u root -p
# 9. 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SELECT * FROM fgedudb.fgedu_users;
”
+———+———-+———————-+——–+———————+
| user_id | username | email | status | created_at |
+———+———-+———————-+——–+———————+
| 1 | fgedu01 | fgedu01@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 2 | fgedu02 | fgedu02@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 3 | fgedu03 | fgedu03@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 4 | fgedu04 | fgedu04@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 5 | fgedu05 | fgedu05@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 6 | fgedu06 | fgedu06@fgedu.net.cn | 1 | 2026-04-07 19:05:00 |
| 7 | fgedu07 | fgedu07@fgedu.net.cn | 1 | 2026-04-07 19:06:00 |
| 8 | fgedu08 | fgedu08@fgedu.net.cn | 1 | 2026-04-07 19:07:00 |
+———+———-+———————-+——–+———————+
# 1. 启用二进制日志
[root@fgedu.net.cn ~]# vi /etc/my.cnf
# 添加以下内容
[mysqld]
log-bin=/mariadb/logs/mariadb-bin
server-id=1
# 重启服务
[root@fgedu.net.cn ~]# systemctl restart mariadb
# 2. 进行全量备份
[root@fgedu.net.cn ~]# mysqldump –all-databases –single-transaction –flush-logs –master-data=2 > /mariadb/backup/pitr_full.sql
# 3. 查看二进制日志位置
[root@fgedu.net.cn ~]# head -20 /mariadb/backup/pitr_full.sql
— CHANGE MASTER TO MASTER_LOG_FILE=’mariadb-bin.000001′, MASTER_LOG_POS=12345;
# 4. 模拟数据操作
[root@fgedu.net.cn ~]# mysql -u root -p fgedudb
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu06’, ‘fgedu06@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu07’, ‘fgedu07@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
MariaDB [fgedudb]> INSERT INTO fgedu_users (username, email) VALUES (‘fgedu08’, ‘fgedu08@fgedu.net.cn’);
Query OK, 1 row affected (0.00 sec)
# 5. 模拟误操作
MariaDB [fgedudb]> DELETE FROM fgedu_users WHERE username LIKE ‘fgedu%’;
Query OK, 8 rows affected (0.00 sec)
# 6. 恢复全量备份
[root@fgedu.net.cn ~]# mysql -u root -p < /mariadb/backup/pitr_full.sql
# 7. 查看二进制日志
[root@fgedu.net.cn ~]# ls -la /mariadb/logs/
-rw-rw—- 1 mysql mysql 123456 Apr 7 19:00 mariadb-bin.000001
# 8. 应用二进制日志到误操作之前
# 查看二进制日志内容
[root@fgedu.net.cn ~]# mysqlbinlog /mariadb/logs/mariadb-bin.000001 | grep -A 5 -B 5 “DELETE FROM fgedu_users”
# 应用二进制日志到指定位置
[root@fgedu.net.cn ~]# mysqlbinlog –start-position=12345 –stop-position=67890 /mariadb/logs/mariadb-bin.000001 | mysql -u root -p
# 9. 验证恢复
[root@fgedu.net.cn ~]# mysql -u root -p -e “SELECT * FROM fgedudb.fgedu_users;
”
+———+———-+———————-+——–+———————+
| user_id | username | email | status | created_at |
+———+———-+———————-+——–+———————+
| 1 | fgedu01 | fgedu01@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 2 | fgedu02 | fgedu02@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 3 | fgedu03 | fgedu03@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 4 | fgedu04 | fgedu04@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 5 | fgedu05 | fgedu05@fgedu.net.cn | 1 | 2026-04-07 18:00:00 |
| 6 | fgedu06 | fgedu06@fgedu.net.cn | 1 | 2026-04-07 19:05:00 |
| 7 | fgedu07 | fgedu07@fgedu.net.cn | 1 | 2026-04-07 19:06:00 |
| 8 | fgedu08 | fgedu08@fgedu.net.cn | 1 | 2026-04-07 19:07:00 |
+———+———-+———————-+——–+———————+
风哥提示：安全开发是防止SQL注入的第一道防线
Part05-风哥经验总结与分享
5.1 常见问题与解决
- 备份失败：检查权限、磁盘空间、网络连接
- 恢复失败：检查备份文件完整性、权限、配置
- PITR失败：确保二进制日志完整、时间点准确
- 备份时间过长：使用增量备份、压缩备份
- 备份空间不足：定期清理旧备份、使用压缩
5.2 最佳实践
风哥提示：生产环境应定期进行备份，并测试恢复流程，确保在数据丢失时能够快速恢复。
5.3 备份检查清单
- 定期进行全量备份
- 启用二进制日志
- 使用增量备份减少备份时间
- 备份文件存储在不同位置
- 定期测试恢复流程
- 监控备份状态
- 记录备份和恢复步骤
- 制定备份恢复预案
from MariaDB视频:www.itpux.com
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
联系我们
在线咨询：
微信号：itpux-com
工作日：9:30-18:30，节假日休息