[TOC]

0x00 利用PHP实现读写分离

描述:在做PHP读写分离前需要拿到运维部门给好的读写数据库的连接地址,提前定义好数据库的操作类程序,然后编写开发文档让所有的开发同时都统一调用这个类来执行SQL语句;

目前要实现mysql的主从读写分离,主要有以下几种方案:

  • 方法1:通过程序实现程序判断SQL语句(DQL-数据查询语言/DML-数据操作语言)比较复杂,如果添加从服务器要更改多台服务器的代码。
  • 方法2:自己开发接口实现通过自写类调用实现(传入参数的方法),这种方案门槛高,开发成本高,不是一般的小公司能承担得起。
  • 方法3:通过mysql-proxy来实现,由于mysql-proxy的主从读写分离是通过lua脚本来实现,目前lua的脚本的开发跟不上节奏,而写没有完美的现成的脚本,因此导致用于生产环境的话风险比较大,据网上很多人说mysql-proxy的性能不高。
  • 方法4:一些读写分离的软件比如amoeba

WeiyiGeek.执行流程

方法优缺点

  • 方法1:
    • 优点:开发人员无需自行区分是读库还是写库,程序根据SQL语句进行自动鉴别,从而区分连接;
    • 缺点:需要进行SQL语句的字符截取,影响效率;
  • 方法2:
    • 优点:效率高,无需截取多余的字符串进行判断;
    • 缺点:开发人员在开发的时候容易把读库当作写库来操作,由于传入类是true还是false;

方法1:伪代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
#首先定义读库和写库(连接数据库的账户密码IP这里不定义)
define('IDATABASE','INSERTDB');
define('SDATABASE','SELECTDB');


#类方法
/**
@ 作用:截取SQL语句的字符,从而判断进行读写分离
@ 参数:传入执行的SQL语句
**/
public function execute($sql)
{
$check_sql = strtolower(trim($sql)); //去掉空格键字符串转变为空格
if(substr($check_sql,0,6)=='select')
{
return $this->getAll($sql); //读库
}else{
return $this->exec($sql); //写库
}
}

方法2:伪代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
#类ConnectMysql
/**
@ 利用实例化类传入的参数进行判断是读库还是写库
@ __construct构造方法:传入flag判断false为读,true为写
**/
class ConnectMysql
{
private $flag = 'false';
function __construct($flag)
{
$this->flag=$flag;
if($this->flag == 'false')
{
$this->DBselect();
}else{
$this->DBwrite();
}
}
}


0x01 amoeba架构实现读写分离

1.简介

Amoeba[英 /ə’miːbə/]是一个以MySQL为底层数据存储,并对应用提供MySQL协议接口的proxy,Amoeba相当于一个SQL请求的路由器(进行转发请求),它集中地响应应用的请求,依据用户事先设置的规则,将SQL请求发送到特定的数据库上执行,并且需要结合使用MySQL的 Replication等机制来实现副本同步等功能,基于此可以实现负载均衡、读写分离、高可用性等需求,

WeiyiGeek.amoeba执行流程

WeiyiGeek.amoeba执行流程

Amoeba体系架构:

WeiyiGeek.amoeba架构图

WeiyiGeek.amoeba架构图

为什么要用Amoeba?
答:利用阿里巴巴的开源项目Amoeba来实现,具有负载均衡、高可用性、sql过滤、读写分离、可路由相关的query到目标数据库,并且安装配置非常简单

2.环境需求

安装环境:

  • CentOS Linux release 7.6.1810 (Core)
  • JDK : Java SE Development Kit 8u211
  • MySQL : 8.0.16

TIPS: #Amoeba框架是居于JDK1.5开发的,采用了JDK1.5的特性,所以还需要安装java环境,建议使用javaSE1.5以上的JDK版本

名称 系统IP 描述
MYSQL 192.168.1.100 单机多实例化3306/3307
Amoeba 192.168.1.101 Amoeba主机和phpMyadmin主机

环境安装
Step1. MySQL安装以及主从复制搭建,这里看前面的主从多实例配置文章即可;

WeiyiGeek.MYSQL8.0

WeiyiGeek.MYSQL8.0

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
#解压二进制包
xz -d mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
tar xf mysql-8.0.16-linux-glibc2.12-x86_64.tar

#建立mysql数据库用户
useradd mysq

#建立软连接
ln -s /opt/mysql8/bin/* /usr/local/bin/

#多实例目录
mkdir -vp /data/{{3307,3306}/{data,tmp,binlog,innodb_ts,innodb_log,undo},backup,scripts}
mkdir: 已创建目录 "/data"
mkdir: 已创建目录 "/data/3307"
mkdir: 已创建目录 "/data/3307/data"
mkdir: 已创建目录 "/data/3307/tmp"
mkdir: 已创建目录 "/data/3307/binlog"
mkdir: 已创建目录 "/data/3307/innodb_ts"
#非常重要
chown -R mysql:mysql /data
chown -R mysql:mysql /opt/mysql8

#8.x多实例 my.cnf配置
[client]
default-character-set=utf8 # 设置mysql客户端默认字符集
port = 3306
socket = /data/3306/mysql.sock

# The MySQL server
[mysqld]
port = 3306
mysqlx_port = 33060
user = mysql
server-id = 3306
socket = /data/3306/mysql.sock
mysqlx_socket=/data/3306/mysqlx.sock
pid-file = /data/3306/mysql.pid
basedir = /opt/mysql8/
datadir = /data/3306/data
tmpdir = /data/3306/tmp #非必须
log-bin = /data/3306/binlog/mysql-bin #从库建议关闭log-bin
log-error = /data/3306/mysqlerror.log
explicit_defaults_for_timestamp
character-set-server=utf8 #服务端默认字符集


#初始化实例与启动数据库:
mysqld --defaults-file=/data/3307/my.cnf --initialize --user=mysql --basedir=/opt/mysql8
mysqld --defaults-file=/data/3306/my.cnf --initialize --user=mysql --basedir=/opt/mysql8
mysqld_safe --defaults-file=/data/3306/my.cnf --user=mysql&
mysqld_safe --defaults-file=/data/3307/my.cnf --user=mysql&


#账户密码在mysqlerror.log
3306:[email protected]: W=!_hK2qjlFl
3307:[email protected]: l7OuDBq_2zQj

# 端口启动验证
netstat -tlnp | grep "mysql"
tcp6 0 0 :::3306 :::* LISTEN 10402/mysqld
tcp6 0 0 :::3307 :::* LISTEN 10102/mysqld
tcp6 0 0 :::33070 :::* LISTEN 10102/mysqld
tcp6 0 0 :::33060 :::* LISTEN 10402/mysqld


mysql> ALTER USER USER() IDENTIFIED BY '/[email protected]#888';
Query OK, 0 rows affected (0.20 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

##mysql 8.0 主从账户
CREATE USER 'rep'@'%' IDENTIFIED WITH mysql_native_password BY '[email protected]';
GRANT REPLICATION SLAVE ON *.* TO 'rep'@'%';

#获取主节点当前binary log文件名和位置(position)- 不再导入以前的库
MASTER> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 | 1659 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.09 sec)

#在从(Slave)节点上设置主节点参数 并启动主从(不用导入以前的库)
CHANGE MASTER TO MASTER_HOST='192.168.1.100',
MASTER_USER='rep',
MASTER_PASSWORD='[email protected]',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=1659;
#开启从库
start slave;
#主从开启成功
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.100
Master_User: rep
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 1659
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 322
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
WeiyiGeek.成功登陆

WeiyiGeek.成功登陆

Step2. 在Amoeba机器上安装JDK及配置环境:(CENTOS7安装jdk)
下载地址:https://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html

WeiyiGeek.JDK64位

WeiyiGeek.JDK64位

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#1.下载Linux x64	185.96 MB  	jdk-8u211-linux-x64.tar.gz 上传到opt目录并解压
$tar -zxf jdk-8u211-linux-x64.tar.gz
$ls
jdk1.8.0_211 jdk-8u211-linux-x64.tar.gz

#2.添加java的环境变量(非常重要)并刷新环境变量
$vi /etc/profile

#Java Env
export JAVA_HOME=/opt/jdk1.8.0_211
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin


#更新环境变量
$source /etc/profile

#3.查看JDK版本(如果不存在执行权限就添加)
java -version
java version "1.8.0_211"
Java(TM) SE Runtime Environment (build 1.8.0_211-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.211-b12, mixed mode)

Step3. 安装amoeba软件(已经停止开发了-2013年版本3.0.5)
下载地址:https://datapacket.dl.sourceforge.net/project/amoeba/Amoeba%20for%20mysql/2.2.x/amoeba-mysql-binary-2.2.0.tar.gz

1
2
3
4
5
6
7
8
9
$tar -zxf amoeba-mysql-binary-2.2.0.tar.gz && ll
-rw-r--r--. 1 root root 3161433 6月 2 01:00 amoeba-mysql-binary-2.2.0.tar.gz
drwxr-xr-x. 2 root root 63 6月 2 01:00 benchmark
drwxr-xr-x. 2 root root 131 2月 29 2012 bin
-rw-r--r--. 1 root root 3976 8月 29 2012 changelogs.txt
drwxr-xr-x. 2 root root 243 6月 2 01:00 conf
drwxr-xr-x. 3 root root 4096 6月 2 01:00 lib
-rw-r--r--. 1 root root 34520 8月 29 2012 LICENSE.txt
-rw-r--r--. 1 root root 2031 8月 29 2012 README.html


3.实际案例

Step0. 分别在主从库创建mysqlproxy用户

1
2
3
4
5
6
7
8
CREATE USER 'mysqlproxy'@'%' IDENTIFIED WITH mysql_native_password BY '[email protected]'; #注意加密方式,不加默认是 Authentication plugin 'caching_sha2_password

#主库(插入测试数据)
GRANT INSERT ON demo.* TO 'mysqlproxy'@'%' ;
mysql> insert into demo.user value (1,'weiyigekk'),(2,'k9s'),(3,'docker');

#从库(执行)
GRANT select ON demo.* TO 'mysqlproxy'@'%';

Step1. 修改配置文件 dbServer.xml 文件在 amoeba/conf/目录下

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
<!-- 数据库连接配置的公共部分 -->
<dbServer name="abstractServer" abstractive="true">
<factoryConfig class="com.meidusa.amoeba.mysql.net.MysqlServerConnectionFactory">
<property name="manager">${defaultManager}</property>
<property name="sendBufferSize">64</property>
<property name="receiveBufferSize">128</property>

<!-- mysql port 端口号 -->
<property name="port">3306</property>

<!-- mysql schema amoeba 访问主从数据库真实库-->
<property name="schema">demo</property>

<!-- mysql user 主从数据库分配给Amoeba访问数据的用户名 -->
<property name="user">mysqlproxy</property>

<!-- mysql password 主从数据库分配给Amoeba访问数据的密码-->
<property name="password">[email protected]</property>

</factoryConfig>
<poolConfig class="com.meidusa.amoeba.net.poolable.PoolableObjectPool">
<property name="maxActive">500</property>
<property name="maxIdle">500</property>
<property name="minIdle">10</property>
<property name="minEvictableIdleTimeMillis">600000</property>
<property name="timeBetweenEvictionRunsMillis">600000</property>
<property name="testOnBorrow">true</property>
<property name="testWhileIdle">true</property>
</poolConfig>
</dbServer>
<!-- Master 的独立部分,也就只有 IP 了这里 写了主机名 由于我是单机多实例所有填写一样的IP -->
<dbServer name="master" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.100</property>
</factoryConfig>
</dbServer>
<!-- Slave 的独立部分,也就只有 IP 了这里 写了主机名 ,如果有多个Slave服务器,可以配置多个dbServer -->
<dbServer name="slave" parent="abstractServer">
<factoryConfig>
<!-- mysql ip -->
<property name="ipAddress">192.168.1.100</property>
</factoryConfig>
</dbServer>

<!-- 数据库池,虚拟服务器,实现读取的负载均衡,如果有多个Slave,则<property name="poolNames">slave1,slave2</property>用逗号隔开 -->
<dbServer name="slaves" virtual="true">
<poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
<!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
<property name="loadbalance">1</property>

<!-- Separated by commas,such as: server1,server2,server1 -->
<property name="poolNames">slave</property>
</poolConfig>
</dbServer>

Step2. 修改amoeba.xml文件,设置读写分离

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
<proxy>

<!-- service class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba for Mysql" class="com.meidusa.amoeba.net.ServerableConnectionManager">
<!-- Amoeba 端口号 ,客户端client 链接amoeba端口号,不能和主从数据库 冲突-->
<property name="port">8066</property>

<!-- bind ipAddress -->
<property name="ipAddress">192.168.1.100</property>

<property name="manager">${clientConnectioneManager}</property>

<property name="connectionFactory">
<bean class="com.meidusa.amoeba.mysql.net.MysqlClientConnectionFactory">
<property name="sendBufferSize">128</property>
<property name="receiveBufferSize">64</property>
</bean>
</property>

<property name="authenticator">
<bean class="com.meidusa.amoeba.mysql.server.MysqlClientAuthenticator">
<!-- Amoeba 账号 ,客户端client 链接amoeba端 账号-->
<property name="user">root</property>
<!-- Amoeba 账号 ,客户端client 链接amoeba端 密码-->
<property name="password">root</property>
<property name="filter">
<bean class="com.meidusa.amoeba.server.IPAccessController">
<property name="ipFile">${amoeba.home}/conf/access_list.conf</property>
</bean>
</property>
</bean>
</property>

</service>

<!-- server class must implements com.meidusa.amoeba.service.Service -->
<service name="Amoeba Monitor Server" class="com.meidusa.amoeba.monitor.MonitorServer">
<!-- port -->
<!-- default value: random number
<property name="port">9066</property>
-->
<!-- bind ipAddress -->
<property name="ipAddress">192.168.1.100</property>
<property name="daemon">true</property>
<property name="manager">${clientConnectioneManager}</property>
<property name="connectionFactory">
<bean class="com.meidusa.amoeba.monitor.net.MonitorClientConnectionFactory"></bean>
</property>

</service>

<runtime class="com.meidusa.amoeba.mysql.context.MysqlRuntimeContext">
<!-- proxy server net IO Read thread size -->
<property name="readThreadPoolSize">20</property>

<!-- proxy server client process thread size -->
<property name="clientSideThreadPoolSize">30</property>

<!-- mysql server data packet process thread size -->
<property name="serverSideThreadPoolSize">30</property>

<!-- per connection cache prepared statement size -->
<property name="statementCacheSize">500</property>

<!-- query timeout( default: 60 second , TimeUnit:second) -->
<property name="queryTimeout">60</property>
</runtime>

</proxy>

<!--
Each ConnectionManager will start as thread
manager responsible for the Connection IO read , Death Detection
-->
<connectionManagerList>
<connectionManager name="clientConnectioneManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.ConnectionManager</property>
<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</connectionManager>
<connectionManager name="defaultManager" class="com.meidusa.amoeba.net.MultiConnectionManagerWrapper">
<property name="subManagerClassName">com.meidusa.amoeba.net.AuthingableConnectionManager</property>

<!--
default value is avaliable Processors
<property name="processors">5</property>
-->
</connectionManager>
</connectionManagerList>

<!-- default using file loader -->
<dbServerLoader class="com.meidusa.amoeba.context.DBServerConfigFileLoader">
<property name="configFile">${amoeba.home}/conf/dbServers.xml</property>
</dbServerLoader>

<queryRouter class="com.meidusa.amoeba.mysql.parser.MysqlQueryRouter">
<property name="ruleLoader">
<bean class="com.meidusa.amoeba.route.TableRuleFileLoader">
<property name="ruleFile">${amoeba.home}/conf/rule.xml</property>
<property name="functionFile">${amoeba.home}/conf/ruleFunctionMap.xml</property>
</bean>
</property>
<property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
<property name="LRUMapSize">1500</property>

<!-- 默认数据库,主数据库 -->
<property name="defaultPool">master</property>
<!-- 写数据库 / 读数据库,dbServer.xml 中配置的 虚拟数据库,数据库池 -->
<property name="writePool">master</property>
<property name="readPool">slaves</property>
<property name="needParse">true</property>
</queryRouter>

Step3. 启动amoeba启动失败了,原因 Amoeba 启动 指定的堆栈大小太小,指定至少228k;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
./amoeba
The stack size specified is too small, Specify at least 228k
Error: Could not create the Java Virtual Machine.
Error: A fatal exception has occurred. Program will exit.

#解决办法 :
打开bin/amoeba,DEFAULT_OPTS=”-server -Xms256m -Xmx256m -Xss128k”改成:DEFAULT_OPTS="-server -Xms512m -Xmx512m -Xmn100m -Xss1204k"

#再次启动
./amoeba start
log4j:WARN log4j config load completed from file:/opt/amoeba/conf/log4j.xml
2019-06-02 01:08:48,521 INFO context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/opt/amoeba/conf/access_list.conf
2019-06-02 01:08:49,517 INFO net.ServerableConnectionManager - Amoeba for Mysql listening on /192.168.1.101:8066.
2019-06-02 01:08:49,522 INFO net.ServerableConnectionManager - Amoeba Monitor Server listening on /192.168.1.101:39401.

Step 4.读写分离测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#在amoeba机器上执行 (注意amoeba客户端端口-在上面的配置文件里面)
mysql -h 192.168.1.101 -P8066 -uroot -proot
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1186796719
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 #注意观察这里不一样

mysql> insert into demo.user value(5,'zhangwei');
Query OK, 1 row affected (0.10 sec)

主从mysql> select * from user;
+----+-----------+
| id | name |
+----+-----------+
| 1 | weiyigekk |
| 2 | k9s |
| 3 | docker |
| 5 | zhangwei |
+----+-----------+
4 rows in set (0.01 sec)

#下面关闭slave
mysql> stop slave;
mysql> insert into demo.user value(4,'this is slave insert');
Query OK, 1 row affected (0.02 sec)

#采用amoeba进行读取
MySQL [(none)]> select * from demo.user;
+----+----------------------+
| id | name |
+----+----------------------+
| 1 | weiyigekk |
| 2 | k9s |
| 3 | docker |
| 5 | zhangwei |
| 4 | this is slave insert |
+----+----------------------+

WeiyiGeek.mysql

WeiyiGeek.mysql


0x02 mysql-proxy实现读写分离

1. 简介

前言:在实际的生产环境中由单台Mysql作为独立的数据库是完全不能满足实际需求的无论是在安全性,高可用性以及高并发等各个方面;
常常在大规模集群中通过主从复制(Master-Slave)的方式来同步数据,再通过读写分离(MySQL-Proxy)来提升数据库的并发负载能力,常使用这样的方案来进行部署与实施的。

MySQL-proxy 是通过网络利用MySQL的网络协议,并且提供一个或多个MySQL服务器与一个或多个MySQL客户端相互沟通的程序,又因为MySQL-Proxy使用MySQL网络协议,所以它兼容任何MySQL客户端并且无需修改,其功能:

  • MySQL-Proxy 可以在查询队列发送到服务器之前插入一些查询请求
  • MySQL-Proxy 可以在服务器应答中将对应的应答删除
  • 管理员可以对每个查询进行跟踪并获取报告,如监控其执行时间或其他调试信息,并分别记录,同时还能降正确应答返还给客户端:

MySQL-Proxy的读写分离主要是通过lua脚本实现的因此需要安装lua(后面会进行相应安装的介绍),并且从设定上将lua分为两类:

  • 一类负责管理模块的控制,对应参数admin-lua-script
  • 另一类负责代理模块控制,对应参数proxy-lua-script
    两类脚本的编码规则完全相同,只是对应功能有差异,管理模块侧重与代理服务器相关状态的控制,代理模块则侧重于客户端的CRUD操作;

TIPS: 貌似只有alpha版本,可能不稳定不建议在实际环境中使用;


2. 安装环境

环境准备

WeiyiGeek.

场景描述:

  • 192.168.1.100:3306/3307:数据库Master主服务器/Slave从服务器
  • 192.168.1.101:4040/4041:MySQL-Proxy调度服务器(客户端/管理端)

WeiyiGeek.场景接收

环境安装:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#1. MySQL-Proxy上安装所需软件包
yum install -y gcc* gcc-c++* autoconf* automake* zlib* libxml* ncurses-devel* libmcrypt* libtool* flex* pkgconfig* libevent* glib* readline-devel*


#2. 编译安装LUA
#从http://www.lua.org/download.html下载源码包并安装
wget http://www.lua.org/ftp/lua-5.3.5.tar.gz -O /opt/
tar -zxf lua-5.3.5.tar.gz && cd lua-5.3.5
make linux && make install #注意发生错误的先执行make clean 编译过程中遗留的文件
# make[1]: 进入目录“/opt/lua-5.3.5/src”
# make all SYSCFLAGS="-DLUA_USE_LINUX" SYSLIBS="-Wl,-E -ldl -lreadline"
# make[2]: 进入目录“/opt/lua-5.3.5/src”


#3. 下载mysql-proxy的二进制包解压并复制
wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
tar -zxf mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit.tar.gz
mv mysql-proxy-0.8.5-linux-glibc2.3-x86-64bit/ mysql-proxy/
cd mysql-proxy
mkdir lua logs #创建脚本存放目录/与日志文件
cp share/doc/mysql-proxy/rw-splitting.lua ./lua/
cp share/doc/mysql-proxy/admin-sql.lua ./lua/



Step 3. 配置修改
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#1.创建配置文件
vi /etc/mysql-proxy.cnf #创建配置文件
[mysql-proxy]
user=root
#运行mysql-proxy用户
admin-username=admin
#主从mysql共有的用户
admin-password=admin
#用户的密码
proxy-address=192.168.1.101:4040
#mysql-proxy运行ip和端口,不加端口,默认4040 ip:port
proxy-read-only-backend-addresses=192.168.1.100:3307
#指定后端从slave读取数据 ip:port (简写 -r)
proxy-backend-addresses=192.168.1.100:3306
#指定后端主master写入数据 (简写 -b)
proxy-lua-script=/opt/mysql-proxy/lua/rw-splitting.lua
#指定读写分离配置文件位置
admin-lua-script=/opt/mysql-proxy/lua/admin-sql.lua
#指定管理脚本
log-file=/opt/mysql-proxy/logs/mysql-proxy.log
#日志位置
log-level=info
#定义log日志级别,由高到低分别有(error|warning|info|message|debug)
pid-file=/opt/mysql-proxy/mysql-proxy.pid

#2.修改读写分离脚本//修改默认连接,进行快速测试,不修改的话要达到连接数为4时才启用读写分离
$vi /opt/mysql-proxy/lua/rw-splitting.lua
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1, ##默认超过4个连接数时,才开始读写分离改为1
max_idle_connections = 1, #默认为8
is_debug = false
}
end

#3.手动启用并且验证是否启用
chmod 600 /etc/mysql-proxy.cnf
/opt/mysql-proxy/bin/mysql-proxy --defaults-file=/etc/mysql-proxy.cnf
#支持的选项
# --daemon \ //定义以守护进程模式启动
# --keepalive \ //使进程在异常关闭后能够自动恢复
# --pid-file=$PROXY_PID \ //定义mysql-proxy PID文件路径
# --user=mysql \ //以mysql用户身份启动服务
# --log-level=warning \ //定义log日志级别,由高到低分别有(error|warning|info|message|debug)
# --log-file=/opt/mysql-proxy/log/mysql-proxy.log //定义log日志文件路径

netstat -tupln | grep 4040 #已经启动
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 192.168.1.101:4040 0.0.0.0:* LISTEN 10767/mysql-proxy


killall -9 mysql-proxy #关闭mysql-proxy使用



4.或者创建mysql-proxy服务管理脚本

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
$vi /etc/init.d/mysql-proxy
#!/bin/sh
# mysql-proxy This script starts and stops the mysql-proxy daemon
# chkconfig: - 78 30
# processname: mysql-proxy
# description: mysql-proxy is a proxy daemon to mysql
# Source function library.
. /etc/rc.d/init.d/functions

#定义mysql-proxy服务二进制文件路径
PROXY_PATH=/opt/mysql-proxy/bin
prog="mysql-proxy"

# Source networking configuration.
. /etc/sysconfig/network
# Check that networking is up.
#[ ${NETWORKING} == "no" ] && exit 0

#设置默认mysql-proxy选项(也可以采用配置文件的形式 -b为mater -r 指定slave)
PROXY_OPTIONS="--log-level=info \
--plugins=proxy -b 192.168.1.100:3306 -r 192.168.1.100:3307 \
--proxy-lua-script=/opt/mysql-proxy/lua/rw-splitting.lua \
--plugins=admin \
--admin-username=admin \
--admin-password=admin \
--admin-lua-script=/opt/mysql-proxy/lib/mysql-proxy/lua/admin.lua"

#pid文件路径
PROXY_PID=/opt/mysql-proxy/mysql-proxy.pid

# Source mysql-proxy configuration.
if [ -f /etc/sysconfig/mysql-proxy ]; then
. /etc/sysconfig/mysql-proxy
fi

PATH=$PATH:/usr/bin:/usr/local/bin:$PROXY_PATH
# By default it's all good
RETVAL=0

# See how we were called.
case "$1" in
start)
# Start daemon.
echo -n $"Starting $prog: "
$NICELEVEL $PROXY_PATH/mysql-proxy $PROXY_OPTIONS --daemon --pid-file=$PROXY_PID --user=mysql
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
touch /var/lock/subsys/mysql-proxy
fi
;;

stop)
# Stop daemons.
echo -n $"Stopping $prog: "
killproc $prog
RETVAL=$?
echo
if [ $RETVAL = 0 ]; then
rm -f /var/lock/subsys/mysql-proxy
rm -f $PROXY_PID

fi
;;
restart)
$0 stop
sleep 3
$0 start
;;

condrestart)
[ -e /var/lock/subsys/mysql-proxy ] && $0 restart
;;

status)
status mysql-proxy
RETVAL=$?
;;
*)

echo "Usage: $0 {start|stop|restart|status|condrestart}"
RETVAL=1
;;
esac
exit $RETVAL


3. 操作实例

mysql-proxy读写分离的流程步骤:
当在mysql-proxy插入数据时写入到了master上,查询数据是从slave上查看插入主库数据,停止主从后当在slave上插入数据,在mysql-proxy上可以看到,则说明读是从slave上,写是在master上。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
#1.启动nysql-proxy和赋值权限
chmod 755 /etc/init.d/mysql-proxy
chmod 600 /etc/mysql-proxy.cnf
/etc/init.d/mysql-proxy start
/etc/init.d/mysql-proxy status
# ● mysql-proxy.service - SYSV: mysql-proxy is a proxy daemon to mysql
# Loaded: loaded (/etc/rc.d/init.d/mysql-proxy; bad; vendor preset: disabled)
# Active: active (running) since 日 2019-06-02 21:29:00 CST; 2s ago

netstat -tlnp
# Active Internet connections (only servers)
# Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
# tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 12741/mysql-proxy
# tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 12741/mysql-proxy


#2.登录主库建立一个test用户从数据库中暂时关闭主从复制的功能
% | test | mysql_native_password | #注意认证插件
mysql> grant select on *.* to 'test'@'%'; #从库在停止主从前执行
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql>flush privileges;
Query OK, 0 rows affected (0.01 sec)


#3. 登录mysql-porxy管理段主从查看状态(与/opt/mysql-proxy/lua/rw-splitting.lua 配置文件有关)
MySQL [(none)]> SELECT * FROM help;
+------------------------+------------------------------------+
| command | description |
+------------------------+------------------------------------+
| SELECT * FROM help | shows this help |
| SELECT * FROM backends | lists the backends and their state |
+------------------------+------------------------------------+
2 rows in set (0.00 sec)

MySQL [(none)]> SELECT * FROM backends; #有可能需要登录有多个终端才能触发从库up
+-------------+--------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+--------------------+-------+------+------+-------------------+
| 1 | 192.168.1.100:3306 | up | rw | NULL | 1 |
| 2 | 192.168.1.100:3307 | up | ro | NULL | 1 |
+-------------+--------------------+-------+------+------+-------------------+
# up:表示读写分离生效
# unKnown:还没生效

#4.连接连接MySQL-Proxy并且插入数据到主库(注意这里是数据库的账号密码)
$mysql -h 192.168.1.101 -P 4040 -utest [email protected]#888
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.16 MySQL Community Server - GPL

$MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| demo |
| information_schema |
| mysql |
| performance_schema |
| phpmyadmin |
| sys |
+--------------------+
6 rows in set (0.12 sec)

MySQL [(none)]> use demo
MySQL [demo]> insert into user value (10,'mysql-proxy-insert');
Query OK, 1 row affected (0.12 sec)
WeiyiGeek.mysql-proxy-write

WeiyiGeek.mysql-proxy-write

1
2
3
#4.登录主库查看从MySQL-proxy插入到的数据,再登录从库插入一条数据,然后在MySQL-proxy中查看
mysql> insert into user value (11,'SLAVE INSERT - MYSQL-PROXY-READ');
Query OK, 1 row affected (0.09 sec)
WeiyiGeek.mysql-proxy-read

WeiyiGeek.mysql-proxy-read


4. 入坑

问题1:编译lua时候出现 lua.c:82:31: 致命错误:readline/readline.h:没有那个文件或目录

1
2
3
#include <readline/readline.h>
解决:安装
yum install libtermcap-devel ncurses-devel libevent-devel readline-devel



问题2:登录mysql-proxy管理段查看到从库状态为unkown?
原因:由于没有达到读写分离连接数限制;
解决方法:多登录几个mysql-proxy终端进行查询和插入即可将状态转变成为up;