2. 从库参数规划：
– server-id：唯一的服务器ID
– relay-log：中继日志路径
– relay-log-recovery：中继日志自动恢复
– read-only：从库只读
– slave-parallel-workers：并行复制线程数
– slave-parallel-type：并行复制类型

3. 网络参数规划：
– 网络缓冲区大小：优化网络传输
– 连接超时：设置合理的连接超时时间
– 重试次数：设置合理的重试次数

# 推荐参数配置
# 主库参数
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 1G
gtid-mode = ON
enforce-gtid-consistency = ON

# 从库参数
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON
slave-parallel-workers = 4
slave-parallel-type = LOGICAL_CLOCK

2. 网络带宽：
– 估算复制所需带宽：根据主库的写入量，估算所需的网络带宽
– 预留带宽：预留足够的带宽，确保复制不受网络瓶颈影响

3. 网络延迟：
– 监控网络延迟：定期监控主从库之间的网络延迟
– 优化网络路径：选择最优的网络路径，减少网络延迟

4. 网络冗余：
– 多网络路径：配置多网络路径，确保网络故障时的可用性
– 网络隔离：将管理网络和业务网络分离，提高安全性

# 网络参数优化
# 系统网络参数
# vi /etc/sysctl.conf
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216
net.ipv4.tcp_rmem = 4096 12582912 16777216

# 应用配置
sysctl -p

# MySQL网络参数
[mysqld]
max_connections = 1000
connect_timeout = 10
wait_timeout = 3600

# 步骤2：配置主库
# 编辑主库的my.cnf文件
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 1G

# 重启主库
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 | | | |
+—————+———-+————–+——————+——————-+

# 步骤3：配置从库
# 编辑从库的my.cnf文件
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON

# 重启从库
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;

# 步骤4：验证复制状态
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:

# 步骤5：解锁主库
mysql> UNLOCK TABLES;

# 步骤6：测试复制
# 在主库上创建数据库和表
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 |
+—-+———-+

# 启用二进制日志
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

# 二进制日志缓存大小
binlog_cache_size = 32M

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

# 从库参数配置
[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-parallel-workers = 4

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

# 复制延迟
# sql_slave_skip_counter = 1

# 网络参数配置
# 系统网络参数
# vi /etc/sysctl.conf
net.core.rmem_max = 16777216
net.core.wmem_max = 16777216
net.ipv4.tcp_wmem = 4096 12582912 16777216
net.ipv4.tcp_rmem = 4096 12582912 16777216

# 应用配置
sysctl -p

# MySQL网络参数
[mysqld]
max_connections = 1000
connect_timeout = 10
wait_timeout = 3600

# 关键状态值
# Slave_IO_Running: Yes – IO线程正常运行
# Slave_SQL_Running: Yes – SQL线程正常运行
# Seconds_Behind_Master: 0 – 复制延迟为0

# 方法2：测试数据同步
# 在主库上执行写操作
mysql> CREATE DATABASE test_replication;
mysql> USE test_replication;
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_replication;
mysql> SELECT * FROM test_table;
+—-+———-+
| id | data |
+—-+———-+
| 1 | test data |
+—-+———-+

# 方法3：检查复制延迟
mysql> SHOW SLAVE STATUS\G | grep Seconds_Behind_Master

# 方法4：检查二进制日志和中继日志
# 检查主库的二进制日志
mysql> SHOW BINARY LOGS;

# 检查从库的中继日志
ls -l /mysql/logs/relay-bin.*

# 方法5：使用MySQL工具验证
# 安装MySQL Utilities
yum install -y mysql-utilities

# 验证复制状态
mysqlreplicate –master=repl:ReplPassword123!@192.168.1.100:3306 –slave=repl:ReplPassword123!@192.168.1.101:3306

# 方法6：监控复制状态
# 使用Prometheus和Grafana监控复制状态
# 配置MySQL Exporter
# 导入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-recovery = ON
read-only = ON

# 重启从库
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_binlog;
mysql> USE test_binlog;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));
mysql> INSERT INTO test_table (data) VALUES (‘test binlog replication’);

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

# 重启主库
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;

# 步骤2：配置从库
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON
gtid-mode = ON
enforce-gtid-consistency = ON

# 重启从库
systemctl restart mysqld

# 配置从库复制（使用GTID）
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.100′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’ReplPassword123!’,
-> MASTER_AUTO_POSITION=1;

# 启动复制
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: 12345678-1234-1234-1234-1234567890ab:1-10
Executed_Gtid_Set: 12345678-1234-1234-1234-1234567890ab:1-10
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:

# 步骤4：测试复制
# 在主库上执行写操作
mysql> CREATE DATABASE test_gtid;
mysql> USE test_gtid;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));
mysql> INSERT INTO test_table (data) VALUES (‘test GTID replication’);

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

# 步骤2：配置主库
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 1G

# 半同步复制配置
rpl_semi_sync_master_enabled = 1
rpl_semi_sync_master_timeout = 10000 # 10秒

# 重启主库
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;

# 步骤3：配置从库
# vi /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON

# 半同步复制配置
rpl_semi_sync_slave_enabled = 1

# 重启从库
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;

# 步骤4：验证半同步复制状态
# 在主库上查看半同步复制状态
mysql> SHOW GLOBAL STATUS LIKE ‘Rpl_semi_sync_master%’;
+—————————————+——-+
| Variable_name | Value |
+—————————————+——-+
| Rpl_semi_sync_master_clients | 1 |
| Rpl_semi_sync_master_net_avg_wait_time | 0 |
| Rpl_semi_sync_master_net_wait_time | 0 |
| Rpl_semi_sync_master_net_waits | 0 |
| Rpl_semi_sync_master_no_times | 0 |
| Rpl_semi_sync_master_no_tx | 0 |
| Rpl_semi_sync_master_status | ON |
| Rpl_semi_sync_master_timefunc_failures | 0 |
| Rpl_semi_sync_master_tx_avg_wait_time | 0 |
| Rpl_semi_sync_master_tx_wait_time | 0 |
| Rpl_semi_sync_master_tx_waits | 0 |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0 |
| Rpl_semi_sync_master_wait_sessions | 0 |
| Rpl_semi_sync_master_yes_tx | 0 |
+—————————————+——-+

# 在从库上查看半同步复制状态
mysql> SHOW GLOBAL STATUS LIKE ‘Rpl_semi_sync_slave%’;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| Rpl_semi_sync_slave_status | ON |
+—————————-+——-+

# 步骤5：测试半同步复制
# 在主库上执行写操作
mysql> CREATE DATABASE test_semisync;
mysql> USE test_semisync;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));
mysql> INSERT INTO test_table (data) VALUES (‘test semi-sync replication’);

# 在从库上验证
mysql> SHOW DATABASES;
mysql> USE test_semisync;
mysql> SELECT * FROM test_table;
+—-+——————————+
| id | data |
+—-+——————————+
| 1 | test semi-sync replication |
+—-+——————————+

# 步骤1：配置主库
# vi /etc/my.cnf
[mysqld]
server-id = 1
log-bin = /mysql/logs/binlog
binlog-format = ROW
expire-logs-days = 7
max-binlog-size = 1G

# 重启主库
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;

# 步骤2：配置从库1（作为中间节点）
# vi /etc/my.cnf
[mysqld]
server-id = 2
log-bin = /mysql/logs/binlog # 启用二进制日志，作为其他从库的主库
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON
log-slave-updates = ON # 允许从库将复制的事务记录到二进制日志

# 重启从库1
systemctl restart mysqld

# 配置从库1复制主库
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：配置从库2
# vi /etc/my.cnf
[mysqld]
server-id = 3
relay-log = /mysql/logs/relay-bin
relay-log-recovery = ON
read-only = ON

# 重启从库2
systemctl restart mysqld

# 获取从库1的二进制日志状态
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 456 | | | |
+—————+———-+————–+——————+——————-+

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

# 启动复制
mysql> START SLAVE;

# 步骤4：验证级联复制状态
# 查看从库1的复制状态
mysql> SHOW SLAVE STATUS\G

# 查看从库2的复制状态
mysql> SHOW SLAVE STATUS\G

# 步骤5：测试级联复制
# 在主库上执行写操作
mysql> CREATE DATABASE test_cascade;
mysql> USE test_cascade;
mysql> CREATE TABLE test_table (id INT PRIMARY KEY AUTO_INCREMENT, data VARCHAR(100));
mysql> INSERT INTO test_table (data) VALUES (‘test cascade replication’);

# 在从库1上验证
mysql> SHOW DATABASES;
mysql> USE test_cascade;
mysql> SELECT * FROM test_table;
+—-+—————————–+
| id | data |
+—-+—————————–+
| 1 | test cascade replication |
+—-+—————————–+

# 在从库2上验证
mysql> SHOW DATABASES;
mysql> USE test_cascade;
mysql> SELECT * FROM test_table;
+—-+—————————–+
| id | data |
+—-+—————————–+
| 1 | test cascade replication |
+—-+—————————–+