Mysql读写分离

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介:

mysql 读写分离


实验背景:


 1.配置2台MySQL服务器+1台代理服务器,实现MySQL代理的读写分离。


 2.用户只需要访问MySQL代理服务器,实际的SQL查询、写入操作交给后台的2台MySQL服务器来完成。

 3.其中Master服务器允许SQL查询、写入,Slave服务器只允许SQL查询。


实验方案;


 使用4台RHEL 6.4虚拟机,其中192.168.4.10、192.168.4.20分别作为


MySQL主、从服务器,是整个服务的后端;另一台192.168.4.100作为MySQL代理服务器,

是直接面向客户的服务前端;客户机192.168.4.120用作访问测试。


对比两种方式的读写分离效果——


?MySQL主从复制:客户机访问Master服务器来写数据库,客户机访问Slave服务器来读数据库。这种情况下,需要客户端自行区分向何处写、从何处读。


?MySQL主从复制+代理:客户机访问Proxy服务器,读、写请求交给Proxy识别,如

果是写数据库操作则交给Master,如果是读数据库操作则交给Slave处理,具体由分配策略


控制。这种情况下,无需客户端区分读、写目标,而是由Proxy服务器代劳了,从而降低了

客户端程序的复杂度。


其中MySQL主、从复制结构的搭建参考上篇的Mysql主从同步,具体过程这里不再赘述。

若希望测试方便,可去除同步库的限制,由Slave自动复制Master上的所有更新:



实验实现:



1.部署mysql-proxy代理服务器


1)安装MySQL官方提供的mysql-proxy软件包


MySQL官方提供了mysql-proxy的免安装版本,解压后即可使用。由于msyql-proxy使用了

LUA脚本语言,因此需要提前安装好lua软件包(RHEL6镜像内已含)以确保其正常使用:


# yum -y install lua //安装lua脚本语言


然后部署mysql-proxy软件包:


[root@pxysvr pub]# tar zxf mysql-proxy-0.8.3-linux-rhel5-x86-64bit.tar.gz

//解压

[root@pxysvr pub]# mv mysql-proxy-0.8.3-linux-rhel5-x86-64bit /usr/local/mysql-proxy



 切换到部署后的目录,可确认相关目录、可执行程序、脚本。一般情况下,使用bin目录下的mysql-proxy脚本来启动代理服务:



[root@pxysvr pub]# cd /usr/local/mysql-proxy/

[root@pxysvr mysql-proxy]# ls

.bin include lib libexec licenses share

[root@pxysvr mysql-proxy]# ls bin/ libexec/

bin/:                                                 //服务脚本目录

mysql-binlog-dump mysql-myisam-dump mysql-proxy

libexec/:                                             //可执行程序目录

mysql-binlog-dump mysql-myisam-dump mysql-proxy



2)准备读写分离的LUA策略脚本


直接复制mysql-proxy提供的样例策略即可:


[root@pxysvr mysql-proxy]# cp share/doc/mysql-proxy/rw-splitting.lua ./


[root@pxysvr mysql-proxy]# file rw-splitting.lua         //查看脚本类型


rw-splitting.lua: ASCII Pascal program text


3)启动mysql-proxy代理服务


主要命令选项:


?-P:指定代理监听的IP地址、端口

?-r:指定读服务器的IP地址、端口

?-b:指定写服务器的IP地址、端口

?-s:指定lua脚本文件

?--keepalive:如果服务进程崩溃,尝试重启此进程



以监听本机的3306端口为例,分别指定读、写服务器,相关操作如下:


[root@pxysvr ~]# cd /usr/local/mysql-proxy/


[root@pxysvr mysql-proxy]# bin/mysql-proxy -P 192.168.4.100:3306 \

-b 192.168.4.10:3306 \

-r 192.168.4.20:3306 \

-s rw-splitting.lua &



启动后可确认监听状态:


[root@pxysvr mysql-proxy]# netstat -anpt | grep mysql


tcp 0 0 192.168.4.100:3306 0.0.0.0:* LISTEN 3177/mysql-proxy


这样一来,对于客户端来说,这台代理主机192.168.4.100就相当于一台可读可写的MySQL数据库服务器了,尽管其自身并未运行MySQL服务程序。


4)启动mysql-proxy代理服务


 为了在每次开机后能够自动运行mysql-proxy,可以将相关操作写到/etc/rc.local配置文件内:

[root@pxysvr ~]# vim /etc/rc.local

.. ..

/usr/local/mysql-proxy/bin/mysql-proxy -P 192.168.4.100:3306 \

-b 192.168.4.10:3306 \

-r 192.168.4.20:3306 \

-s rw-splitting.lua &



2.测试通过mysql-proxy的读写分离


相关测试操作可参考以下过程。


1)在MySQL Master服务器上设置用户授权


以root用户为例,允许其从192.168.4.0/24网段的客户机远程访问。首先登入到Master服务器添加下列授权:


mysql> GRANT all ON *.* TO root@'192.168.4.%' IDENTIFIED BY '1234567';

Query OK, 0 rows affected (0.00 sec)


因为此前已配置mysql库的主从同步,SLAVE上的root授权会自动更新:


2)从客户机192.168.4.120访问MySQL数据库


注意连接的是mysql-proxy服务器,而并不是Master或Slave:


[root@pc120 ~]# mysql -u root -p -h 192.168.4.100

Enter

password:                                     //验证口令

mysql>                                             //可成功登入

测试数据库写入操作:

mysql> CREATE DATABASE proxydb;                         //新建库

Query OK, 1 row affected (0.00 sec)

mysql> USE proxydb;                                     //切换到新建的库

Database changed

mysql> CREATE TABLE proxytb(

-> id int(4),host varchar(48)

-> );                                                 //新建表

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO proxytb VALUES(1,'dbsvr1'),         //插入2条表记录

-> (2,'dbsbr2');

Query OK, 2 rows affected (0.01 sec)

Records: 2 Duplicates: 0 Warnings: 0

测试数据库读取操作:

mysql> SELECT * FROM proxytb;

+------+--------+

| id | host |

+------+--------+

| 1 | dbsvr1 |

| 2 | dbsbr2 |

+------+--------+

2 rows in set (0.01 sec)


3)在Master和Slave上确认客户端新建的库、表

切换到新建的proxydb库:

mysql> USE proxydb;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

查看包括哪些表:

mysql> SHOW TABLES;

+-------------------+

| Tables_in_proxydb |

+-------------------+

| proxytb |

+-------------------+

1 row in set (0.00 sec)

查询表记录:

mysql> SELECT * FROM proxytb;

+------+--------+

| id | host |

+------+--------+

| 1 | dbsvr1 |

| 2 | dbsbr2 |

+------+--------+

2 rows in set (0.00 sec)

4)观察MySQL代理访问的网络连接

当并发的客户端连接较多时,可同时观察Master或Slave上的连接情况。

比如,在Master上可看到来自Slave和Proxy代理的网络连接:

[root@dbsvr1 ~]# netstat -anpt | grep mysql


2.4 扩展

1.MySQL代理与读(或写)服务器的集成

在许多企业应用中,MySQL数据库基本上都是读多写少,

因此可以将mysql-proxy与Master或Slave安装到一起,从而减少一台服务器,节省成本。比如:

?Master+Proxy:192.168.4.10/24

?Slave:192.168.4.20/24

这种情况下,要避免mysql-proxy与MySQL服务程序的端口冲突,必要时可更改相关程序的监听地址、端口。

若要修改MySQL服务程序的监听地址、端口,可采用以下选项:

?--bind-address=:用来限定监听地址,比如可设为127.0.0.1

?--port=:用来改端口号,比如可改为33060

当然,最简单的方法还是直接将mysql-proxy改用其他端口。比如,在Master上部署mysql-proxy,启用时可监听192.168.4.10的3333端口:

[root@pxysvr ~]# cd /usr/local/mysql-proxy/

[root@pxysvr mysql-proxy]# bin/mysql-proxy -P 192.168.4.10:3333 \

-b 192.168.4.10:3306 \

-r 192.168.4.20:3306 \

-s rw-splitting.lua &

这样,客户端只要访问192.168.4.10的3333端口,就可以读写数据库了。当服务器采用非标准的MySQL端口时,需要为mysql工具添加 -P 选项来指定端口号:


# mysql -u root -p -h 192.168.4.100 -P 3333




实验总结:


1、由于msyql-proxy使用了,LUA脚本语言,因此需要提前安装好lua软件包(RHEL6镜像内已含)以确保其正常使用


2、mysql-proxy代理服务


主要命令选项:


?-P:指定代理监听的IP地址、端口

?-r:指定读服务器的IP地址、端口

?-b:指定写服务器的IP地址、端口

?-s:指定lua脚本文件

?--keepalive:如果服务进程崩溃,尝试重启此进程





      本文转自Jx战壕  51CTO博客,原文链接:http://blog.51cto.com/xujpxm/1386302,如需转载请自行联系原作者



相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
1月前
|
存储 关系型数据库 MySQL
MySQL 读写分离原理
MySQL 读写分离原理
70 0
MySQL 读写分离原理
|
6月前
|
SQL 关系型数据库 MySQL
小白带你学习linux的mysql服务(主从mysql服务和读写分离三十一)
小白带你学习linux的mysql服务(主从mysql服务和读写分离三十一)
100 0
|
1月前
|
关系型数据库 MySQL Java
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
MySQL的主从复制 && SpringBoot整合Sharding-JDBC解决读写分离
45 0
|
9月前
|
SQL 存储 关系型数据库
MySQL主从复制、读写分离
MySQL主从复制、读写分离
144 0
|
1月前
|
SQL 关系型数据库 MySQL
Mycat【Mycat部署安装(核心配置及目录结构、安装以及管理命令详解)Mycat高级特性(读写分离概述、搭建读写分离、MySQL双主双从原理)】(三)-全面详解(学习总结---从入门到深化)
Mycat【Mycat部署安装(核心配置及目录结构、安装以及管理命令详解)Mycat高级特性(读写分离概述、搭建读写分离、MySQL双主双从原理)】(三)-全面详解(学习总结---从入门到深化)
109 0
|
8月前
|
关系型数据库 MySQL 中间件
企业实战(10)基于Maxscale中间件实现Mysql读写分离实战
企业实战(10)基于Maxscale中间件实现Mysql读写分离实战
|
8月前
|
存储 SQL 关系型数据库
数据库魔法师:使用ShardingSphere实现MySQL读写分离与分片指南跟着爆叔的节奏稳了!
数据库魔法师:使用ShardingSphere实现MySQL读写分离与分片指南跟着爆叔的节奏稳了!
77 0
|
17天前
|
SQL 关系型数据库 MySQL
mysql 主从复制与读写分离
mysql 主从复制与读写分离
|
1月前
|
负载均衡 关系型数据库 MySQL
MySQL读写分离技术深度解析
在高并发、大数据量的互联网应用环境中,数据库作为数据存储的核心组件,其性能直接影响着整个系统的运行效率。MySQL作为最常用的开源关系型数据库之一,虽然功能强大,但在处理大量并发读写请求时,单点服务器的性能瓶颈逐渐显现。为了解决这一问题,MySQL读写分离技术应运而生,成为提升数据库性能、实现负载均衡的有效手段。
|
1月前
|
Java 关系型数据库 MySQL
②⑩② 【读写分离】Sharding - JDBC 实现 MySQL读写分离[SpringBoot框架]
②⑩② 【读写分离】Sharding - JDBC 实现 MySQL读写分离[SpringBoot框架]
48 0