MySQL主从复制与读写分离

1、MySQL读写分离

（1）什么是读写分离

读写分离,基本的原理是让主数据库处理事务性增、改、删操作（insert、update、delete），而从数据库处理select查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

（2）为什么要读写分离

因为数据库的“写”（写10000条数据可能要3分钟）操作是比较耗时的。
但是数据库的“读”（读10000条数据可能只要5秒钟）。
所以读写分离，解决的是：数据库的写入，影响了查询的效率。

（3）什么时候要读写分离

数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用。利用数据库主从同步，再通过读写分离可以分担数据库压力，提高性能。

（4）主从复制与读写分离

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。因此，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

（5）MySQL 读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性操作，而从数据库处理 select 查询。数据库复制被用来把主数据库上事务性操作导致的变更同步到集群中的从数据库。

（6）常见的两种 MySQL 读写分离

1）基于程序代码内部实现

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序。
（1）MySQL-Proxy。MySQL-Proxy 为 MySQL 开源项目，通过其自带的 lua 脚本进行SQL 判断。
（2）Atlas。是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。360内部使用Atlas运行的mysql业务，每天承载的读写请求数达几十亿条。支持事物以及存储过程。
（3）Amoeba。由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
（4）Mycat。是一款流行的基于Java语言编写的数据库中间件，是一个实现了MySql协议的服务器，其核心功能是分库分表。配合数据库的主从模式还可以实现读写分离。

由于使用MySQL Proxy 需要写大量的Lua脚本，这些Lua并不是现成的，而是需要自己去写。这对于并不熟悉MySQL Proxy 内置变量和MySQL Protocol 的人来说是非常困难的。
Amoeba是一个非常容易使用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

2、MySQL主从复制原理

（1） MySQL复制类型

（1）STATEMENT：基于语句的复制。在服务器上执行sql语句，在从服务器上执行同样的语句，mysql默认采用基于语句的复制，执行效率高。但是高并发情况下，会出现SQL语句执行顺序紊乱的错误。
（2）ROW：基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
（3）MIXED：混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

（2）主从复制原理

Master节点将数据的改变记录成二进制日志（bin log），当Master上的数据发生改变时，则将其改变写入二进制日志中。
Slave节点会在一定时间间隔内对Master的二进制日志进行探测，其是否发生改变，如果发生改变，则开启I/O线程请求Master的二进制事件。
同时Master节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至Slave节点本地的中继日志（Relay log）中，Slave节点将启动SQL线程从中继日志中读取数据记录在本地重放，即解析成SQL语句逐一执行，使得其数据内容和Master节点保持一致，最后I/O线程和SQL线程将进入睡眠状态，等待下一次被唤醒。

主从复制原理总结：

关键词：两个日志，三个线程

主开启二进制日志，从开启中继日志；
客户端在数据库中更新数据，相关的操作记录则会根据二进制日志格式，写入到二进制日志文件中；
从服务器检测到二进制日志发生改变，就会开启I/O线程向主节点请求；
主节点接收到I/O线程之后，会开启dump线程，向从服务器发送二进制日志事件；
从服务器接收到主服务器发来的二进制日志事件后，会将二进制日志记录保存到自己的中继日志中；
从服务器开启SQL线程读取中继日志中的二进制记录，并解析成SQL语句在本地逐一执行，从而实现主从服务器的数据同步。

（3）主从复制的作用

主从复制为了实现：

读写分离；
跨主机热备份数据;
作为数据库高可用性的基础。

3、实验：实现MySQL主从复制

（1）准备工具

三台装了mysql的虚拟机

20.0.0.10：主服务器

20.0.0.20：从服务器1

20.0.0.30：从服务器2

（2）系统初始化

systemctl stop firewalld
setenforce 0

（3）设置时间同步

vim /etc/chrony.conf
systemctl restart chronyd
timedatectl

（4）主从服务器启动mysql

systemctl start mysqld

（5）配置主从服务器

主服务器（20.0.0.10）

vim /etc/my.cnf
log-bin=mysql-bin						#添加，主服务器开启二进制日志
binlog_format=mixed                     #设置二进制日志格式
systemctl restart mysqld
mysql -u root -pabc123     
create user 'myslave'@'20.0.0.%' identified by 'myslave123';    #创建用户
grant replication slave on *.* to 'myslave'@'20.0.0.%';   #授权
flush privileges;   #刷新
show master status;

从服务器1（20.0.0.20）

vim /etc/my.cnf
server-id = 2  #修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=relay-log-bin.index			#定义中继日志文件的位置和名称，一般和relay-log在同一目录
systemctl restart mysqld
mysql -u root -pabc123;
change master to master_host='20.0.0.10', master_port=3306, master_user='myslave', master_password='myslave123', master_log_file='mysql-bin.000001', master_log_pos=767;
#配置同步，注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致
start slave;  #开启同步
show slave status\G  #查看

从服务器2（20.0.0.30）

vim /etc/my.cnf
server-id = 3  #修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=relay-log-bin.index			#定义中继日志文件的位置和名称，一般和relay-log在同一目录
systemctl restart mysqld
mysql -u root -pabc123;
change master to master_host='20.0.0.10', master_port=3306, master_user='myslave', master_password='myslave123', master_log_file='mysql-bin.000001', master_log_pos=767;
#配置同步，注意 master_log_file 和 master_log_pos 的值要与 Master 查询的一致
start slave;  #开启同步
show slave status\G  #查看

（6）验证

主服务器（20.0.0.10）进程创建库、表，并插入数据

数据会同步到从服务器中

从服务器1（20.0.0.20）

从服务器2（20.0.0.30）

（7）双"1"设置概述

1是每次事务提交时，MySQL都会把事务日志缓存区的数据写入日志文件中，并且刷新到磁盘中。
2是每次事务提交时，MySQL都会把事务日志缓存区的数据写入日志文件中，但不会同时刷新到磁盘上，而是会每秒执行一次刷新磁盘操作。
0是系统自动每秒将缓存区的数据写入到日志文件中，并同时刷新磁盘操作。

双’1’设置就是innodb_flush_log_at_trx_commit和sync_binlog两个参数设置，都设置为1就是双 1 设置。MySQL 默认配置就是双1配置。

innodb_flush_logs_at_trx_commit=1
详解： redo log（事务日志）的刷盘策略，每次事务提交时,MySQL都会把事务日志缓存区的数据写入日志文件中，并且刷新到磁盘中.

sync_binlog=1
详解： 在进行每次事务提交（写入二进制日志）以后，Mysql将执行一次fsync的磁盘同步指令，将缓冲区数据刷新到磁盘.

innodb_flush_logs_at_trx_commit=2
详解： 每次事务提交时MySQL都会把日志缓存区的数据写入日志文件中，但是并不会同时刷新到磁盘上。该模式下，MySQL会每秒执行一次刷新磁盘操作.

4、主从服务器延迟问题

延迟的产生：

当主库的TPS并发较高时，由于主库是多线程写入的，而从库的SQL线程是单线程的，导致从库SQL可能会跟不上主库的处理速度（生产者比消费者快，导致商品堆积）。

延迟产生的可能原因：

master服务器高并发，形成大量事务
网络延迟
主从硬件设备导致，cpu主频、内存io、硬盘io等
是同步复制、而不是异步复制
解决办法：

从库优化Mysq1参数。比如增大innodb buffer pool size，让更多操作在Mysql内存中完成，减少磁盘操作。
从库使用高性能主机。包括cpu强悍、内存加大。避免使用虚拟云主机，使用物理主机，这样提升了i/o方面性。
从库使用SSD磁盘
网络优化，避免跨机房实现同步

（1）如何判断MySQL主从复制是否发生延迟？

主从延迟判断的方法，通常有两种方法:Seconds_Behind_Master和mk-heartbeat

Seconds_Behind_Master的判断是：通过监控show slave status\G命令输出的Seconds_Behind_Master参数的值来判断。

Seconds_Behind_Master:是通过比较sql_thread执行的event的timestamp和io_thread复制好的event的timestamp(简写为ts)进行比较，而得到的这么一个差值;

0―该值为零，是我们极为渴望看到的情况，表示主从复制良好，可以认为lag不存在。
正值―表示主从已经出现延时，数字越大表示从库落后主库越多。
负值一几乎很少见，我只是听一些资深的DBA说见过，其实，这是一个BUG值，该参数是不支持负值的，也就是不应该出现。

（2）如何解决主从复制不一致问题？

方法一：忽略错误后，继续同步

该方法适用于主从库数据相差不大，或者要求数据可以不完全统一的情况，数据要求不严格的情况.

解决措施：

stop slave;
#表示跳过一步错误，后面的数字可变
set global sql_slave_skip_counter =1;
start slave;
之后再用mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
ok，现在主从同步状态正常了。。。

方式二：重新做主从，完全同步

该方法适用于主从库数据相差较大，或者要求数据完全统一的情况.

解决措施：

（1）先进入主库，进行锁表，防止数据写入

mysql> flush tables with read lock;

（2）进行数据备份

#把数据备份到mysql.bak.sql文件
[root@server01 mysql]#mysqldump -uroot -p -hlocalhost > mysql.bak.sql
这里注意一点：数据库备份一定要定期进行，可以用shell脚本或者python脚本，都比较方便，确保数据万无一失

（3）查看master状态

mysql> show master status;
+-------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+-------------------------------+
| mysqld-bin.000001 | 3260 | | mysql,test,information_schema |
+-------------------+----------+--------------+-------------------------------+
1 row in set (0.00 sec)

（4）把mysql备份文件传到从库，进行数据恢复

#使用scp命令
[root@server01 mysql]# scp mysql.bak.sql root@20.0.0.20:/tmp/

（5）停止从库的状态

mysql> stop slave;

（6）然后到从库执行mysql命令，导入数据备份

mysql> source /tmp/mysql.bak.sql

（7）设置从库同步，注意该处的同步点，就是主库show master status信息里的| File| Position两项

change master to master_host = '20.0.0.10', master_user = 'rsync', master_port=3306, master_password='', master_log_file = 'mysqld-bin.000001', master_log_pos=3260;

（8）重新开启从同步

mysql> start slave;

（9）查看同步状态

mysql> show slave status\G 查看：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes

5、MySQL主从复制的同步模式

（1）三种同步模式

异步复制（Asynchronous replication）

MySQL默认的复制即是异步的，主库在执行完客户端提交的事务后会立即将结果返回给客户端，并不关心从库是否已经接收并处理。这样就会有一个问题，主如果宕掉了，此时主上已经提交的事务可能并没有传到从上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。

全同步复制（Fully synchronous replication）

指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。

半同步复制（Semisynchronous replication）

介于异步复制和全同步复制之间，主库在执行完客户端提交的事务后不是立刻返回给客户端，而是等待至少一个从库接收到并写到relay log中才返回给客户端。相对于异步复制，半同步复制提高了数据的安全性，同时它也造成了一定程度的延迟，这个延迟最少是一个TCP/IP往返的时间。所以，半同步复制最好在低延时的网络中使用。

（2）案例实施：搭建半同步模式

此实验基于以上主从复制实验结果为基础

主服务器（20.0.0.10）

1）添加半同步模式参数，并重启mysql服务；

vim /etc/my.cnf
###添加半同步模式的参数
plugin-load=rpl_semi_sync_master=semisync_master.so	   #加载mysql半同步复制的插件
rpl_semi_sync_master_enabled=ON		  #或者设置为"1"，即开启半同步复制功能				
rpl-semi-sync-master-timeout=1000     #超时时间为1000ms，即1s
 
systemctl restart mysqld

从服务器（两台一样的操作）

vim /etc/my.cnf	
plugin-load=rpl_semi_sync_slave=semisync_slave.so
rpl_semi_sync_slave_enabled=ON
 
systemctl restart mysqld

主服务器（20.0.0.10）查看

mysql  -u root -pabc123;
show status like '%Rpl_semi%';	 #在主库查询半同步状态

参数说明：

Rpl_semi_sync_master_clients              		#半同步复制客户端的个数
Rpl_semi_sync_master_net_avg_wait_time    		#平均等待时间（默认毫秒）
Rpl_semi_sync_master_net_wait_time        		#总共等待时间
Rpl_semi_sync_master_net_waits            		#等待次数
Rpl_semi_sync_master_no_times             		#关闭半同步复制的次数
Rpl_semi_sync_master_no_tx                		#表示没有成功接收slave提交的次数
Rpl_semi_sync_master_status               		#表示当前是异步模式还是半同步模式，on为半同步
Rpl_semi_sync_master_timefunc_failures    		#调用时间函数失败的次数
Rpl_semi_sync_master_tx_avg_wait_time     		#事物的平均传输时间
Rpl_semi_sync_master_tx_wait_time         		#事物的总共传输时间
Rpl_semi_sync_master_tx_waits             		#事物等待次数
Rpl_semi_sync_master_wait_pos_backtraverse		#可以理解为"后来的先到了，而先来的还没有到的次数"
Rpl_semi_sync_master_wait_sessions        		#当前有多少个session因为slave的回复而造成等待
Rpl_semi_sync_master_yes_tx               		#成功接受到slave事物回复的次数

（3）半同步模式 VS 异步模式

面试题： 你们数据库主从复制采用是什么模式？什么情况下半同步模式会转换为异步模式？异步模式又会在什么模式下变为半同步模式？

主从复制在延迟超时时间内，主没有收到从的反馈响应，主会暂时关闭半同步复制，转而使用异步复制模式，也就是会自动降为异步工作。当主dump线程发送完一个事务的所有事件之后，如果在延迟超时时间内，主又收到了从库的响应， 主就会再次恢复为半同步模式。

6、实验：搭建MySQL读写分离

基于以上主从复制实验结果为基础

20.0.0.10：主服务器

20.0.0.20：从服务器1

20.0.0.30：从服务器2

20.0.0.40：Amoeba服务器

（1）Amoeba服务器（20.0.0.40）

1）初始化操作

[root@zx4 ~]# systemctl stop firewalld
[root@zx4 ~]# setenforce 0
setenforce: SELinux is disabled
[root@zx4 ~]#

2）安装 Java 环境

[root@zx4 ~]# rpm -qa | grep jdk
java-1.8.0-openjdk-1.8.0.332.b09-1.el7_9.x86_64
java-1.7.0-openjdk-headless-1.7.0.261-2.6.22.2.el7_8.x86_64
copy-jdk-configs-3.3-10.el7_5.noarch
java-1.7.0-openjdk-1.7.0.261-2.6.22.2.el7_8.x86_64
java-1.8.0-openjdk-headless-1.8.0.332.b09-1.el7_9.x86_64
[root@zx4 ~]# yum remove java*
[root@zx4 ~]# cd /opt/
[root@zx4 opt]# ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin  rh
[root@zx4 opt]#
[root@zx4 opt]# chmod +x jdk-6u14-linux-x64.bin
[root@zx4 opt]# ./jdk-6u14-linux-x64.bin  ##ctrl+c 写yes
[root@zx4 opt]# mv jdk1.6.0_14/ /usr/local
[root@zx4 opt]# mkdir /usr/local/amoeba
[root@zx4 opt]# tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
[root@zx4 opt]# cd /usr/local/
[root@zx4 local]# ls
amoeba  bin  etc  games  include  jdk1.6.0_14  lib  lib64  libexec  sbin  share  src
[root@zx4 local]# vim /etc/profile.d/jdk1.6.sh
##添加配置
export JAVA_HOME=/usr/local/jdk1.6.0_14
export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$AMOEBA_HOME/bin

[root@zx4 local]# source /etc/profile
[root@zx4 local]#
[root@zx4 local]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)
[root@zx4 local]#
[root@zx4 local]# cd /usr/local/amoeba/bin
[root@zx4 bin]# ./amoeba
amoeba start|stop
[root@zx4 bin]# vim /etc/profile.d/jdk1.6.sh
[root@zx4 bin]# cd /usr/local/amoeba/conf/
[root@zx4 conf]# cp amoeba.xml{,.bak}
[root@zx4 conf]# vim amoeba.xml


##30行修改用户名
<property name="user">zx</property>
##32行修改密码
<property name="password">zx123</property>
##115行默认服务器连接到master组                      #该用户名及密码为客户端连接amoeba的用户名及密码
<property name="defaultPool">master</property>
##117行去掉注释并修改
<property name="writePool">master</property>
<property name="readPool">slaves</property>

（2）主从服务器创建用户

1）主服务器（20.0.0.10）

mysql> create user 'amoeba'@'20.0.0.%' identified by 'amoeba123';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on *.* to 'amoeba'@'20.0.0.%';
Query OK, 0 rows affected (0.00 sec)

mysql>

2）两个从服务器（20.0.0.20/30）

grant all on *.* to 'amoeba'@'20.0.0.%' identified by 'amoeba123';

3）Amoeba服务器

[root@zx4 bin]# cd /usr/local/amoeba/conf/
[root@zx4 conf]# cp amoeba.xml{,.bak}
[root@zx4 conf]# vim amoeba.xml
[root@zx4 conf]# vim amoeba.xml
[root@zx4 conf]# vim amoeba.xml
[root@zx4 conf]# cd /usr/local/amoeba/conf/
[root@zx4 conf]# cp dbServers.xml{,.bak}
[root@zx4 conf]# vim dbServers.xml
注释23行
##设置为amoeba用户
<property name="user">amoeba</property> 
##删除30行注释，28行注释添加完整，29行密码修改
<property name="password">amoeba123</property>
##44行47行分别设置主服务器名字和地址              ##此处用户名和密码为amoeba与主从服务器连接用
<dbServer name="master"  parent="abstractServer">
<property name="ipAddress">192.168.9.150</property>
##51行54行分别设置从服务器名字和地址
<dbServer name="slave1"  parent="abstractServer">
<property name="ipAddress">192.168.9.210</property>
##56行下额外添加一个从服务器配置
         <dbServer name="slave2"  parent="abstractServer">
                  <factoryConfig>
                          <!-- mysql ip -->
                         <property name="ipAddress">192.168.9.120</property>
                </factoryConfig>
          </dbServer>
##64行设置服务器池名称
<dbServer name="slaves" virtual="true">
##70行定义两个服务器地址
<property name="poolNames">slave1,slave2</property>


[root@zx4 conf]# cd /usr/local/amoeba/bin
[root@zx4 bin]# ./amoeba start &
[1] 12559
[root@zx4 bin]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2024-06-27 15:16:27,788 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2024-06-27 15:16:27,911 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2024-06-27 15:16:27,912 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:61481.

[root@zx4 bin]#
[root@zx4 bin]# netstat -lntp | grep 8066
tcp6       0      0 :::8066                 :::*          

（3）测试

使用navicat

navicat 来插入数据

主服务器（20.0.0.10）

从服务器（20.0.0.20/30）

关闭从库进行验证

测试1

stop slave;  #关闭两个从库

navicat 插入id为4的数据

但主服务器显示

两台从服务器不显示

测试2

从服务器1（20.0.0.20）插入id为5的内容

从服务器2（20.0.0.30）插入id为6的内容

navicat查询会来回切换

当两台从服务器重新开启时

start slave;

主服务器的内容会重新同步给从服务器