# 基于现有my.cnf的MySQL主从复制配置指南

## 📋 当前配置状态

✅ **67机器（主库）**: `/etc/my.cnf` - 无主从配置，需要添加  
✅ **68机器（从库）**: `/etc/my.cnf` - 无主从配置，需要添加  
✅ **数据状态**: 两个数据库的`hzdyjs_xg`表模型和数据已一致  

---

## 🚀 快速配置（推荐使用脚本）

### 方法一：使用自动化脚本（最简单）

#### 步骤1：在主库(67)执行

```bash
# 1. 进入部署脚本目录
cd /Users/wuzilong/work/00code/hzdyjs_xg/docs/部署文档/生产环境双机部署方案/部署脚本/

# 2. 赋予执行权限
chmod +x setup-mysql-replication.sh

# 3. 执行主库配置
./setup-mysql-replication.sh
```

**脚本会自动完成：**
- ✅ 在`/etc/my.cnf`末尾添加主库配置
- ✅ 重启MySQL服务
- ✅ 创建复制用户`repl@172.16.0.68`
- ✅ 显示`SHOW MASTER STATUS`输出

**⚠️ 重要：记录输出的binlog信息**
```
File: mysql-bin.000001
Position: 154
```

#### 步骤2：在从库(68)执行

```bash
# 1. 进入部署脚本目录
cd /Users/wuzilong/work/00code/hzdyjs_xg/docs/部署文档/生产环境双机部署方案/部署脚本/

# 2. 执行从库配置
./setup-mysql-replication.sh
```

**输入步骤1记录的binlog信息：**
- `MASTER_LOG_FILE`: 输入File值（如：`mysql-bin.000001`）
- `MASTER_LOG_POS`: 输入Position值（如：`154`）

#### 步骤3：验证配置

```bash
# 在从库执行
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master"
```

**正常输出：**
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
```

---

## 🔧 手动配置方法（如果脚本执行失败）

### 主库(67)手动配置

#### 1. 编辑主库配置文件

```bash
vi /etc/my.cnf
```

**在文件末尾（`[client]`段之后）添加以下配置：**

```ini
# 主从复制配置
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = hzdyjs_xg
expire_logs_days = 7
max_binlog_size = 500M

# 性能优化配置（减少主从复制对主库性能影响）
# 使用性能优先方案，性能影响约1-2%
sync_binlog = 0
binlog_cache_size = 2M
max_binlog_cache_size = 512M
```

**完整的67机器my.cnf应该是：**

```ini
[mysqld]
# 基础配置
port = 3306
default-storage-engine = InnoDB
datadir = /iflytek/mysql/data/mysql
log-error=/iflytek/mysql/log/mysqld.log
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
lower_case_table_names = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 安全优化
ft_min_word_len = 1
symbolic-links=0

# 内存配置（核心）
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 128M
key_buffer_size = 256M

# I/O配置
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 连接配置
max_connections = 1000
thread_cache_size = 100
back_log = 500
table_open_cache = 4096
table_definition_cache = 4096

# 查询优化（关键调整）
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 1G
max_heap_table_size = 1G

# 网络配置
max_allowed_packet = 256M
net_buffer_length = 16K
wait_timeout = 300
interactive_timeout = 300

# 高级配置
innodb_file_per_table = ON
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_page_cleaners = 8

# 监控日志
slow_query_log = ON
slow_query_log_file = /iflytek/mysql/log/slow.log
long_query_time = 2
log_queries_not_using_indexes = OFF
performance_schema = ON

# 主从复制配置
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
binlog-do-db = hzdyjs_xg
expire_logs_days = 7
max_binlog_size = 500M

# 性能优化配置（减少主从复制对主库性能影响）
sync_binlog = 0
binlog_cache_size = 2M
max_binlog_cache_size = 512M

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
```

#### 2. 重启MySQL服务

```bash
systemctl restart mysqld
sleep 5

# 检查MySQL是否正常启动
systemctl status mysqld
```

#### 3. 创建复制用户

```bash
mysql -u root -p <<EOF
CREATE USER IF NOT EXISTS 'repl'@'172.16.0.68' IDENTIFIED BY 'Repl@123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'172.16.0.68';
FLUSH PRIVILEGES;
SHOW MASTER STATUS;
EOF
```

**⚠️ 重要：记录输出的`File`和`Position`值**

---

### 从库(68)手动配置

#### 1. 编辑从库配置文件

```bash
vi /etc/my.cnf
```

**在文件末尾（`[client]`段之后）添加以下配置：**

```ini
# 主从复制配置
server-id = 2
relay-log = mysql-relay-bin
read-only = 1
replicate-do-db = hzdyjs_xg
```

**完整的68机器my.cnf应该是：**

```ini
[mysqld]
# 基础配置
port = 3306
default-storage-engine = InnoDB
datadir = /iflytek/mysql/data/mysql
log-error=/iflytek/mysql/log/mysqld.log
socket = /var/lib/mysql/mysql.sock
pid-file = /var/run/mysqld/mysqld.pid
lower_case_table_names = 1
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

# 安全优化
ft_min_word_len = 1
symbolic-links=0

# 内存配置（核心）
innodb_buffer_pool_size = 20G
innodb_buffer_pool_instances = 16
innodb_buffer_pool_chunk_size = 128M
key_buffer_size = 256M

# I/O配置
innodb_log_file_size = 2G
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# 连接配置
max_connections = 1000
thread_cache_size = 100
back_log = 500
table_open_cache = 4096
table_definition_cache = 4096

# 查询优化（关键调整）
join_buffer_size = 8M
sort_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 1G
max_heap_table_size = 1G

# 网络配置
max_allowed_packet = 256M
net_buffer_length = 16K
wait_timeout = 300
interactive_timeout = 300

# 高级配置
innodb_file_per_table = ON
innodb_thread_concurrency = 0
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_page_cleaners = 8

# 监控日志
slow_query_log = ON
slow_query_log_file = /iflytek/mysql/log/slow.log
long_query_time = 2
log_queries_not_using_indexes = OFF
performance_schema = ON

# 主从复制配置
server-id = 2
relay-log = mysql-relay-bin
read-only = 1
replicate-do-db = hzdyjs_xg

[mysql]
default-character-set = utf8mb4

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock
```

#### 2. 重启MySQL服务

```bash
systemctl restart mysqld
sleep 5

# 检查MySQL是否正常启动
systemctl status mysqld
```

#### 3. 配置主从复制

```bash
# 替换下面的值为主库的实际值（从主库SHOW MASTER STATUS获取）
MASTER_LOG_FILE="mysql-bin.000001"  # 替换为实际值
MASTER_LOG_POS=154                   # 替换为实际值

mysql -u root -p <<EOF
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='172.16.0.67',
  MASTER_USER='repl',
  MASTER_PASSWORD='Repl@123456',
  MASTER_LOG_FILE='$MASTER_LOG_FILE',
  MASTER_LOG_POS=$MASTER_LOG_POS;
START SLAVE;
SHOW SLAVE STATUS\G
EOF
```

---

## ✅ 配置验证

### 1. 检查从库复制状态

```bash
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -E "Slave_IO_Running|Slave_SQL_Running|Seconds_Behind_Master|Master_Host|Master_Log_File"
```

**正常输出：**
```
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
Master_Host: 172.16.0.67
Master_Log_File: mysql-bin.000001
```

### 2. 测试数据同步

```bash
# 在主库插入测试数据
mysql -u root -p hzdyjs_xg -e "CREATE TABLE IF NOT EXISTS test_replication (id INT PRIMARY KEY, name VARCHAR(50));"
mysql -u root -p hzdyjs_xg -e "INSERT INTO test_replication VALUES (1, 'test');"

# 等待1秒
sleep 1

# 在从库查询测试数据
mysql -u root -p hzdyjs_xg -e "SELECT * FROM test_replication;"

# 如果能看到数据，说明主从复制正常
# 清理测试数据
mysql -u root -p hzdyjs_xg -e "DROP TABLE test_replication;"
```

---

## ⚠️ 配置说明

### 主库配置参数说明

- **`server-id = 1`**: 主库唯一标识，必须与从库不同
- **`log-bin = mysql-bin`**: 开启binlog，文件名前缀为mysql-bin
- **`binlog-format = ROW`**: 使用ROW格式，数据一致性最好
- **`binlog-do-db = hzdyjs_xg`**: 只记录hzdyjs_xg数据库的binlog
- **`sync_binlog = 0`**: 性能优先，不强制同步binlog到磁盘（性能影响约1-2%）
- **`expire_logs_days = 7`**: binlog保留7天

### 从库配置参数说明

- **`server-id = 2`**: 从库唯一标识，必须与主库不同
- **`relay-log = mysql-relay-bin`**: relay log文件名
- **`read-only = 1`**: 从库只读，防止误操作
- **`replicate-do-db = hzdyjs_xg`**: 只复制hzdyjs_xg数据库

---

## 🔍 常见问题排查

### 问题1：从库IO线程未运行

```bash
# 检查网络连通性
ping 172.16.0.67
telnet 172.16.0.67 3306

# 检查复制用户
mysql -u root -p -e "SELECT user, host FROM mysql.user WHERE user='repl';"

# 查看详细错误
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -i "last_io_error"
```

### 问题2：从库SQL线程未运行

```bash
# 查看具体错误
mysql -u root -p -e "SHOW SLAVE STATUS\G" | grep -i "last_sql_error"

# 检查从库是否可写
mysql -u root -p -e "SHOW VARIABLES LIKE 'read_only';"
```

### 问题3：配置文件语法错误

```bash
# 检查配置文件语法
mysqld --help --verbose | head -1

# 查看MySQL错误日志
tail -f /iflytek/mysql/log/mysqld.log
```

---

## 📚 相关文档

- [快速配置主从复制.md](./快速配置主从复制.md) - 3步快速配置指南
- [MySQL主从复制配置步骤.md](./MySQL主从复制配置步骤.md) - 完整配置步骤和故障排查
- [部署脚本/setup-mysql-replication.sh](./部署脚本/setup-mysql-replication.sh) - 自动化配置脚本

---

**📝 文档版本**: v1.0  
**🔄 最后更新**: 2025-01-27  
**✅ 适用场景**: 数据已一致的67/68机器主从复制配置