mycat实现简单的mysql读写分离

什么是mycat呢？

简单理解为一个MySQL中间件，它支持分流、基于心跳的自动故障切换，支持读写分离，支持mysql主从，基于Nio管理线程的高并发… 
详见官网：http://www.mycat.io/ 

为什么需要mysql集群？

一个庞大的分布式系统的性能瓶颈中，最脆弱的就是连接，一个是客户端与后端的连接，另一个是后端与数据库的连接，说白了就是发送端请求太多，接收端能够的接收和处理的请求并不多，在客户端与后端中可以利用类似nginx的负载均衡解决，而在后端与数据库中可以利用类似mycat的负载均衡实现mysql集群，提高mysql的总体性能。

开始我们的旅程，在此之前，需要准备以下：

1 两台服务器(最少),一主（master）一从（slave），这里用vmware里面装的两个ubuntu server 虚拟机演示，每台虚拟机的网络连接方式都设置为桥接模式，两台虚拟机的ip如下

   主：ubuntu server1: 192.168.11.109
   从：ubuntu server2: 192.168.11.117
 
 
1
2
 
 
1
2

（vmware安装ubuntu server16.04在上两篇博文有详细介绍: 
http://blog.csdn.net/change_on/article/details/74969314 
http://blog.csdn.net/change_on/article/details/74979993）

2 虚拟机分别安装好mysql 5.7

3 jdk1.7以上，这里以jdk-7u67-Linux-x64.tar.gz为例

4 mycat，以Mycat-server-1.6-RELEASE-20161012170031-linux.tar.gz为例

搭建mycat+mysql集群的步骤

• 1 mysql主从配置

  • 1.1 主虚拟机配置（192.168.11.109）

  • 1.2 从虚拟机配置（192.168.11.117）

  • 1.3 测试

• 2 配置mycat

  • 2.1 安装jdk

  • 2.2 安装mycat

  • 2.3 配置server.xml

  • 2.4 配置schema.xml

  • 2.5 测试

mysql主从配置

注意 ：mysql5.7的配置文件是 /etc/mysql/mysql.conf.d/mysqld.cnf ！不是/etc/mysql/my.cnf !

• 1主虚拟机配置（192.168.11.109）

  • 1.1 修改/etc/mysql/mysql.conf.d/mysqld.cnf文件

    • 1.1.1 在[mysqld]下添加以下

        vi /etc/mysql/mysql.conf.d/mysqld.cnf
        character_set_server = utf8
        init_connect = 'SET NAMES utf8'
        log-bin=mysql-bin
        server-id=109
 
 
1
2
3
4
5
 
 
1
2
3
4
5

• • 1.2 在[mysqld]下注释掉bind-address = 127.0.0.1 
    
     
    
    

  • 1.3 配置master

    • 1.3.1 进入mysql，创建一个具有slave复制权限的用户wen

     GRANT REPLICATION SLAVE ON *.* to 'wen'@'%' identified by 'wen';
         
         
    1
         
         
    1

    • 1.3.2 刷新权限

    flush privileges; 
         
         
    1
         
         
    1

    • 1.3.3 显示master

    show master status;
         
         
    1
         
         
    1

• 2 从虚拟机配置（192.168.11.117）

  • 2.1 修改/etc/mysql/mysql.conf.d/mysqld.cnf文件

    • 2.1.1 在[mysqld]下添加以下

        vi /etc/mysql/mysql.conf.d/mysqld.cnf
        character_set_server = utf8
        init_connect = 'SET NAMES utf8'
        log-bin=mysql-bin
        server-id=117
 
 
1
2
3
4
5
 
 
1
2
3
4
5

• • 2.2 在[mysqld]下注释掉bind-address = 127.0.0.1

  • 2.3 配置slave

    • 2.3.1 进入mysql,重置slave

    这里的 master_log_file 和 master_log_pos 在主虚拟机的 mysql 的 master 中，就是上面的图所示！

      stop slave;
      reset slave;
      change master to           master_host='192.168.11.109',master_user='wen',master_password='wen',master_log_file='mysql-bin.000013',master_log_pos=1609;
     
     
  1
  2
  3
     
     
  1
  2
  3

  • • 2.3.2 启动slave

       start slave; 
     
     
  1
     
     
  1

  • • 2.2.3 显示slave

      其中的Slave_IO_Running 和 Slave_SQL_Running 一定要 Yes 才表示slave启动正确！

       show slave status \G
     
     
  1
     
     
  1

• • 2.4 测试

  在master虚拟机中创建一个数据库，然后再slave虚拟机中查看，如果，slave中有刚刚测试的数据库，说明mysql主从配置成功。

  主： 
  
  

从：

配置mycat（在master虚拟机中配置）

jdk与mycat的压缩包都放在/opt目录下

• 1 安装jdk

  mycat依赖于java1.7以上的环境，先安装jdk1.7

  解压jdk

  tar -zxvf jdk-7u67-linux-x64.tar.gz
  

  配置环境变量

  vi /etc/profile
  

  在末尾添加

     # set for java
     export JAVA_HOME=/opt/jdk1.7.0_67
     export JRE_HOME=$JAVA_HOME/jre
     export CLASSPATH=.:$CLASSPATH:$JAVA_HOME/lib:$JRE_HOME/lib
     export PATH=$PATH:$JAVA_HOME/bin:$JRE_HOME/bin
 
 
1
2
3
4
5
 
 
1
2
3
4
5

保存退出

• 2 安装 mycat

  解压mycat

      tar -zxvf Mycat-server-1.6-RELEASE-20161012170031-linux.tar.gz
  

  配置环境变量

       vi /etc/profile
  

  在末尾添加

       export MYCAT_HOME=/opt/mycat
       export PATH=$PATH:$MYCAT_HOME/bin
  

  保存退出

  使环境变量生效

       source /etc/profile
  

  
  

  

  
  

• 3.配置server.xml

  到 mycat 的 conf 目录下，修改 server.xml 文件添加 test 和user 用户，test 用户，密码也为 test，user 用户，密码也为user

       <user name="test">
                <property name="password">test</property>
                <property name="schemas">TESTDB</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 name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
           </user>
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

• 4.配置schema.xml

  设置读和写服务器的ip与端口，以及集群策略。这里的 schema 的 name 要与 server.xml 的 schema 保持一致， database 对应的是 mysql 里面已经存在的数据库，也就是说，mycat 的 TESTDB 代理了 主/从虚拟机的 mysql 的 test 数据库

  <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
  </schema>

  <dataNode name="dn1" dataHost="master" database="test" />

  <dataHost name="master" maxCon="1000" minCon="10" balance="1" writeType="0"     dbType="mysql" dbDriver="native">
             <heartbeat>show slave status</heartbeat>
             <writeHost host="host2" url="192.168.11.109:3306" user="root" password="root" />
            <writeHost host="host4" url="192.168.11.117:3306" user="root" password="root" />
</dataHost>
 
 
1
2
3
4
5
6
7
8
9
10
 
 
1
2
3
4
5
6
7
8
9
10

show slave status 表示一种集群策略，只适用在一主一从的环境中，当主 down 掉， 从可以充当主和从

balance属性

balance=”0”, 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上 
balance=”1”，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡 
balance=”2”，所有读操作都随机的在 writeHost、 readhost 上分发。 
balance=”3”， 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力

writeType 属性

负载均衡类型，目前的取值有 3 种： 
writeType=”0”, 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost， 重新启动后已切换后的 为准，切换记录在配置文件中:dnindex.properties 
writeType=”1”，所有写操作都随机的发送到配置的 writeHost

• 5 启动mycat

 cd /opt/mycat/bin
 ./mycat start
 
 
1
2
 
 
1
2

补充：mycat的常用命令

关闭

mycat stop

重启

mycat restart

查看mycat状态

mycat status

• 6 测试

现在 mycat 可以代理 mysql 了，用 navicat 连接 mycat（8066端口）

往数据库里面添加一条数据 wen

现在到主虚拟机用命令行查看数据库，看看刚在 navicat 写进 mycat 的数据有没有显示出来

用 mycat 的账号密码登录到主虚拟机的 mycat，查看并添加一条数据 tom

mysql -utest -ptest -h127.0.0.1 -P8066
 
 
1
 
 
1

再切到从虚拟机，用 mysql -uroot -p 登录 mysql，看看刚刚在主虚拟机的 mycat 添加的数据 tom 在从虚拟机能不能看到

如果想看看读写分离的细节，可以到 logs 目录下查看日志

/opt/mycat/logs/
tail -f mycat.log
 
 
1
2
 
 
1
2

———————————————————分割线—————————————————————-

mysql的问题其实是由于一系列的软肋决定的，所以不得不利用中间件或者其它方案去解决，包括：

1. 在强制约束和事务与全文索引之间做出选择(InnoDb vs MyISAM)

2. 在客户机代码中“模拟”事务是不容易的

3. 如果不执行约束，就很容易得到不一致的db状态

4. 如果没有全文搜索，会变得疯狂，比如% y %

5. 必须在更新触发器之前创建检查约束的错误

6. 当数据变得太大时，Mysql的承受能力就不妙了

7. Mysql创建的执行计划效率低下

8. Mysql有超过多个连接的问题(最好说多个连接)