MyCat实现MySQL读写分离

首先来准备环境：

system: CentOS 7.5

MyCat:
192.168.0.11   mycat

MySQL:
192.168.0.12  master
192.168.0.13  slave

MySQL版本：5.7

安装MyCat：

部署mycat环境：
[root@mycat ~]# tar xf jdk-8u181-linux-x64.tar -C /usr/local/ 
[root@mycat ~]# mv /usr/local/jdk1.8.0_181 /usr/local/java
[root@mycat ~]# vim /etc/profile 
export JAVA_HOME=/usr/local/java 
export PATH=$JAVA_HOME/bin:$PATH 
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar
[root@mycat ~]# java -version 
java version "1.8.0_181" 
Java(TM) SE Runtime Environment (build 1.8.0_181-b13) 
Java HotSpot(TM) 64-Bit Server VM (build 25.181-b13, mixed mode)

安装mycat：
[root@mycat ~]# wget http://dl.mycat.io/1.6.5/Mycat-server-1.6.5-release20180122220033-linux.tar.gz 
[root@mycat ~]# tar xf Mycat-server-1.6.5-release-20180122220033linux.tar.gz -C /usr/local/
[root@mycat mycat]# vim /etc/profile 
export MYCAT_HOME=/usr/local/mycat 
[root@mycat mycat]# source /etc/profile 
[root@mycat mycat]# ln -s /usr/local/mycat/bin/mycat /usr/bin/mycat

修改MyCat配置文件：
#修改mycat配置文件时用到的数据库在下面的mysql读写分离中
#切记一定要分清主库、从库和逻辑数据库

[root@mycat ~]# vim /usr/local/mycat/conf/server.xml
<?xml version="1.0" encoding="UTF-8"?> 
<!DOCTYPE mycat:server SYSTEM "server.dtd"> 
<mycat:server xmlns:mycat="http://io.mycat/">

        <system>
        <property name="defaultSqlParser">druidparser</property>

        </system>
<user name="replication" defaultAccount="true">    #replication是授权的用户
                <property name="password">#Password#</property>    # #Password#是授权的密码
                <property name="schemas">(逻辑数据库名)</property>   #逻辑数据库写虚拟库名

        </user>

        <user name="user">
                <property name="password">#Password#</property>    #Password#授权密码
                <property name="schemas">(逻辑数据库名)</property>
                <property name="readOnly">true</property>
        </user>

</mycat:server>

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

        <schema name="(逻辑数据库名)" checkSQLschema="false"  sqlMaxLimit="100" dataNode="dn1">    #( 逻辑数据库名)写虚拟数据库名  
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="后端数据库名称" />
        <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="hostM1" url="192.168.0.12:3306" user="chen"#后端写入数据库ip端口和用户
                                   password="#(Password)#"> #密码
                        <!-- can have multi read hosts -->
                        <readHost host="hostS2" url="192.168.0.13:3306" user="chen" password="#(Password)#" />   #另一台数据库IP端口和用户、密码
                </writeHost>
        </dataHost>
</mycat:schema>

[root@mycat ~]# vim /usr/local/mycat/conf/wrapper.conf 
wrapper.java.command=%JAVA_HOME%/bin/java
[root@mycat ~]# /usr/local/mycat/bin/mycat start

image.png

若起不来如图修改配置文件wrapper.conf

/usr/local/mycat/bin/mycat console #若显示successfully 则配置成功
查看端口 ss -anptu | grep : 8066

image.png

#连接MyCat主机
mysql -ureplication -p1qaz2WSX -P8066 -h127.0.0.1
#连接成功说明，配置MyCat 完毕

MySQL主从复制：

下载安装地址:https://dev.mysql.com/get/mysql80-community-release-el71.noarch.rpm
rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el71.noarch.rpm
yum -y install mysql-community-server
systemctl start mysqld && systemctl enable mysqlld

主库设置：
[root@master ~]# vi /etc/my.cnf 
[mysqld]
server-id=12
log-bin=mysql-bin
[root@master ~]# systemctl start mysqld && systemctl enable mysqld 
[root@master ~]# grep 'password' /var/log/mysqld.log  #使用初始密码登陆
注意修改密码

[root@master ~]# mysql -uroot -p'1qaz2WSX#'
mysql> CREATE USER 'replication'@'192.168.0.13' IDENTIFIED BY "1qaz2WSX#";
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'replication'@'192.168.0.13'; 
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW MASTER STATUS;
+------------------+----------+--------------+------------------+------------------+
 | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
 +------------------+----------+--------------+------------------+------------------+ 
| mysql-bin.000002 |      877 |              |                  |                   | 
+------------------+----------+--------------+------------------+------------------+
 1 row in set (0.00 sec)
主库设置完毕

从库设置：
[root@slave ~]# vi /etc/my.cnf 
[mysqld] 
server_id=13

[root@slave ~]# systemctl start mysqld && systemctl enable mysqld 
[root@slave ~]# grep 'password' /var/log/mysqld.log
注意修改密码
[root@slave ~]# mysql -uroot -p'1qaz2WSX#'

mysql> CHANGE MASTER TO
    -> MASTER_HOST='192.168.0.12',
    -> MASTER_USER='replication',
    -> MASTER_PASSWORD='1qaz2WSX#',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=877;
mysql> start slave;
mysql> show slave status \G  #查询slave状态
当IO和SQL都处于YES状态，说明已经成功实现了主从复制
            Slave_IO_Running: Yes
            Slave_SQL_Running: Yes 

开始测试吧

感谢 @bavdu 提供技术支持！