# MSRepl

**Repository Path**: rancedxk/msrepl

## Basic Information

- **Project Name**: MSRepl
- **Description**: SpringBoot+MyBatis+MySQL读写分离
- **Primary Language**: Unknown
- **License**: Not specified
- **Default Branch**: main
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 1
- **Created**: 2025-08-04
- **Last Updated**: 2025-08-04

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# SpringBoot+MyBatis+MySQL读写分离

## 一、主从配置

> 1.环境

**操作系统**: CentOS-7

**MySQL**: mysql-5.7

192.168.15.129　　master

192.168.15.130　　slave

> 2.主库配置

- `vi /etc/my.cnf`在[mysqld]下增加如下两行设置：

  ```sh
  [mysqld]
  log-bin=mysql-bin 
  server-id=1　　　　
  ```

- 创建数据同步账户

  ```mysql
  CREATE USER 'repl'@'192.168.15.%' IDENTIFIED BY '666666';
  
  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.15.%';
  
  FLUSH PRIVILEGES;
  ```

- 查看matser状态: `show master status;`

> 3.从库配置

- `vi /etc/my.cnf`

  ```sh
  [mysqld]
  log-bin=mysql-bin 
  server-id=2
  log-error=/var/log/mysqld.log   # 异常日志存放位置
  ```

- 执行同步命令

  ```mysql
  change master to master_host='192.168.15.129', master_user='repl', master_password='666666', master_log_file='mysql-bin.000002', master_log_pos=154;
  
  # 设置从库只读
  set global super_read_only=1;
  
  
  # 锁表
  flush tables with read lock;   # 锁表（所有角色都只读，但是会影响主从同步）
  
  # 解锁
  unlock tables;
  ```

- 查看slave状态: `show slave status\G;`

> 4.异常解决

- **slave 在主库找不到binlog文件** 

```sh
[ERROR] Slave I/O for channel '': Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from position > file size', Error_code: 1236
```

**==解决方法：==**

- **master端**

```mysql
mysql> flush logs;
Query OK, 0 rows affected (0.02 sec)

mysql> show master status;
+---------------------+----------+--------------+------------------+-------------------+
| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------------+----------+--------------+------------------+-------------------+
| dbmaster-bin.000005 |      120 |              |                  |                   |
+---------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
```

记住==file==和==position==这两个选项

- **slave端**

```mysql
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)

mysql> change master to master_log_file ='dbmaster-bin.000005',master_log_pos=120;
Query OK, 0 rows affected (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
```

搞定收工！

## 二、读写分离
gitee地址：https://gitee.com/kk-dad/msrepl