MYSQL数据库读写分离实例

2019-06-01
Database
DBA
运维

MYSQL数据库读写分离实例

2019-06-01

[TOC]

0x00 利用PHP实现读写分离

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

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

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

weiyigeek.top-执行流程

方法优缺点：

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

方法1：伪代码

点击阅读完整原文

[TOC]

0x00 利用PHP实现读写分离

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

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

方法优缺点：

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

方法1：伪代码

#首先定义读库和写库（连接数据库的账户密码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：伪代码

#类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等机制来实现副本同步等功能,基于此可以实现负载均衡、读写分离、高可用性等需求，

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安装以及主从复制搭建,这里看前面的主从多实例配置文章即可;

#解压二进制包
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：root@localhost: W=!_hK2qjlFl
3307：root@localhost: 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 '/weiye!@#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 'System123@'; 
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='System123@',
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

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

#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

$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用户

CREATE USER 'mysqlproxy'@'%' IDENTIFIED WITH mysql_native_password BY 'System123@'; #注意加密方式,不加默认是 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/目录下

<!-- 数据库连接配置的公共部分 -->
       <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">System123@</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文件，设置读写分离

<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；

./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.读写分离测试

#在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 |
+----+----------------------+

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. 安装环境

环境准备：

系统：
lua版本: http://www.lua.org/ftp/lua-5.3.5.tar.gz
mysql-proxy版本：0.8.5

场景描述：

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

环境安装：

#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.创建配置文件
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服务管理脚本

$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.启动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 -pweiye!@#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)

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)

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;

欢迎各位志同道合的朋友一起学习交流，如文章有误请在下方留下您宝贵的经验知识，个人邮箱地址【master#weiyigeek.top】或者个人公众号【WeiyiGeek】联系我。

更多文章来源于【WeiyiGeek Blog - 为了能到远方，脚下的每一步都不能少】, 个人首页地址( https://weiyigeek.top )

专栏书写不易，如果您觉得这个专栏还不错的，请给这篇专栏 【点个赞、投个币、收个藏、关个注、转个发、赞个助】，这将对我的肯定，我将持续整理发布更多优质原创文章！。

最后更新时间：2023-06-06 17:20:21
文章原始路径：_posts/数据存储/Database-运维/MySQL/运维实战/MYSQL数据库读写分离实例.md
转载注明出处，原文地址：https://blog.weiyigeek.top/2019/6-1-88.html
本站文章内容遵循知识共享署名 - 非商业性 - 相同方式共享 4.0 国际协议

WeiyiGeeker