type
status
date
slug
summary
tags
category
icon
password
MySQL Binlog文件丢失导致容器启动失败的完整解决方案
📖 问题背景
在使用Docker Compose部署Spring Boot +
MySQL应用时，遇到了一个典型的生产环境问题：由于误删除了MySQL的二进制日志文件（binlog.000501），导致整个应用栈无法正常启动。
🚨 错误现象
1\. MySQL容器启动失败
mysqld: File './binlog.000501' not found (OS errno 2 - No such file or directory)
[ERROR] [MY-010958] [Server] Could not open log file.
[ERROR] [MY-010041] [Server] Can't init tc log
[ERROR] [MY-010119] [Server] Aborting
2\. Java应用连接失败
com.mysql.cj.jdbc.exceptions.CommunicationsException: Communications link failure
java.net.UnknownHostException: mysql: Temporary failure in name resolution
3\. 配置冲突错误
java.lang.IllegalStateException: Duplicate key modelLimits
🔧 解决方案演进
阶段一：网络连接问题
docker-compose.yml 关键配置
version: '3.8'
services:
mysql:
image: mysql:8
container_name: mysql # 固定容器名称
restart: always
ports:
- "3306:3306" # 端口映射
networks:
- app-network # 明确网络配置
app:
depends_on:
mysql:
condition: service_healthy
networks:
- app-network
networks:
app-network:
driver: bridge
阶段二：MySQL初始化问题
完全清理MySQL数据目录
sudo rm -rf data/mysql/*
sudo rm -rf data/mysql/.*
sudo chown -R 999:999 data/mysql/
sudo chmod -R 755 data/mysql/
阶段三：用户权限问题
DROP USER IF EXISTS 'appuser'@'%';
CREATE USER 'appuser'@'%' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON *.* TO 'appuser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
阶段四：数据导入和配置冲突
DELETE FROM app_config WHERE `key` = 'modelLimits';
DELETE c1 FROM app_config c1
INNER JOIN app_config c2
WHERE c1.id > c2.id AND c1.`key` = c2.`key`;
🎯 最佳实践总结
1\. Docker Compose配置规范
version: '3.8'
services:
mysql:
image: mysql:8
container_name: mysql
restart: always
ports:
- "3306:3306"
volumes:
- ./data/mysql/:/var/lib/mysql/
- ./docker-entrypoint-initdb.d/:/docker-entrypoint-initdb.d/
environment:
TZ: Asia/Shanghai
MYSQL_ROOT_PASSWORD: "root_password"
MYSQL_DATABASE: "app_db"
MYSQL_USER: "app_user"
MYSQL_PASSWORD: "app_password"
command: >
networks:
- app-network
healthcheck:
test: ["CMD", "mysqladmin", "ping", "-h", "localhost"]
timeout: 20s
retries: 10
app:
image: app_image
container_name: app
restart: always
depends_on:
mysql:
condition: service_healthy
environment:
SPRING_DATASOURCE_URL: jdbc:mysql://mysql:3306/app_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai
SPRING_DATASOURCE_USERNAME: app_user
SPRING_DATASOURCE_PASSWORD: app_password
networks:
- app-network
ports:
- "8080:8080"
networks:
app-network:
driver: bridge
2\. 数据备份和恢复策略
backup.sh - MySQL自动备份脚本
BACKUP_DIR="/opt/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_NAME="app_db"
创建备份目录
mkdir -p $BACKUP_DIR
备份数据库结构
docker exec mysql mysqldump -u root -p$MYSQL_ROOT_PASSWORD \
备份数据
docker exec mysql mysqldump -u root -p$MYSQL_ROOT_PASSWORD \
保留最近30天的备份
find $BACKUP_DIR -name "*.sql" -mtime +30 -delete
echo "备份完成: $DATE"
1. 停止应用服务
docker-compose stop app
2. 备份当前数据（预防措施）
docker exec mysql mysqldump -u root -p password app_db > current_backup.sql
3. 清空并重新创建数据库
docker exec mysql mysql -u root -p password -e "DROP DATABASE IF EXISTS app_db; CREATE DATABASE app_db;"
4. 导入结构和数据
docker exec -i mysql mysql -u root -p password app_db < backup/structure_file.sql
docker exec -i mysql mysql -u root -p password app_db < backup/data_file.sql
5. 验证数据完整性
docker exec mysql mysql -u root -p password app_db -e "SHOW TABLES; SELECT COUNT(*) FROM main_table;"
6. 重启应用
docker-compose start app
3\. 故障排查流程
检查容器状态
docker ps -a
检查网络配置
docker network ls
docker network inspect network_name
测试容器间连通性
docker exec app_container ping mysql_container
查看容器日志
docker logs container_name
测试MySQL连接
docker exec -it mysql_container mysql -u username -p
检查用户权限
docker exec mysql_container mysql -u root -p password -e "SELECT User, Host FROM mysql.user;"
验证数据库存在
docker exec mysql_container mysql -u root -p password -e "SHOW DATABASES;"
DESCRIBE config_table;
SELECT config_key, COUNT(*) FROM config_table
GROUP BY config_key HAVING COUNT(*) > 1;
SELECT * FROM config_table WHERE config_key = 'problematic_key';
4\. 预防措施
使用数据卷标签保护重要数据
docker volume create --label keep=true mysql_data
避免危险的清理命令
❌ 危险：docker system prune -af
✅ 安全：docker system prune -a --filter "label!=keep=true"
添加到crontab
监控备份状态
find /opt/backups -name "*.sql" -mtime -1 | wc -l
使用环境变量管理敏感信息
environment:
- MYSQL_ROOT_PASSWORD_FILE=/run/secrets/mysql_root_password
- DB_PASSWORD_FILE=/run/secrets/db_password
secrets:
mysql_root_password:
file: ./secrets/mysql_root_password.txt
db_password:
file: ./secrets/db_password.txt
- 作者:Maynor
- 链接:https://maynor1024.live/article/2dd1f390-6aa9-8141-93da-e535ea0484e2
- 声明:本文采用 CC BY-NC-SA 4.0 许可协议，转载请注明出处。
