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]下增加如下两行设置：

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

创建数据同步账户

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

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

执行同步命令

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文件

[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> 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> 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

little_lunatic / MSRepl

SpringBoot+MyBatis+MySQL读写分离

一、主从配置

二、读写分离

简介

发行版

贡献者

近期动态

little_lunatic / MSRepl .gitee-modal { width: 500px !important; }

SpringBoot+MyBatis+MySQL读写分离

一、主从配置

二、读写分离

简介

发行版

贡献者

近期动态

搜索帮助

little_lunatic / MSRepl