1. MySQL读写分离概念
MYSQL读写分离的原理其实就是让Master数据库处理事务性增加、删除、修改、更新操作（create、insert、update、delete），而让Slave数据库处理查询（select）操作，MySQL读写分离前提是基于MySQL主从复制，这样可以保证在Master上修改数据，Slave同步之后，WEB应用可以读取到Slave端的数据。
1.1 读写分离实现方式
实现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实现读写分离
Proxy: 192.168.48.182
Master: 192.168.48.183
Slave: 192.168.48.184
Slave: 192.168.48.185
2.1 工作原理图解
2.2 配置Proxy
Proxy可以选择和mysql部署在同一台服务器，也可以选择单独部署在另一台独立服务器。
# 下载MySQL-Proxy：
[root@node02 ~]# cd /usr/src/
[root@node02 src]#
[root@node02 src]# wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
# 解压：
[root@node02 src]# tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz
[root@node02 src]#
[root@node02 src]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy
# 配置环境变量：
[root@node02 src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh
[root@node02 src]#
[root@node02 src]# . /etc/profile.d/mysql-proxy.sh
[root@node02 src]# echo $PATH
/usr/local/mysql-proxy/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@node02 src]#
# 启动MYSQL-Proxy中间件:
[root@node02 src]# useradd -r mysql-proxy
[root@node02 src]#
[root@node02 src]# mysql-proxy --daemon --log-level=debug --user=mysql-proxy --keepalive --log-file=/var/log/mysql-proxy.log --plugins="proxy" --proxy-backend-addresses="192.168.48.183:3306" --proxy-read-only-backend-addresses="192.168.48.184:3306" --proxy-read-only-backend-addresses="192.168.48.185: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"
[root@node02 src]#
# 查看端口/日志：
[root@node02 src]# netstat -ntlp |grep 404
tcp 0 0 0.0.0.0:4040 0.0.0.0:* LISTEN 11803/mysql-proxy
tcp 0 0 0.0.0.0:4041 0.0.0.0:* LISTEN 11803/mysql-proxy
[root@node02 src]#
2.3 启动的相关参数
# 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 # 默认使用的配置文件路径，其配置段使用[mysql-proxy]标识；
--proxy-skip-profiling # 禁用profile；
--pid-file=/path/to/pid_file_name # 进程文件名；
2.4 启动master/slave
[root@node03 ~]# systemctl start mariadb
[root@node04 ~]# systemctl start mariadb
[root@node05 ~]# systemctl start mariadb
2.5 查看读写分离状态
基于4041端口MySQL-Proxy查看读写分离状态，登录4041管理端口 ：
[root@node02 ~]# mysql -h 192.168.48.182 -uadmin -padmin -P4041
# 这时可以看到后端数据库信息，只是状态为unknown,表示还没有客户端连接，可以通过4040代理端口通过
查询数据等操作激活。
MySQL [(none)]> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.48.183:3306 | unknown | rw | NULL | 0 |
| 2 | 192.168.48.184:3306 | unknown | ro | NULL | 0 |
| 3 | 192.168.48.185:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.01 sec)
MySQL [(none)]>
2.5 在master数据库上授权proxy
MariaDB [(none)]> grant all on *.* to "mysql-proxy"@"192.168.48.182" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
2.6 通过代理创建数据
通过4040代理端口插入数据，该sql语句会走master，于是可以激活master状态：
[root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "create database superman charset utf8;"
[root@node02 ~]#
在4041管理端口，再次查看：
[root@node02 ~]# mysql -h 192.168.48.182 -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)]>
MySQL [(none)]> select * from backends;
+-------------+---------------------+---------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+---------+------+------+-------------------+
| 1 | 192.168.48.183:3306 | up | rw | NULL | 0 |
| 2 | 192.168.48.184:3306 | unknown | ro | NULL | 0 |
| 3 | 192.168.48.185:3306 | unknown | ro | NULL | 0 |
+-------------+---------------------+---------+------+------+-------------------+
3 rows in set (0.00 sec)
MySQL [(none)]>
2.7 通过代理查询数据
# 先在主库选择superman数据库（因为主从关系，在主库创建的superman会同步至从库），创建表格，并插入数据：
MariaDB [(none)]> use superman;
Database changed
MariaDB [superman]> create table t1(id int,name varchar(20));
Query OK, 0 rows affected (0.00 sec)
MariaDB [superman]> insert t1 values(1,"xiaoming");
Query OK, 1 row affected (0.00 sec)
MariaDB [superman]>
通过4040代理端口查询数据，该sql语句会走slave，于是可以激活slave状态：
# 多执行几次！
[root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;"
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
[root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;"
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
[root@node02 ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;"
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
[root@node02 ~]#
在4041管理端口，再次查看：
[root@node02 ~]# mysql -h 192.168.48.182 -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)]> select * from backends;
+-------------+---------------------+-------+------+------+-------------------+
| backend_ndx | address | state | type | uuid | connected_clients |
+-------------+---------------------+-------+------+------+-------------------+
| 1 | 192.168.48.183:3306 | up | rw | NULL | 0 |
| 2 | 192.168.48.184:3306 | up | ro | NULL | 0 |
| 3 | 192.168.48.185:3306 | up | ro | NULL | 0 |
+-------------+---------------------+-------+------+------+-------------------+
3 rows in set (0.00 sec)
MySQL [(none)]>
3. 基于Mycat实现读写分离
MyCAT基于阿里开源的Cobar产品而研发 , 一个彻底开源的，面向企业应用开发的大数据库集群 , 一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群 ，MyCAT并不依托于任何一个商业公司， 永不收费，永不闭源 ！
MyCAT: 192.168.48.182
Master: 192.168.48.183
Slave: 192.168.48.184
Slave: 192.168.48.185
3.1 安装MyCAT
# 下载mycat:
[root@node02 ~]# cd /usr/src/
[root@node02 src]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@node02 src]#
# 将Mycat解压/usr/local目录下：
[root@node02 src]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/
[root@node02 src]#
[root@node02 src]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Apr 11 2018 bin
drwxr-xr-x. 2 root root 6 Apr 11 2018 etc
drwxr-xr-x. 2 root root 6 Apr 11 2018 games
drwxr-xr-x. 2 root root 6 Apr 11 2018 include
drwxr-xr-x. 2 root root 6 Apr 11 2018 lib
drwxr-xr-x. 2 root root 6 Apr 11 2018 lib64
drwxr-xr-x. 2 root root 6 Apr 11 2018 libexec
drwxr-xr-x 7 root root 85 Jun 8 04:10 mycat
drwxr-xr-x 8 7161 wheel 87 Dec 24 2013 mysql-proxy
drwxr-xr-x. 2 root root 6 Apr 11 2018 sbin
drwxr-xr-x. 5 root root 49 May 26 00:11 share
drwxr-xr-x. 2 root root 6 Apr 11 2018 src
[root@node02 src]#
# 安装java-jdk：
[root@node02 src]# yum install java-1.8.0-openjdk -y
[root@node02 src]#
[root@node02 src]# java -version
openjdk version "1.8.0_252"
OpenJDK Runtime Environment (build 1.8.0_252-b09)
OpenJDK 64-Bit Server VM (build 25.252-b09, mixed mode)
[root@node02 src]#
# 配置mycat环境变量：
[root@node02 ~]# echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh
[root@node02 ~]#
[root@node02 ~]# . /etc/profile.d/mycat.sh
[root@node02 ~]#
[root@node02 ~]# echo $PATH
/usr/local/mycat/bin:/usr/local/mysql-proxy/bin:/usr/lib64/qt-3.3/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@node02 ~]#
3.2 在master数据库上授权mycat
# 在主库执行授权信息，从库会自动同步：
[root@node03 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 13
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant all on *.* to "mycat-proxy"@"192.168.48.182" identified by "123456";
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
3.3 配置MyCAT
# 配置server.xml
[root@node02 ~]# cp /usr/local/mycat/conf/server.xml{,.bak}
[root@node02 ~]# vi /usr/local/mycat/conf/server.xml
# 默认管理用户，可读可写：
<user name="mycat" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">super</property> ---schemas为逻辑库
...
</user>
# 只读用户：
<user name="user">
<property name="password">user</property>
<property name="schemas">super</property> ---schemas为逻辑库
<property name="readOnly">true</property>
</user>
# 配置schema.xml
[root@node02 ~]# cp /usr/local/mycat/conf/schema.xml{,.bak}
[root@node02 ~]# vi /usr/local/mycat/conf/schema.xml
# 设置逻辑库以及数据库节点
<schema name="super" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
# 配置数据库节点对应的后端真实的数据库(指定master的数据库ip及数据库名)
<dataNode name="dn1" dataHost="localhost1" database="superman" />
# 配置读写库以及均衡（dataHost name指定mastet的ip）
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="2"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="hostM1" url="192.168.48.183:3306" user="mycat-proxy"
password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.48.184:3306" user="mycat-proxy" password="123456" />
<readHost host="hostS2" url="192.168.48.185:3306" user="mycat-proxy" password="123456" />
</writeHost>
<writeHost host="hostS3" url="192.168.48.184:3306" user="mycat-proxy"
password="123456" />
</dataHost>
</mycat:schema>
# 注意dataHost节点的下面三个属性
balance， switchType， writeType
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
balance="1"，全部的readHost与stand by writeHost参与select语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
balance="2"，所有读操作都随机的在writeHost、readhost上分发。
balance="3"，所有读请求随机的分发到writeHost下的readhost执行，writeHost不负担读压力
writeType表示写模式
writeType="0"，所有的操作发送到配置的第一个writehost
writeType="1"，随机发送到配置的所有writehost
writeType="2"，不执行写操作
switchType指的是切换的模式，目前的取值也有4种：
switchType=‘-1‘?表示不自动切换
switchType=‘1‘?默认值，表示自动切换
switchType=‘2‘?基于MySQL主从同步的状态决定是否切换,心跳语句为show slave status
switchType=‘3‘基于MySQL galary cluster的切换机制（适合集群）（1.4.1），心跳语句为show status like ‘wsrep%‘。
3.3 启动MyCAT
[root@node02 ~]# mycat start
Starting Mycat-server...
[root@node02 ~]#
[root@node02 ~]# netstat -ntlp|grep 66
tcp6 0 0 :::9066 :::* LISTEN 36231/java
tcp6 0 0 :::8066 :::* LISTEN 36231/java
[root@node02 ~]#
3.4 连接测试
[root@node02 ~]# mysql -umycat -p123456 -P8066 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB)
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)]> show databases;
+----------+
| DATABASE |
+----------+
| super |
+----------+
1 row in set (0.01 sec)
MySQL [(none)]>
# 可以在后端主库创建一个表，继续查询表测试：
[root@node03 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 24
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use superman;
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
MariaDB [superman]>
MariaDB [superman]> show tables;
+--------------------+
| Tables_in_superman |
+--------------------+
| t1 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [superman]>
MariaDB [superman]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MariaDB [superman]>
# 在从库1插入数据，继续查询：
[root@node04 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use superman;
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
MariaDB [superman]> show tables;
+--------------------+
| Tables_in_superman |
+--------------------+
| t1 |
+--------------------+
1 row in set (0.00 sec)
MariaDB [superman]>
MariaDB [superman]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MariaDB [superman]>
MariaDB [superman]> insert into t1 values(2,"xiaowang");
Query OK, 1 row affected (0.00 sec)
MariaDB [superman]>
MariaDB [superman]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaowang |
+------+----------+
2 rows in set (0.00 sec)
MariaDB [superman]>
# 在从库2插入一条数据，继续查询：
[root@node05 ~]# mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 5.5.60-MariaDB MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> use superman
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
MariaDB [superman]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MariaDB [superman]>
MariaDB [superman]> insert into t1 values(3,"xiaozhang");
Query OK, 1 row affected (0.07 sec)
MariaDB [superman]>
MariaDB [superman]> select * from t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 3 | xiaozhang |
+------+-----------+
2 rows in set (0.00 sec)
MariaDB [superman]>
在主库查不到数据，通过代理可以查到，即可验证读写分离成功。
[root@node02 ~]# mysql -umycat -p123456 -P8066 -h192.168.48.182
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.1-release-20190627191042 MyCat Server (OpenCloudDB)
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)]>
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| super |
+----------+
1 row in set (0.00 sec)
MySQL [(none)]>
MySQL [(none)]> use super;
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 [super]>
MySQL [super]> show tables;
+--------------------+
| Tables_in_superman |
+--------------------+
| t1 |
+--------------------+
1 row in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 3 | xiaozhang |
+------+-----------+
2 rows in set (0.01 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaowang |
+------+----------+
2 rows in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 3 | xiaozhang |
+------+-----------+
2 rows in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+-----------+
| id | name |
+------+-----------+
| 1 | xiaoming |
| 3 | xiaozhang |
+------+-----------+
2 rows in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
+------+----------+
1 row in set (0.00 sec)
MySQL [super]>
MySQL [super]> select * from t1;
+------+----------+
| id | name |
+------+----------+
| 1 | xiaoming |
| 2 | xiaowang |
+------+----------+
2 rows in set (0.00 sec)
MySQL [super]>
3.5 报错解决
MySQL [superman]> show tables;
ERROR 1184 (HY000): Invalid DataSource:0
可能是后端节点出现了问题，也有可能是代理端无法连上后端节点导致：
可以先在代理端直接用授权用户名和密码登录后端数据库测试连接问题：
[root@node02 conf]# mysql -umycat-proxy -h192.168.48.134 -p123456
ERROR 1129 (HY000): Host 'node3' is blocked because of many connection errors; unblock with 'mysqladmin flush-hosts'
# 可以看到因为多次错误，代理端服务器被锁定了，所以也会出现上面的报错：
在后端主库执行如下命令：
mysqladmin flush-hosts
再次测试，一般问题就能解决。
