MySQL读写分离实战

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;

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

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

| 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

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

MySQL [(none)]>

MySQL [(none)]> select * from backends;

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

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

| 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

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

MySQL [(none)]> select * from backends;

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

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

| 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

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

# 默认管理用户，可读可写：

<property name="schemas">super</property> ---schemas为逻辑库

...

</user>

# 只读用户：

<property name="schemas">super</property> ---schemas为逻辑库

</user>

# 配置schema.xml

[root@node02 ~]# cp /usr/local/mycat/conf/schema.xml{,.bak}

[root@node02 ~]# vi /usr/local/mycat/conf/schema.xml

# 设置逻辑库以及数据库节点

</schema>

# 配置数据库节点对应的后端真实的数据库(指定master的数据库ip及数据库名)

# 配置读写库以及均衡（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>

<writeHost host="hostM1" url="192.168.48.183: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)

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

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

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

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)

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

再次测试，一般问题就能解决。