一、读写分离

1.什么是读写分离

读写分离：用来提升Mysql并发处理能力

2.Mysql读写分离环境搭建

1、主库准备  3306
     1.1 先进入到mysql目录，找到my.ini文件

     1.2 然后配置my.ini文件，增加
          server-id=1
          log-bin=mysql-bin

     1.3 然后启动master节点
		mysqld.exe --defaults-file=D:\work\net\mysql\MySQL-Server-5.7\my.ini

     1.4 然后创建slaver连接用户
           create user tony
           grant replication slave on *.* to 'tony'@'127.0.0.1'identified by 'mysql' 
           flush privileges
     1.5 查看主库状态
     	show master status

2、从库准备  3308
      1.1 先进入到mysql目录，找到my.ini文件

      1.2 然后配置my.ini文件，增加
          server-id=2
         relay-log=mysql-relay-bin

      1.3 然后启动slaver节点
           mysqld.exe --defaults-file=D:\work\net\mysql\MySQL-Server-5.7-3308\my.ini

      1.4 然后从master增加同步权限
       change master to master_host='127.0.0.1',
		master_port=3306,
		master_user='root',
		master_password='root',
		master_log_file='mysql-bin.000037',
		master_log_pos=1464;
	
		change master to master_host='127.0.0.1',master_port=3306,master_user='tony',master_password='mysql',master_log_file='mysql-bin.000061',master_log_pos=15556;
	  1.5 然后显示从节点状态
      	  show slave status
      1.6 然后开启slave同步
         start slave
3、从库准备  3309
      1.1 先进入到mysql目录，找到my.ini文件

      1.2 然后配置my.ini文件，增加
          server-id=3
         relay-log=mysql-relay-bin

      1.3 然后启动slaver节点
           mysqld.exe --defaults-file=D:\work\net\mysql\MySQL-Server-5.7-3309\my.ini

      1.4 然后从master增加同步权限
       change master to master_host='127.0.0.1',
		master_port=3306,
		master_user='root',
		master_password='root',
		master_log_file='mysql-bin.000037',
		master_log_pos=1464;
	
		change master to master_host='127.0.0.1',master_port=3306,master_user='tony',master_password='mysql',master_log_file='mysql-bin.000061',master_log_pos=15556;
	  1.5 然后显示从节点状态
      	  show slave status
      1.6 然后开启slave同步
         start slave

注意：server-uuid不能相同

1、如果相同，修改D:\work\net\mysql\MySQL Server 5.7\Data目录下的auto.conf文件就可以了
   [auto]
   server-uuid=3a15fbcf-ae39-11eb-a3de-8c1645d792a9

2、然后重启mysql
 mysqld.exe --defaults-file=D:\work\net\mysql\MySQL-Server-5.7-3308\my.ini

二、读写分离原理

读写分离主要依赖于：binlog、relaylog 进行数据同步。读写分离默认是异步同步模式会有缺陷，如果slave宕机，会导致数据不一致性风险。

要是数据一直则需要配置同步模式进行同步

1、前提准备
  1.1 、先查询mysql是否可以安装版本
     mysq> select @@have_dynamic_loading
+------------------------+
| @@have_dynamic_loading |
+------------------------+
| YES                    |
+------------------------+
1 row in set
Time: 0.016s

2、rpl_semi_sync_master准备
    2.1 先在master节点上安装rpl_semi_sync_master
        mysql root@127.0.0.1:(none)> INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
Query OK, 0 rows affected
Time: 0.015s
    2.2 然后查询插件是否安装成功
mysql root@127.0.0.1:(none)> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
+-------------------------------------------+------------+
6 rows in set
Time: 0.015s
     2.3 然后开启master复制
         set global rpl_semi_sync_master_enabled=ON;
     2.4 然后查询salver信息
         show global status like '%semi%';
      
3、rpl_semi_sync_slave准备
  3.1 先在master节点上安装rpl_semi_sync_slave
  mysql root@127.0.0.1:(none)> INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
Query OK, 0 rows affected
Time: 0.006s
  3.2 然后查询插件是否安装成功
mysql root@127.0.0.1:(none)> show global variables like 'rpl_semi%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | OFF        |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_semi_sync_slave_enabled               | OFF        |
| rpl_semi_sync_slave_trace_level           | 32         |
+-------------------------------------------+------------+
8 rows in set
Time: 0.012s

 3.3 然后开启salver复制
         set global rpl_semi_sync_slave_enabled=ON;
 3.4 然后开启半同步复制模式
     stop slave io_thread;
start slave io_thread;

三、Mysql读写分离重置绑定

1、停止已经启动的绑定

stop slave

2、重置绑定

reset master

3、执行复制主机命令

change master to
master_host=‘127.0.0.1’,
master_user=‘slave’,
master_password=‘123456’,
master_log_file=‘mysql-bin.000001’,
master_log_pos=655;

4、启动复制

start slave

5、查看状态

show slave status