MyCat做MySQL读写分离

     

转载：http://bokek.cn/mycat-do-mysql-separate-read-and-write.html

前言：现在普遍的公司都是在用MySQL做数据持久化，但是稍微有点规模的网站就会发现，web应用做负载均衡什么的，so easy,加台机器，加个nginx前端代理服务器，可能稍微有一点点复杂的是使用redis或者memcache做一个session会话集中管理罢了，加了负载均衡后，瞬间感觉高大上了，两个web应用负载分流庞大的用户流量，然而然并卵，网站还是依旧的慢如蜗牛。。。，那个这个是什么问题呢？可以解决吗？

of course，像这种加了web负载没效果的，我想定是mysql爆了，需要下点功夫在mysql或者是缓存上下点功夫了。

下面我就从mysql数据库(架构)方便优化这个问题。

优化mysql的话，就需要考虑到mysql的集群，做mysql的读写分离，就会使用到mysql的主从复制机制，和使用第三方中间件来实现了。

下面我就跟大家介绍其中一种中间件--mycat来实现mysql的读写分离。

1.参与机器

10.0.4.80 3306 主数据库

10.0.4.101 3306 从数据库，主80

10.0.4.105 3306 从数据库，主101

10.0.4.102   mycat部署机器

2.搭建主从

1主2从，采用的是链式主从方案，可以详细看上面的机器列表，就做了简单的标记，已经配置好主从复制的，请跳过，没有配置好的，请移步~~~

3.部署mycat

mycat架构图

Shell

1

2

3

4

5

6

#创建mycat部署目录

mkdir/home/mycat

#下载mycat压缩包

wgethttps://github.com/MyCATApache/Mycat-download/blob/master/1.5-RELEASE/Mycat-server-1.5.1-RELEASE-20160518143538-linux.tar.gz

#解压mycat

tarzxvf Mycat-server-1.5.1-RELEASE-20160518143538-linux.tar.gz

4.配置主从

MyCat主从配置

Shell

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

#打开配置读写分离的配置文件

vimmycat/conf/schema.xml

#打开以后按照如下配置

<?xmlversion="1.0"?>

<!DOCTYPEmycat:schemaSYSTEM "schema.dtd">

<mycat:schemaxmlns:mycat="http://org.opencloudb/">

 

        <!-- 配置mycat逻辑库，类似为mysql的数据库，用法和mysql数据库无异  -->

        <schemaname="bokek"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1">

                <!-- 这里配置表的分库和分片的，不在本文的分享之内，所有就留空白，不做说明 -->

        </schema>

        <!--配置数据库节点-->

        <dataNodename="dn1"dataHost="localhost1"database="bkk_dev"/>

        <!--配置数据节点，读写分离的重头戏，请仔细看-->

        <dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"  writeType="0"

                  dbType="mysql"dbDriver="native"switchType="2"slaveThreshold="100">

                <heartbeat>showslave status</heartbeat>

                <!-- 主库，写节点 -->

                <writeHosthost="hostM1"url="10.0.4.80:3306"user="root"

                        password="bokek80">

                        <!-- 从库，都节点 -->

                        <readHosthost="hostS2"url="10.0.4.101:3306"user="root"password="bokek101"/>

                </writeHost>

                <!--主故障，顶替写节点,主正常是分担都读压力-->

                <writeHosthost="hostS1"url="10.0.4.105:3306"user="root"password="bokek105"/>

        </dataHost>

</mycat:schema>

配置详解：

schema 标签用于定义MyCat实例中的逡辑库，MyCat可以有对个逻辑库，每个逻辑库都有自身的相关配置。可以使用 schema 标签来划分返回不同的逻辑。 如果丌配置 schema 标签，所有癿表配置，会属二同一个默讣的逻辑库。

             name 该属性配置逻辑库的名字，类似mysql的数据库名称

             dataNode 该属行用绑定辑库刡某个具体的database上，1.3版本如果配置了dataNode，则不可以配置分片表，1.4可以配置默认分片，叧需要配置需要分片的表即可，具体如下配置:

mycat1.3配置

Shell

1

2

3

<schemaname="bokek"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn1">

    <!—里面不能配置仸何表-->

</schema>

mycat1.5配置

Shell

1

2

3

4

<schemaname="bokek"checkSQLschema="false"sqlMaxLimit="100"dataNode="dn12">

    <!—配置需要分片的表-->

    <tablename=“tb_order”dataNode=”dn2”/>

</schema>

        checkSQLschema 当该属性值设置为 true 时，如果我们执行语句  select * from bokek.mycat_sequence_test;  则MyCat会把语句修改为  select * from mycat_sequence_test; 。即把表示schema的字符去掉，避免发送到后端数据库执行时报 （ERROR 1146 (42S02): Table ‘bokek.mycat_sequence_test’ doesn’t exist）。不过，即使设置该值为 true ，如果语句所带来的是并非是schema指定的名字，例如：  select * from bokek.mycat_sequence_test;   那么MyCat并不会删除bokek这个字段，如果没有定义诠库癿话则会报错，所以在提供SQL询句癿最好是不要带这个字段。

            sqlMaxLimit 当该值设置为某个数值时。每条执行的SQL询句，如果没有加上limit语句，MyCat也会自动的加上所对应的值。例如设置为100，执行  select * from bokek.mycat_squeuce_test;  的效果为与执行  select * from bokek.mycat_squeuece_test limit 100;  相同。 不设置该值的话，MyCat默认会把查询到的信息全部都展示出来，造成过多的输出。所以，在正常使用中，迓是建议加上一个值，用与减少过多的数据返回。当然SQL语句可以显式的设置limit的大小，不受该属性的任何约束。 需要注意的事，如果运行的schema为非拆分库时，那么该属性则不会生效。需要手动添加limit语句。

dataNode 标签

数据节点，数据分片

Shell

1

<dataNodename="dn1"dataHost="localhost1"database="bkk_dev"/>

dataNode 标签定义了MyCat中的数据节点，也就是我们通常说所的数据分片。一个dataNode标签就是一个独立的数据分片。

例子中所表述的意思为：使用名字为localhost1数据库实例上的bkk_dev物理数据库，这就组成一个数据分片，最后，我们使用名字dn1标识这个分片。

name 该属性定义数据节点的名字，这个名字需要唯一的，我们需要在table标签上应用这个名字，来建立表不分片对应的关系。

dataHost 该属性用于定义该分片属于哪个数据库实例上，该属性值是引用dataHost标签上定义的name属性。

database 该属性用于定义该分片属于哪个数据库实例上的具体的那个数据库，因为这里使用两个纬度来定义分片，就是：实例+具体的库。因为每个库上建立的表和表结构是一样的。所以这样做就可以轻松的对表进行水平拆分。

dataHost 标签 作为Schema.xml中最后的一个标签，该标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句。现在我们就解析下这个标签。

dataHost标签配置

Shell

1

2

3

4

5

6

7

8

9

10

11

12

13

<!--配置数据节点，读写分离的重头戏，请仔细看-->

<dataHostname="localhost1"maxCon="1000"minCon="10"balance="1"  writeType="0"

    dbType="mysql"dbDriver="native"switchType="2"slaveThreshold="100">

    <heartbeat>showslave status</heartbeat>

    <!-- 主库，写节点 -->

    <writeHosthost="hostM1"url="10.0.4.80:3306"user="root"

        password="bokek80">

    <!-- 从库，都节点 -->

    <readHosthost="hostS2"url="10.0.4.101:3306"user="root"password="bokek101"/>

    </writeHost>

    <!--主故障，顶替写节点,主正常是分担都读压力-->

    <writeHosthost="hostS1"url="10.0.4.105:3306"user="root"password="bokek105"/>

</dataHost>

    name 该属性唯一标示dataHost标签，供上层的标签使用。

    maxCon 该属性指定每个实例连接池的最多连接。也就是说，标签内嵌套的writeHost、readHost标签都会使用这个属性的值来实例化出连接池的最多连接数。

    minCon 该属性指定每个读写实例连接池的最小连接，初始化连接池的大小。

    balance 属性，负载均衡类型，目前的值有3种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。

2. balance="1"，全部的readHost与stand by writeHost参与select询句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参不select语句的负载均衡。

3. balance="2"，所有读操作都随机的在writeHost、readhost上分发。

4. balance="3"，所有读请求随机的分发到wiriterHost对应的readhost执行，writerHost不负担读压力，注意balance=3

    writeType 属性

负载均衡类型，目前的取值有3种：

1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切换到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .

2. writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐使用。

    switchType 属性

-1 表示不自动切换

1 默认值，自动切换

2 基二MySQL主从同步的状态决定是否切换

    dbType 属性

指定后端连接数据库类型，目前支持二进制的mysql协议，还有其他使用JDBC连接的数数据库。例如：mongodb、oracle、spark等。

    dbDriver 属性

指定连接后端数据库使用的Driver，目前可选的值有native和JDBC。使用native的话，因为这个值执行的是二进制的mysql协议，所以可使用mysql和maridb。其他类型的数据库需要使用JDBC驱劢来支持。

如果使用JDBC的话需要将符合JDBC 4标准的驱动JAR包放刡MYCAT\lib目录下，并检查驱劢JAR包中包括如下目录结构的文件：META-INF\services\java.sql.Driver。在这个文件内写上具体的Driver类名，例如：com.mysql.jdbc.Driver。

switchType 属性

-1 表示丌自劢切换

1 默讣值，自劢切换

2 基二MySQL主仅同步癿状忏决定是否切换 心跳询句为 show slave status

3 基二MySQL galary cluster癿切换机刢（适吅集群）（1.4.1） 心跳询句为 show status like ‘wsrep%’.

tempReadHostAvailable 属性

如果配置了返个属忓writeHost 下面癿readHost仄旧可用，默讣0 可配置（0、1）。

heartbeat 标签

这个标签内指明于和后端数据库进行心跳检查的询句。例如,MYSQL可以使用select user()，Oracle可以使用select 1 from dual等。 这个标签还有一个connectionInitSql属性，主要是当使用Oracla数据库时，需要执行的初始化SQL语句就这个放到这里面来。例如：alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss' 1.4主从切换的语句必项是：show slave status
writeHost标签、readHost标签

这两个标签都指定后端数据库的相关配置给mycat，用与实例化后端连接池。唯一不同的是，writeHost指定写实例、readHost指定读实例，组装这些读写实例来满足系统的要求。

在一个dataHost内可以定义多个writeHost和readHost。但是，如果writeHost指定的后端数据库宕机，那么这个writeHost绑定的所有readHost都将不可用。另一方面，由于这个writeHost宕机系统会自动的检测到，并切换到备用的writeHost上去。 这两个标签的属性相同，这里就一起介绍。

host属性

用与标识不同实例，一般writeHost我们使用*M1，readHost我们用*S1。
url属性

后端实例连接地址，如果是使用native的dbDriver，则一般为address:port返种形式。用JDBC或其他的dbDriver，则需要特殊指定。当使用JDBC时则可以这么写：jdbc:mysql://localhost:3306/。

user属性

后端物理数据数据实例所需要的用户名

password属性

后端物理数据数据实例所需要的密码

weight 属性

权重 配置在readhost 中作为读节点的权重

usingDecrypt 属性

是否对密码加密默认0 否 如需要开启配置1，同时使用加密程序对密码加密，加密命令为： 执行mycat.jar 程序

 

生成加密username、password

Shell

1

java-cpMycat-server-1.4.1-dev.jarorg.opencloudb.util.DecryptUtil1:host:user:passwordMycat-server-1.4.1-dev.jar 为mycatdownload 下载目录的jar1:host:user:password 中 1 为db后加密标识，host为dataHost 的host 名称

server.xml配置文件

server.xml几乎保存了所有mycat需要的系统配置信息。

配置用户信息

Shell

1

2

3

4

5

6

7

<username="test">

    <propertyname="password">test</property>

    <propertyname="schemas">bokek</property>

    <propertyname="readOnly">true</property>

    <propertyname="benchmark">11111</property>

    <propertyname="usingDecrypt">1</property>

</user>

server.xml中的标签本就不多，这个标签主要用与定义登陆mycat的用户权限。例如上面的例子中，我定义了一个用户，用户名为test、密码也为test，可访问的schema也只有bokek一个。 如果我在schema.xml中定义了多个schema，那么这个用户是无法访问其他的schema。在mysql客户端看来则是无法使用use切换到其他的数据库。

如果使用了use命令，则mycat会报出这样的错误提示：

 

使用了user命令，错误提示

Shell

1

ERROR1044 (HY000):Access denied for user 'test' to database 'xxx'

这个标签嵌套的property标签则是具体声明的属性值，正如上面的例子。我们可以修改user标签的name属性来指定用户名；修改password内的文本来修改密码；修改readOnly为true 或false来限制用户是否叧是可读的；修改schemas内的文本来控制用户可访问的schema；修改schemas内的文本来控制用户可访问的schema，同时访问多个schema的话使用 , 隔开，例如:

 

配置多个逻辑库权限

1

<propertyname="schemas">bokek,bokek1,bokek2</property>

Benchmark属性

 

Shell

1

2

3

Benchmark:mycat连接服务降级处理：

benchmark 基准, 当前端的整体connection数达到基准值是, 对来自该账户的请求开始拒绝连接，0或不设表示不限制

例如 <propertyname="benchmark">1000</property>

usingDecrypt 属性

是否对密码加密默认0 否 如需要开启配置1，同时使用加密程序对密码加密，加密命令为： 执行mycat jar 程序：

 

加密密码

Shell

1

2

3

java-cpMycat-server-1.4.1-dev.jarorg.opencloudb.util.DecryptUtil0:user:password

Mycat-server-1.4.1-dev.jar 为mycatdownload 下载目录的jar

1:host:user:password 中 0 为前端加密标识

system标签

字符集设置：

配置charset

Shell

1

2

3

4

5

6

7

8

9

10

11

配置属性charset

<system>

<propertyname="charset">utf8</property>

</system>

如果需要配置utf8mb2等特殊字符集可以在

index_to_charset.properties 配置中 配置数据库短的字符集ID=字符集

例如： 224=utf8mb4

 

配置字符集的时候一定要坚持mycat的字符集与后端数据库的字符集是一致的，可通过变量来查询：

showvariables like 'collation_%';

showvariables like 'character_set_%';

defaultSqlParser属性

由与mycat最初的时候Foundation DB的sql解析器，而后来才添加Druid的解析器。所以这个属性用来指定默认的解析器。目前的可用的取值有：druidparser和 fdbparser。使用的时候可以选择其中的一种，目前一般都使用druidparser。 1.3 解析器默认为fdbparser，1.4 默认为druidparser，1.4以后fdbparser作废。

processors属性

这个属性主要用二进制系统可用的线程数，默认值为机器CPU核心线程数。 主要影响processorBufferPool、processorBufferLocalPercent、processorExecutor属性。NIOProcessor的个数也是由这个属性定义的，所以调优的时候可以适当的调高这个属性。

processorBufferChunk属性

这个属性指定每次分配Socket Direct Buffer的多小，默认是4096个字节。这个属性也影响buffer pool的长度。如果一次性获取的数过大buffer不够用 经常出现警告，则可以适当调大。

processorBufferPool属性

这个属性指定bufferPool计算 比例值。由于每次执行NIO读、写操作都需要使用到buffer，系统初始化的时候会建立一定长度的buffer池来加快读、写的效率，减少建立buffer的时间。

Mycat中有两个主要的buffer池:

BufferPool

ThreadLocalPool

BufferPool由ThreadLocalPool组合而成，每次仅BufferPool中获取buffer都会优先获取ThreadLocalPool中的buffer，未命中之后才会去获取BufferPool中的buffer。也就是该ThreadLocalPool是作为BufferPool的二级缓存，每个线程内部自己使用。当然，这其中还有一写限制条件需要线程的名字是由$_开头。然后，BufferPool上的buffer则是每个NIOProcessor都共享的。

默认这个属性的值为： 默认bufferChunkSize(4096) * processors属性 * 1000
BufferPool癿总长度 = bufferPool / bufferChunk。

若bufferPool不是bufferChunk的整数倍，则总长度为前面计算得出的商 + 1 假设系统xianc为4，其仈都为属忓癿默讣值，则： bufferPool ＝ 4096 * 4 * 1000 BufferPool癿总长度 : 4000 = 16384000 / 4096

processorBufferLocalPercent属性

前面提到了ThreadLocalPool。这个属性就是用来控制分配这个pool的大小用的，但其也并不是一个准确的值，也是一个比例值。返个属性默认值为100。

线程缓存百分比 = bufferLocalPercent / processors属性。

例如，系统可以同时运行4个线程，使用默认值，则根捤公式每个线程的百分比为25。

最后根据这个百分比来计算出具体的ThreadLocalPool的长度公式如下：

ThreadLocalPool癿长度 = 线程缓存百分比 * BufferPool长度 / 100

假设BufferPool的长度为 4000，其他保持默认值。

那么最后每个线程建立上的ThreadLocalPool癿长度为： 1000 = 25 * 4000 / 100

processorExecutor属性

这个属性主要用于指定NIOProcessor上共享的businessExecutor固定线程池大小。mycat在需要处理一些异步逻辑时候会把仸务提交到这个线程池中。新版本中这个连接池的使用频率不是特别大，可以设置一个较小的值。

sequnceHandlerType属性

指定使用Mycat全局序列类型。0为本地文件方式，1为数捤库方式，2为时间戳序列方式。

默认是使用本地文件方式0，文件使用方式主要用于测试使用。

TCP连接相关属性

StandardSocketOptions.SO_RCVBUF
StandardSocketOptions.SO_SNDBUF
StandardSocketOptions.TCP_NODELAY
以上这三个属性，分别由：

frontSocketSoRcvbuf 默认值： 1024 * 1024

frontSocketSoSndbuf 默认值： 4 * 1024 * 1024

frontSocketNoDelay  默认值： 1

backSocketSoRcvbuf 默认值： 4 * 1024 * 1024

backSocketSoSndbuf 默认值： 1024 * 1024

backSocketNoDelay   默认值： 1

各自设置TCP连接参数。Mycat在每次建立前、后端连接的时候都会使用这些参数初始化连接。可以按系统要求适当调整这些buffer的大小。TCP连接参数的定义，可以查看Javadoc。

Mysql连接相关属性

初始化mysql前后端连接所涉及到的一些属性：

packetHeaderSize : 指定Mysql协议中的报文头长度。默认4。

maxPacketSize : 指定Mysql协议可以携带数据的最大长度。默认16M。

idleTimeout : 指定连接的空闲超时时间。某连接在发起空闲检查下，发现距离上次使用超过了空闲时间，那么这个连接就会被回收，就是被直接的关闭掉。默认30分钟，单位毫秒。

charset : 连接的初始化字符集。默认为utf8。

txIsolation : 前端连接的初始化亊务隔离级别，叧在初始化的时候使用，后续会根据客户端传过来的属性对后端数据库连接进行同步。默认为REPEATED_READ，设置值为数字默认3。

READ_UNCOMMITTED = 1;

READ_COMMITTED = 2;

REPEATED_READ = 3;

SERIALIZABLE = 4;

sqlExecuteTimeout:SQL执行超时时间，Mycat会检查连接上最后一次执行SQL的时间，若超过这个时间则会直接关闭这个连接。默认时间为300秒，单位秒。

心跳属性

mycat中有几个周期性的任务来异步的处理一互我需要的工作。这些属性就在系统调优的过程中也是必不可少的。

processorCheckPeriod : 清理NIOProcessor上前后端空闲、超时和关闭连接的间隔时间。默认是1秒，单位毫秒。。

dataNodeIdleCheckPeriod : 对后端连接进行空闲、超时检查的时间间隔，默认是300秒，单位毫秒。

dataNodeHeartbeatPeriod : 对后端所有读、写库发起心跳的间隔时间，默认是10秒，单位毫秒。

服务相关属性

这里介绍一个与服务相关的属性，主要会影响外部系统对myact的感知。

bindIp : mycat服务监听的IP地址，默认值为0.0.0.0。

serverPort : 定义mycat癿使用竢口，默认值为8066。

managerPort : 定义mycat癿管理竢口，默认值为9066。