# 启用二进制日志
log-bin = /mysql/logs/binlog

# 二进制日志格式，建议使用ROW格式
binlog-format = ROW

# 二进制日志过期时间，单位秒
expire-logs-days = 7

# 最大二进制日志文件大小
max-binlog-size = 1G

# 启用GTID
gtid-mode = ON
enforce-gtid-consistency = ON

# 从库参数配置
[mysqld]
# 服务器ID，必须唯一
server-id = 2

# 启用中继日志
relay-log = /mysql/logs/relay-bin

# 中继日志索引文件
relay-log-index = /mysql/logs/relay-bin.index

# 中继日志自动恢复
relay-log-recovery = ON

# 从库只读
read-only = ON

# 跳过复制错误
# slave-skip-errors = 1062,1032

# 并行复制
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4

# 复制延迟
# sql_slave_skip_counter = 1

2. 硬件优化：
– 为从库配置足够的CPU和内存
– 使用高速存储设备，如SSD
– 确保主从库的硬件配置相当

3. 参数优化：
– 调整二进制日志格式，使用ROW格式
– 启用并行复制，提高从库的应用速度
– 调整复制线程的优先级

4. 架构优化：
– 使用级联复制，减轻主库的复制压力
– 合理分配从库的用途，如专门用于备份或读负载
– 考虑使用多源复制，集中数据

5. 监控优化：
– 配置完善的监控机制，及时发现复制延迟
– 定期检查复制状态，确保复制正常运行
– 建立复制延迟告警机制

# 性能优化参数
# 主库参数
binlog_cache_size = 32M
sync_binlog = 1

# 从库参数
slave_parallel_workers = 4
slave_parallel_type = LOGICAL_CLOCK
relay_log_recovery = 1
read_only = 1

2. 从库的IO线程：
– 连接主库，请求二进制日志
– 接收主库发送的二进制日志，写入中继日志

3. 从库的SQL线程：
– 读取中继日志，解析其中的SQL语句
– 执行SQL语句，将数据应用到从库

# 主从复制流程
1. 主库执行写操作，将操作记录到二进制日志
2. 从库的IO线程连接主库，请求二进制日志
3. 主库的binlog dump线程将二进制日志发送给从库
4. 从库的IO线程将接收到的二进制日志写入中继日志
5. 从库的SQL线程读取中继日志，执行其中的SQL语句
6. 从库的数据与主库保持一致

# 复制过程中的关键步骤
1. 主库生成二进制日志：主库将写操作记录到二进制日志
2. 从库请求二进制日志：从库的IO线程向主库请求二进制日志
3. 主库发送二进制日志：主库的binlog dump线程将二进制日志发送给从库
4. 从库写入中继日志：从库的IO线程将二进制日志写入中继日志
5. 从库应用中继日志：从库的SQL线程执行中继日志中的SQL语句
6. 从库确认复制：从库记录已应用的二进制日志位置

2. 二进制日志的格式：
– STATEMENT格式：记录SQL语句
– ROW格式：记录行的变更
– MIXED格式：混合使用STATEMENT和ROW格式

3. 二进制日志的作用：
– 主从复制：从库通过复制二进制日志来同步数据
– 数据恢复：通过二进制日志进行时间点恢复
– 审计：记录所有写操作，用于审计和故障分析

4. 二进制日志的管理：
– 二进制日志文件：以binlog.000001、binlog.000002等命名
– 二进制日志索引：记录所有二进制日志文件的名称
– 二进制日志过期：通过expire-logs-days参数设置过期时间

# 二进制日志格式比较
| 格式 | 优点 | 缺点 | 适用场景 |
|——|——|——|———-|
| STATEMENT | 日志文件小，可读性好 | 可能导致数据不一致 | 简单的SQL操作 |
| ROW | 数据一致性好 | 日志文件大 | 复杂的SQL操作，如更新、删除 |
| MIXED | 平衡优缺点 | 管理复杂 | 一般场景 |

# 二进制日志配置
[mysqld]
log-bin = /mysql/logs/binlog
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 1G

2. 从库的IO线程：
– 连接主库，请求二进制日志
– 接收主库发送的二进制日志
– 将二进制日志写入中继日志
– 记录已接收的二进制日志位置

3. 从库的SQL线程：
– 读取中继日志中的事件
– 解析事件，执行相应的SQL语句
– 记录已执行的中继日志位置

# 复制线程的状态
1. 主库的binlog dump线程状态：
– Sending binlog event to slave：正在发送二进制日志事件
– Waiting for binlog to be updated：等待二进制日志更新

2. 从库的IO线程状态：
– Connecting to master：正在连接主库
– Waiting for master to send event：等待主库发送事件
– Reconnecting after a failed binlog dump request：连接失败后重连

3. 从库的SQL线程状态：
– Waiting for slave workers to process their queues：等待并行复制线程处理队列
– Slave has read all relay log; waiting for more updates：已读取所有中继日志，等待更多更新

# 复制线程的优化
1. 增加从库的SQL线程数量：启用并行复制
2. 优化从库的IO线程：增加网络缓冲区大小
3. 监控复制线程状态：及时发现和处理线程异常

# 重启MySQL服务
systemctl restart mysqld

# 创建复制用户
mysql> CREATE USER ‘repl’@’192.168.1.%’ IDENTIFIED BY ‘ReplPassword123!’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’192.168.1.%’;
mysql> FLUSH PRIVILEGES;

# 锁定表并获取主库状态
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 123 | | | |
+—————+———-+————–+——————+——————-+

# 步骤2：配置从库
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin
relay-log-index = /mysql/logs/relay-bin.index
relay-log-recovery = ON
read-only = ON
slave-parallel-type = LOGICAL_CLOCK
slave-parallel-workers = 4

# 重启MySQL服务
systemctl restart mysqld

# 配置从库复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;

# 启动复制
mysql> START SLAVE;

# 步骤3：验证复制状态
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 123
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 123
Relay_Master_Log_File: binlog.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 123
Relay_Log_Space: 123
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 12345678-1234-1234-1234-1234567890ab
Master_Info_File: /mysql/data/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

# 步骤4：测试复制
# 在主库上创建数据库和表
mysql> CREATE DATABASE test_db;
mysql> USE test_db;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));
mysql> INSERT INTO test_table (data) VALUES (‘test data’);

# 在从库上验证复制
mysql> SHOW DATABASES;
mysql> USE test_db;
mysql> SELECT * FROM test_table;
+—-+———-+
| id | data |
+—-+———-+
| 1 | test data |
+—-+———-+

# 配置Prometheus
# vi /etc/prometheus/prometheus.yml
scrape_configs:
– job_name: ‘mysql’
static_configs:
– targets: [‘192.168.1.100:9104’, ‘192.168.1.101:9104’]

# 启动Prometheus
systemctl start prometheus
systemctl enable prometheus

# 步骤2：配置MySQL Exporter
# 安装MySQL Exporter
download mysql_exporter

# 配置MySQL Exporter
# 创建监控用户
mysql> CREATE USER ‘exporter’@’localhost’ IDENTIFIED BY ‘ExporterPassword123!’;
mysql> GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO ‘exporter’@’localhost’;
mysql> FLUSH PRIVILEGES;

# 创建配置文件
# vi /etc/.mysqld_exporter.cnf
[client]
user=exporter
password=ExporterPassword123!

# 启动MySQL Exporter
nohup ./mysqld_exporter –config.my-cnf=/etc/.mysqld_exporter.cnf &

# 步骤3：配置Grafana
# 安装Grafana
yum install -y grafana

# 启动Grafana
systemctl start grafana-server
systemctl enable grafana-server

# 配置Grafana数据源
# 登录Grafana，添加Prometheus数据源

# 导入MySQL监控面板
# 导入ID为7362的MySQL监控面板

# 步骤4：配置告警
# 在Grafana中配置告警规则
# 配置邮件告警

# 步骤5：创建监控脚本
# vi /mysql/scripts/monitor_replication.sh
#!/bin/bash
# monitor_replication.sh

LOG_FILE=”/mysql/logs/replication_monitor.log”

# 检查复制状态
SLAVE_STATUS=$(mysql -u root -pPassword123! -e “SHOW SLAVE STATUS\G” | grep -E “Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master”)

if echo “$SLAVE_STATUS” | grep -q “Slave_IO_Running: No”; then
echo “[$(date +%Y-%m-%d%H:%M:%S)] Slave IO is not running!” >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave IO is not running” admin@example.com < $LOG_FILE fi if echo "$SLAVE_STATUS" | grep -q "Slave_SQL_Running: No"; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave SQL is not running!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave SQL is not running” admin@example.com < $LOG_FILE fi SECONDS_BEHIND_MASTER=$(echo "$SLAVE_STATUS" | grep "Seconds_Behind_Master" | awk '{print $2}') if [ "$SECONDS_BEHIND_MASTER" -gt 300 ]; then echo "[$(date +%Y-%m-%d%H:%M:%S)] Slave is behind master by $SECONDS_BEHIND_MASTER seconds!" >> $LOG_FILE
# 发送告警邮件
mail -s “Replication Alert: Slave is behind master” admin@example.com < $LOG_FILE fi # 设置执行权限 chmod +x /mysql/scripts/monitor_replication.sh # 添加cron任务 # crontab -e */5 * * * * /mysql/scripts/monitor_replication.sh # 步骤6：验证监控效果 # 访问Grafana面板 # http://localhost:3000 # 检查监控数据 # 查看MySQL复制状态 # 查看复制延迟

# 处理步骤：
1. 检查网络连接
ping 192.168.1.100

2. 检查主库状态
systemctl status mysqld

3. 检查复制用户权限
mysql> SHOW GRANTS FOR ‘repl’@’192.168.1.%’;

4. 重启IO线程
mysql> STOP SLAVE IO_THREAD;
mysql> START SLAVE IO_THREAD;

5. 检查复制状态
mysql> SHOW SLAVE STATUS\G

# 故障2：Slave SQL线程停止
# 症状：Slave_SQL_Running: No
# 原因：SQL语句执行失败、主键冲突、表结构不一致

# 处理步骤：
1. 查看错误信息
mysql> SHOW SLAVE STATUS\G | grep Last_Error

2. 跳过错误（谨慎使用）
mysql> SET GLOBAL sql_slave_skip_counter = 1;
mysql> START SLAVE SQL_THREAD;

3. 修复数据一致性
# 重新同步数据

# 故障3：复制延迟
# 症状：Seconds_Behind_Master值较大
# 原因：从库性能不足、网络延迟、主库写入量大

# 处理步骤：
1. 检查从库性能
# 查看CPU、内存、IO使用情况
top
iostat -x

2. 优化从库参数
# 启用并行复制
slave-parallel-workers = 4

3. 检查网络延迟
ping 192.168.1.100

4. 检查主库写入量
mysql> SHOW GLOBAL STATUS LIKE ‘Com_%’;

# 故障4：主库故障
# 症状：主库不可用
# 处理步骤：
1. 提升从库为主库
mysql> STOP SLAVE;
mysql> RESET SLAVE ALL;
mysql> RESET MASTER;

2. 更新应用连接配置
# 修改应用的数据库连接地址

3. 重新配置其他从库
# 将其他从库指向新的主库
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 同步二进制日志到磁盘的频率
sync_binlog = 1

# 二进制日志格式
binlog-format = ROW

# 二进制日志过期时间
expire-logs-days = 7

# 最大二进制日志文件大小
max-binlog-size = 1G

# 步骤2：优化从库参数
# vi /etc/my.cnf
[mysqld]
# 并行复制线程数
slave-parallel-workers = 4

# 并行复制类型
slave-parallel-type = LOGICAL_CLOCK

# 中继日志自动恢复
relay-log-recovery = 1

# 从库只读
read-only = 1

# 步骤3：网络优化
# 配置网络缓冲区大小
# vi /etc/sysctl.conf
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216

# 应用配置
sysctl -p

# 步骤4：硬件优化
# 为从库配置SSD存储
# 增加从库的CPU和内存

# 步骤5：架构优化
# 使用级联复制，减轻主库压力
# 配置：主库 -> 从库1 -> 从库2

# 在从库1上启用binlog
# vi /etc/my.cnf
[mysqld]
log-bin = /mysql/logs/binlog

# 在从库2上配置复制
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=123;
mysql> START SLAVE;

# 步骤6：监控优化
# 配置复制延迟告警
# 在Grafana中设置复制延迟告警规则

# 步骤7：验证优化效果
# 监控复制延迟
mysql> SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

# 测试复制性能
# 在主库上执行大量写操作
mysql> CREATE DATABASE test_perf;
mysql> USE test_perf;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(1000));
# 插入10000条数据
mysql> DELIMITER //
mysql> CREATE PROCEDURE insert_data()
BEGIN
DECLARE i INT DEFAULT 0;
WHILE i < 10000 DO INSERT INTO test_table (data) VALUES (REPEAT('a', 1000)); SET i = i + 1; END WHILE; END // mysql> DELIMITER ;
mysql> CALL insert_data();

# 检查从库的复制状态
mysql> SHOW SLAVE STATUS\G | grep Seconds_Behind_Master