Skip to content

Real-time MySQL replication tracker using Node.js, React, @vlasky/zongji, and @vlasky/mysql. Supports multi-source binlog monitoring, WebSocket live updates, CORS, error tracking, and Docker Compose deployment with a modern dashboard UI.

Notifications You must be signed in to change notification settings

justinayson/mysql-replicator

Folders and files

NameName
Last commit message
Last commit date

Latest commit

Β 

History

1 Commit
Β 
Β 
Β 
Β 
Β 
Β 
Β 
Β 

Repository files navigation

MySQL Replicator Documentation

Overview

MySQL Replicator is a real-time database replication monitoring system that tracks changes across multiple MySQL sources and replicates them to a target database. It provides a web-based dashboard with live statistics and WebSocket-based real-time updates.

Architecture

  • Backend: Node.js application using ZongJi for MySQL binlog monitoring
  • Frontend: React application with real-time WebSocket connection
  • Database: MySQL replication using binary logs
  • Deployment: Docker Compose with containerized services

Features

  • πŸ“‘ Real-time MySQL binlog monitoring
  • πŸ“Š Live statistics dashboard
  • πŸ”„ Multi-source replication support
  • πŸ”’ CORS and origin validation
  • πŸ“ˆ WebSocket-based live updates
  • πŸ₯ Health monitoring and error tracking
  • πŸ” Detailed source information

Prerequisites

  • Docker and Docker Compose
  • MySQL servers with binary logging enabled
  • Network access between replicator and MySQL servers

Configuration

Environment Variables

Create a .env file in the project root:

# Target Database (where data is replicated to)
TARGET_DB_HOST=your-target-host
TARGET_DB_PORT=3306
TARGET_DB_USER=replicator_user
TARGET_DB_PASSWORD=your-password

# MySQL Replication User (must have REPLICATION SLAVE privileges)
MYSQL_REPL_USER=replication_user
MYSQL_REPL_PASSWORD=replication_password

# Source 1 Configuration
SOURCE1_NAME=primary_db
SOURCE1_HOST=192.168.1.100
SOURCE1_PORT=3306
SOURCE1_SCHEMAS=database1,database2,database3

# Source 2 Configuration (optional)
SOURCE2_NAME=secondary_db
SOURCE2_HOST=192.168.1.101
SOURCE2_PORT=3306
SOURCE2_SCHEMAS=database4,database5

# Application Port
PORT=3005

MySQL Setup

1. Enable Binary Logging

Add to your MySQL configuration (my.cnf):

[mysqld]
log-bin=mysql-bin
server-id=1
binlog-format=ROW

2. Create Replication User

-- On source MySQL servers
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

-- On target MySQL server
CREATE USER 'replicator_user'@'%' IDENTIFIED BY 'your-password';
GRANT ALL PRIVILEGES ON your_databases.* TO 'replicator_user'@'%';

FLUSH PRIVILEGES;

Installation & Deployment

Using Docker Compose

  1. Clone or create project structure:
mkdir mysql-replicator
cd mysql-replicator
mkdir backend frontend
  1. Place the backend code in backend/index.js

  2. Create backend Dockerfile (backend/Dockerfile):

FROM node:20-alpine

WORKDIR /usr/src/app
COPY package*.json ./
RUN npm install --production
COPY . .
EXPOSE 3005
CMD ["node", "index.js"]
  1. Create backend package.json (backend/package.json):
{
  "name": "mysql-replicator-backend",
  "version": "1.0.0",
  "description": "MySQL replication monitor",
  "main": "index.js",
  "scripts": {
    "start": "node index.js",
    "dev": "nodemon index.js"
  },
  "dependencies": {
    "@vlasky/mysql": "^2.18.1",
    "@vlasky/zongji": "^0.4.0",
    "cors": "^2.8.5",
    "dotenv": "^16.0.3",
    "express": "^4.18.2",
    "ws": "^8.13.0"
  }
}
  1. Create docker-compose.yml in project root:
version: "3.8"

services:
  backend:
    build:
      context: ./backend
    container_name: mysql-replicator-backend
    restart: unless-stopped
    env_file: .env
    ports:
      - "8089:3005"
    extra_hosts:
      - "host.docker.internal:host-gateway"
    networks:
      - appnet

  frontend:
    build:
      context: ./frontend
      args:
        REACT_APP_API_URL: http://localhost:8089
        REACT_APP_WS_URL: ws://localhost:6001/ws
    container_name: mysql-replicator-frontend
    restart: unless-stopped
    depends_on:
      - backend
    ports:
      - "3005:80"
    networks:
      - appnet

networks:
  appnet:
    driver: bridge
  1. Configure environment variables in .env

  2. Deploy:

docker-compose up -d

API Endpoints

Health Check

GET /health

Response:

{
  "status": "ok",
  "uptime": "2h 30m 45s",
  "uptimeSeconds": 9045,
  "activeSources": 2,
  "sources": [
    {
      "name": "primary_db",
      "status": "connected",
      "errors": 0,
      "lastActivity": "2024-01-15T10:30:00.000Z"
    }
  ],
  "timestamp": "2024-01-15T10:35:00.000Z"
}

Statistics

GET /stats

Response:

{
  "timestamp": "2024-01-15T10:35:00.000Z",
  "stats": {
    "primary_db": {
      "writerows": 1250,
      "updaterows": 850,
      "deleterows": 45
    }
  },
  "connectionStatus": {
    "primary_db": "connected"
  },
  "errorCounts": {
    "primary_db": 0
  },
  "lastActivity": {
    "primary_db": "2024-01-15T10:30:00.000Z"
  }
}

Source Information

GET /sources

Response:

{
  "timestamp": "2024-01-15T10:35:00.000Z",
  "sources": [
    {
      "name": "primary_db",
      "host": "192.168.1.100",
      "port": 3306,
      "schemas": ["database1", "database2"],
      "status": "connected",
      "stats": {
        "writerows": 1250,
        "updaterows": 850,
        "deleterows": 45
      },
      "errors": 0,
      "lastActivity": "2024-01-15T10:30:00.000Z"
    }
  ]
}

WebSocket Connection

Connect to real-time statistics:

const ws = new WebSocket("ws://localhost:60001/ws");

ws.onmessage = (event) => {
  const data = JSON.parse(event.data);
  if (data.type === "stats") {
    console.log("Live stats:", data.data);
  }
};

WebSocket Message Format:

{
  "type": "stats",
  "data": {
    "timestamp": "2024-01-15T10:35:00.000Z",
    "stats": {
      /* statistics object */
    },
    "connectionStatus": {
      /* connection status */
    },
    "errorCounts": {
      /* error counts */
    },
    "lastActivity": {
      /* last activity timestamps */
    }
  }
}

Monitoring & Troubleshooting

View Logs

# All services
docker-compose logs -f

# Backend only
docker-compose logs -f backend

# Frontend only
docker-compose logs -f frontend

Common Issues

1. Connection Refused

  • Check MySQL server accessibility
  • Verify firewall rules
  • Ensure MySQL is accepting connections from replicator IP

2. Permission Denied

  • Verify replication user has correct privileges
  • Check target database user permissions
  • Ensure user can connect from replicator host

3. Binary Log Issues

  • Confirm binary logging is enabled (SHOW VARIABLES LIKE 'log_bin')
  • Check binlog format is ROW (SHOW VARIABLES LIKE 'binlog_format')
  • Verify server-id is set and unique

4. CORS Errors

  • Update allowed origins in corsOptions
  • Check frontend API URL configuration
  • Verify network connectivity

Performance Monitoring

Monitor these metrics:

  • Write/Update/Delete Rates: Track replication activity
  • Error Counts: Monitor replication failures
  • Connection Status: Ensure sources stay connected
  • Last Activity: Detect stalled replication

Security Considerations

  • πŸ”’ CORS Protection: Only specified origins allowed
  • πŸ”‘ Database Credentials: Use dedicated replication users with minimal privileges
  • 🌐 Network Security: Restrict MySQL access to replicator IP
  • πŸ“ Logging: All connections and errors are logged

Scaling & High Availability

Multiple Sources

Add additional sources by extending environment variables:

SOURCE3_NAME=tertiary_db
SOURCE3_HOST=192.168.1.102
SOURCE3_PORT=3306
SOURCE3_SCHEMAS=database6,database7

Load Balancing

  • Use reverse proxy (nginx) for multiple backend instances
  • Implement sticky sessions for WebSocket connections
  • Consider Redis for shared state across instances

Maintenance

Backup Considerations

  • Monitor replication lag
  • Ensure target database backups include replicated data
  • Test failover procedures regularly

Updates

# Update application
git pull
docker-compose build
docker-compose up -d

# Update dependencies
cd backend
npm update
docker-compose build backend
docker-compose up -d backend

Support

For issues and questions:

  1. Check application logs
  2. Verify MySQL configuration
  3. Test network connectivity
  4. Review error counts in /health endpoint

Last updated: June 2025

About

Real-time MySQL replication tracker using Node.js, React, @vlasky/zongji, and @vlasky/mysql. Supports multi-source binlog monitoring, WebSocket live updates, CORS, error tracking, and Docker Compose deployment with a modern dashboard UI.

Resources

Stars

Watchers

Forks

Releases

No releases published

Packages

No packages published