Hello，欢迎来到程序员社区。 今天聊一聊 MySQL 主从复制 与 读写分离，希望对大家有所帮助。

Java面试手册PDF下载：点击下载最全Java面试手册

前言

编程电子书汇总

• MySQL 数据是存储到硬盘中的，性能会受到 IO 瓶颈的影响，业务量大时压力较大、会降低效率。

• 在实际企业应用中，只有一台数据库服务器，去处理读和写，是不能满足实际需求的。单台数据库服务器无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

• 所以，实际企业中，会配置多台主从数据库服务器，通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。有点类似于rsync，但是不同的是rsync是对磁盘文件做备份，而mysql主从复制是对数据库中的数据、语句做备份。

一、MySQL 主从复制

• MySQL 的主从复制 和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。
• 数据库复制被用来把事务性操作导致的变更 同步到集群中的 从数据库。

1.1 MySQL 的复制类型

二进制日志的三种格式：

• 基于语句的复制（STATEMENT，MySQL 默认类型）：在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。
• 基于行的复制（ROW）：把改变的内容一行一行的复制到从服务器上执行，而不是把命令在从服务器上执行一遍。精确度高，效率低。
• 混合类型的复制（MIXED）：默认采用基于语句（STATEMENT）的复制，一旦发现基于语句无法精确复制时，就会采用基于行（ROW）的复制。

1.2、MySQL主从复制的工作过程

• 主MySQL服务器：Master
• 从MySQL服务器：Slave

第一步：Master 服务器保存记录到二进制日志

• 在每个事务更新数据完成之前，Master 服务器在二进制日志（Binary log）记录这些改变。写入二进制日志完成后，Master 服务器通知存储引擎提交事务。

第二步：Slave 服务器复制 Master 服务器的日志

• Slave 服务器将 Master 服务器的二进制日志复制到其中继日志（Relay log）中。
  Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。
  MASTER服务器收到slave IO线程发来的日志请求信息，io线程去将bin-log内容，position返回给slave IO线程。
  slave服务器收到bin-log日志内容，将bin-log日志内容写入relay-log中继日志，创建一个master.info的文件，该文件记录了master ip 用户名 密码 master bin-log名称，bin-log position。
  slave端开启SQL线程，实时监控relay-log日志内容是否有更新，解析文件中的SQL语句，在slJava面试手册ave数据库中去执行。

• 首先 Slave 服务器开始一个工作线程（I/O），I/O线程 在 Master 服务器上打开一个普通的连接，然后开始 Binlog dump process。Binlog dump process 从 Master 服务器的二进制日志中读取事件，如果 Slave 服务器已经跟上 Master 服务器（Master 没有新的增删改操作），它会睡眠并等待 Master 服务器产生新的事件，I/O线程将这些事件写入中继日志。

第三步：Slave 服务器重放复制过来的日志

• SQL slave thread（SQL从线程）处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其中的事件而更新 Slave 服务器的数据，使其与 Master 服务器中的数据一致，只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 缓存中，所以中继日志的开销很小。

注意： 复制过程有一个很重要的限制，即复制在 Slave 服务器上是串行化的，也就是说 Master 服务器上的并行更新操作不能在 Slave 服务器上并行操作。

总结：

• relay-log日志记录的是从服务器I/O线程将主服务器的二进制日志读取过来记录到从服务器本地文件，然后SQL线程会读取relay-log日志的内容并应用到从服务器。

两个文件，三个线程
Slave 通过IO线程连接master，并且请求某个bin-log，position之后的内容。
Master 通过dump 线程，把数据交给从服务器，Slave 再写入到 中继日志中，在通过 SQL线程写入到从数据库中。

二、读写分离

• MySQL 是表锁定级别，读写同时进行会互相锁定。读的时候会锁定写，写的时候会锁定读。读写分离 用来解决这个问题

1、读写分离的概念

• 读写分离基本的原理：让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE）、让而从数据库处理SELECT 查询操作。

2、读写分离存在意义

• 因为数据库的“写”操作是比较耗时的。（写3000条数据可能要1分钟）
• 但是数据库的“读”。（读3000条数据可能只要1秒钟）

所以读写分离解决的是：数据库在写入的时候，会影响了查询的效率的问题；

3、什么时候要读写分离

数据库不一定非要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用（从服务器用来读）。

利用数据库主从同步，再通过读写分离可以分担数据库压力，以此提高数据库的性能。

4、MySQL 读写分离原理

• **读写分离就是只在主服务器上写，只在从服务器上读。**基本的原理是让主数据库处理事务性查询，而从数据库处理 select 查询。
• 应用场景：读的频率比较多，写的频率少

常见的 MySQL 读写分离种类
1）基于程序代码内部实现

• 在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。
• 优点：性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支；
• 缺点：需要开发人员来实现，运维人员无从下手。

注意：并不是所有的应用都适合在程序代码中实现读写分离，像一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。

2）基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器接到客户端请求后通过判断后转发到后端数据库，有以下代表性程序：

• MySQL-Proxy：MySQL-Proxy 为 MySQL 开源项目，由于使用MySQL Proxy需要写大量的Lua脚本，这些Lua脚本不是现成的，而需要自己编写，这对于并不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说是非常困难的。
• Atlas：是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy 0.8.2版本的基础上，对其进行了优化，增加了一些新的功能特性。支持事物以及存储过程。
• Amoeba：由Java语言进行开发，不支持事务和存储过程。Amoeba是一个非常容易使用，可移植性非常强的软件，因此它在生产环境中被广泛用于数据库的代理层。

三、主从复制和读写分离部署

首先关闭防火墙 ！！！

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

3.1 搭建 MySQL 主从复制

一主两从
Master 192.168.80.30 7-8
Slave1服务器：192.168.80.13 7-3
Slave2服务器：192.168.80.14

3.1.1 Mysql 主从服务器时间同步

首先先配置主服务器是时间源，两台从服务器使用该时间源。

Master 192.168.10.10

#安装 ntp时间服务器
yum -y install ntp

vim /etc/ntp.conf

#末尾添加
server 127.127.10.0          #设置本地Master是时钟源(注意修改网段) 127 表示本机，10表示网段
fudge 127.127.10.0 stratum 8 #设置时间层级为8（限制在15内）

service ntpd start

Slave
Slave1服务器：192.168.10.40
Slave2服务器：192.168.10.50

yum -y install ntp ntpdate

service ntpd start

/usr/sbin/ntpdate 192.168.10.10  #进行时间同步，指向Master服务器IP

#每30分钟执行一次
crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.10.10

#定时任务需要确保crond服务开启
 systemctl status crond

两台Slave都操作

3.1.2 主服务器的mysql配置

Master 服务器：192.168.10.10

因为从服务需要获取主服务器的二进制日志的事件，所以主服务器需要开启二进制日志。

vim /etc/my.cnf

server-id = 1
log-bin=master-bin      #添加，主服务器开启二进制日志
binlog_format=MIXED     #二进制日志使用混合模式
log-slave-updates=true  #添加，允许从服务器更新二进制日志

#重启
systemctl restart mysqld

进入mysql中，授权给从服务器：

mysql -u root -p
#主给从服务器授权
grant replication slave on *.* to 'myslave'@'192.168.10.%' identified by 'abc123';

#myslave 是自己起的从服务器的名字

#刷新权限
flush privileges;  

#查看主服务器配置
show master status;
#File 列显示日志名，Position 列显示偏移量

3.1.3 从服务器的mysql配置

Slave1服务器：192.168.10.40
Slave2服务器：192.168.10.50

vim /etc/my.cnf
#修改，注意id与Master的不同，两个Slave的id也要不同
server-id = 2   #server-id = 3

#添加，开启中继日志，从主服务器上同步日志文件记录到本地
relay-log=relay-log-bin

#添加，定义中继日志文件的位置和名称,在 /data 目录中
relay-log-index=slave-relay-bin.index  

systemctl restart mysqld

两台从服务器都需要配置同步

mysql -u root -p

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

start slave;                 #启动同步，如有报错执行 reset slave;
show slave statusG               #查看 Slave 状态

//确保 IO 和 SQL 线程都是 Yes，代表同步正常。
Slave_IO_Running: Yes           #负责与主机的io通信
Slave_SQL_Running: Yes          #负责自己的slave mysql进程

一般 Slave_IO_Running: 为 No 的可能性：

• 网络不通
• my.cnf 文件中配置有问题
• 对应主服务器中设置的 密码、file文件名、pos偏移量参数不对
• 防火墙没有关闭
• 在从机上进行了写操作（非双机热备情况下）。
• slave 机器重启，事务回滚。
• 各种原因导致的数据不同步。

如果从机查看状态（show slave statusG;）Slave_SQL_Running 为 No，解决方法:

这个解决的是因为事务的原因导致的sql进程停止。

mysql> stop slave;
mysql> set GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> start slave;

3.1.4 验证主从复制效果

在主服务器中，新建数据库新建表，新增记录，在两台从服务器数据库中都能查到新增的记录。

但是配置主从复制之前的主库中的数据从服务器中没有。所以如果是后来才做的主从复制，需要先将主服务器上的数据库同步到从服务器上，再开启主从复制服务。

只能从复制主，主复制不了从。

3.2 搭建 MySQL 读写分离

• MySQL 的主从复制 和 MySQL 的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

3.2.1 Amoeba 服务器配置

• 所用主机：192.168.10.111

• 将 jdk-6u14-linux-x64.bin 和 amoeba-mysql-binary-2.2.0.tar.gz.0 上传到 /opt 目录下。

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

（1）安装 Java 环境

cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/

cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
./jdk-6u14-linux-x64.bin
按空格到最后一行
按yes，按enter

mv jdk1.6.0_14/ /usr/local/jdk1.6

vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.6
export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib
export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin
export AMOEBA_HOME=/usr/local/amoeba
export PATH=$PATH:$AMOEBA_HOME/bin

source /etc/profile
java -version

yes 或回车，安装完成。



（2）安装 Amoeba软件

mkdir /usr/local/amoeba

tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
//如显示amoeba start|stop 说明安装成功

3.2.2 在主从服务器的mysql上授权

编程电子书汇总

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

#给192.168.10网段的 test 用户授权所有权限，装了mysql的机器都要添加

grant all on *.* to 'test'@'192.168.10.%' identified by 'abc123';

3.2.3 配置 Amoeba读写分离，两个 Slave 读负载均衡

（1）修改 Amoeba 配置文件

cd /usr/local/amoeba/conf/

cp amoeba.xml amoeba.xml.bak  #备份

vim amoeba.xml           #修改Amoeba配置文件

#---------30修改-------
property name="user">amoeba/property>
#---------32修改-------
property name="password">abc123/property>

#------115修改，默认是转到主服务器-----------
property name="defaultPool">master/property>
#-----117去掉注释，设置主服务器用来写，从服务器用来读-----
property name="writePool">master/property>
property name="readPool">slaves/property>

（2）修改数据库配置文件

#备份
cp dbServers.xml{,bak} 

vim dbServers.xml

#--------23行注释掉-------
#作用：MySQL会默认进入test库 以防启动时 mysql中没有test库时，就会报错
#因为 5.5、5.6 版本会自带 test 库，5.7就没有test 库
!-- mysql schema
property name="schema">test/property>
-->

#----26修改---改成MySQL机器grant授权的用户名和密码-----
!-- mysql user -->
property name="user">test/property>
#----28-30去掉注释---
property name="password">abc123/property>

#----45修改，设置主服务器的名Master--
dbServer name="master"  parent="abstractServer">
#----48修改，设置主服务器的地址----
property name="ipAddress">192.168.10.10/property>
#-----52修改，设置从服务器的名slave1----
dbServer name="slave1"  parent="abstractServer">
#----55修改，设置从服务器1的地址-----
property name="ipAddress">192.168.10.40/property>

#-----复制上面6行粘贴，设置从服务器2的名slave2和地址---
dbServer name="slave2"  parent="abstractServer">
property name="ipAddress">192.168.10.50/property>

#------修改后的65或66修改---------
dbServer name="slaves" virtual="true">
#------71修改，添加从服务器名称----
property name="poolName编程电子书汇总s">slave1,slave2/property>

#保存退出

/usr/local/amoeba/bin/amoeba start&     #启动Amoeba软件，按ctrl+c 返回，& 后台启动
netstat -anpt | grep java             #查看8066端口是否开启，默认端口为TCP 8066

从服务器池中，默认使用轮循算法

3.2.4 测试读写分离

客户端先连接 Amoeba 服务器，然后写的操作交给 Master 机器，再通过主从复制把事件写到两台从服务器中。读的操作通过轮循调度算法交给 其中一台Slave 从服务器执行，再返回结果。

四、主从复制延迟

造成MySQL主从复制延迟的原因：

• 1、master服务器高并发，形成大量事务；
• 2、网络延迟；
• 3、主从硬件设备性能导致：CPU主频、内存I/O、硬盘I/O性能；
• 4、本来就不是同步复制、而是异步复制；

降低从复制延迟方法：

• 从库优化Mysal参数：比如增大 Innodb buffer pool size，让更多操作在Mysql内存中完成，减少磁盘操作。
• 从库使用高性能主机：包括cpu强悍、加大内存。避免使用虚拟云主机，使用物理主机，进行提升I/O性能。
• 从库使用SSD磁盘
• 进行网络优化，避免跨机房实现同步

面试题链接

> 时间不一定能证明很多东西，但是一定能看透很多东西。坚信自己的选择，不动摇，使劲跑，明天会更好。