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

2. Place the backend code in backend/index.js

3. 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"]

4. 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"
  }
}

5. 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

6. Configure environment variables in .env

7. 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