sadiksajid/db-sync repository overview

Database Synchronization Proxy A powerful, production-grade database synchronization tool that replicates data between MySQL or PostgreSQL databases. Built with Rust for high performance, reliability, and parallel processing.

🚀 Key Features Multi-Slave Synchronization Parallel Sync: Synchronize to multiple slave databases simultaneously Automatic Status Tracking: Real-time monitoring of each slave database connection Individual Slave Management: Add, edit, or remove slave databases independently Concurrent Processing: Leverages Tokio for efficient parallel operations Synchronization Modes Full Sync (MySQL only): Initial migration + continuous real-time monitoring Initial Sync Only: One-time schema and data transfer (MySQL & PostgreSQL) Real-time Sync Only (MySQL only): Continuous change monitoring via binlog Reset Mode: Optionally DROP and recreate slave databases before sync Automated Scheduling Cron-Based Scheduler: Schedule automatic syncs at specific times or intervals User-Friendly Interface: Define schedules with: Time Delay: Run every X hours or days Exact Time: Run at specific times (e.g., daily at 15:30, weekdays at 09:00) Multiple Schedules: Create unlimited scheduled tasks Automatic Reset: Scheduled syncs automatically reset slave databases for fresh data Persistent: Schedules continue running even after page refresh or system restart Database Support MySQL to MySQL: Complete synchronization with real-time monitoring Uses general_log for change detection Supports mysqldump for reliable data transfer PostgreSQL to PostgreSQL: Schema and data synchronization Uses pg_dump and psql for production-grade transfers Initial sync only (real-time not available) Modern Web UI Responsive Dashboard: Works on desktop, tablet, and mobile devices Live Statistics: Real-time operation tracking with hourly analytics Interactive Charts: Beautiful visualizations of sync operations over time User Authentication: Secure login system with bcrypt password hashing Persistent Configuration: All settings saved in SQLite database Dark Theme: Modern, eye-friendly interface Ad Integration: Built-in ad display system Production Features Data Integrity: Uses standard tools (mysqldump, pg_dump) for reliable transfers Error Handling: Comprehensive error reporting with detailed logs Connection Testing: Test database connections before starting sync Session Management: Secure authentication with HTTP-only cookies Docker Ready: Full Docker and Docker Compose support 📋 Requirements Source Database MySQL: MySQL 5.7+ or MariaDB 10.2+ general_log must be enabled for real-time sync (tool enables it automatically with SUPER privilege) User permissions: SELECT on source database, SELECT on mysql.general_log, SUPER privilege PostgreSQL: PostgreSQL 10+ User permissions: SELECT on source database Slave Databases MySQL: MySQL 5.7+ or MariaDB 10.2+ User permissions: CREATE DATABASE, DROP DATABASE, CREATE tables, INSERT, UPDATE, DELETE PostgreSQL: PostgreSQL 10+ User permissions: CREATE DATABASE, DROP DATABASE, CREATE tables and schemas, INSERT, UPDATE, DELETE System Requirements Docker (recommended) or Rust 1.70+ 512MB RAM minimum (1GB+ recommended for multiple slaves) Network access between source and all slave databases mysql client for MySQL sync psql and pg_dump clients for PostgreSQL sync 🔧 Installation Using Docker (Recommended) Clone the repository cd "DB sync" Start with Docker Compose docker-compose up -d The Web UI will be available at http://localhost:5009⁠

Using Docker Run docker build -t db-sync-proxy . docker run -d -p 5009:5009 -v ./db_sync_data:/app/data db-sync-proxy --web-ui From Source

⁠Install Rust

curl --proto '=https' --tlsv1.2 -sSf https://sh.rustup.rs⁠ | sh

⁠Install database clients (Ubuntu/Debian)

sudo apt-get install mysql-client postgresql-client

⁠Build the project

cargo build --release

⁠Run with web UI

./target/release/db_sync_proxy --web-ui ⚙️ Configuration Initial Setup Open http://localhost:5009⁠ in your browser Create first user account (appears on first run) Login with your credentials Configure Master Database Go to Settings tab Select Database Type (MySQL or PostgreSQL) Fill in Source Database details: Host, Port, Database Name Username, Password Click Test Connection to verify Add Slave Databases In the Slave Databases section Fill in slave database details: Host, Port, Database Name Username, Password Click Add Slave Database Repeat for multiple slaves (supports unlimited slaves) Each slave's status is displayed with color indicators Configure Sync Options Batch Size: Records per batch for data transfer (default: 100) Poll Interval: Seconds between change checks for real-time mode (default: 10) Reset Database: Enable to DROP and recreate slave databases before each sync 🎯 Usage Manual Synchronization Go to Home tab Select Sync Mode: Full Sync (MySQL only): Initial migration + real-time monitoring Initial Sync Only: One-time schema and data transfer Real-time Sync Only (MySQL only): Monitor changes only Check Reset Database if you want fresh slaves (⚠️ Warning: Deletes all data in slaves) Click Start Sync Monitor progress in real-time: Live logs showing each operation Slave database status indicators Quick stats (tables synced, rows processed) Scheduled Synchronization Go to Schedules tab

Click Add Schedule

Choose Schedule Type:

Time Delay:

Repeat every X hours or days Examples: Every 12 hours, Every 2 days Exact Time:

Run at specific time with recurrence Examples: Daily at 15:30, Weekdays at 09:00, Every Monday at 08:00 Name your schedule (e.g., "Daily Backup", "Hourly Sync")

Enable/Disable with toggle switch

Save - Schedule runs automatically in background

Important Notes:

Schedules run "Initial Sync Only" mode (Schema + Data) All configured slave databases are synced automatically Scheduled syncs always reset slave databases (fresh data) Schedules persist across restarts and page refreshes Monitoring Home Tab: Current status, active schedules, connected databases Statistics Tab: Detailed operation counts and success rates Chart Tab: Visual graphs of operations over time Schedules Tab: Manage all scheduled tasks 🔄 Sync Modes Explained

1. Full Sync (MySQL Only) What it does:

Phase 1: Schema migration and initial data transfer using mysqldump Phase 2: Catch-up sync (replays changes during transfer) Phase 3: Real-time monitoring via general_log Best for:

First-time MySQL migration Complete database replication Continuous synchronization 2. Initial Sync Only (MySQL & PostgreSQL) What it does:

Exports schema using mysqldump or pg_dump Transfers all data in batches Stops after completion Best for:

One-time migration Creating database snapshots Scheduled backups Both MySQL and PostgreSQL 3. Real-time Sync Only (MySQL Only) What it does:

Monitors MySQL general_log for changes Replicates INSERT, UPDATE, DELETE operations Assumes schema and initial data already exist Best for:

Resuming after interruption Ongoing replication After initial migration 📅 Scheduling Examples Time Delay Examples Every 6 hours → Runs every 6 hours from now Every 12 hours → Runs twice daily Every 2 days → Runs every 48 hours Exact Time Examples Daily at 02:00 → Every day at 2 AM Weekdays at 09:00 → Monday-Friday at 9 AM Weekends at 23:00 → Saturday-Sunday at 11 PM Every Monday at 08:00 → Weekly backup Every Friday at 18:00 → End of week sync Cron Format (Auto-Generated): The system automatically converts your selections to cron expressions (6-field format with seconds).

📊 Statistics & Monitoring Real-Time Statistics Hourly aggregation: Operations grouped by hour Operation breakdown: INSERT, UPDATE, DELETE counts Success rates: Track successful vs failed operations Per-slave tracking: Monitor each slave individually Persistent storage: SQLite database for historical data Interactive Charts Line graphs: Operation trends over time Color-coded: Green (INSERT), Blue (UPDATE), Red (DELETE) Smooth curves: Easy-to-read visualizations Hover tooltips: Exact counts on demand Last 24 hours: Recent activity at a glance Slave Database Status Each slave shows:

Connection status: Connected / Disconnected indicator Last sync time: When last synchronized Current state: Syncing / Idle / Error 🔐 Security Authentication First-time setup: Create admin user on first launch Password hashing: bcrypt with secure salt (cost: 12) Session management: HTTP-only cookies with expiration Protected routes: All API endpoints require authentication Logout: Secure session termination User Management Profile: View and update email Password change: Secure password updates with re-hashing Session timeout: Automatic logout after inactivity Database Security Credentials encryption: Sensitive data stored securely in SQLite No plaintext passwords: All passwords hashed before storage Secure connections: Supports SSL/TLS for database connections 🐳 Docker Deployment Using Pre-built Image from GitHub Container Registry The easiest way to get started is using the pre-built image from GitHub Container Registry:

⁠Pull the latest image

docker pull ghcr.io/OWNER/REPO:latest

⁠Run the container

docker run -d
--name db-sync-proxy
-p 5009:5009
-v ./db_sync_data:/app/data
-e RUST_LOG=info
ghcr.io/OWNER/REPO:latest --web-ui Note: Replace OWNER/REPO with your GitHub username and repository name (e.g., ghcr.io/yourusername/db-sync:latest)

Available Image Tags latest - Latest stable release from main/master branch v* - Semantic version tags (e.g., v1.0.0, v1.0.1) main or master - Latest build from the main branch sha-XXXXXXX - Specific commit builds Docker Compose with Pre-built Image version: '3.8'

services: db-sync: image: ghcr.io/OWNER/REPO:latest ports: - "5009:5009" volumes: - ./db_sync_data:/app/data environment: - RUST_LOG=info command: ["--web-ui"] restart: unless-stopped Docker Compose (Build from Source) version: '3.8'

services: db-sync: build: . ports: - "5009:5009" volumes: - ./data:/app/data environment: - RUST_LOG=info command: ["--web-ui"] restart: unless-stopped Volume Persistence The /app/data directory contains:

config.db: SQLite database with all settings User credentials and sessions Operation statistics and logs Schedule configurations Connection history Environment Variables RUST_LOG: Logging level (debug, info, warn, error) All database config done via Web UI 🛠️ Troubleshooting General_log Errors (MySQL) Problem: general_log is not enabled

Solution: Tool enables it automatically with SUPER privilege. Manual enable:

SET GLOBAL general_log = 'ON'; SET GLOBAL log_output = 'TABLE'; Database Type Mismatch Problem: "Source and target database types must be the same"

Solution: Only same-type sync supported. All databases must be either MySQL or PostgreSQL.

Connection Errors Solutions:

Verify credentials in Settings Check network connectivity between container and databases Ensure user has required permissions Use Test Connection button Check firewall rules Slave Not Syncing Solutions:

Verify slave is added in Settings and saved Check slave connection status on Home page Review logs for specific error messages Ensure Reset Database permissions if enabled Schedule Not Running Solutions:

Verify schedule is enabled (toggle switch on) Check cron expression is valid Ensure at least one slave database is configured Review logs during scheduled time Check system time is correct Chart Shows No Data Solutions:

Start real-time sync (not available for PostgreSQL) Make changes in source database Wait 2-4 seconds for detection Check Statistics tab for operation counts Verify general_log is enabled (MySQL) 📚 API Endpoints Authentication GET /api/auth/check - Check if authenticated GET /api/auth/has-users - Check if users exist POST /api/auth/setup - Create first user POST /api/auth/login - User login POST /api/auth/logout - User logout Configuration GET /api/config - Get current configuration POST /api/config - Update configuration POST /api/test-connection - Test database connections Sync Control POST /api/sync/start - Start synchronization POST /api/sync/stop - Stop synchronization GET /api/status - Get current sync status Monitoring GET /api/stats - Get sync statistics GET /api/logs - Get operation logs GET /api/chart-stats - Get hourly chart data Scheduling GET /api/schedules - Get all schedules GET /api/schedules/active - Get active schedules only POST /api/schedules - Create new schedule POST /api/schedules/:id - Update schedule DELETE /api/schedules/:id - Delete schedule POST /api/schedules/:id/toggle - Enable/disable schedule User Management GET /api/profile/me - Get user profile POST /api/profile/update-email - Update email POST /api/profile/update-password - Change password 🏗️ Architecture Technology Stack Backend: Rust with Tokio async runtime Web Framework: Axum 0.7 Database Drivers: sqlx 0.7 (MySQL, PostgreSQL, SQLite) Scheduler: tokio-cron-scheduler Frontend: Vanilla JavaScript, Chart.js, Tailwind CSS Icons: Iconify Storage: SQLite for config, stats, and schedules Components Schema Readers: Extract schema from MySQL or PostgreSQL Data Migrators: Use mysqldump or pg_dump for reliable transfers Binlog Reader (MySQL): Monitors general_log for real-time changes Database Writers: Apply operations to multiple slaves in parallel Stats Logger: Track operations in SQLite with hourly aggregation Scheduler Service: Cron-based task scheduler with job management Web Server: Axum HTTP server with middleware authentication Configuration Store: SQLite-backed persistent configuration Data Flow Master DB → Schema Reader → mysqldump/pg_dump → Slaves (Parallel) ↓ ↑ General Log → Binlog Reader → Event Queue → Writers (Parallel) ↓ Stats Logger → SQLite → Web API → Dashboard ↓ Scheduler → Automated Syncs Parallel Processing Multiple slave syncs run concurrently using tokio::spawn Each slave gets independent sync task Results aggregated for unified reporting Errors isolated per slave (one failure doesn't stop others) 🤝 Contributing Contributions are welcome! Please:

Fork the repository Create a feature branch (git checkout -b feature/amazing-feature) Commit your changes (git commit -m 'Add amazing feature') Push to the branch (git push origin feature/amazing-feature) Open a Pull Request 📄 License MIT License - See LICENSE file for details

🙋 Support For issues, questions, or feature requests:

Open an issue on GitHub Check logs at /api/logs endpoint Enable RUST_LOG=debug for detailed logging Review Troubleshooting section above 🎉 Acknowledgments Built with:

Rust - Systems programming language Tokio - Async runtime Axum - Web framework sqlx - Database toolkit Chart.js - Data visualization Tailwind CSS - Styling framework tokio-cron-scheduler - Task scheduling 💡 Use Cases Development Database Cloning: Quickly clone production to staging Test Data: Keep test databases in sync with development Team Collaboration: Share database state across team members Production Read Replicas: Create read-only replicas for load distribution Backup: Scheduled backups to separate servers Disaster Recovery: Maintain hot standby databases Multi-Region: Sync databases across geographical regions Analytics Reporting Databases: Separate analytics workload from production Data Warehousing: Feed data to warehouse systems Business Intelligence: Real-time data for BI tools Version: 2.0.0 Last Updated: December 2025

⚠️ Important Notes Same-Type Only: Synchronization only between identical database types (MySQL→MySQL or PostgreSQL→PostgreSQL) Real-time Limitations: Real-time sync only available for MySQL via general_log PostgreSQL: Supports initial sync only (no real-time monitoring) Reset Mode: USE WITH CAUTION - Drops and recreates slave databases (all data lost) Scheduled Syncs: Always use reset mode for data consistency Performance: Large databases may take time for initial sync Testing: Always test in development before production deployment Permissions: Ensure database users have all required privileges Network: Verify connectivity between sync server and all databases Monitoring: Review logs regularly for errors or warnings 🚧 Limitations Cross-Database Sync: MySQL ↔ PostgreSQL not supported DDL Changes: Schema changes during real-time sync may cause issues PostgreSQL Real-time: Not implemented (architecture limitation) Binary Data: Large binary objects may slow sync performance Conflict Resolution: Last-write-wins strategy (no conflict detection) 🔮 Future Enhancements WAL-based PostgreSQL real-time sync Cross-database type synchronization (MySQL ↔ PostgreSQL) Bidirectional sync support Conflict detection and resolution Custom transformation rules Webhook notifications Prometheus metrics export CLI improvements Web UI enhancements 📝 Changelog Version 2.0.0 (December 2025) ✨ Added multi-slave parallel synchronization ✨ Implemented automated scheduling with cron ✨ Added reset database mode for fresh syncs ✨ Enhanced UI with responsive design ✨ Added active schedules display on home page 🔧 Improved error handling and reporting 🔧 Optimized data transfer with mysqldump/pg_dump 🗑️ Removed Gemini AI integration 🗑️ Removed PostgreSQL real-time sync (not production-ready) 🐛 Fixed cron expression parsing (6-field format) 🐛 Fixed schedule list refresh issues 📚 Updated documentation Version 1.0.0 (Initial Release) Basic MySQL and PostgreSQL sync Web UI with authentication Real-time monitoring (MySQL) Statistics and charts