MySQL的主从复制与读写分离

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

一、主从复制

1.主从复制工作过程

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

1.1主从复制类型

• 基于语句的复制
• 基于行的复制
• 混合类型的复制

1.2三个主要线程

dump线程：由主数据库开启，用于读取主的二进制日志并传输给从数据库 。

io线程：由从数据库开启，用于接收二进制日志并保存为中继日志。

sql线程：由从数据库开启，用于将中继日志写入备数据库中完成主从复制。

2.搭建MySQL主从复制

准备环境：

MASTER	192.168.21.10
SLAVE1	192.168.21.30
SLABE2	192.168.21.40

2.1Mysql主从服务器时间同步

2.11主服务器设置

yum install ntp -y


vim /etc/ntp.conf
--末尾添加--
server 127.127.10.0							#设置本地是时钟源，注意修改网段
fudge 127.127.10.0 stratum 8				#设置时间层级为8（限制在15内）

2.12 从服务器设置

yum install ntp ntpdate -y

service ntpd start
/usr/sbin/ntpdate 192.168.21.10				#进行时间同步

2.2主服务器的mysql配置

vim /etc/my.cnf
server-id = 11
log-bin=master-bin							#添加，主服务器开启二进制日志
binlog_format = MIXED
log-slave-updates=true						#添加，允许slave从master复制数据时可以写入到自己的二进制日志

然后重启Mysql，并查看其中有无数据库

 

GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.21.%' IDENTIFIED BY '123456';			#给从服务器授权
FLUSH PRIVILEGES;

show master status;

File 列显示日志名，Position 列显示偏移量

File：当前二进制日志（Binary Log）文件的名称

Position：在当前二进制日志文件中的下一个事件（event）的位置。这个位置值对于从服务器而言至关重要，因为它需要从这个位置开始读取和执行主服务器上的事件，从而保持数据同步

2.3从服务器的Mysql配置

3.1配置从服务器接收

server-id = 22								#修改，注意id与Master的不同，两个Slave的id也要不同
relay-log=relay-log-bin						#添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index		#添加，定义中继日志文件的位置和名称，一般和relay-log在同一目录
relay_log_recovery = 1                      #选配项
#当 slave 从库宕机后，假如 relay-log 损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的 relay-log，并且重新从 master 上获取日志，这样就保证了relay-log 的完整性。默认情况下该功能是关闭的，将 relay_log_recovery 的值设置为 1 时， 可在 slave 从库上开启该功能，建议开启。

修改后重新启动服务器

另一台也是同样操作

修改后重新启动服务器

3.2连接主服务器

登录进入mysql

change master to master_host='192.168.21.10',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=603;
#配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

记得一定要关防火墙！

start slave;                        #启动同步，如有报错执行 reset slave;
show slave status\G                    #查看 Slave 状态
//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes                #负责与主机的io通信
Slave_SQL_Running: Yes                #负责自己的slave mysql进程

查看状态时需要确认 IO 和 SQL 线程都是 Yes，代表同步正常

另一台也是这样

主从复制服务已经配置完毕，在主服务器上进行SQL语句的操作，那么从服务器上就会通过获取二进制日志文件同步操作

在主服务器创建work数据库 ，从服务器也会同步创建

二、读写分离

读写分离是数据库架构优化的一种常用手段，其核心思想是将数据库的读操作和写操作分离开，通过多个数据库节点分摊压力。在MySQL中，通常会有一个主数据库（Master）负责处理所有写入请求，多个从数据库（Slave）负责处理读取请求。

1.读写分离的作用

在工作环境中对数据库的操作，往往查询操作要远远多于增、删、改。写数据的操作往往要比查询更加耗费资源，影响查询的效率，所以需要进行读写分离

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

2.实现读写分离的方式

1.基于数据库层面

MySQL主从复制：主数据库负责处理写操作，并通过二进制日志将变更同步到从数据库，从数据库负责处理读操作。可以配置多个从库以实现读负载均衡。Oracle RAC与DG：Oracle数据库提供了Real Application Clusters (RAC) 和Data Guard两种技术实现读写分离，前者通过集群实现读写共享，后者通过数据同步实现读写分离。

2.中间件代理方式

MySQL Proxy：作为一个轻量级的MySQL中间件，可以根据SQL语句类型自动路由到主库或从库。

MyCat、ShardingSphere：这些数据库中间件支持读写分离和分库分表等功能，可以灵活地将读请求分发到多个从库。

Amoeba：由陈思儒开发，作者曾就职于阿里巴巴。该程序由Java语言进行开发，阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
 

3.程序代码层面

自定义数据库连接池：在应用程序中，根据SQL语句类型（读/写）使用不同的数据库连接池，读操作连接到从库，写操作连接到主库。ORM框架支持：一些ORM框架（如Hibernate、MyBatis等）提供了读写分离的支持，开发者可以通过配置轻松实现读写分离。

4.云服务提供商集成方案

AWS Aurora、阿里云RDS等云数据库服务通常提供内置的读写分离功能，只需简单配置即可启用。

5.负载均衡器

软件负载均衡器（如HAProxy、Nginx等）可以配合MySQL主从复制实现读写分离，通过配置规则将读请求转发到从库群组，写请求始终发送到主库。

3.实现读写分离

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

1.基于主从复制

首先部署好主从复制环境，这是实现读写分离的基础

读写分离可以使用一主一从实现，但是读写分离的作用主要就是缓解读的压力，所以一般从服务器都在两台及以上

2.部署Amoeba服务器

因为 Amoeba 基于是 jdk1.5 开发的，所以官方推荐使用 jdk1.5 或 1.6 版本，高版本不建议使用。

先安装jdk

[root@zqsl opt]#ls
jdk-6u14-linux-x64.bin
#准备好安装脚本
[root@zqsl opt]#cp jdk-6u14-linux-x64.bin /usr/local/
[root@zqsl opt]#cd /usr/local/
[root@zqsl local]#chmod +x jdk-6u14-linux-x64.bin
[root@zqsl local]#./jdk-6u14-linux-x64.bin    #执行该文件进行安装
#按回车到yes/no选择界面，输入yes进行安装，而后继续回车
[root@zqsl local]#ls
bin  etc  games  include  jdk1.6.0_14  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
#生成jdk1.6.0_14目录文件

3.安装Amoeba

[root@zqsl opt]#ls
amoeba-mysql-binary-2.2.0.tar.gz  jdk-6u14-linux-x64.bin
[root@zqsl opt]#mkdir /usr/local/amoeba
#创建安装目录
[root@zqsl opt]#tar xf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
#将amoeba的tar包解压到安装目录
[root@zqsl opt]#ls /usr/local/
amoeba  bin  etc  games  include  jdk1.6  jdk-6u14-linux-x64.bin  lib  lib64  libexec  sbin  share  src
[root@zqsl opt]#chmod -R 755 /usr/local/amoeba/
#设置权限
[root@zqsl opt]#/usr/local/amoeba/bin/amoeba
amoeba start|stop
#绝对路径执行amoeba命令，显示amoeba start|stop说明安装成功

显示amoeba start|stop说明安装成功

4.配置Amoeba读写分离

首先在Master、Slave1、Slave2 的mysql上开放权限给 Amoeba 访问

mysql> grant all on *.* to test@'192.168.21.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.01 sec)

修改amoeba配置文件

修改amoeba.xml文件

设置用户，在连接MySQL数据库是使用该用户进行验证

将117行与120行注释删除

定义writePoll 为master

定义readPoll 为slaves

<property name="defaultPool">master</property>：
#这一行设置了默认的数据库连接池为“master”。这意味着，除非明确指定读写类型，否则所有的
#数据库操作（读写）都会通过连接到名为“master”的数据库进行。
 
<property name="writePool">master</property>：
#这一配置指定了所有的写操作。应当通过名为“master”的数据库连接池进行。
 
<property name="readPool">slaves</property>：
#这一行则定义了所有的读操作应当通过名为“slaves”的数据库连接池进行。
#这里的“slaves”可能是指一组从库，即读操作会通过负载均衡的方式分散到多个从库上，
#以此提高读取性能和系统吞吐量。

修改dbServers.xml文件

dbServer标签定义了三个数据库服务器，分别是master、slave1和slave2，它们都继承
自abstractServer配置。
 
master服务器的数据库IP地址为192.168.21.10 #通常用于处理写入操作。
 
slave1服务器的数据库IP地址为192.168.21.30。
slave2服务器的数据库IP地址为192.168.21.40，
#它们通常用于处理读取操作，以实现读写分离和负载均衡。
 
 
 
定义了一个虚拟的数据库服务器slaves，它实际上不是一个真实的数据库服务器，
而是代表了前面定义的slave1和slave2两个从服务器。
 
<property name="loadbalance">1</property> 表示负载均衡策略为轮询（ROUNDROBIN）。
<property name="poolNames">slave1,slave2</property> 
指定了组成slaves虚拟服务器的实体从服务器列表，即读操作将在这两个从服务器之间进行轮询分配。

启动amoeba

启用之后重新打开一个终端，查看是否开启成功

监听本机的8066端口，同时连接主从服务器的3306端口

总结

主从复制与读写分离是MySQL服务当中重要的调优部分，会增加读写性能与抗高并发。

不过需要注意的是

1.在修改配置文件时，注意文件的备份，很多新手小白在修改配置文件时，没有备份，配置文件修改错误之后，无法恢复

2.使用主从复制时，从服务器的MySQL版本一定要高于主服务器的版本，因为高版本可以向下兼容，若主版本高于从版本，可能无法兼容，导致同步失败

3.不论是读写分离还是主从复制，都会有一定因素影响它们正常运行，比如网络延迟、错误的SQL语句，导致某一台从服务器掉线，防火墙与核心防护是否开启，都可能导致运行出现错误，需要有一定经验去排查错误