【数据库系列教程】Mycat读写分离

一、关于Mycat

Mycat是一个开源数据库中间件；它可以管理你的所有数据库，并对他们进行读写分离，分库分表等。
使用起来的话Mycat就是一个近似于MySQL的数据库服务器，你可以用连接MySQL的方式去连接Mycat(除了端口不同，默认的Mycat端口是8066而非MySQL的3306)

二、配置Mycat读写分离

1.配置jdk环境

[root@localhost ~]# wget https://mirrors.huaweicloud.com/java/jdk/8u191-b12/jdk-8u191-linux-x64.tar.gz

如果提示找不到wget命令请使用yum install -y wget安装！

[root@localhost ~]# tar xf jdk-8u191-linux-x64.tar.gz -C /usr/local
[root@localhost ~]# ls /usr/local
bin  etc  games  include  jdk1.8.0_191  lib  lib64  libexec  sbin  share  src

A.配置jdk环境

[root@localhost ~]# vim /etc/profile
export JAVA_HOME=/usr/local/jdk1.8.0_191
export PATH=$PATH:$JAVA_HOME/bin		#文件后面加上2行内容
#配置文件并不完整，请勿删改其他！

B.让配置文件生效，测试jdk环境

[root@localhost ~]# source /etc/profile
[root@localhost ~]# java -version
java version "1.8.0_191"
Java(TM) SE Runtime Environment (build 1.8.0_191-b12)
Java HotSpot(TM) 64-Bit Server VM (build 25.191-b12, mixed mode)

2.下载Mycat

自行寻找即可

3.解压Mycat

[root@localhost ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

4.准备MySQL主从复制环境

请观看MySQL主从复制教程
192.168.177.30主库
192.168.146.100从库

5.创建Mycat登录用户

mysql> grant all on *.* to 'root'@'%' identified by 'WWW.1.com';
Query OK, 0 rows affected, 1 warning (0.00 sec)

6.修改Mycat配置文件

配置文件并不完整

[root@localhost mycat]# vim conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

        <schema name="wordpress" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
                <!-- auto sharding by id (long) -->
                <table name="wp" primaryKey="id" dataNode="dn1" />
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="wordpress" />
        <!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
         <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
        <dataNode       name="jdbc_dn2" dataHost="jdbchost" database="db2" />
        <dataNode name="jdbc_dn3"       dataHost="jdbchost" database="db3" /> -->
        <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
                          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.146.100:3306" user="root"
                                   password="WWW.1.com">
                        <!-- can have multi read hosts -->
                </writeHost>
                <writeHost host="hostS1" url="192.168.177.30:3306" user="root"
                                   password="WWW.1.com" />

7.在server.xml文件定义Mycat用户

[root@localhost mycat]# vim conf/server.xml
 <user name="wsjj">
                <property name="password">WWW.1.com</property>
                <property name="schemas">wordpress</property>
        </user>

8.启动Mycat

[root@localhost mycat]# ./bin/mycat start

9.测试连接

mycat01

注意Mycat默认使用8066端口

mycat02