MySQL主从复制和读写分离

前言

MySQL使用主从复制与读写分离的概述

1、在企业应用中，成熟的业务通常数据量都比较大

2、单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求

3、配置多台主从数据库服务器以实现读写分离

一、MySQL主从复制原理

1、MySQL的复制类型

（1）基于语句的复制

在主服务器上执行的 SQL 语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。

（2）基于行的复制

把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

（3）混合类型的复制（语句、行、日志文件）

默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

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

二、MySQL读写分离

原因：数据库每天都要承担的网站的数据更新，所以大量的读写任务都在一个机器上压力非常大。使用读写分离可以有效的缓解数据库压力。

1、MySQL读写分离原理

（1）只在主服务器上写，旨在从服务器上读。

（2）主数据库处理事务性查询，从数据库处理SELECT查询。

（3）数据库复制用于将事务性查询的变更同步到集群中的从数据库。

2、读写分离方案

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

在代码中根据 select、insert 进行路由分类，这类方法也是目前生产环境应用最广泛的。

优缺点：

优点是性能较好，因为在程序代码中实现，不需要增加额外的设备为硬件开支

缺点是需要开发人员来实现，运维人员无从下手。

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

（2）基于中间代理层实现

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

MySQL-Proxy。MySQL- -Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断,虽然是MySQL官方产品，但是MySQL官方并不建议将MySQL- -Proxy 用到生产环境。

Amoeba (变形虫)。由陈思儒开发，其曾就职于阿里巴巴。该程序用Java语言进行开发,阿里巴巴将其用于生产环境。它不支持事务和存储过程。

三、MySQL主从复制和读写分离实验示例

拓扑图：

1、实验思路

第一步：客户端client访问代理服务器amoeba

第二步：代理服务器读写判断

写操作：写入到主服务器

第三步：主服务器将增删改写入自己二进制日志

第四步：从服务器将主服务器的二进制日志同步至自己中继日志

第五步：从服务器重放中继日志到数据库中

读操作：直接访问从服务器

最终结果：降低负载，起到负载均衡作用

2、环境配置

主机	操作系统	IP地址	所需工具/软件/安装包
Amoeba	CentOS7	192.168.10.11	jdk-6u14-linux-x64.bin、amoeba-mysql-binary-2.2.0.tar.gz
Master	CentOS7	192.168.10.12	ntp 、 mysql-boost-5.7.20.tar.gz
Slave1	CentOS7	192.168.10.13	ntp 、ntpdate 、 mysql-boost-5.7.20.tar.gz
Slave2	CentOS7	192.168.10.14	ntp 、ntpdate 、mysql-boost-5.7.20.tar.gz
客户端	CentOS7	192.168.10. 15

关闭所有主机的防火墙和安全防护机制

systemctl stop firewalld
systemctl disable firewalld
setenforce 0

3、搭建 MySQL 主从复制

（1）Mysql主从服务器时间同步

Master服务器：192.168.10.12

#yum安装ntp服务
yum -y install ntp

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

#开启ntp服务
service ntpd start

·


Slave1服务器：192.168.10.13
Slave2服务器：192.168.10.14

yum -y install ntp ntpdate

#开启ntpd服务
service ntpd start

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

crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.10.12

※※※ 另一台的从服务器的配置一样，这边就不做演示了

（2）主服务器的mysql配置

Master服务器：192.168.10.12

这边我的mysql已经安装编译好了，不演示了

vim