一、主从配置

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

SpringBoot+MyBatis+MySQL读写分离