# 执行备份
MariaDB [(none)]> mysqldump -u root -p –all-databases –single-transaction > /backup/full_backup_20230101.sql
# 计算备份文件的md5值
md5sum /backup/full_backup_20230101.sql > /backup/full_backup_20230101.sql.md5
# 验证备份文件的完整性
md5sum -c /backup/full_backup_20230101.sql.md5
/backup/full_backup_20230101.sql: OK
# 检查备份文件的大小
ls -lh /backup/full_backup_20230101.sql
-rw-r–r– 1 root root 100M Jan 1 00:00 /backup/full_backup_20230101.sql
# 验证备份文件的格式
head -n 10 /backup/full_backup_20230101.sql
— MariaDB dump 10.19 Distrib 10.6.12-MariaDB, for Linux (x86_64)
—
— Host: fgedu.localhost Database:
— ——————————————————
— Server version 10.6.12-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

执行结果：

mysqldump: [Warning] Using a password on the command line interface can be insecure.
/backup/full_backup_20230101.sql: OK
-rw-r–r– 1 root root 100M Jan 1 00:00 /backup/full_backup_20230101.sql
— MariaDB dump 10.19 Distrib 10.6.12-MariaDB, for Linux (x86_64)
—
— Host: fgedu.localhost Database:
— ——————————————————
— Server version 10.6.12-MariaDB
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

# 停止服务
MariaDB [(none)]> systemctl stop mariadb
# 清空数据目录
rm -rf /mariadb/fgdata/*
# 恢复全量备份
mysql -u root -p < /backup/full_backup_20230101.sql
# 启动服务
systemctl start mariadb
# 验证数据库列表
mysql -u root -p -e “SHOW DATABASES;
”
+——————–+
| Database |
+——————–+
| information_schema |
| fgedudb |
| mysql |
+——————–+
# 验证表列表
mysql -u root -p -e “SHOW TABLES IN fgedudb;
”
+——————-+
| Tables_in_fgedudb |
+——————-+
| fgedu_users |
| fgedu_articles |
+——————-+
# 验证数据完整性
mysql -u root -p -e “SELECT COUNT(*) FROM fgedudb.fgedu_users;
”
+———-+
| COUNT(*) |
+———-+
| 1000 |
# 验证特定数据
mysql -u root -p -e “SELECT * FROM fgedudb.fgedu_users WHERE id = 1;
”
+—-+———-+———-+
| id | username | password |
+—-+———-+———-+
| 1 | admin | admin123 |
+—-+———-+———-+

执行结果：

Stopping mariadb.service…
Starting mariadb.service…
+——————–+
| Database |
+——————–+
| information_schema |
| fgedudb |
| mysql |
+——————–+
+——————-+
| Tables_in_fgedudb |
+——————-+
| fgedu_users |
| fgedu_articles |
+——————-+
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
+—-+———-+———-+
| id | username | password |
+—-+———-+———-+
| 1 | admin | admin123 |
+—-+———-+———-+

# 创建自动化测试脚本
MariaDB [(none)]> vi /mariadb/app/scripts/backup_test.sh
# 脚本内容
#!/bin/bash
# backup_test.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# 配置
BACKUP_DIR=”/backup”
DATE=$(date +%Y%m%d)
USER=”root”
PASSWORD=”password”
LOG_FILE=”$BACKUP_DIR/test.log”
# 记录开始时间
echo “Backup test started at $(date)” >> $LOG_FILE
# 执行备份
echo “Performing backup” >> $LOG_FILE
mysqldump -u $USER -p$PASSWORD –all-databases –single-transaction > $BACKUP_DIR/test_backup_$DATE.sql
if [ $? -eq 0 ];
then
echo “Backup completed successfully” >> $LOG_FILE
else
echo “Backup failed” >> $LOG_FILE
exit 1
fi
# 验证备份文件
echo “Verifying backup file” >> $LOG_FILE
md5sum $BACKUP_DIR/test_backup_$DATE.sql > $BACKUP_DIR/test_backup_$DATE.sql.md5
md5sum -c $BACKUP_DIR/test_backup_$DATE.sql.md5 >> $LOG_FILE
# 停止服务
echo “Stopping mariadb service” >> $LOG_FILE
systemctl stop mariadb
# 清空数据目录
echo “Clearing data directory” >> $LOG_FILE
rm -rf /mariadb/fgdata/*
# 恢复备份
echo “Restoring backup” >> $LOG_FILE
mysql -u $USER -p$PASSWORD < $BACKUP_DIR/test_backup_$DATE.sql
if [ $? -eq 0 ];
then
echo “Restore completed successfully” >> $LOG_FILE
else
echo “Restore failed” >> $LOG_FILE
exit 1
fi
# 启动服务
echo “Starting mariadb service” >> $LOG_FILE
systemctl start mariadb
# 验证数据
echo “Verifying data” >> $LOG_FILE
mysql -u $USER -p$PASSWORD -e “SELECT COUNT(*) FROM fgedudb.fgedu_users;
” >> $LOG_FILE
# 记录结束时间
echo “Backup test finished at $(date)” >> $LOG_FILE
echo “———————————-” >> $LOG_FILE
# 设置执行权限
chmod +x /mariadb/app/scripts/backup_test.sh
# 执行测试
/mariadb/app/scripts/backup_test.sh

执行结果：

# 查看测试日志
tail -f /backup/test.log
Backup test started at Sun Jan 1 00:00:00 CST 2023
Performing backup
Backup completed successfully
Verifying backup file
/backup/test_backup_20230101.sql: OK
Stopping mariadb service
Clearing data directory
Restoring backup
Restore completed successfully
Starting mariadb service
Verifying data
+———-+
| COUNT(*) |
+———-+
| 1000 |
+———-+
Backup test finished at Sun Jan 1 00:05:00 CST 2023
———————————-