# 复制类型
– 异步复制（Asynchronous Replication）：主服务器不需要等待从服务器确认，性能最好，但可能有数据丢失
– 半同步复制（Semi-synchronous Replication）：主服务器需要等待至少一个从服务器确认，数据安全性更高
– 组复制（Group Replication）：基于Paxos协议的多主复制，提供高可用性

# 复制架构
– 主从复制：一主多从，适合读多写少的场景
– 主主复制：多主互备，适合写入分散的场景
– 级联复制：主 -> 从 -> 从，适合大规模部署

# 集群规模
– 1主1从：基本高可用配置
– 1主多从：提高读性能，适合读多写少的场景
– 多主多从：提高写性能，适合写入分散的场景

# 复制拓扑
– 星型拓扑：一主多从
– 链式拓扑：主 -> 从 -> 从
– 环形拓扑：多主互备

# 高可用设计
– 主从切换：当主服务器故障时，从服务器提升为主服务器
– 负载均衡：使用代理（如ProxySQL、MySQL Router）进行负载均衡
– 监控与告警：实时监控复制状态，及时发现问题

# 服务器要求
– CPU：多核处理器，推荐8核以上
– 内存：16GB以上，根据数据量和并发调整
– 存储：SSD存储，提高I/O性能
– 网络：万兆网卡，确保网络带宽

# 主服务器要求
– 更高的CPU和内存配置：处理写入操作
– 更快的存储：处理二进制日志写入
– 冗余电源和网络：提高可靠性

# 从服务器要求
– 与主服务器相同或相似的配置：确保复制性能
– 足够的存储空间：存储数据和中继日志

# 网络架构
– 专用复制网络：用于主从之间的复制通信
– 业务网络：用于客户端访问
– 管理网络：用于集群管理

# 网络配置
– 静态IP：所有节点使用静态IP
– 网络隔离：使用VLAN隔离不同网络
– 网络冗余：多网卡绑定
– 网络监控：监控网络状态

# 网络安全
– 防火墙：限制网络访问
– 加密通信：使用SSL/TLS加密复制流量
– 访问控制：限制复制用户的权限

# 安装MySQL
$ sudo dnf install -y mysql-server

# 启动MySQL服务
$ sudo systemctl start mysqld
$ sudo systemctl enable mysqld

# 查看MySQL状态
$ sudo systemctl status mysqld
● mysqld.service – MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since Sun 2026-04-03 08:00:00 CST; 1min ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 1234 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Process: 1233 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Process: 1232 ExecStartPre=/usr/libexec/mysql-audit-plugin-init (code=exited, status=0/SUCCESS)
Main PID: 1235 (mysqld)
Tasks: 38 (limit: 4915)
Memory: 300.1M
CPU: 1.244s
CGroup: /system.slice/mysqld.service
└─1235 /usr/libexec/mysqld –basedir=/usr

# 初始化MySQL
$ sudo mysql_secure_installation

# 编辑MySQL配置文件
$ sudo vim /etc/my.cnf

# 主服务器配置
[mysqld]
server-id = 1
binlog-format = ROW
log-bin = /var/lib/mysql/binlog
binlog-row-image = FULL
expire-logs-days = 7
max-binlog-size = 100M
sync-binlog = 1

# 重启MySQL服务
$ sudo systemctl restart mysqld

# 创建复制用户
$ mysql -u root -p
Enter password:
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl123’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 156 | | | |
+—————+———-+————–+——————+——————-+
1 row in set (0.00 sec)

# 安装MySQL
$ sudo dnf install -y mysql-server

# 启动MySQL服务
$ sudo systemctl start mysqld
$ sudo systemctl enable mysqld

# 初始化MySQL
$ sudo mysql_secure_installation

# 编辑MySQL配置文件
$ sudo vim /etc/my.cnf

# 从服务器配置
[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-bin
read-only = 1

# 重启MySQL服务
$ sudo systemctl restart mysqld

# 配置从服务器复制
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=156;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 156
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 320
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: 156
Relay_Log_Space: 534
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.slave_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:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

# 案例：企业级MySQL主从复制
# 环境：1主2从
# 目标：实现数据同步和负载均衡

# 1. 主服务器配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 1
binlog-format = ROW
log-bin = /var/lib/mysql/binlog
binlog-row-image = FULL
expire-logs-days = 7
max-binlog-size = 100M
sync-binlog = 1

# 2. 从服务器1配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 2
relay-log = /var/lib/mysql/relay-bin
read-only = 1

# 3. 从服务器2配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 3
relay-log = /var/lib/mysql/relay-bin
read-only = 1

# 4. 创建复制用户
$ mysql -u root -p
Enter password:
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl123’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;
mysql> SHOW MASTER STATUS;
+—————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+—————+———-+————–+——————+——————-+
| binlog.000001 | 156 | | | |
+—————+———-+————–+——————+——————-+

# 5. 配置从服务器1
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=156;
mysql> START SLAVE;

# 6. 配置从服务器2
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.学习交流加群风哥QQ113257174101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_LOG_FILE=’binlog.000001′,
-> MASTER_LOG_POS=156;
mysql> START SLAVE;

# 7. 测试复制
# 在主服务器上创建数据库和表
$ mysql -u root -p
Enter password:
mysql> CREATE DATABASE fgedudb;
mysql> USE fgedudb;
mysql> CREATE TABLE fgedu_users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100));
mysql> INSERT INTO fgedu_users (name, email) VALUES (‘风哥1号’, ‘zhangsan@fgedu.net.cn’), (‘风哥2号’, ‘lisi@fgedu.net.cn’);

# 在从服务器上验证数据
$ mysql -u root -p
Enter password:
mysql> USE fgedudb;
mysql> SELECT * FROM fgedu_users;
+—-+——–+————————-+
| id | name | email |
+—-+——–+————————-+
| 1 | 风哥1号 | zhangsan@fgedu.net.cn |
| 2 | 风哥2号 | lisi@fgedu.net.cn |
+—-+——–+————————-+
2 rows in set (0.00 sec)

# 案例：GTID主从复制
# 环境：1主2从
# 目标：使用GTID实现更可靠的复制

# 1. 主服务器配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 1
binlog-format = ROW
log-bin = /var/lib/mysql/binlog
gtid-mode = ON
enforce-gtid-consistency = ON

# 2. 从服务器配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 2
gtid-mode = ON
enforce-gtid-consistency = ON
read-only = 1

# 3. 重启MySQL服务
$ sudo systemctl restart mysqld

# 4. 创建复制用户
$ mysql -u root -p
Enter password:
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl123’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;

# 5. 配置从服务器
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_AUTO_POSITION=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.更多视频教程www.fgedu.net.cn168.1.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000001
Read_Master_Log_Pos: 156
Relay_Log_File: relay-bin.000001
Relay_Log_Pos: 320
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: 156
Relay_Log_Space: 534
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.slave_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: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set (0.00 sec)

# 案例：双主复制
# 环境：2台服务器互为主从
# 目标：实现双向数据同步

# 1. 服务器1配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 1
binlog-format = ROW
log-bin = /var/lib/mysql/binlog
gtid-mode = ON
enforce-gtid-consistency = ON
auto-increment-offset = 1
auto-increment-increment = 2

# 2. 服务器2配置
$ sudo vim /etc/my.cnf
[mysqld]
server-id = 2
binlog-format = ROW
log-bin = /var/lib/mysql/binlog
gtid-mode = ON
enforce-gtid-consistency = ON
auto-increment-offset = 2
auto-increment-increment = 2

# 3. 重启MySQL服务
$ sudo systemctl restart mysqld

# 4. 在服务器1上创建复制用户
$ mysql -u root -p
Enter password:
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl123’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;

# 5. 在服务器2上创建复制用户
$ mysql -u root -p
Enter password:
mysql> CREATE USER ‘repl’@’%’ IDENTIFIED BY ‘repl123’;
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘repl’@’%’;
mysql> FLUSH PRIVILEGES;

# 6. 配置服务器1作为服务器2的从服务器
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.102′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_AUTO_POSITION=1;
mysql> START SLAVE;

# 7. 配置服务器2作为服务器1的从服务器
$ mysql -u root -p
Enter password:
mysql> CHANGE MASTER TO
-> MASTER_HOST=’192.168.1.101′,
-> MASTER_USER=’repl’,
-> MASTER_PASSWORD=’repl123′,
-> MASTER_AUTO_POSITION=1;
mysql> START SLAVE;

# 8. 测试双主复制
# 在服务器1上创建数据
$ mysql -u root -p
Enter password:
mysql> CREATE DATABASE fgedudb;
mysql> USE fgedudb;
mysql> CREATE TABLE fgedu_users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50), email VARCHAR(100更多学习教程公众号风哥教程itpux_com));
mysql> INSERT INTO fgedu_users (name, email) VALUES (‘风哥1号’, ‘zhangsan@fgedu.net.cn’);

# 在服务器2上验证数据
$ mysql -u root -p
Enter password:
mysql> USE fgedudb;
mysql> SELECT * FROM fgedu_users;
+—-+——–+————————-+
| id | name | email |
+—-+——–+————————-+
| 1 | 风哥1号 | zhangsan@fgedu.net.cn |
+—-+——–+————————-+
1 row in set (0.00 sec)

# 在服务器2上插入数据
$ mysql -u root -p
Enter password:
mysql> USE fgedudb;
mysql> INSERT INTO fgedu_users (name, email) VALUES (‘风哥2号’, ‘lisi@fgedu.net.cn’);

# 在服务器1上验证数据
$ mysql -u root -p
Enter password:
mysql> USE fgedudb;
mysql> SELECT * FROM fgedu_users;
+—-+——–+————————-+
| id | name | email |
+—-+——–+————————-+
| 1 | 风哥1号 | zhangsan@fgedu.net.cn |
| 2 | 风哥2号 | lisi@fgedu.net.cn |
+—-+——–+————————-+
2 rows in set (0.00 sec)

# 常见问题与解决方案

## 1. 复制延迟
– 原因：主服务器负载过高、从服务器性能不足、网络延迟
– 解决方案：优化主服务器性能、增加从服务器资源、使用专用复制网络

## 2. 复制错误
– 原因：数据不一致、权限问题、网络中断
– 解决方案：修复数据一致性、检查权限配置、确保网络稳定

## 3. 主服务器故障
– 原因：硬件故障、软件故障、网络故障
– 解决方案：将从服务器提升为主服务器、使用故障转移工具

## 4. 数据丢失
– 原因：异步复制、主服务器崩溃
– 解决方案：使用半同步复制、定期备份、使用GTID

## 5. 复制配置错误
– 原因：配置文件错误、复制参数设置不当
– 解决方案：检查配置文件、使用正确的复制参数