MySQL Database Synchronization

A lightweight Docker-based solution for synchronizing MySQL databases from a local source to an external destination. The script runs every 5 minutes using cron and provides comprehensive logging and error handling.

Features

• ✅ MySQL 8+ Support: Uses mysqldump and mysql client for reliable synchronization
• ✅ Environment-based Configuration: All settings via environment variables
• ✅ Scheduled Execution: Runs every 5 minutes using cron
• ✅ Comprehensive Logging: Detailed logs with timestamps and error tracking
• ✅ Error Handling: Robust error handling with connection testing
• ✅ Automatic Cleanup: Dump files are automatically cleaned up after import
• ✅ Multi-database Support: Sync multiple databases from a JSON list
• ✅ Lightweight: Minimal Docker image with PHP 8.3 and MySQL client

Quick Start

1. Create Environment File

Create a .env file in the project directory:

# Source database configuration (local)
SOURCE_HOST=localhost
SOURCE_USER=root
SOURCE_PASS=your_source_password
SOURCE_PORT=3306

# Destination database configuration (external)
DEST_HOST=remote.example.com
DEST_USER=remote_user
DEST_PASS=remote_password
DEST_PORT=3306

# List of databases to sync (JSON array)
DATABASES=["database1", "database2", "database3"]

2. Build and Run

# Build the Docker image
docker-compose build

# Start the synchronization service
docker-compose up -d

# View logs
docker-compose logs -f

3. Monitor Synchronization

# View sync logs
docker exec mysql-db-sync tail -f /app/logs/sync.log

# Check container status
docker-compose ps

Configuration

Environment Variables

Variable	Description	Required	Default
SOURCE_HOST	Source database host	Yes	localhost
SOURCE_USER	Source database username	Yes	root
SOURCE_PASS	Source database password	Yes	-
SOURCE_PORT	Source database port	No	3306
DEST_HOST	Destination database host	Yes	-
DEST_USER	Destination database username	Yes	-
DEST_PASS	Destination database password	Yes	-
DEST_PORT	Destination database port	No	3306
DATABASES	JSON array of database names	Yes	-

Example Environment Configuration

# Local development setup
SOURCE_HOST=localhost
SOURCE_USER=root
SOURCE_PASS=mypassword123
SOURCE_PORT=3306

# Production server
DEST_HOST=prod-mysql.company.com
DEST_USER=sync_user
DEST_PASS=secure_password_456
DEST_PORT=3306

# Multiple databases
DATABASES=["production_db", "staging_db", "test_db"]

Project Structure

SyncDBs/
├── Dockerfile              # Docker image definition
├── docker-compose.yml      # Docker Compose configuration
├── .env                    # Environment variables (create this)
├── README.md              # This file
├── src/
│   ├── sync.php           # Main synchronization script
│   └── cron.d             # Cron configuration
└── logs/                  # Log files directory (created automatically)
    └── sync.log           # Synchronization logs

How It Works

1. Connection Testing: The script first tests connections to both source and destination databases
2. Database Dumping: For each database in the list, it creates a dump using mysqldump
3. Import Process: The dump is imported to the destination database using mysql
4. Cleanup: Temporary dump files are automatically removed
5. Logging: All operations are logged with timestamps and error details
6. Scheduling: The process runs every 5 minutes via cron

Logging

The synchronization process creates detailed logs in /app/logs/sync.log:

[2024-01-15 10:00:01] [INFO] Starting database synchronization process
[2024-01-15 10:00:01] [INFO] Testing source database connection...
[2024-01-15 10:00:01] [INFO] Successfully connected to source database
[2024-01-15 10:00:02] [INFO] Testing destination database connection...
[2024-01-15 10:00:02] [INFO] Successfully connected to destination database
[2024-01-15 10:00:02] [INFO] Processing database: production_db
[2024-01-15 10:00:02] [INFO] Creating dump for database: production_db
[2024-01-15 10:00:05] [INFO] Dump created successfully: 2048576 bytes
[2024-01-15 10:00:05] [INFO] Importing database: production_db to destination
[2024-01-15 10:00:08] [INFO] Successfully imported database: production_db
[2024-01-15 10:00:08] [INFO] Synchronization completed. Success: 1, Errors: 0

Troubleshooting

Common Issues

1. Connection Failed

   ERROR: Cannot connect to source database at localhost:3306
   

   • Check if the MySQL server is running
   • Verify host, port, username, and password
   • Ensure the user has proper permissions

2. Database Not Found

   ERROR: Database 'database_name' doesn't exist
   

   • Verify the database exists on the source server
   • Check the database name in the DATABASES JSON array

3. Permission Denied

   ERROR: Access denied for user 'username'@'host'
   

   • Grant necessary permissions to the database user
   • For source: SELECT, SHOW VIEW, EVENT, TRIGGER
   • For destination: CREATE, DROP, INSERT, UPDATE, DELETE

Useful Commands

# Test database connection manually
docker exec mysql-db-sync mysql -hSOURCE_HOST -uSOURCE_USER -pSOURCE_PASS -e "SELECT 1;"

# Run synchronization manually (outside of cron)
docker exec mysql-db-sync php /app/sync.php

# View recent logs
docker exec mysql-db-sync tail -n 50 /app/logs/sync.log

# Check cron status
docker exec mysql-db-sync crontab -l

# Restart the service
docker-compose restart

Security Considerations

• Store sensitive credentials in environment variables, not in code
• Use dedicated database users with minimal required permissions
• Consider using SSL/TLS connections for production environments
• Regularly rotate database passwords
• Monitor logs for any suspicious activity

Performance Tips

• The synchronization process uses --single-transaction for consistent dumps
• Large databases may take longer to sync; consider the 5-minute interval
• Monitor disk space as temporary dump files are created during sync
• For very large databases, consider incremental synchronization strategies

Dokploy Deployment

This project is ready for deployment with Dokploy. Simply:

1. Push to GitHub (or any Git repository)
2. In Dokploy, create a new project and connect your repository
3. Set Environment Variables in Dokploy's interface:

   SOURCE_HOST=your_source_host
   SOURCE_USER=your_source_user
   SOURCE_PASS=your_source_password
   SOURCE_PORT=3306
   DEST_HOST=your_dest_host
   DEST_USER=your_dest_user
   DEST_PASS=your_dest_password
   DEST_PORT=3306
   DATABASES=["db1", "db2", "db3"]
   

4. Deploy - Dokploy will automatically build and run the container

The container will start and begin synchronizing databases every 5 minutes.

License

This project is open source and available under the MIT License.