二十五、Mysql-读写分离

1、介绍

MYSQL读写分离的原理其实就是让Master数据库处理事务性增、删除、修改、更新操作（CREATE、 INSERT、UPDATE、DELETE），而让Slave数据库处理SELECT操作，MYSQL读写分离前提是基于 MYSQL主从复制，这样可以保证在Master上修改数据，Slave同步之后，WEB应用可以读取到Slave端的数据。
实现MYSQL读写分离可以基于第三方插件，也可以通过开发修改代码实现，具体实现的读写分离的常见 方式有如下四种：

Amoeba读写分离
MySQL-Proxy读写分离
Mycat读写分离
基于程序读写分离(效率很高，实施难度大，开发改代码)

Amoeba 是阿里08年开源的以MySQL为底层数据存储，并对WEB、APP应用提供MySQL协议接口的 proxy。它集中地响应WEB应用的请求，依据用户事先设置的规则，将SQL请求发送到特定的数据库上执行，基于此可以实现负载均衡、读写分离、高可用性等需求。
Mysql-Proxy 是MySQL官方提供的mysql中间件服务，支持无数客户端连接，同时后端可连接若干台 Mysql-Server服务器，MYSQL-Proxy自身基于MySQL协议，连接MYSQL-Proxy的客户端无需修改任何设置， 跟正常连接MYSQL Server没有区别，无需修改程序代码。
Mycat 是基于阿里12年开源的cobar开发的一个数据库中间件，在架构体系中是位于数据库和应用层之 间的一个组件，并且对于应用层是透明的，它可实现读写分离，分库分表。

2、mysql-proxy实现读写分离

2.1 准备三台机器

proxy:  10.0.0.136
master: 10.0.0.138
slave:  10.0.0.137

2.2 安装配置proxy

# 下载mysql-proxy： 
[root@localhost src]#  wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linuxel6-x86-64bit.tar.gz 

#解压
[root@localhost src]#  tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz 
[root@localhost src]#  mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy 

# 配置环境变量： 
[root@localhost src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh 
[root@localhost src]# . /etc/profile.d/mysql-proxy.sh 

# 启动MYSQL-Proxy中间件: 
[root@localhost src]# useradd  -r  mysql-proxy 
[root@localhost src]#  mysql-proxy --daemon \
--log-level=debug \
--user=mysql-proxy \
--keepalive \
--log-file=/var/log/mysql-proxy.log \
--plugins="proxy" \
--proxy-backend-addresses="10.0.0.138:3306" \
--proxy-read-only-backend-addresses="10.0.0.137:3306"  \
--proxy-lua-script="/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua" \
--plugins=admin \
--admin-username="admin" \
--admin-password="admin" \
--admin-lua-script="/usr/local/mysql-proxy/lib/mysql-proxy/lua/admin.lua" 

# 查看端口/日志，看到4040和4041端口即表示正常启动
[root@localhost src]#   netstat -ntlp |grep 40 
tcp        0      0 0.0.0.0:4040            0.0.0.0:*               LISTEN      1348/mysql-proxy     
tcp        0      0 0.0.0.0:4041            0.0.0.0:*               LISTEN      1348/mysql-proxy

参数解析：

--help-all                           ：获取全部帮助信息； 
--proxy-address=host:port            ：代理服务监听的地址和端口，默认为4040； 
--admin-address=host:port            ：管理模块监听的地址和端口，默认为4041； 
--proxy-backend-addresses=host:port   ：后端mysql服务器的地址和端口； 
--proxy-read-only-backend-addresses=host:port ：后端只读mysql服务器的地址和端口； 
--proxy-lua-script=file_name          ：完成mysql代理功能的Lua脚本； 
--daemon                         ：以守护进程模式启动mysql-proxy； 
--keepalive                          ：在mysql-proxy崩溃时尝试重启之； 
--log-file=/path/to/log_file_name        ：日志文件名称； 
--log-level=level                    ：日志级别； 
--log-use-syslog                     ：基于syslog记录日志； 
--plugins=plugin                     ：在mysql-proxy启动时加载的插件； 
--user=user_name                 ：运行mysql-proxy进程的用户； 
--defaults-file=/path/to/conf_file_name ：默认使用的配置文件路径，其配置段使用[mysqlproxy]标识； 
--proxy-skip-profiling               ：禁用profile； 
--pid-file=/path/to/pid_file_name        ：进程文件名；

2.3 分别启动master/slave

syetemctl start mariadb

2.4 代理服务器查看读写分离状态

基于4041端口Mysql-Proxy查看读写分离状态，登录4041管理端口 ：

[root@localhost src]# mysql -h10.0.0.136 -uadmin -padmin -P4041
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.99-agent-admin

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]>
# 这时可以看到后端数据库信息，只是状态为unknown,表示还没有客户端连接，可以通过4040代理端口通过查询数据等操作激活。 
MySQL [(none)]> select * from backends;
+-------------+-----------------+---------+------+------+-------------------+
| backend_ndx | address         | state   | type | uuid | connected_clients |
+-------------+-----------------+---------+------+------+-------------------+
|           1 | 10.0.0.138:3306 | unknown | rw   | NULL |                 0 |
|           2 | 10.0.0.137:3306 | unknown | ro   | NULL |                 0 |
+-------------+-----------------+---------+------+------+-------------------+

2.5 master授权proxy
启动master后进行登录，执行授权语句

MariaDB [(none)]> grant all on *.* to "mysql-proxy"@"10.0.0.136" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

2.6 通过代理创建数据库
可再开启一个shell通过4040代理端口进行连接。

[root@localhost src]#  mysql -h10.0.0.136 -umysql-proxy -p123456 -P4040；

创建数据库，并查询。创建会走master，于是可以激活master状态，查询会走slave，于是可以激活slave。lua脚本读写分离默认激活次数为4次连接，所以需要多查询几次才可进行slave激活，当然也可以修改lua激活次数（可以先进行创建不查询，观察状态变化）。

MariaDB [(none)]> create database teachers charset utf8;
Query OK, 1 row affected (0.01 sec)

MariaDB [(none)]> use teachers;
Database changed
MariaDB [teachers]> create table t1(
    -> id int,
    -> name varchar(10)
    -> );
Query OK, 0 rows affected (0.01 sec)
MariaDB [teachers]> insert into t1 set  id=1, name="xiaohong";
Query OK, 1 row affected (0.00 sec)
MariaDB [teachers]> select * from t1;

通过4041管理端口再次查看：

MySQL [(none)]> select * from backends;
+-------------+-----------------+-------+------+------+-------------------+
| backend_ndx | address         | state | type | uuid | connected_clients |
+-------------+-----------------+-------+------+------+-------------------+
|           1 | 10.0.0.138:3306 | up    | rw   | NULL |                 0 |
|           2 | 10.0.0.137:3306 | up    | ro   | NULL |                 0 |
+-------------+-----------------+-------+------+------+-------------------+

看到state由unknown变成up，表示激活已经成功，可以通过分别在master、slave、proxy端进行数据创建和查询的验证了。
至此，基于mysql-proxy的读写分离配置成功！

3、基于mycat实现读写分离
Mycat基于阿里开源的Cobar产品而研发 , 一个彻底开源的，面向企业应用开发的大数据库集群 , 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 ，MYCAT并不依托于任何一个商业公司， 永不收费，永不闭源 ！

下面为mycat官网的原理图：

3.1 准备三台机器

proxy:  10.0.0.136
master: 10.0.0.138
slave:  10.0.0.137

3.2 安装配置mycat

# 下载mycat: 
[root@localhost src]#  wget http://dl.mycat.io/1.6.7.1/Mycat-server-1.6.7.1-release-20190627191042linux.tar.gz 
# 解压： 
[root@localhost src]#  tar xf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz  -C /usr/local/ 
# 安装java-jdk： 
[root@localhost src]#  yum install java-1.8.0-openjdk -y 
# 配置mycat环境变量： 
[root@localhost src]#  echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh . /etc/profile.d/mycat.sh

3.3 分别启动master/slave

syetemctl start mariadb

3.4 master授权mycat

启动master后进行登录，执行授权语句

# 在主库执行授权信息，从库会自动同步： 
MariaDB [(none)]> grant all on *.* to "mycat-proxy"@"10.0.0.136" identified by "123456";
Query OK, 0 rows affected (0.00 sec)

3.5 配置mycat

配置server.xml

[root@localhost src]# vim /usr/local/mycat/conf/server.xml

# 默认管理用户，可读可写： 
		<user name="mycat" defaultAccount="true">
                <property name="password">123456</property>
                #逻辑库fenlidb
                <property name="schemas">fenlidb</property>
        </user>
        
# 只读用户
        <user name="user">
                <property name="password">user</property>
                <property name="schemas">fenlidb</property>
                <property name="readOnly">true</property>
        </user>

配置schema.xml

#设置逻辑库和数据库节点
 <schema name="fenlidb" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
        </schema>
    # 配置数据库节点对应的后端真实的数据库
        <dataNode name="dn1" dataHost="localhost1" database="students" />
# 配置读写库以及均衡： 
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
                          writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
               #心跳检测，默认一秒查询一次，查不到认为主库挂了
                <heartbeat>select user()</heartbeat>
               
                <writeHost host="hostM1" url="10.0.0.138:3306" user="mycat-proxy"
                                   password="123456">
                        
                        <readHost host="hostS1" url="10.0.0.137:3306" user="mycat-proxy" password="123456" />
                </writeHost>
                
                #主库挂了从库可写
                <writeHost host="hostM2" url="10.0.0.138:3306" user="mycat-proxy"
                                   password="123456" />
                
        </dataHost>
   

3.6 启动mycat

[root@localhost src]# mycat start
或者绝对路径启动
[root@localhost src]# /usr/local/mycat/bin/mycat start

3.7 通过代理进行连接测试

[root@localhost src]# mysql -umycat -p123456 -P8066 -h127.0.0.1 

MariaDB [(none)]> show databases; 
+-----------+ 
| DATABASE  | 
+-----------+ 
| fenlidb| 
+-----------+ 
1 row in set (0.00 sec)

接下来可以在后端主库对应的students数据库建表进行数据写入和查询验证了。
至此，基于mycat的读写分离配置成功！