235 lines
5.6 KiB
Markdown
Executable File
235 lines
5.6 KiB
Markdown
Executable File
# MySQL 5.7 自动备份指南
|
||
|
||
## 📋 配置说明
|
||
|
||
### 🔧 需要修改的配置项
|
||
|
||
在 `docker-compose-mysql-backup.yml` 文件中，请修改以下配置：
|
||
|
||
```yaml
|
||
environment:
|
||
# 🔗 远程 MySQL 5.7 服务器配置
|
||
MYSQL_HOST: "your-mysql-server.com" # ← 修改为你的MySQL服务器地址
|
||
MYSQL_PORT: "3306" # ← 修改为你的MySQL端口
|
||
MYSQL_USER: "backup_user" # ← 修改为你的备份用户
|
||
MYSQL_PASSWORD: "backup_password" # ← 修改为你的备份密码
|
||
MYSQL_VERSION: "5.7" # ← MySQL版本 (已设置为5.7)
|
||
|
||
# 📁 备份配置
|
||
BACKUP_RETENTION_DAYS: "7" # ← 备份保留天数
|
||
BACKUP_SCHEDULE: "0 2 * * *" # ← 备份时间 (每天凌晨2点)
|
||
|
||
# 🔧 备份选项
|
||
BACKUP_TYPE: "full" # ← 备份类型: full(全量) / incremental(增量)
|
||
COMPRESS_BACKUP: "true" # ← 是否压缩备份
|
||
PARALLEL_THREADS: "2" # ← 并行线程数 (MySQL 5.7 建议使用较少线程)
|
||
```
|
||
|
||
## 🚀 执行步骤
|
||
|
||
### 步骤1: 准备环境
|
||
|
||
```bash
|
||
# 1. 创建必要的目录
|
||
mkdir -p backup logs scripts monitor
|
||
|
||
# 2. 设置目录权限
|
||
chmod 755 backup logs scripts monitor
|
||
```
|
||
|
||
### 步骤2: 修改配置
|
||
|
||
```bash
|
||
# 编辑配置文件
|
||
nano docker-compose-mysql-backup.yml
|
||
|
||
# 或者使用 vim
|
||
vim docker-compose-mysql-backup.yml
|
||
```
|
||
|
||
**重要**: 请将以下配置修改为你的实际信息：
|
||
- `MYSQL_HOST`: 你的MySQL服务器地址
|
||
- `MYSQL_PORT`: MySQL端口 (通常是3306)
|
||
- `MYSQL_USER`: 备份用户账号
|
||
- `MYSQL_PASSWORD`: 备份用户密码
|
||
|
||
### 步骤3: 启动备份服务
|
||
|
||
```bash
|
||
# 启动备份服务
|
||
docker-compose -f docker-compose-mysql-backup.yml up -d
|
||
|
||
# 查看服务状态
|
||
docker-compose -f docker-compose-mysql-backup.yml ps
|
||
```
|
||
|
||
### 步骤4: 验证备份
|
||
|
||
```bash
|
||
# 查看备份日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs -f mysql-backup
|
||
|
||
# 查看备份文件
|
||
ls -la backup/
|
||
|
||
# 手动触发备份 (可选)
|
||
docker-compose -f docker-compose-mysql-backup.yml exec mysql-backup /scripts/backup.sh
|
||
```
|
||
|
||
### 步骤5: 访问监控界面
|
||
|
||
```bash
|
||
# 访问监控界面
|
||
open http://localhost:8080
|
||
# 或者
|
||
curl http://localhost:8080
|
||
```
|
||
|
||
## 📊 备份时间配置
|
||
|
||
### 常用时间配置
|
||
|
||
```bash
|
||
# 每天凌晨2点备份
|
||
BACKUP_SCHEDULE: "0 2 * * *"
|
||
|
||
# 每6小时备份一次
|
||
BACKUP_SCHEDULE: "0 */6 * * *"
|
||
|
||
# 每周日凌晨2点备份
|
||
BACKUP_SCHEDULE: "0 2 * * 0"
|
||
|
||
# 每月1日凌晨2点备份
|
||
BACKUP_SCHEDULE: "0 2 1 * *"
|
||
|
||
# 每天上午8点和晚上8点备份
|
||
BACKUP_SCHEDULE: "0 8,20 * * *"
|
||
```
|
||
|
||
### 时间格式说明
|
||
|
||
```
|
||
# crontab 格式: 分 时 日 月 周
|
||
# 分: 0-59
|
||
# 时: 0-23
|
||
# 日: 1-31
|
||
# 月: 1-12
|
||
# 周: 0-7 (0和7都表示周日)
|
||
```
|
||
|
||
## 🔍 故障排除
|
||
|
||
### 常见问题
|
||
|
||
#### 1. 连接失败
|
||
```bash
|
||
# 检查网络连接
|
||
ping your-mysql-server.com
|
||
|
||
# 检查端口是否开放
|
||
telnet your-mysql-server.com 3306
|
||
|
||
# 查看详细错误日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs mysql-backup
|
||
```
|
||
|
||
#### 2. 权限问题
|
||
```bash
|
||
# 检查目录权限
|
||
ls -la backup/ logs/ scripts/
|
||
|
||
# 修复权限
|
||
chmod 755 backup/ logs/ scripts/
|
||
chown -R $USER:$USER backup/ logs/ scripts/
|
||
```
|
||
|
||
#### 3. 备份失败
|
||
```bash
|
||
# 查看详细日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs mysql-backup
|
||
|
||
# 手动测试连接
|
||
docker-compose -f docker-compose-mysql-backup.yml exec mysql-backup \
|
||
mysql -h your-mysql-server.com -P 3306 -u backup_user -p
|
||
```
|
||
|
||
### 日志分析
|
||
|
||
```bash
|
||
# 实时查看日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs -f mysql-backup
|
||
|
||
# 查看最近的日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs --tail=100 mysql-backup
|
||
|
||
# 查看特定时间的日志
|
||
docker-compose -f docker-compose-mysql-backup.yml logs --since="2024-01-15T00:00:00" mysql-backup
|
||
```
|
||
|
||
## 📁 备份文件结构
|
||
|
||
```
|
||
backup/
|
||
├── full/ # 全量备份目录
|
||
│ ├── 20240115_020000/ # 按时间戳命名的备份目录
|
||
│ │ └── backup.tar.gz # 压缩的备份文件
|
||
│ └── 20240116_020000/
|
||
│ └── backup.tar.gz
|
||
└── incremental/ # 增量备份目录
|
||
├── 20240115_140000/
|
||
│ └── backup.tar.gz
|
||
└── 20240115_200000/
|
||
└── backup.tar.gz
|
||
```
|
||
|
||
## 🔧 高级配置
|
||
|
||
### 自定义备份脚本
|
||
|
||
```bash
|
||
# 创建自定义备份脚本
|
||
cat > scripts/custom-backup.sh << 'EOF'
|
||
#!/bin/bash
|
||
# 自定义备份逻辑
|
||
echo "执行自定义备份..."
|
||
# 在这里添加你的自定义逻辑
|
||
EOF
|
||
|
||
chmod +x scripts/custom-backup.sh
|
||
```
|
||
|
||
### 备份到云存储
|
||
|
||
```bash
|
||
# 安装云存储工具
|
||
docker-compose -f docker-compose-mysql-backup.yml exec mysql-backup \
|
||
apt-get update && apt-get install -y awscli
|
||
|
||
# 配置云存储
|
||
docker-compose -f docker-compose-mysql-backup.yml exec mysql-backup \
|
||
aws configure
|
||
```
|
||
|
||
## 📞 支持
|
||
|
||
如果遇到问题，请检查：
|
||
|
||
1. **网络连接**: 确保可以访问MySQL服务器
|
||
2. **用户权限**: 确保备份用户有足够权限
|
||
3. **磁盘空间**: 确保有足够的存储空间
|
||
4. **日志文件**: 查看详细的错误日志
|
||
|
||
## 🎯 总结
|
||
|
||
这个配置提供了：
|
||
|
||
- ✅ **自动备份**: 定时自动执行备份
|
||
- ✅ **MySQL 5.7 支持**: 使用兼容的Percona XtraBackup 2.4
|
||
- ✅ **压缩备份**: 节省存储空间
|
||
- ✅ **自动清理**: 自动删除旧备份
|
||
- ✅ **监控界面**: Web界面查看备份状态
|
||
- ✅ **详细日志**: 完整的备份日志记录
|
||
- ✅ **错误处理**: 完善的错误检测和处理
|
||
|
||
按照上述步骤配置后，你的MySQL 5.7数据库将自动进行定时备份！
|