用mycat实现MySQL的读写分离

使用mycat来进行mysql的读写分离
实验思路：
1.需要搭建好主从环境
2.准备好mycat数据包 和 依赖包（jbk）
环境：192.168.100.101 master
192.168.100.102 slave1
192.168.100.103 slave2
192.168.100.104 mycat ------->准备安装包和依赖包
步骤：
1.在101(master)上
授权： mysql> grant replication slave on . to repl@‘192.168.100.%’ identified by ‘123456’;
添加日志：[root@localhost ~]# vim /etc/my.cnf
log-bin=mysql-log-master
server-id=1
重启：[root@localhost ~]# /etc/init.d/mysqld restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!
2.在102（slave1）上
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> change master to master_host=‘192.168.100.101’,master_user=‘repl’,master_password=‘123456’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看状态：mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-log-master.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 381
Relay_Master_Log_File: mysql-log-master.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3.在103（slave2）上：
mysql> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql>
mysql> change master to master_host=‘192.168.100.101’,master_user=‘repl’,master_password=‘123456’;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql>
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)
查看状态：
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-log-master.000001
Read_Master_Log_Pos: 154
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 381
Relay_Master_Log_File: mysql-log-master.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4.在104（mycat）上：
解包： [root@mycat ~]# tar -zxvf Mycat-server-1.6.6.1-release-20181031195535-linux.tar.gz -C /usr/local/
创建mycat用户：
[root@mycat ~]# useradd mycat
Mycat 需要安装JDK 1.7 或者以上版本：
[root@mycat ~]# tar -zxvf jdk-8u171-linux-x64.tar.gz -C /usr/local/
[root@mycat ~]# vim /etc/profile ----------------->>>>>>在文件最后加入一下行
JAVA_HOME=/usr/local/jdk1.8.0_171
PATH=$JAV{A}_{H}OME/bin:$PATH
CLASSPATH=$JAV{A}_{H}OME/jre/lib/ext:$JAVA_HOME/lib/tools.jar
export PATH JAVA_HOME CLASSPATH
[root@cong11 ~]# source /etc/profile ---------->>>>使环境变量生效
查看java环境：
[root@mycat ~]# java -version
java version “1.8.0_171”
Java™ SE Runtime Environment (build 1.8.0_171-b11)
Java HotSpot™ 64-Bit Server VM (build 25.171-b11, mixed mode)
添加为系统服务：
[root@mycat~]# ln -s /usr/local/mycat/bin/* /usr/local/bin/
配置hosts文件
[root@mycat ~]# vim /etc/hosts
192.168.100.101 master
192.168.100.102 slave1
192.168.100.103 slave2
192.168.100.104 mycat
：
配置mycat用户账号和授权信息
[root@mycat ~]# vim /usr/local/mycat/conf/server.xml

123456 books

            <!-- 表级 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="lisi">
            <property name="password">123456</property>
            <property name="schemas">books</property>
            <property name="readOnly">true</property>
    </user>

编辑MyCAT的配置文件schema.xml
[root@mycat ~]# vim /usr/local/mycat/conf/schema.xml

<?xml version="1.0" encoding="UTF-8"?>

<mycat:schema xmlns:mycat=“http://io.mycat/”>

 <schema name="books" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1" />
 <dataNode name="dn1" dataHost="localhost1" database="books" />
 <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" 
     dbDriver="native" switchType="-1"  slaveThreshold="100">
     <heartbeat>select user()</heartbeat>
     <!-- can have multi write hosts -->
     <writeHost host="master" url="192.168.1.101:3306" user="zhangsan" password="123456">
     </writeHost>
     <writeHost host="slave1" url="192.168.1.102:3306" user="zhangsan" password="123456">
     </writeHost>
     <writeHost host="slave2" url="192.168.1.103:3306" user="zhangsan" password="123456">
     </writeHost>
 </dataHost>

</mycat:schema>

在master（101）上给所有mysql root远程登陆权限并刷新
mysql> grant all on . to root@‘192.168.1.%’ identified by ‘123456’;
mysql>flush privileges；

启动mycat服务
[root@mycat ~]# mycat console --------------->>>>>>>使用console进行检测

Running Mycat-server…
wrapper | --> Wrapper Started as Console
wrapper | Launching a JVM…
jvm 1 | Java HotSpot™ 64-Bit Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0
jvm 1 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
jvm 1 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
jvm 1 |
jvm 1 | MyCAT Server startup successfully. see logs in logs/mycat.log
检测成功，执行开启
[root@mycat]# mycat start
[root@slave2 ~]# mysql -uzhangsan -p123456 -P8066 -h192.168.100.104—>>这个地址是mycat的地址
连接成功