mysql安装请看这里 传送门 这里不做赘述

这张图是网上找的,我觉得画的很好,简单明了的解释出mysql数据库读写分离的全过程
- Java web app:是客户端请求,会对数据库发起读写操作请求,具体是发送SQL指令
- Mysql Proxy:对读写操作请求的SQL指令进行路由,使得读写分离
- direct:一个负载分发引擎,对Mysql Proxy分发得读操作,按照一定得算法进行分发至后端得从服务器
- master:主服务器,主要接受用户的写操作,并且负责将二进制日志同步给从服务器
- slave-n:从服务器,主要负责用户的读操作(分担主服务器的读写压力),并且负责重放master的写操作,还能实现容灾能力,保证高可用(如果主服务器挂掉,slvae顶上去)
mysql主从复制
配置主master服务器使之具备以下能力
- 记录二进制日志
- 为从服务提供一个用户(设置密码),提高二进制日志同步得安全性
配置从服务器slave使之具备以下能力
- 记录中继日志
- 连接到mysql可以启动SLAVE功能,并且设置Master信息,通过配置信息,开启IO_THREAD和SQL_THREAD线程
1 2 3 4 5 6 7 8 9 10 11 |
主master配置: 1.开启二进制日志 binlog 2.配置唯一的server-id 3.获得master二进制文件名及位置 4.创建一个用于slave和master通信的用户账号 vim /etc/my.cnf 在[mysqld]部分插入如下: log-bin=mysql-bin #开启二进制日志 server-id=140 #设置server-id,建议使用ip最后3位 |
1 2 3 4 5 6 7 8 9 |
从服务器Slave配置 1.配置唯一的server-id 2.使用master分配的用户账号读取master二进制日志 3.启动slave服务 vim /etc/my.cnf 在[mysqld]部分插入如下: relay-log=mysql-relay #开启中继日志 server-id=141 #设置server-id,建议使用ip最后3位 |
重启mysql服务
systemctl restart mysqld.service
在主机 maser上建立账户并授权slave
1 2 3 4 5 6 7 |
GRANT REPLICATION SLAVE ON *.* TO 'mysql122'@'192.168.6.122' IDENTIFIED BY 'mysql122';(多个ip授权,多执行几次,不可逗号分隔) GRANT REPLICATION SLAVE ON *.* TO 'mysql122'@'192.168.6.123' IDENTIFIED BY 'mysql122'; flush privileges; #查询master的状态 show master status\G |


1 2 3 4 5 |
File: mysql-bin.000005 Position: 605 记录上图结果中File和Position的值。 这里的值在后续所有slave上都会用到 注意:执行完此步骤后不要再操作主服务器MySQL,防止主服务器状态发生状态值变化。 |
告知从服务器(122)二进制文件名与位置
1 2 3 4 5 |
CHANGE MASTER TO master_host = '192.168.6.121', master_user = 'mysql122', master_password = 'mysql122', master_log_file = 'mysql-bin.000005', master_log_pos = 605; |
查看从服务器状态
1 2 3 4 5 |
//开启复制 start slave; //查看主从复制是否配置成功 SHOW SLAVE STATUS\G |

1 2 3 4 5 |
当看到 Slave_IO_State:Waiting for master ot send event Slave_IO_Running: YES Slave_SQL_Running: YES 才表明状态正常。 |
测试主从复制是否成功
1 2 |
Master中和Slave中执行SQL 查看当前都有哪些数据库 show databases; |


在master中创建数据库并创建数据表并且插入一条语句
1 2 3 4 |
create database test; use test; create table tab1(id int auto_increment,name varchar(10),primary key(id)); insert into tab1(id,name) values (1,'why'); |

在Slave中查询这个数据库,查询这条数据

至此,MySQL主从复制就实现了。
主从热备常用语句
1 2 3 4 5 |
show master status: 查看master的状态,尤其是当前的日志及位置 show slave status 查看slave的状态 reset slave 重置slave状态 start slave 启动slave状态 stop slave 暂停slave状态 |
mysql数据库分库分表读写分离
使用Mycat实现读写分离
Mycat需要jvm支持,安装前请先安装JDK
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
1.下载jdk1.8(根据你的系统版本来选对应的jdk) 下载地址:http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html 2.查看系统位数 getconf LONG_BIT 3.下载解压 tar -zxvf jdk-8u231-linux-x64.tar.gz 4.解压后放到opt目录下 5.配置环境变量(vim /etc/profile) export JAVA_HOME=/opt/jdk1.8.0 export PATH=$JAVA_HOME/bin:$PATH export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib 6.刷新环境变量 . /etc/profile 7.测试是否安装成功 java -version |

1.下载安装Mycat
- Mycat官网:http://www.mycat.io/
可以了解下Mycat的背景和应用情况,这样使用起来比较有信心。 - Mycat下载地址:http://dl.mycat.io/
官网有个文档,属于详细的介绍,初次入门,看起来比较花时间。
建议大家选择 1.6.5之后的 版本,1.6.5之前不支持AX事务,会让你很蛋疼,特别是做集群。

安装
根据不同的系统选择不同的版本。包括linux、windows、mac,作者考虑还是非常周全的,当然,也有源码版的。
Mycat的安装其实只要解压下载的目录就可以了,非常简单。
安装完成后,目录如下:

配置
Mycat的配置文件都在conf目录里面,这里介绍几个常用的文件:

Mycat的架构其实很好理解,Mycat是代理,Mycat后面就是物理数据库。和Web服务器的Nginx类似。对于使用者来说,访问的都是Mycat,不会接触到后端的数据库。
我们现在做一个主从、读写分离,简单分表的示例。结构如下图:

Mycat作为主数据库中间件,肯定是与代码弱关联的,所以代码是不用修改的,使用Mycat后,连接数据库是不变的,默认端口是8066。连接方式和普通数据库一样,如:jdbc:mysql://192.168.0.2:8066
配置Mycat
我们只针对mycat实现简单的分库分表和,数据库读写分离。
mysql分库分表(数据库读写分离在后边,先看分库分表然后再看数据库读写分离更好理解)
我们真实的物理数据库名称为 wechat,包含了一些表(如下图),以免大家对后面的配置不明白。

server.xml
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
<user name="root"> <property name="password">123456</property> <property name="schemas">wechat</property> <property name="readOnly">false</property> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user> #注意这里配置一个user 下面那个没用的删掉 |

解释:
1 2 3 4 5 6 |
user:用户配置节点 name:登录的用户名,也就是连接myCat的用户名 password:登录的密码,也就是连接myCat的密码 schemas:数据库名,这里会和schema.xml中的配置关联,多个用逗号分开,例如这个用户需要管理2个数据库db1,db2则配置 db1,db2 privileges:配置用户针对表的增删改查权限,具体见文档 # 我这里账号是root 密码是:123455 ,针对数据库wechat,读写权限都有,没有针对表做什么特殊权限设置 |
schema.xml是最主要的配置项,首先看我的配置文件
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 数据库配置,与server.xml中的数据库对应 --> <schema name="wechat" checkSQLschema="false" sqlMaxLimit="100"> <table name="wp_users" dataNode="dn1" /> <table name="wp_usermeta" dataNode="dn1" /> <table name="wp_links" dataNode="dn2" /> <table name="wp_terms" dataNode="dn2" /> <table name="wp_options" dataNode="dn3" /> <table name="wp_posts" dataNode="dn3" /> <table name="wp_termmeta" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="mod-long" /> </schema> <!-- 分片配置 --> <dataNode name="dn1" dataHost="test1" database="wechat" /> <dataNode name="dn2" dataHost="test2" database="wechat" /> <dataNode name="dn3" dataHost="test2" database="wechat" /> <!-- 物理数据库配置 --> <dataHost name="test1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.6.121:3306" user="root" password="123456"> </writeHost> </dataHost> <dataHost name="test2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostS1" url="192.168.6.122:3306" user="root" password="123456"> </writeHost> </dataHost> <dataHost name="test3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native"> <heartbeat>select user();</heartbeat> <writeHost host="hostN1" url="192.168.6.123:3306" user="root" password="123456"> </writeHost> </dataHost> </mycat:schema> |
1 2 3 4 |
解释:参数说明 schema:数据库设置,此数据库为逻辑数据库,name与server.xml中schema对应 dataNode:分片信息,也就是分库相关配置 dataHost:物理数据库,真正存储数据的数据库 |
每个节点属性逐一说明
schema:
1 2 3 |
name:逻辑数据库名,与server.xml中的schema对应 checkSQLschema:数据库前缀相关设置,建议看文档,这里暂时设为false sqlMaxLimit:select 时默认的limit,避免查询全表 |
table:
1 2 3 4 5 |
name:表名,物理数据库中表名 dataNode:表存储到哪些节点,多个节点用逗号分隔。节点为下文dataNode设置的name primaryKey:主键字段名,自动生成主键时需要设置 autoIncrement:是否自增 rule:分片规则名,具体规则下文rule详细介绍 |
dataNode:
1 2 3 |
name:节点名,与table中dataNode对应 datahost:物理数据库名,与datahost中name对应 database:物理数据库中数据库名 |
dataHost:
1 2 3 4 5 |
name:物理数据库名,与dataNode中dataHost对应 balance:均衡负载的方式 writeType:写入方式 dbType:数据库类型 heartbeat:心跳检测语句,注意语句结尾的分号要加 |

我在192.168.6.121 ,192.168.6.122 ,192.168.6.123 都有数据库webchat
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
wp_users wp_usermeta #上面这2张表都只写入节点dn1,也就是192.168.6.121 wp_links wp_terms #上面这2张表都只写入节点dn2,也就是192.168.6.122 wp_options wp_posts #上面这2张表都只写入节点dn3,也就是192.168.6.123 wp_termmeta #最后这张表写入dn1,dn2,dn3三个节点,分片的规则为:rule=mod-long |

主要关注rule属性,rule属性的内容来源于rule.xml这个文件,Mycat支持10种分表分库的规则,基本能满足你所需要的要求,这个必须赞一个,其他数据库中间件好像都没有这么多。
table中的rule属性对应的就是rule.xml文件中tableRule的name,具体有哪些分表和分库的实现,建议还是看下文档。我这里选择的mod-long就是将数据平均拆分。因为我后端是三台物理库,所以rule.xml中mod-long对应的function count为3,见下面部分代码:
注意看下rule.xml 这几行配置是否正确
1 2 3 4 5 6 7 8 9 10 11 |
<tableRule name="mod-long"> <rule> <columns>id</columns> <algorithm>mod-long</algorithm> </rule> </tableRule> <function name="mod-long" class="io.mycat.route.function.PartitionByMod"> <!-- how many data nodes --> <property name="count">3</property> </function> |
数据库读写分离(配置)
前面的server.xml配置都相同
schema.xml如下
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <!-- 数据库配置,与server.xml中的数据库对应 --> <schema name="fms" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" /> <!-- 分片配置 --> <dataNode name="dn1" dataHost="test1" database="fms" /> <!-- 物理数据库配置 --> <dataHost name="test1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1"> <heartbeat>select user();</heartbeat> <writeHost host="hostM1" url="192.168.6.121:3306" user="shooter" password="123456"> <readHost host="hostS1" url="192.168.6.122:3306" user="sread" password="123456" /> <readHost host="hostN1" url="192.168.6.123:3306" user="sread" password="123456" /> </writeHost> <writeHost host="hostS1" url="192.168.6.122:3306" user="shooter" password="123456"> <readHost host="hostM1" url="192.168.6.121:3306" user="sread" password="123456" /> <readHost host="hostN1" url="192.168.6.123:3306" user="sread" password="123456" /> </writeHost> </dataHost> </mycat:schema> |
dataHost配置详解:
1 2 3 4 5 6 7 8 9 10 11 |
balance 属性负载均衡类型,目前的取值有 4 种: balance="0",不开启读写分离机制,所有读操作都发送到当前可用的writeHost 上。 balance="1",所有读操作都随机发送到当前的writeHost对应的readHost和备用的writeHost和对应的readHost上写操作也是如此 (一主多从不建议使用,会间歇性出现readonly) balance="2",所有读操作都随机的在 writeHost、 readhost 上分发。 balance="3", 所有的读操作都只发送到当前writeHost的readHost上, writerHost 不负担读压力,注意 balance=3 只有1.4 及其以后版本有, 1.3 没有。 writeType 属性,负载均衡类型,目前的取值有 3 种: (双主可以使用这种,第一个master挂了写就自动切换到下一个) writeType="0",所有写操作发送到配置的第一个 writeHost,第一个挂了切到还生存的第二个writeHost,重新启动后已切换后的为准,切换记录在配置文件中:dnindex.properties . writeType="1",所有写操作都随机的发送到配置的 writeHost,1.5 以后废弃不推荐。 writeType="2",官方文档没有介绍。 switchType 属性: |
1 2 3 |
注意:读库的用户test是添加的mysql用户,只具有读权限的用户: GRANT Select ON *.* TO 'sread'@'%' IDENTIFIED BY "123456" |
这样的配置与前一个示例配置改动如下:
删除了table分配的规则,以及dataNode只有一个
dataHost也只有一台,但是writeHost总添加了readhost, balance改为1,表示读写分离。
以上配置达到的效果就是102.168.6.121为主库,(192.168.6.122,192.168.6.123)为从库。
注意:Mycat主从分离只是在读的时候做了处理,写入数据的时候,只会写入到writehost,需要通过mysql的主从复制功能将数据复制到readhost,这个问题我纠结了好久,数据写入writehost后,readhost一直没有数据,以为是自己配置的问题,后面才发现Mycat本身没有实现主从复制的功能,毕竟数据库本身自带的主从复制功能才是最高效稳定的。
至于其他的场景,如同时主从和分表分库也是支持的了,只要了解这个实现以后再去修改配置,都是可以实现的。而热备及故障转移官方推荐使用haproxy配合mycat一起使用,大家可以试试。
最后加入配置文件的test1主机名 到hosts

Mycat的启动
1 2 3 4 5 6 7 8 |
##启动 mycat start ##停止 mycat stop ##重启 mycat restart |
建立软件连接
1 2 3 4 5 6 7 8 |
在 /opt/mycat/bin/mycat 开头加一行source /etc/profile (有些机器不需要,这个步骤是让他正确加载环境变量,否则可能会报jvm找不到的问题,也有可能是软链接不会启用) ln -s /opt/mycat/bin/mycat /etc/init.d/mycat service mycat start #加入开机启动 chkconfig --add mycat |
如果在启动时发现异常,在logs目录中查看日志。
- wrapper.log 为程序启动的日志,启动时的问题看这个
- mycat.log 为脚本执行时的日志,SQL脚本执行报错后的具体错误内容,查看这个文件。mycat.log是最新的错误日志,历史日志会根据时间生成目录保存。
mycat启动后,执行命令不成功,可能实际上配置有错误,导致后面的命令没有很好的执行。

Mycat带来的最大好处就是使用是完全不用修改原有代码的,在mycat通过命令启动后,你只需要将数据库连接切换到Mycat的地址就可以了。如下面就可以进行连接了:
测试(数据库)读写分离
写:这个没啥测的,登录主master插入一条数据,看看从库是不是同步过去了

查看日志

这个错误是说mysql连接失败了,具体原因是mysql只能通过localhost访问登录,而不能使用本机ip访问的问题。
解决办法:
1.或者你在schema.xml 把ip改成localhost或者127.0.0.1

2.或者你不改ip访问,执行如下语句
1 2 3 |
GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.6.121' IDENTIFIED BY '123456'; #注意如果有多台机器mycat集群,记得其他机器也要允许访问,否则其他集群机器访问不了主master,只需要在主机器允许就行,其他从机器会同步主数据. ... |
3.Mycat启动报NumberFormatException解决方案(原因:server内存太大)
1 2 3 4 5 6 7 8 9 10 11 |
java.lang.NumberFormatException: Size must be specified as bytes (b), kibibytes (k), mebibytes (m), gibibytes (g), tebibytes (t), or pebibytes(p). E.g. 50b, 100k, or 250m. 解决方案: 解决方法,下面的配置改为0 vim conf/server.xml <!-- off heap for merge/order/group/limit 1开启 0关闭 --> <property name="useOffHeapForMerge">0</property> |
3.连接myCat
1 2 3 4 |
#连接到mycat,mycat的端口为8066 mysql -h 192.168.6.121 -P 8066 -u root -p123456 #Mycat还有一个管理的连接,端口号是9066. mysql -h 192.168.6.121 -P 9066 -uroot -p123456 |
mycat服务默认的数据端口是8066 , 而9066端口则是mycat管理端口,用于管理mycat的整个集群状态。监听的端口可以在server.xml配置文件中修改 (注意不要在9066端口上查询语句啊 执行mysql语句,他只是管理,不支持那些操作,想要插入数据,请移步8066)
连接8066 向wp_user表插入一条语句
1 |
mysql -h 192.168.6.121 -P 8066 -u root -p123456 |

然后看下另外两台从机器,数据是否已经同步过去了(这里要说明,主从复制不止mycat的功能,是因为前面我们一开始我们配置的主从复制作用的)


测试读:(我们修改从机器的插入数据的name 然后在主机器读这条数据,如果能读到并且是修改过的 说明读走的是从机器,与主机器master无关)
修改192.168.6.122机器的数据 将user_login修改为rabbit

然后在主机器master(192.168.6.121)查看该条数据

到此mysql 读(slave)写(master)分离完结
补充内容:9066管理端的一些常用命令
reload命令
1 2 3 4 5 6 7 8 |
#重新加载配置文件,在修改完配置文件之后,不用重启mycat,可以使用如下命令重新加载: reload @@config; 开启SQL监控分析功能: reload @@sqlstat=open. 关闭SQL监控分析功能: reload @@sqlstat=close. 设置慢SQL时间阈值: reload @@sqlslow= 重置SQL监控分析的数据: reload @@user_stat :这个命令用于清除缓存。改命令工作在9066端口,用来将客户端执行 show @@sql; show @@sql.sum;show@@slow.success;命令之后的缓存信息清除、 |
show命令
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
show @@database; show @@dataNode; #NAME:表示dataNode的名称。 #DATAHOST:表示对应的datahost属性的值,即数据主机。 #ACTIVE:表示活跃的连接数量,IDLE:表示空闲的连接数量。SIZE:表示对应的总连接数量 show @@dataNode where schema=wechat; 这个命令可以和where子句结合,查找对应schema的节点数量。因为这里只有一个schema,因此显示的结果一样的。 show @@heartbeat; #检查心跳状态。 RS_CODE状态如下: OK_STATUS=1代表正常状态。 ERROR_STATUS =-1 代表连接错误 TIMEOUT_STATUS=-2代表连接超时 INIT_STATUS=0代表初始化状态 若节点发生故障,则会连续进行默认的5个周期检测,心跳连接失败后就会变成-1,节点故障确认,然后可能发生切换。 show @@connection; #该命令用于获取mycat当前连接状态,即应用于mycat的连接。 kill @@connection 21;#用于强制关闭连接,id值可由show @@connection命令查看。 show @@backend; #查看后端连接状态: show @@cache; #用于查看mycat缓存 show @@datasource; #查看数据源的状态,如果配置了主从或者多主,则可以切换。 show @@syslog limit: #用于显示系统日志 可以加显示行数如 show @@syslog limit=3; |
SQL统计命令
1 2 3 |
show @@sql:显示在mycat中执行过的sql语句。 show @@sql.slow:显示慢SQL语句。 show @@sql.sum: 显示sql语句的整体执行情况,读写比例等。 |

- 本文固定链接: https://www.yoyoask.com/?p=770
- 转载请注明: shooter 于 SHOOTER 发表