mycat+mysql集群:实现读写分离，分库分表

分手后的思念是犯贱 2022-08-18 13:29 273阅读 0赞

**1.mycat文档**：https://github.com/MyCATApache/Mycat-doc    
    **官方网站**：http://www.mycat.org.cn/  
**2.mycat的优点**：

*  配置简单，灵活
 *  可实现读写分离
 *  可利用多种规则实现分库分表
 *  心跳机制，自动踢出故障机组
 *  免费开源，长期维护，社区活跃

**mycat的缺点：**

*  主要是分片之后有一些限制，如只能2表join

**3.架构图：可实现读写分离，分库分表**  
![Center][]  
  
**4.所需软件，机器配置与ip地址**

<table> 
 <tbody> 
  <tr> 
   <td>名称</td> 
   <td>版本</td> 
   <td>下载地址</td> 
  </tr> 
  <tr> 
   <td>mysql server</td> 
   <td>mysql&nbsp; Ver 14.14 Distrib 5.6.27, for Linux (x86_64)</td> 
   <td>yum源安装，yum源地址：http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm</td> 
  </tr> 
  <tr> 
   <td>mycat</td> 
   <td>1.3.0.3</td> 
   <td>https://github.com/MyCATApache/Mycat-download/blob/master/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz</td> 
  </tr> 
  <tr> 
   <td>keepalived</td> 
   <td>&nbsp;</td> 
   <td>http://www.keepalived.org/download.html</td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td>机器名</td> 
   <td>&nbsp;ip地址</td> 
   <td>配置</td> 
   <td>用途</td> 
  </tr> 
  <tr> 
   <td>mycat1</td> 
   <td>192.168.2.155/192.168.10.30</td> 
   <td>4G,4C</td> 
   <td>mycat+keepalived (主)</td> 
  </tr> 
  <tr> 
   <td>mycat2</td> 
   <td>192.168.2.156/192.168.10.31</td> 
   <td>4G,4C</td> 
   <td>mycat+keepalived (备)</td> 
  </tr> 
  <tr> 
   <td>db1</td> 
   <td>192.168.10.155</td> 
   <td>8G,4C</td> 
   <td>mysql master1</td> 
  </tr> 
  <tr> 
   <td>db2</td> 
   <td>192.168.10.156</td> 
   <td>8G,4C</td> 
   <td>mysql master2</td> 
  </tr> 
  <tr> 
   <td>db3</td> 
   <td>192.168.10.157</td> 
   <td>8G,4C</td> 
   <td>mysql slave1</td> 
  </tr> 
 </tbody> 
</table>

**5.安装，配置mysql server**

*  获取官方yum源：  wget http://dev.mysql.com/get/mysql57-community-release-el6-7.noarch.rpm

*  安装源：yum install mysql57-community-release-el6-7.noarch.rpm

*  默认yum安装的是5.7,我们修改yum源，下载5.6的mysql server

*  进入yum源配置文件：cd /etc/yum.repos.d/

*  找到并编辑：vim mysql-community.repo

*  找到下面这些内容：enabled=1就是可用,把\[mysql56-community\]段的enabled=0改为enabled=1,相应的把\[mysql57-community\]段的改为0，保存退出

\# Enable to use MySQL 5.6  
                \[mysql56-community\]  
                name=MySQL 5.6 Community Server  
                baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/  
                enabled=0  
                gpgcheck=1  
                gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql  
                  
                \[mysql57-community\]  
                name=MySQL 5.7 Community Server  
                baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/  
                enabled=1  
                gpgcheck=1  
                gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

*  查找安装包（会更新源，时间几分钟，看你的网速咯）：yum list |grep mysql

*  如果顺利就会看到这一行：mysql-community-server.x86\_64              5.6.27-2.el6

*  没错就是它，安装：yum install -y mysql-community-server.x86\_6

*  启动（这一步会自动初始化一些内容）：service mysqld start

*  修改root密码：mysqladmin -uroot --password ‘xxxxx’

*  修改配置文件，我测试时候的配置文件：

*  **db1：**

> > \[mysqld\]  
> > datadir=/var/lib/mysql  
> > socket=/var/lib/mysql/mysql.sock  
> > character\_set\_server = utf8  
> >   
> > back\_log=1024  
> > max\_connections = 10000  
> > max\_connect\_errors = 1000  
> > read\_buffer\_size = 4M  
> > query\_cache\_size = 64M  
> > key\_buffer\_size=400M  
> > max\_allowed\_packet=128M  
> > innodb\_flush\_log\_at\_trx\_commit=0  
> > innodb\_log\_file\_size = 200M  
> > innodb\_log\_files\_in\_group = 3  
> > innodb\_buffer\_pool\_size = 2048M  
> > innodb\_log\_buffer\_size = 16M  
> > query\_cache\_size = 0  
> > \#init\_connect='SET autocommit=0'  
> > innodb\_lock\_wait\_timeout = 50  
> >   
> > \# Disabling symbolic-links is recommended to prevent assorted security risks  
> > symbolic-links=0  
> >   
> > \# Recommended in standard MySQL setup  
> > sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
> > \#replication setting  
> > server-id=1  
> > log-bin=mysql-bin  
> > relay-log=mysql-relay-bin  
> > binlog-do-db=33hao  
> > binlog-do-db=jaydb  
> > binlog-do-db=sbtest  
> > binlog-do-db=shopnc  
> > binlog-ignore-db=mysql  
> > binlog-ignore-db=performance\_schema  
> > binlog-ignore-db=information\_schema  
> > log-slave-updates=1  
> > master-info-repository=table  
> > relay-log-info-repository=table  
> > relay-log-recovery=1  
> > \#for mmm setting  
> > \#read\_only=1  
> > \#general log  
> > general\_log=1  
> > general\_log\_file=/var/log/mysql/mysql-row.log  
> > \#no lower\_case  
> > lower\_case\_table\_names = 1  
> > \#slow log  
> > slow\_query\_log=1  
> > slow\_query\_log\_file=/var/log/mysql/mysql-slow.log  
> > long\_query\_time=2  
> > log-queries-not-using-indexes  
> > \#default timestamp  
> > explicit\_defaults\_for\_timestamp=true  
> >   
> > \[mysqld\_safe\]  
> > log-error=/var/log/mysql/mysqld.log  
> > pid-file=/var/run/mysqld/mysqld.pid  
> >

*  **db2：**

> > \[mysqld\]  
> > datadir=/var/lib/mysql  
> > socket=/var/lib/mysql/mysql.sock  
> > character\_set\_server = utf8  
> >   
> > back\_log=1024  
> > max\_connections = 10000  
> > max\_connect\_errors = 1000  
> > read\_buffer\_size = 4M  
> > query\_cache\_size = 64M  
> > key\_buffer\_size=400M  
> > max\_allowed\_packet=128M  
> > innodb\_flush\_log\_at\_trx\_commit=0  
> > innodb\_log\_file\_size = 200M  
> > innodb\_log\_files\_in\_group = 3  
> > innodb\_buffer\_pool\_size = 2048M  
> > innodb\_log\_buffer\_size = 16M  
> > query\_cache\_size = 0  
> > \#init\_connect='SET autocommit=0'  
> > innodb\_lock\_wait\_timeout = 50  
> >   
> > \# Disabling symbolic-links is recommended to prevent assorted security risks  
> > symbolic-links=0  
> >   
> > \# Recommended in standard MySQL setup  
> > sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
> > \#replication setting  
> > server-id=2  
> > log-bin=mysql-bin  
> > relay-log=mysql-relay-bin  
> > binlog-do-db=33hao  
> > binlog-do-db=jaydb  
> > binlog-do-db=sbtest  
> > binlog-do-db=shopnc  
> > binlog-ignore-db=mysql  
> > binlog-ignore-db=performance\_schema  
> > binlog-ignore-db=information\_schema  
> > log\_slave\_updates=1  
> > master-info-repository=table  
> > relay-log-info-repository=table  
> > relay-log-recovery=1  
> > \#for mmm setting  
> > \#read\_only=1  
> > \#general log  
> > general\_log=1  
> > general\_log\_file=/var/log/mysql/mysql-row.log  
> > \#no lower\_case  
> > lower\_case\_table\_names = 1  
> > \#slow log  
> > slow\_query\_log=1  
> > slow\_query\_log\_file=/var/log/mysql/mysql-slow.log  
> > long\_query\_time=2  
> > log-queries-not-using-indexes  
> > \#default timestamp  
> > explicit\_defaults\_for\_timestamp=true  
> >   
> >   
> > \[mysqld\_safe\]  
> > log-error=/var/log/mysql/mysqld.log  
> > pid-file=/var/run/mysqld/mysqld.pid  
> >

*  **db3：  **

> > \[mysqld\]  
> > datadir=/var/lib/mysql  
> > socket=/var/lib/mysql/mysql.sock  
> > character\_set\_server = utf8  
> >   
> > back\_log=1024  
> > max\_connections = 10000  
> > max\_connect\_errors = 1000  
> > read\_buffer\_size = 4M  
> > query\_cache\_size = 64M  
> > key\_buffer\_size=400M  
> > max\_allowed\_packet=128M  
> > innodb\_flush\_log\_at\_trx\_commit=0  
> > innodb\_log\_file\_size = 200M  
> > innodb\_log\_files\_in\_group = 3  
> > innodb\_buffer\_pool\_size = 2048M  
> > innodb\_log\_buffer\_size = 16M  
> > query\_cache\_size = 0  
> > \#init\_connect='SET autocommit=0'  
> > innodb\_lock\_wait\_timeout = 50  
> >   
> > \# Disabling symbolic-links is recommended to prevent assorted security risks  
> > symbolic-links=0  
> >   
> > \# Recommended in standard MySQL setup  
> > sql\_mode=NO\_ENGINE\_SUBSTITUTION,STRICT\_TRANS\_TABLES  
> > \#replication setting  
> > server-id=3  
> > \#log-bin=mysql-bin  
> > relay-log=mysql-relay-bin  
> > binlog-do-db=33hao  
> > binlog-do-db=jaydb  
> > binlog-do-db=sbtest  
> > binlog-do-db=shopnc  
> > binlog-ignore-db=mysql  
> > binlog-ignore-db=performance\_schema  
> > binlog-ignore-db=information\_schema  
> > master-info-repository=table  
> > relay-log-info-repository=table  
> > relay-log-recovery=1  
> > \#for mmm setting  
> > \#read\_only=1  
> > \#general log  
> > general\_log=1  
> > general\_log\_file=/var/log/mysql/mysql-row.log  
> > \#no lower\_case  
> > lower\_case\_table\_names = 1  
> > \#slow log  
> > slow\_query\_log=1  
> > slow\_query\_log\_file=/var/log/mysql/mysql-slow.log  
> > long\_query\_time=2  
> > log-queries-not-using-indexes  
> > \#default timestamp  
> > explicit\_defaults\_for\_timestamp=true  
> >   
> >   
> > \[mysqld\_safe\]  
> > log-error=/var/log/mysql/mysqld.log  
> > pid-file=/var/run/mysqld/mysqld.pid  
> >

>       
>

**6.从最上面的架构图中可以看出，这里只有一个主主（db1,db2），一个主从（db1,db3），下面分别配置主主，主从**

**7.mysql主主复制配置**

> (1)分别编辑db1，db2配置文件，把第5步相应的内容复制到/etc/my.cnf中  
> (2)接下来手动同步数据  
> (3)mysql>FLUSH TABLES WITH READ LOCK  
> (4)不要退出终端，另外开一个，用mysqldump工具导出所有数据库数据，到db2数据库中  
> (5)在db1(192.168.10.155)的mysql上授权：grant replication slave on \*.\* to 'repl\_user'@'192.168.10.156' identified by 'repl\_password';  
> (6)查看db1的master状态，设置从(db2机器的mysql)的时候需要  
>

>                 mysql> show master status;  
>                 +------------------+----------+--------------+------------------+-------------------+  
>                 | File             | Position | Binlog\_Do\_DB | Binlog\_Ignore\_DB | Executed\_Gtid\_Set |  
>                 +------------------+----------+--------------+------------------+-------------------+  
>                 | mysql-bin.000001 |      336 |              |                  |                   |  
>                 +------------------+----------+--------------+------------------+-------------------+  
> (7)在db2中设置master参数,(master\_log\_file,master\_log\_pos,都来自上一步)  
>                 mysql> change master to master\_host='192.168.10.156', master\_user='repl\_user',master\_password='repl\_passwd',master\_log\_file='mysql-bin.000001',master\_log\_pos=336;  
> (8)启动db2从的复制  
>                 mysql>slave start;  
> (9)查看从的状态  
>                 mysql>show slave status\\G;  
>                 Slave\_IO\_Running: Yes  
>                 Slave\_SQL\_Running: Yes  
>                 Seconds\_Behind\_Master: 0  
>                 看到这几行说明复制成功。  
> (10)把db1设置为db2的从  
>                 方法同上，重复(5)-(9),只是把db1和db2，相应的IP地址互换即可，注意重复第(7)的时候master\_log\_file,master\_log\_pos是db2的master status了。  
>

**8.把db3(192.168.10.157)设置为db1的从**

> 方法通与7步类似，重复其中的(1)-(7)步，只是把db2和192.168.10.156换成db3和192.168.10.157。  
>

**9.至此，mysql的设置全部完成。**

**10.下载，安装mycat**

*  wget https://github.com/MyCATApache/Mycat-download/blob/master/1.3.0.3-release/Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz

*  tar -zxf Mycat-server-1.3.0.3-release-20150527095523-linux.tar.gz

*  解压完成就可以使用，建议把解压后的文件夹放在/opt下，并建立mycat的软连接（ln -s “解压出来的文件夹” mycat），方便以后升级，维护。

**11.配置mycat/conf/schema.xml**

> (1)在这里，我对33hao数据库实现读写分离  
> (2)对sbtest数据库所有表实现，读写不分离（当然，也可以针对某一张表，这种设置应对读写实时要求很高的情况，即单表或单库读写不分离）  
> (3)对transdb数据库的表实现分片，users表不分片（type="global"）提高join效率，orders表，用id通过mod-log规则分片（primaryKey="id" rule="mod-long"）  
>

<?xml version="1.0"?>  
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">  
<mycat:schema xmlns:mycat="http://org.opencloudb/">  
  
  
    <schema name="33hao" checkSQLschema="false" dataNode="dn1">  
    </schema>  
    <schema name="sbtest" checkSQLschema="false" dataNode="dn2">  
    </schema>  
    <schema name="jaydb" checkSQLschema="false" dataNode="dn3">  
    </schema>  
    <schema name="transdb" checkSQLschema="false">  
        <table name="users" primaryKey="id" type="global" dataNode="dn4,dn5,dn6" />  
  
                  
            <table name="orders" primaryKey="id" rule="mod-long" dataNode="dn4,dn5,dn6" />  
    </schema>  
    <schema name="shopnc" checkSQLschema="false" dataNode="dn7">  
    </schema>  
    <dataNode name="dn1" dataHost="localhost1" database="33hao" />  
    <dataNode name="dn2" dataHost="localhost2" database="sbtest" />  
    <dataNode name="dn3" dataHost="localhost1" database="jaydb" />  
    <dataNode name="dn4" dataHost="localhost4" database="transdb" />  
    <dataNode name="dn5" dataHost="localhost5" database="transdb" />  
    <dataNode name="dn6" dataHost="localhost6" database="transdb" />  
    <dataNode name="dn7" dataHost="localhost1" database="shopnc" />  
  
    <dataHost name="localhost1" maxCon="100000" minCon="10" balance="1"  
        writeType="0" dbType="mysql" dbDriver="native">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostM1" url="db1:3306" user="root"  
            password="123456">  
              
            <readHost host="hostS1" url="db3:3306" user="root" password="123456"/>  
            <readHost host="hostS2" url="db2:3306" user="root" password="123456"/>  
        </writeHost>  
        <writeHost host="hostM2" url="db2:3306" user="root"  
            password="123456">  
              
        </writeHost>  
          
    </dataHost>  
    <dataHost name="localhost2" maxCon="100000" minCon="10" balance="1"  
        writeType="0" dbType="mysql" dbDriver="native">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostM1" url="db1:3306" user="root"  
            password="123456">  
        </writeHost>  
    </dataHost>  
    <dataHost name="localhost4" maxCon="100000" minCon="10" balance="0"  
        writeType="0" dbType="mysql" dbDriver="native">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostM1" url="db1:3306" user="root" password="123456"></writeHost>  
    </dataHost>  
    <dataHost name="localhost5" maxCon="100000" minCon="10" balance="0"  
        writeType="0" dbType="mysql" dbDriver="native">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostM2" url="db2:3306" user="root" password="123456"></writeHost>  
    </dataHost>  
    <dataHost name="localhost6" maxCon="100000" minCon="10" balance="0"  
        writeType="0" dbType="mysql" dbDriver="native">  
        <heartbeat>select user()</heartbeat>  
          
        <writeHost host="hostS1" url="db3:3306" user="root" password="123456"></writeHost>  
    </dataHost>  
  
**12.设置mycat/conf/server.xml**

*  这里是针对mycat的设置，如外部访问mycat的端口，用户名，密码，mycat的数据库有哪些等

<?xml version="1.0" encoding="UTF-8"?>  
  
<!DOCTYPE mycat:server SYSTEM "server.dtd">  
<mycat:server xmlns:mycat="http://org.opencloudb/">  
        <system>  
        <property name="defaultSqlParser">druidparser</property>  
              <property name="processors">32</property>  
              <property name="processorExecutor">32</property>  
              <property name="serverPort">3306</property>  
              <property name="managerPort">9066</property>  
  
        </system>  
        <user name="root">  
                <property name="password">123456</property>  
                <property name="schemas">33hao,sbtest,jaydb,transdb,shopnc</property>  
        </user>  
</mycat:server>  
**13.启动mycat**

*  /opt/mycat/bin/mycat start

**14.连接mycat**

*  和连接mysql方法一样，如果连接不上，看看是否在没个节点上都授予了mycat机器通过root/123456（在schema.xml中配置的）的方式访问：
 *  mysql -uroot -p123456 -h127.0.0.1 -P3306

15.测试mycat

*  我开起了general\_log，数据库任何操作都会被记录，依次打开三个db1 db2 db3的日志：tail -f /var/log/mysql/mysql-row.log
 *  透过mycat连接数据库 mysql -uroot -p123456 -h127.0.0.1 -P3306
 *  在相应的数据库中执行操作，并观察日志。
 *  比如对33hao的写总是在db1上，然后replication到db2，db3，如果db1宕机，写总是在db2上，db1恢复后，db2不会被抢占写的角色。读在db2，db3上随机执行。
 *  而transdb的orders表被均匀地复制到db1，db2，db3的transdb的orders表中，users则在所有mysql（这里为db1,db2,db3）中保存副本（因为在**schema.xml**中设置为：type="global"）。

[Center]: /images/20220731/8a62dc0105824271a9c3cfb2e90b0e0c.png