# MySQL主从集群部署指南：原理分析与Docker实践

在现代数据库架构中，MySQL主从复制是实现高可用性和负载均衡的核心技术。通过在主数据库与从数据库之间建立复制关系，不仅能够提供数据冗余备份，还能有效分散读操作压力。本文将深入解析主从复制原理，并通过Docker容器化部署展示完整实现流程。

## 主从复制原理深度解析

MySQL主从复制基于二进制日志（Binary Log）实现，其核心流程包含三个关键阶段：

```sql

-- 主从复制核心流程示意

-- 1. 主库写入阶段

-- 主库将数据更改记录到二进制日志

-- 格式：Statement-Based（SQL语句）或 Row-Based（行数据）

-- 配置示例：

SHOW MASTER STATUS;

-- 输出：

-- +------------------+----------+--------------+------------------+

-- +------------------+----------+--------------+------------------+

-- | mysql-bin.000001 | 107 | | |

-- +------------------+----------+--------------+------------------+

-- 2. 从库读取阶段

-- 从库I/O线程连接主库，读取二进制日志

-- 配置复制通道：

CHANGE MASTER TO

MASTER_HOST='master_host',

MASTER_USER='replica_user',

MASTER_PASSWORD='password',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=107;

-- 3. 从库应用阶段

-- 从库SQL线程读取中继日志并执行SQL

-- 查看复制状态：

SHOW SLAVE STATUS\G

-- 关键字段：

-- Slave_IO_Running: Yes -- I/O线程状态

-- Slave_SQL_Running: Yes -- SQL线程状态

-- Seconds_Behind_Master: 0 -- 复制延迟秒数

```

## Docker环境准备与网络配置

通过Docker搭建主从集群环境，确保网络连通性和数据持久化：

```yaml

# docker-compose-mysql-cluster.yaml

version: '3.8'

services:

# MySQL主节点

mysql-master:

image: mysql:8.0

container_name: mysql-master

restart: unless-stopped

environment:

MYSQL_ROOT_PASSWORD: master_root_password

MYSQL_REPLICATION_USER: replica_user

MYSQL_REPLICATION_PASSWORD: replica_password

MYSQL_DATABASE: app_db

TZ: Asia/Shanghai

ports:

- "3306:3306"

volumes:

- ./master-data:/var/lib/mysql

- ./master-config:/etc/mysql/conf.d

- ./master-logs:/var/log/mysql

- ./scripts:/docker-entrypoint-initdb.d

networks:

- mysql-cluster-net

command:

- --server-id=1

- --log-bin=mysql-bin

- --binlog-format=ROW

- --gtid-mode=ON

- --enforce-gtid-consistency=ON

- --log-slave-updates=ON

- --binlog-do-db=app_db

# MySQL从节点1

mysql-slave-1:

image: mysql:8.0

container_name: mysql-slave-1

restart: unless-stopped

depends_on:

- mysql-master

environment:

MYSQL_ROOT_PASSWORD: slave_root_password

TZ: Asia/Shanghai

ports:

- "3307:3306"

volumes:

- ./slave1-data:/var/lib/mysql

- ./slave1-config:/etc/mysql/conf.d

- ./slave1-logs:/var/log/mysql

networks:

- mysql-cluster-net

command:

- --server-id=2

- --relay-log=mysql-relay-bin

- --read->

- --super-read->

# MySQL从节点2

mysql-slave-2:

image: mysql:8.0

container_name: mysql-slave-2

restart: unless-stopped

depends_on:

- mysql-master

environment:

MYSQL_ROOT_PASSWORD: slave_root_password

TZ: Asia/Shanghai

ports:

- "3308:3306"

volumes:

- ./slave2-data:/var/lib/mysql

- ./slave2-config:/etc/mysql/conf.d

- ./slave2-logs:/var/log/mysql

networks:

- mysql-cluster-net

command:

- --server-id=3

- --relay-log=mysql-relay-bin

- --read->

- --super-read->

# 监控工具

mysql-monitor:

image: phpmyadmin/phpmyadmin

container_name: mysql-monitor

restart: unless-stopped

depends_on:

- mysql-master

- mysql-slave-1

- mysql-slave-2

environment:

PMA_ARBITRARY: 1

PMA_HOST: mysql-master

ports:

- "8080:80"

networks:

- mysql-cluster-net

networks:

mysql-cluster-net:

driver: bridge

ipam:

config:

- subnet: 172.20.0.0/24

volumes:

master-data:

slave1-data:

slave2-data:

master-config:

slave1-config:

slave2-config:

```

## 主库配置与初始化脚本

创建主库的初始化配置和用户权限设置：

```bash

#!/bin/bash

# init-master.sh - 主库初始化脚本

echo "开始初始化MySQL主库..."

# 等待MySQL服务启动

while ! mysqladmin ping -h"localhost" --silent; do

echo "等待MySQL启动..."

sleep 2

done

echo "MySQL主库已启动，开始配置复制..."

# 创建复制用户

mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << EOF

-- 创建复制用户

CREATE USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY '${MYSQL_REPLICATION_PASSWORD}';

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replica_user'@'%';

-- 创建监控用户

CREATE USER 'monitor_user'@'%' IDENTIFIED BY 'monitor_password';

GRANT SELECT, PROCESS, REPLICATION CLIENT ON *.* TO 'monitor_user'@'%';

-- 创建应用用户

CREATE USER 'app_user'@'%' IDENTIFIED BY 'app_password';

GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app_user'@'%';

-- 刷新权限

FLUSH PRIVILEGES;

-- 查看主库状态

SHOW MASTER STATUS\G

EOF

echo "主库初始化完成"

# 创建测试数据库和数据

mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << EOF

-- 使用应用数据库

USE app_db;

-- 创建测试表

CREATE TABLE IF NOT EXISTS users (

id INT AUTO_INCREMENT PRIMARY KEY,

username VARCHAR(50) NOT NULL UNIQUE,

email VARCHAR(100) NOT NULL,

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

INDEX idx_username (username),

INDEX idx_email (email)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS orders (

id INT AUTO_INCREMENT PRIMARY KEY,

user_id INT NOT NULL,

order_number VARCHAR(50) NOT NULL UNIQUE,

amount DECIMAL(10,2) NOT NULL,

status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,

INDEX idx_user_id (user_id),

INDEX idx_order_number (order_number),

INDEX idx_status (status)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据

INSERT INTO users (username, email) VALUES

('test_user1', 'user1@example.com'),

('test_user2', 'user2@example.com'),

('test_user3', 'user3@example.com');

INSERT INTO orders (user_id, order_number, amount, status) VALUES

(1, 'ORD001', 100.50, 'completed'),

(2, 'ORD002', 200.75, 'processing'),

(3, 'ORD003', 150.25, 'pending');

EOF

echo "测试数据创建完成"

```

## 从库配置与复制设置

配置从库并建立与主库的复制关系：

```bash

#!/bin/bash

# init-slave.sh - 从库初始化脚本

SLAVE_ID=$1

MASTER_HOST=$2

echo "开始初始化从库 ${SLAVE_ID}，连接主库 ${MASTER_HOST}..."

# 等待从库MySQL服务启动

while ! mysqladmin ping -h"localhost" --silent; do

echo "等待从库MySQL启动..."

sleep 2

done

echo "从库MySQL已启动，开始配置复制..."

# 获取主库二进制日志位置

MASTER_INFO=$(mysql -h"${MASTER_HOST}" -ureplica_user -preplica_password -e "SHOW MASTER STATUS\G")

MASTER_LOG_FILE=$(echo "${MASTER_INFO}" | grep "File:" | awk '{print $2}')

MASTER_LOG_POS=$(echo "${MASTER_INFO}" | grep "Position:" | awk '{print $2}')

echo "主库二进制日志位置：${MASTER_LOG_FILE} @ ${MASTER_LOG_POS}"

# 配置从库复制

mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << EOF

-- 停止复制（如果已启动）

STOP SLAVE;

-- 重置复制（清除现有配置）

RESET SLAVE ALL;

-- 配置主库连接

CHANGE MASTER TO

MASTER_HOST='${MASTER_HOST}',

MASTER_USER='replica_user',

MASTER_PASSWORD='replica_password',

MASTER_PORT=3306,

MASTER_LOG_FILE='${MASTER_LOG_FILE}',

MASTER_LOG_POS=${MASTER_LOG_POS},

MASTER_CONNECT_RETRY=10,

MASTER_RETRY_COUNT=86400,

MASTER_HEARTBEAT_PERIOD=60,

MASTER_SSL=0;

-- 启动复制

START SLAVE;

-- 查看复制状态

SHOW SLAVE STATUS\G

EOF

echo "从库 ${SLAVE_ID} 复制配置完成"

# 检查复制状态

sleep 5

mysql -uroot -p"${MYSQL_ROOT_PASSWORD}" << EOF

SHOW SLAVE STATUS\G

EOF

echo "从库初始化完成"

```

## 自动化部署与管理脚本

创建完整的集群管理脚本：

```python

#!/usr/bin/env python3

# mysql_cluster_manager.py

import subprocess

import time

import json

import yaml

from datetime import datetime

from typing import Dict, List, Optional

import mysql.connector

from mysql.connector import Error

class MySQLClusterManager:

def __init__(self, config_file='cluster-config.yaml'):

self.config = self.load_config(config_file)

self.cluster_state = {}

def load_config(self, config_file):

"""加载集群配置"""

with open(config_file, 'r') as f:

config = yaml.safe_load(f)

default_config = {

'cluster': {

'name': 'mysql-cluster',

'network': 'mysql-cluster-net'

},

'master': {

'host': 'mysql-master',

'port': 3306,

'root_password': 'master_root_password',

'replica_user': 'replica_user',

'replica_password': 'replica_password'

},

'slaves': [

{

'name': 'mysql-slave-1',

'host': 'mysql-slave-1',

'port': 3306

},

{

'name': 'mysql-slave-2',

'host': 'mysql-slave-2',

'port': 3306

}

]

}

# 合并配置

if config:

default_config.update(config)

return default_config

def deploy_cluster(self):

"""部署整个集群"""

print("开始部署MySQL主从集群...")

# 1. 启动Docker容器

print("启动Docker容器...")

subprocess.run(['docker-compose', '-f', 'docker-compose-mysql-cluster.yaml', 'up', '-d'],

check=True)

# 2. 等待服务启动

print("等待MySQL服务启动...")

self.wait_for_mysql(self.config['master']['host'], self.config['master']['port'])

# 3. 初始化主库

print("初始化主库...")

self.init_master()

# 4. 初始化从库

print("初始化从库...")

for slave in self.config['slaves']:

self.wait_for_mysql(slave['host'], slave['port'])

self.init_slave(slave['host'])

# 5. 验证集群状态

print("验证集群状态...")

cluster_status = self.check_cluster_status()

print("\n=== 集群部署完成 ===")

print(f"主库: {self.config['master']['host']}:{self.config['master']['port']}")

print(f"从库数量: {len(self.config['slaves'])}")

print(f"集群状态: {cluster_status['overall_status']}")

return cluster_status

def wait_for_mysql(self, host, port, timeout=120):

"""等待MySQL服务启动"""

start_time = time.time()

while time.time() - start_time < timeout:

try:

conn = mysql.connector.connect(

host=host,

port=port,

user='root',

password=self.config['master']['root_password']

)jc.nexgo.vip\rf.nexgo.vip\qp.nexgo.vip\

conn.close()

print(f"✓ MySQL服务 {host}:{port} 已就绪")

return True

except Error:

print(f"等待 {host}:{port} 启动...")

time.sleep(2)

raise TimeoutError(f"MySQL服务 {host}:{port} 启动超时")

def init_master(self):

"""初始化主库配置"""

master_config = self.config['master']

try:

conn = mysql.connector.connect(

host=master_config['host'],

port=master_config['port'],

user='root',

password=master_config['root_password']

)

cursor = conn.cursor()

# 创建复制用户

cursor.execute(f"""

CREATE USER IF NOT EXISTS '{master_config['replica_user']}'@'%'

IDENTIFIED WITH mysql_native_password BY '{master_config['replica_password']}'

""")

cursor.execute(f"""

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.*

TO '{master_config['replica_user']}'@'%'

""")

# 创建监控用户

cursor.execute("""

CREATE USER IF NOT EXISTS 'cluster_monitor'@'%'

IDENTIFIED BY 'monitor_password_123'

""")

cursor.execute("""

GRANT SELECT, PROCESS, REPLICATION CLIENT, SHOW DATABASES

ON *.* TO 'cluster_monitor'@'%'

""")

conn.commit()

cursor.close()

conn.close()

print("✓ 主库用户权限配置完成")

except Error as e:

print(f"主库初始化失败: {e}")

raise

def init_slave(self, slave_host):

"""初始化从库并配置复制"""

master_config = self.config['master']

# 获取主库二进制日志位置

try:

conn = mysql.connector.connect(

host=master_config['host'],

port=master_config['port'],

user=master_config['replica_user'],

password=master_config['replica_password']

)

cursor = conn.cursor(dictionary=True)

cursor.execute("SHOW MASTER STATUS")

master_status = cursor.fetchone()

cursor.close()

conn.close()

if not master_status:

raise Exception("无法获取主库二进制日志状态")

except Error as e:

print(f"获取主库状态失败: {e}")

raise

# 配置从库复制

try:

conn = mysql.connector.connect(

host=slave_host,

port=3306,

user='root',

password=self.config['master']['root_password']

)

cursor = conn.cursor()

# 停止并重置现有复制

cursor.execute("STOP SLAVE")

cursor.execute("RESET SLAVE ALL")

# 配置主库连接

change_master_sql = f"""

CHANGE MASTER TO

MASTER_HOST='{master_config['host']}',

MASTER_USER='{master_config['replica_user']}',

MASTER_PASSWORD='{master_config['replica_password']}',

MASTER_PORT={master_config['port']},

MASTER_LOG_FILE='{master_status['File']}',

MASTER_LOG_POS={master_status['Position']},

MASTER_CONNECT_RETRY=10,

MASTER_RETRY_COUNT=86400,

MASTER_HEARTBEAT_PERIOD=60

"""

cursor.execute(change_master_sql)

# 启动复制

cursor.execute("START SLAVE")

conn.commit()

cursor.close()

conn.close()

print(f"✓ 从库 {slave_host} 复制配置完成")

except Error as e:

print(f"从库 {slave_host} 配置失败: {e}")

raise

def check_cluster_status(self):

"""检查集群状态"""

cluster_status = {

'master': {},

'slaves': [],

'overall_status': 'healthy',

'timestamp': datetime.now().isoformat()

}

# 检查主库状态

try:

conn = mysql.connector.connect(

host=self.config['master']['host'],

port=self.config['master']['port'],

user='root',

password=self.config['master']['root_password']

)

cursor = conn.cursor(dictionary=True)

# 检查主库状态

cursor.execute("SHOW MASTER STATUS")

master_status = cursor.fetchone()

cluster_status['master'] = {

'host': self.config['master']['host'],

'status': 'online',

'binary_log': master_status['File'] if master_status else None,

'position': master_status['Position'] if master_status else None

}

cursor.close()

conn.close()

except Error as e:

cluster_status['master']['status'] = 'offline'

cluster_status['master']['error'] = str(e)

cluster_status['overall_status'] = 'degraded'

# 检查从库状态

for slave in self.config['slaves']:

slave_status = {

'name': slave['name'],

'host': slave['host'],

'status': 'unknown'

}

try:

conn = mysql.connector.connect(

host=slave['host'],

port=slave['port'],

user='root',

password=self.config['master']['root_password']

)

cursor = conn.cursor(dictionary=True)

# 检查从库复制状态

cursor.execute("SHOW SLAVE STATUS")

slave_info = cursor.fetchone()

if slave_info:

slave_status.update({

'status': 'online',

'io_running': slave_info['Slave_IO_Running'],

'sql_running': slave_info['Slave_SQL_Running'],

'seconds_behind_master': slave_info['Seconds_Behind_Master'],

'last_error': slave_info['Last_Error'],

'master_host': slave_info['Master_Host'],

'master_log_file': slave_info['Master_Log_File'],

'read_master_log_pos': slave_info['Read_Master_Log_Pos']

})

# 检查复制是否正常

if (slave_info['Slave_IO_Running'] != 'Yes' or

slave_info['Slave_SQL_Running'] != 'Yes'):

slave_status['replication_status'] = 'error'

cluster_status['overall_status'] = 'degraded'

else:

slave_status['replication_status'] = 'healthy'

cursor.close()

conn.close()

except Error as e:

slave_status['status'] = 'offline'

slave_status['error'] = str(e)

cluster_status['overall_status'] = 'degraded'

cluster_status['slaves'].append(slave_status)

return cluster_status

def promote_slave_to_master(self, slave_host):

"""将从库提升为主库（故障转移）"""

print(f"开始将从库 {slave_host} 提升为主库...")

# 1. 停止所有从库复制

for slave in self.config['slaves']:

if slave['host'] != slave_host:

self.stop_replication(slave['host'])

# 2. 停止目标从库复制并重置

self.stop_replication(slave_host)

try:

conn = mysql.connector.connect(

host=slave_host,

port=3306,

user='root',

password=self.config['master']['root_password']

)

cursor = conn.cursor()

# 重置从库身份

cursor.execute("RESET SLAVE ALL")

# 启用二进制日志

cursor.execute("SET GLOBAL log_bin = ON")

cursor.execute("SET GLOBAL binlog_format = 'ROW'")

conn.commit()

cursor.close()

conn.close()

print(f"✓ 从库 {slave_host} 已提升为主库")

# 更新配置

old_master = self.config['master']['host']

self.config['master']['host'] = slave_host

# 重新配置其他从库连接新的主库

for slave in self.config['slaves']:

if slave['host'] != slave_host:

self.init_slave(slave['host'])

return {

'success': True,

'message': f'故障转移完成，新主库: {slave_host}',

'old_master': old_master,

'new_master': slave_host

}

except Error as e:

return {

'success': False,

'message': f'故障转移失败: {e}'

}

def main():

manager = MySQLClusterManager('cluster-config.yaml')

# 部署集群

status = manager.deploy_cluster()

# 打印状态报告

print("\n集群状态报告:")

print(json.dumps(status, indent=2, default=str))

# 定时监控

import schedule

import time

def monitor_job():

status = manager.check_cluster_status()

print(f"\n[{datetime.now()}] 集群监控检查:")

print(f" 整体状态: {status['overall_status']}")

print(f" 从库数量: {len(status['slaves'])}")

for slave in status['slaves']:

if slave.get('replication_status') == 'healthy':

print(f" ✓ {slave['name']}: 延迟 {slave.get('seconds_behind_master', 'N/A')}秒")

else:

print(f" ✗ {slave['name']}: {slave.get('error', '复制异常')}")

# 每5分钟监控一次

schedule.every(5).minutes.do(monitor_job)

print("\n集群监控已启动，按Ctrl+C退出...")

while True:

schedule.run_pending()

time.sleep(1)

if __name__ == '__main__':

main()

```

## 监控与维护脚本

创建集群监控和维护工具：

```bash

#!/bin/bash

# mysql_cluster_monitor.sh

# 集群监控脚本

MASTER_HOST="mysql-master"

MASTER_PORT=3306

MASTER_USER="cluster_monitor"

MASTER_PASSWORD="monitor_password_123"

SLAVES=("mysql-slave-1" "mysql-slave-2")

CHECK_INTERVAL=60 # 检查间隔(秒)

ALERT_THRESHOLD=30 # 复制延迟告警阈值(秒)

# 发送告警函数

send_alert() {

local severity=$1

local message=$2

local timestamp=$(date '+%Y-%m-%d %H:%M:%S')

echo "[$timestamp] [$severity] $message"

# 发送邮件告警（如果配置）

# echo "$message" | mail -s "MySQL集群告警" admin@example.com

# 发送到Slack（如果配置）

# curl -X POST -H 'Content-type: application/json' \

# --data "{\"text\":\"[$severity] $message\"}" \

# YOUR_SLACK_WEBHOOK_URL

}

# 检查主库状态

check_master() {

echo "检查主库状态..."

if ! mysqladmin ping -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" --silent; then

send_alert "CRITICAL" "主库 $MASTER_HOST 无法连接"

return 1

fi

# 检查主库负载

local master_stats=$(mysql -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SHOW GLOBAL STATUS LIKE 'Threads_connected';" \

-e "SHOW PROCESSLIST;" 2>/dev/null)

echo "主库连接正常"

return 0

}

# 检查从库状态

check_slave() {

local slave_host=$1

echo "检查从库 $slave_host 状态..."

# 检查从库连接

if ! mysqladmin ping -h"$slave_host" -P3306 \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" --silent; then

send_alert "CRITICAL" "从库 $slave_host 无法连接"

return 1

fi

# 获取从库复制状态

local slave_status=$(mysql -h"$slave_host" -P3306 \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SHOW SLAVE STATUS\G" 2>/dev/null)

if [ -z "$slave_status" ]; then

send_alert "WARNING" "从库 $slave_host 未配置复制"

return 1

fi

# 解析关键状态

local io_running=$(echo "$slave_status" | grep "Slave_IO_Running:" | awk '{print $2}')

local sql_running=$(echo "$slave_status" | grep "Slave_SQL_Running:" | awk '{print $2}')

local seconds_behind=$(echo "$slave_status" | grep "Seconds_Behind_Master:" | awk '{print $2}')

local last_error=$(echo "$slave_status" | grep "Last_Error:" | awk '{print $2}')

# 检查复制状态

if [ "$io_running" != "Yes" ] || [ "$sql_running" != "Yes" ]; then

send_alert "CRITICAL" "从库 $slave_host 复制异常: IO=$io_running, SQL=$sql_running"

send_alert "CRITICAL" "错误信息: $last_error"

return 1

fi

# 检查复制延迟

if [ "$seconds_behind" != "NULL" ] && [ "$seconds_behind" -gt "$ALERT_THRESHOLD" ]; then

send_alert "WARNING" "从库 $slave_host 复制延迟: ${seconds_behind}秒"

fi

echo "从库 $slave_host 状态正常，延迟: ${seconds_behind:-0}秒"

return 0

}

# 检查数据一致性

check_data_consistency() {

echo "检查数据一致性..."

# 检查主从数据行数一致性

local master_count=$(mysql -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SELECT COUNT(*) FROM app_db.users;" 2>/dev/null | tail -1)

for slave in "${SLAVES[@]}"; do

local slave_count=$(mysql -h"$slave" -P3306 \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SELECT COUNT(*) FROM app_db.users;" 2>/dev/null | tail -1)

if [ "$master_count" != "$slave_count" ]; then

send_alert "CRITICAL" "数据不一致: 主库users表=$master_count行, 从库$slave=$slave_count行"

return 1

fi

done

echo "数据一致性检查通过"

return 0

}

# 备份检查

check_backups() {

echo "检查备份状态..."

local backup_dir="/var/backups/mysql"

local today=$(date +%Y%m%d)

if [ -f "$backup_dir/full_backup_${today}.sql.gz" ]; then

echo "今日备份已存在"

else

send_alert "WARNING" "今日全量备份未找到"

fi

# 检查备份文件大小

local latest_backup=$(ls -t "$backup_dir"/full_backup_*.sql.gz 2>/dev/null | head -1)

if [ -n "$latest_backup" ]; then

local backup_size=$(du -h "$latest_backup" | cut -f1)

echo "最新备份: $latest_backup ($backup_size)"

fi\pq.nexgo.vip\cf.nexgo.vip\xf.nexgo.vip\

}

# 性能检查

check_performance() {

echo "检查性能指标..."

# 检查慢查询

local slow_queries=$(mysql -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SHOW GLOBAL STATUS LIKE 'Slow_queries';" 2>/dev/null | tail -1 | awk '{print $2}')

if [ "$slow_queries" -gt 100 ]; then

send_alert "WARNING" "慢查询数量较多: $slow_queries"

fi

# 检查连接数

local max_connections=$(mysql -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SHOW VARIABLES LIKE 'max_connections';" 2>/dev/null | tail -1 | awk '{print $2}')

local current_connections=$(mysql -h"$MASTER_HOST" -P"$MASTER_PORT" \

-u"$MASTER_USER" -p"$MASTER_PASSWORD" \

-e "SHOW GLOBAL STATUS LIKE 'Threads_connected';" 2>/dev/null | tail -1 | awk '{print $2}')

local connection_usage=$((current_connections * 100 / max_connections))

if [ "$connection_usage" -gt 80 ]; then

send_alert "WARNING" "连接数使用率过高: ${connection_usage}% (${current_connections}/${max_connections})"

fi

echo "性能检查完成"

}

# 生成监控报告

generate_report() {

local report_file="/var/log/mysql-cluster/report_$(date +%Y%m%d_%H%M%S).log"

{

echo "=== MySQL集群监控报告 ==="

echo "生成时间: $(date)"

echo ""

echo "1. 主库状态:"

check_master

echo ""

echo "2. 从库状态:"

for slave in "${SLAVES[@]}"; do

check_slave "$slave"

done

echo ""

echo "3. 数据一致性:"

check_data_consistency

echo ""

echo "4. 备份状态:"

check_backups

echo ""

echo "5. 性能指标:"

check_performance

echo ""

echo "=== 报告结束 ==="

} > "$report_file"

echo "监控报告已生成: $report_file"

}

# 主监控循环

main() {

echo "开始MySQL集群监控..."

echo "检查间隔: ${CHECK_INTERVAL}秒"

echo "告警阈值: ${ALERT_THRESHOLD}秒延迟"

echo ""

while true; do

local timestamp=$(date '+%Y-%m-%d %H:%M:%S')

echo "[$timestamp] 开始集群检查..."

# 执行检查

check_master

for slave in "${SLAVES[@]}"; do

check_slave "$slave"

done

# 每小时执行一次完整检查

if [ "$(date +%M)" = "00" ]; then

check_data_consistency

check_backups

check_performance

# 每天生成一次完整报告

if [ "$(date +%H)" = "00" ]; then

generate_report

fi

echo "检查完成，等待 ${CHECK_INTERVAL} 秒..."

echo ""

sleep "$CHECK_INTERVAL"

done

}

# 执行主函数

main

```

## 故障恢复与数据同步

处理常见故障场景的恢复方案：

```sql

-- 1. 从库复制中断恢复

-- 查看从库错误

SHOW SLAVE STATUS\G

-- 跳过特定错误（谨慎使用）

STOP SLAVE;

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

START SLAVE;

-- 2. 重新同步从库数据

-- 在主库创建数据快照

mysqldump -h master_host -u root -p \

--single-transaction \

--master-data=2 \

--databases app_db > master_snapshot.sql

-- 在从库恢复

STOP SLAVE;

source master_snapshot.sql;

START SLAVE;

-- 3. GTID故障恢复

-- 查看GTID状态

SELECT @@GLOBAL.GTID_EXECUTED;

SHOW SLAVE STATUS\G

-- 重置GTID

STOP SLAVE;

RESET MASTER;

SET @@GLOBAL.GTID_PURGED='';

START SLAVE;

-- 4. 半同步复制监控

-- 检查半同步状态

SHOW STATUS LIKE 'Rpl_semi_sync%';

-- 启用半同步复制（主库）

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

SET GLOBAL rpl_semi_sync_master_enabled = 1;

-- 从库

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';

SET GLOBAL rpl_semi_sync_slave_enabled = 1;

-- 5. 延迟复制配置

-- 设置从库延迟（秒）

CHANGE MASTER TO MASTER_DELAY = 3600; -- 延迟1小时

-- 查看延迟状态

SHOW SLAVE STATUS\G

-- 关注：SQL_Remaining_Delay 字段

```

## 总结与最佳实践

基于上述部署实践，总结MySQL主从集群的关键要点：

1. **架构设计**：

- 根据业务需求选择复制模式（异步/半同步）

- 合理规划从库数量，避免过多从库影响主库性能

- 考虑读写分离架构，将读操作分散到从库

2. **监控维护**：

- 建立完善的监控告警机制

- 定期检查复制延迟和数据一致性

- 实施定期备份和恢复演练

3. **故障处理**：

- 制定清晰的故障切换流程

- 准备数据不一致的修复方案

- 建立回滚机制应对升级失败

4. **性能优化**：

- 合理配置二进制日志格式和保留策略

- 优化网络连接和缓冲区设置

- 监控并调整从库的并行复制线程

通过Docker容器化部署，MySQL主从集群的搭建和维护变得更加简单高效。这种方案特别适合开发测试环境和中小规模生产部署，能够以较低成本实现数据库的高可用架构。

ITPub博客

MySQL主从集群部署指南：原理分析与Docker实践

最新文章