MySQL主从复制和读写分离详解--陈奇网络工作室

系统操作和维护

在实际生产环境中，如果数据库的读写都在同一个数据库服务器中操作，在安全性、高可用性、高并发性等方面完全无法满足实际需求。那么在实际生产环境中如何解决这个问题呢？这就需要提到对mysql数据库特别重要的两项技术：主从复制和读写分离。

实际环境中，一台MYSQL主服务器带两台Mysql从服务器做数据复制，前端应用写数据库时操作主服务器；读取数据库时，运行两台从服务器，大大减轻了主服务器的压力。如图所示：

一、MySQL主从复制原理

MySQL的主从复制和MySQL的读写分离有着必然的关系。首先，应该部署主从复制。只有完成了主从复制，才能实现数据的读写分离。

1)MySQL支持的复制类型

(1)基于语句的复制：在主服务器上执行的SQL语句在从服务器上执行。MySQL默认使用基于语句的复制，效率更高。但二进制日志功能必须开启；

(2)基于行的复制：复制改变的内容，而不是在从服务器上执行命令；

(3)混合型复制：默认采用基于语句的复制，一旦发现基于语句的复制无法准确复制，就会采用基于行的复制；

2)复制的工作过程

MySQL复制的工作过程如下所示：

详细介绍了MySQL复制的工作过程：

1.在所有数据更新完成之前，Master会在二进制日志中记录这些更改。写入二进制日志后，Master通知存储引擎提交事务；

2.从机将主机的二进制日志复制到其中继日志中。第一个从服务器启动一个工作进程——I/O线程，I/O线程在主服务器上打开一个普通连接，然后启动二进制日志转储进程。Binlog转储进程从主服务器的二进制日志中读取事件，如果它已经跟上主服务器，它将休眠并等待主服务器生成新事件。I/O线程将这些时间写入中继日志；

3.SQL从属线程处理流程的最后一步。SQL线程从中继日志中读取事件，重放事件并更新从数据，使其与主数据一致。只要线程与I/O线程一致，中继日志通常位于OS的缓存中，因此中继日志的开销很小。

复制过程中有一个重要的限制，就是复制在从机上是序列化的，也就是说主机上的并行更新操作不能在从机上并行操作。

二、MySQL读写分离原理

简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本原则是让主数据库处理事务性查询，而从数据库处理选择查询。数据库复制用于同步由对集群中从属数据库的事务性查询引起的更改。如图所示：

目前，常见的MySQL读写分离分为两种：

1)基于程序代码的内部实现

路由分类基于代码中的选择和插入，这也是目前生产环境中应用最广泛的方法。优点是性能更好，因为是在程序代码中实现的，硬件开发不需要额外的设备。缺点是需要开发者来实现，运维人员无从下手；

2)基于中间代理层的实现

代理通常位于客户端和服务器之间。代理服务器收到客户端的请求后，进行判断并转发给后端数据库。有两个代表性的计划：

(1) MySQL-proxy:是MySQL的开源项目，通过自己的lua脚本进行SQL判断，虽然是MySQL的官方产品。但是，MySQL官方不建议在生产环境中使用MySQL—Proxy；

(2)变形虫：由陈思如开发，其层在阿里巴巴工作。程序用Java语言开发，阿里巴巴在生产环境中使用。它不支持事务和存储过程；

经过以上简单的比较，通过程序代码实现MySQL读写分离自然是一个不错的选择，但并不是所有的应用都适合在程序代码中实现读写分离。比如一些大型复杂的Java应用，如果在程序代码中实现了分离，会对代码产生很大的改变。所以这样复杂的应用一般会考虑使用代理层来实现。

第三，构建MySQL主从复制

实验扩展如下：

一般步骤分为：

(1)1)MySQL主服务器建立时间同步；

(2)MySQL从服务器同步时间；

(3)关闭防火墙；

(4)安装MySQL数据库；

(5)配置MySQL主服务器；

(6)配置MySQL从服务器；

(7)验证主从复制效果；

(1)MySQL主服务器建立时间同步。

[root@localhost ~]# yum -y安装ntp

//安装NTP

[root @ localhost ~]# vim/etc/NTP . conf

//写ntp服务的配置文件。

…………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………………

服务器127.127.1.0

//需要同步上层服务器(自身)

捏造127.127.1.0第8层

//同步上层服务器的层数(大小不能超过等于16)

[root@localhost ~]# systemctl重新启动ntpd

//重新启动ntp服务

(2)MySQL从服务器同步时间。

两台从属服务器都需要时间同步！

[root@localhost ~]# yum -y安装ntpdate

[root @ localhost ~]# NTP date 192 . 168 . 1 . 1

//安装ntpdate工具并指定用于同步的ntp服务器。

(3)关闭防火墙(或打开相应的端口)

因为实验环境的原因，防火墙在这里被强制关闭。关闭每台服务器上的防火墙！

[root@localhost ~]# systemctl停止防火墙d

//关闭防火墙

(4)安装MySQL数据库。

安装MySQL数据库请参考博文：编译安装MySQL数据库系统。

安装完成后，启动MySQL数据库。

[root@localhost ~]# systemctl启动mysqld

//启动MySQL数据库

[root @ localhost ~]# netstat-anpt | grep mysqld

TCP 6 0 0:3306:* list EN 3263/mysqld

//mysql数据库的监听端口是TCP/3306，请确认！

[root @ localhost ~]# MySQL admin-u root密码\\\'123456\\\ '

//设置MySQL数据库管理员“root”的密码

主服务器和从服务器都需要安装MySQL数据库，启动并设置密码！

(5)配置MySQL主服务器。

[root @ localhost ~]# vim/etc/my . cn f

//写MySQL数据库的配置文件。

server_id=11

//服务器ID号，自定义但不能和其他MySQL服务器冲突。

log_bin=主箱

//定义bin-log的文件名(默认保存在数据目录下)

日志从属更新=真

//从服务器打开自动备份

[root@localhost ~]# systemctl重启mysqld

//重启MySQL数据库

[root @ localhost ~]# MySQL-u root-p

输入密码：

//登录MySQL数据库。

*上的mysql授权复制从属服务器。*到\\\'myslave\\\'@\\\'192.168.1。%\\\ '由\\\'123456\\\ '标识；

查询正常，0行受影响(0.00秒)

//授权用户myslave，密码为123456，有权限复制192.168.1.0网段内所有MySQL服务器上的所有数据库和表。

mysql flush特权；

查询正常，0行受影响(0.00秒)

//刷新权限

mysql显示主状态；

//查看主服务器的状态。

- - - - -

- - - - -

| master-bin.000001 | 410 | | | |

- - - - -

集合中的1行(0.00秒)

//文件列显示日志名，位置列显示偏移量。

(6)配置MySQL从服务器

两个从服务器的配置几乎是一样的！

[root @ localhost ~]# vim/etc/my . cn f

//写MySQL数据库的配置文件。

server_id=22

//服务器ID号，自定义但不能和其他MySQL服务器冲突。

中继日志=中继日志箱

//定义中继日志文件的名称和路径(默认保存在数据目录下)。

relay-log-index=slave-relay-bin . index

//定义中继日志索引的文件名(记录了很多中继日志文件)

//默认情况下，中继日志使用host_name-relay-bin.nnnnnn格式的文件名，其中host_name是从属服务器的主机名，nnnnnn是序列号。

//创建一个具有连续序列号的连续中继日志文件，从000001开始。从服务器的索引文件中跟踪当前正在使用的中继日志。默认的中继日志索引文件名是host _ name-relay-bin.index。

//默认情况下，这些文件是在从服务器的数据目录下创建的。

[root@localhost ~]# systemctl重启mysqld

//重启MySQL数据库

[root @ localhost ~]# MySQL-u root-p

输入密码：

//登录MySQL数据库。

mysql将master改为master _ host=\ \ ' 192 . 168 . 1 . 1 \ \ \ 'master_user=\\\'myslave\\\ '

- master_password=\\\'123456\\\ 'master _ log _ file=\ \ \ ' master-bin . 000001 \ \ \ 'master _ log _ pos=410

查询正常，0行受影响，2个警告(0.02秒)

//指定主服务的IP地址、登录主服务器的用户名和密码、指定同步的日志和偏移量。

mysql启动从机；

查询正常，0行受影响(0.00秒)

//开始同步

mysql显示从状态\ \ \ \ G；

//检查从机状态，确保以下两项为“是”。

Slave_IO_Running: Yes //I/O线程启动。

Slave_SQL_Running: Yes //SQL线程启动。

在配置了两个从属服务器之后！可以测试一下！

(7)验证主从复制效果。

登录主服务器和从服务器上的MySQL数据库，检查数据库；

[root @ localhost ~]# MySQL-u root-p

输入密码：

mysql显示数据库；

|数据库|

|信息模式|

| mysql |

|性能模式|

测试|

一组4行(0.10秒)

在主服务器上新建一个数据库；

mysql创建数据库QQ；

查询正常，1行受影响(0.00秒)

分别检查主服务器和从服务器上的数据库。

mysql显示数据库；

|数据库|

|信息模式|

| mysql |

|性能模式|

| qq |

测试|

一组5行(0.00秒)

如果显示的数据库相同，则主从复制成功！

第四，构建MySQL读写分离

把mysql和读写分开的基础是MySQL主从复制，后面所有的操作都是基于MySQL主从复制。

用来构建MySQL将读写分开的软件是——阿米巴软件。简单介绍一下：

阿米巴项目的开源框架在2008年发布了一个针对MySQL软件的阿米巴。本软件专用于MySQL的分布式数据库的前端代理。主要作为应用层访问MySQL的SQL路由功能，具有负载均衡、高可用、SQL过滤、读写分离、与目标数据库相关的路由、多个数据库并发请求等功能。通过阿米巴可以实现多数据源的高可用、负载均衡、数据切片等功能。目前MOEBA已经在多家企业的生产线上使用，其版本可以在官网下载。

(1)在阿米巴服务器上安装Java环境。

注意：阿米巴软件千万不要安装在任何mysql服务器上！

因为阿米巴是基于jdk1.5开发的，所以官方推荐使用jdk1.5或1.6。不推荐高配版！查看Centos 7系统的默认Java环境：

[root@localhost ~]# java版本

openjdk版本1.8.0_102

OpenJDK运行时环境(内部版本1.8.0_102-b14)

OpenJDK 64位服务器虚拟机（内部版本25.102-b14，混合模式)

所以，还需要重新部署一下Java 语言(一种计算机语言，尤用于创建网站)语言（一种计算机语言，尤用于创建网站)环境，使用的软件包网盘链接：https://潘。百度一下。com/s/1 shbvliz 2 vdlb _ qw-b _ z4w

提取码：rs1x

操作如下：

[root @ localhost ~]# CP JDK-6u 14-Linux-x64。bin/usr/local

[root @ localhost ~]# chmod x/usr/local/JDK-6u 14-Linux-x64。箱子

[root@localhost ~]# cd /usr/local

[root@localhost local]# ./jdk-6u14-linux-x64.bin //根据提示输入是和回车键

[root @ localhost local]# mv JDK 1。6 .0 _ 14/JDK 1.6//对jdk的安装目录进行重命名

[root @ localhost ~]# vim/etc/profile//编写全局环境变量配置文件

…………………… //省略部分内容，填写以下内容

导出JAVA_HOME=/usr/local/jdk1.6

导出类路径=$ JAVA _ HOME/lib:$ JAVA _ HOME/JRE/lib

导出路径=$ JAVA _ HOME/lib:$ JAVA _ HOME/JRE/bin:$ PATH:$ HOME/bin

导出阿米巴_ HOME=/usr/local/阿米巴/

导出路径=$PATH:$AMOEBA_HOME/bin

[root @ localhost ~]# source/etc/profile

[root@localhost ~]# java版本

Java语言（一种计算机语言，尤用于创建网站）版本1.6.0_14

Java(TM) SE运行时环境（内部版本1.6.0_14-b08)

Java热点(TM) 64位服务器虚拟机（版本14.0-b16，混合模式)

//目前系统的Java 语言(一种计算机语言，尤用于创建网站)语言（一种计算机语言，尤用于创建网站)环境是1.6版本的，可以使用！

(2)安装并配置变形虫软件

变形虫软件的网盘链接：https://潘。百度一下。com/s/1m-gbbcw 4 sxtt 4 b 5 nvqngqq

提取码：w93m

操作如下：

[root @ localhost ~]# mkdir/usr/local/amoeba

[root @ localhost ~]# tar zxf amoeba-MySQL-binary-2 . 2 . 0 . tar . gz-C/usr/local/阿米巴/

[root @ localhost ~]# chmod-R 755/usr/local/amoeba/

[root @ localhost ~]#/usr/local/阿米巴/bin/阿米巴

变形虫开始|停止

//出现这些内容，则表示安装阿米巴安装成功

(3)配置变形虫读写分离，两个奴隶实现读负载均衡

主机、从机1、从机2中都要开放权限给变形虫访问，命令如下：

mysql grant all on * .*到\\\ '测试\\\'@\\\'192.168.1。%\\\ '由\\\'123.com\\\ '标识；

查询正常，0行受影响(0.00秒)

编辑amoeba.xml配置文件。此配置文件主要定义主从服务器地址池。

[root @ localhost ~]# vim/usr/local/amoeba/conf/amoeba。可扩展标记语言

…………………… //由于内容较多，编辑的每条内容都添加了实际的行号

30属性名称=userameba/属性

32属性名称=密码123456/属性

//指定客户机访问阿米巴服务器时的账号、密码！

115属性名=defaultPoolmaster/属性

116

117属性名=writePoolmaster/property

118属性名=readPoolslaves/property

//注意取消原本的注释信息

编辑dbServer.xml配置文件

[root @ localhost ~]# vim/usr/local/amoeba/conf/dbservers。可扩展标记语言

…………………… //由于内容较多，编辑的每条内容都添加了实际的行号

26属性名=用户测试/属性

28属性名称=password123.com/property

//指定阿米巴服务器访问关系型数据库数据库使用的账号密码

43 dbServer name=master parent=抽象服务器

44工厂配置

45 !- mysql ip -

46属性名=地址192 .168 .1 .1/属性

47/工厂配置

48/数据库服务器

50数据库服务器名称=从属服务器1父服务器=抽象服务器

51工厂配置

52 !- mysql ip -

53属性名=地址192 .168 .1 .2/属性

54/工厂配置

55/数据库服务器

56 dbServer name=slave 2 parent=抽象服务器

57工厂配置

58 !- mysql ip -

59属性名=地址192 .168 .1 .3/属性

60/工厂配置

61/数据库服务器

//slave2配置需要复制slave2的配置信息(行号可能与实际不符)。

63 dbServer name=slaves virtual=true

64 pool config class=com . meidusa . amoeba . server . multipleserverpool

65 !-负载平衡策略：1=循环调度，2=基于权重，3=HA -

66属性名=loadbalance1/property

68 !-用逗号分隔，例如：服务器1、服务器2、服务器1 -

69 property name=poolNamesslave1，slave2/property

70/池配置

71/数据库服务器

确认后，启动阿米巴软件，默认端口为TCP 8066。

[root @ localhost ~]#/usr/local/阿米巴/bin/阿米巴start

[root @ localhost ~]# netstat-anpt | grep 8066

tcp6 0 0 :8066 :*列表EN 3799/java

(5)测试

因为实验环境的原因，我们来测试一下读写分离的效果。在实际环境中，不要轻易测试！

在客户端安装mysql工具，通过代理访问mysql。该命令如下所示：

[root@localhost ~]# yum -y安装mariadb

//注意，Centos 7启动的时候，自己的数据库叫mariadb，不叫mysql。

[root@localhost ~]# mysql -u阿米巴-h 192.168.1.4 -P 8066 -p

输入密码：

MySQL [(无)]

//表示成功访问。

在MySQL主服务器上创建一个表，并同步到从服务器。

mysql用QQ；

数据库已更改

mysql创建表Q1(id int)；

查询正常，0行受影响(0.10秒)

分别在两台从属服务器上停止同步，并执行以下命令：

mysql stop slave

查询正常，0行受影响(0.01秒)

在主服务器上插入数据：

mysql插入到q1值(\ \ \ ' 1 \ \ \ ')；

查询正常，1行受影响(0.00秒)

两台从服务器同步表，分别手动插入一条数据。

第一从服务器

mysql用QQ；

数据库已更改

mysql插入到q1值(\ \ \ ' 2 \ \ \ ')；

查询正常，1行受影响(0.00秒)

第二从属服务器

mysql用QQ；

数据库已更改

mysql插入到q1值(\ \ \ ' 3 \ \ \ ')；

查询正常，1行受影响(0.00秒)

测试读取操作

客户端上的第一个查询结果：

MySQL [(无)]select * from QQ . Q1；

| id |

| 3 |

集合中的1行(0.00秒)

客户端上的第二个查询结果：

MySQL [(无)]select * from QQ . Q1；

| id |

| 2 |

集合中的1行(0.00秒)

永远都是这两个结果！

这样实现了读取数据的负载均衡，效果很明显！

测试写入操作

在客户端插入一段数据，但是在客户端找不到！

MySQL [qq]插入到q1值(\ \ \ ' 4 \ \ \ ')；

查询正常，1行受影响(0.00秒)

但是可以在主服务器上找到！

mysql select *来自Q1；

| id |

| 1 |

| 4 |

集合中的2行(0.00秒)

从这个验证来看，MySQL已经脱离了读写。目前都是写在主服务器上，避免了数据同步。所有的读操作都分配给从服务器，分担数据库的压力！

这是本文的结尾。谢谢你阅读它。36860 . 66868686861

更多关于云服务器，域名注册，虚拟主机的问题，请访问西部数码代理官网：www.chenqinet.cn。

相关推荐