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： [ ~]# cd /usr/src/ [ src]# [ src]# wget http://mirrors.163.com/mysql/Downloads/MySQL-Proxy/mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz # 解压： [ src]# tar xf mysql-proxy-0.8.4-linux-el6-x86-64bit.tar.gz [ src]# [ src]# mv mysql-proxy-0.8.4-linux-el6-x86-64bit /usr/local/mysql-proxy # 配置环境变量： [ src]# echo "export PATH=/usr/local/mysql-proxy/bin:$PATH" > /etc/profile.d/mysql-proxy.sh [ src]# [ src]# . /etc/profile.d/mysql-proxy.sh [ 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 [ src]# # 启动MYSQL-Proxy中间件: [ src]# useradd -r mysql-proxy [ src]# [ 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" [ src]# # 查看端口/日志： [ 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 [ 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
[ ~]# systemctl start mariadb [ ~]# systemctl start mariadb [ ~]# systemctl start mariadb
2.5 查看读写分离状态
基于4041端口MySQL-Proxy查看读写分离状态，登录4041管理端口 ：
[ ~]# 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状态：
[ ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "create database superman charset utf8;" [ ~]#
在4041管理端口，再次查看：
[ ~]# 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状态：
# 多执行几次！ [ ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [ ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [ ~]# mysql -h192.168.48.182 -umysql-proxy -p123456 -P4040 -e "select* from superman.t1;" +------+----------+ | id | name | +------+----------+ | 1 | xiaoming | +------+----------+ [ ~]#
在4041管理端口，再次查看：
[ ~]# 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: [ ~]# cd /usr/src/ [ 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 [ src]# # 将Mycat解压/usr/local目录下： [ src]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/ [ src]# [ 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 [ src]# # 安装java-jdk： [ src]# yum install java-1.8.0-openjdk -y [ src]# [ 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) [ src]# # 配置mycat环境变量： [ ~]# echo "export PATH=/usr/local/mycat/bin:$PATH" > /etc/profile.d/mycat.sh [ ~]# [ ~]# . /etc/profile.d/mycat.sh [ ~]# [ ~]# 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 [ ~]#
3.2 在master数据库上授权mycat
# 在主库执行授权信息，从库会自动同步： [ ~]# 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
[ ~]# cp /usr/local/mycat/conf/server.xml{,.bak}
[ ~]# 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
[ ~]# cp /usr/local/mycat/conf/schema.xml{,.bak}
[ ~]# 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
[ ~]# mycat start Starting Mycat-server... [ ~]# [ ~]# netstat -ntlp|grep 66 tcp6 0 0 :::9066 :::* LISTEN 36231/java tcp6 0 0 :::8066 :::* LISTEN 36231/java [ ~]#
3.4 连接测试
[ ~]# 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)]> # 可以在后端主库创建一个表，继续查询表测试： [ ~]# 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插入数据，继续查询： [ ~]# 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插入一条数据，继续查询： [ ~]# 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]>
在主库查不到数据，通过代理可以查到，即可验证读写分离成功。
[ ~]# 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 可能是后端节点出现了问题，也有可能是代理端无法连上后端节点导致： 可以先在代理端直接用授权用户名和密码登录后端数据库测试连接问题： [ 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 再次测试，一般问题就能解决。
相关推荐
要啥自行车一把梭 2020-09-10
emmm00 2020-11-17
暗夜之城 2020-11-11
疯狂老司机 2020-09-08
CoderToy 2020-11-16
bianruifeng 2020-11-16
云中舞步 2020-11-12
世樹 2020-11-11
Coder技术文摘 2020-09-29
huacuilaifa 2020-10-29
Gexrior 2020-10-22
tufeiax 2020-09-03
王艺强 2020-11-17
aydh 2020-11-12
zry 2020-11-11
URML 2020-11-11
spurity 2020-11-10
yifangs 2020-10-13
Andrea0 2020-09-18
Ida 2020-09-16
ltd00 2020-09-12
xjd0 2020-09-10