MyCAT中间件服务实现读写分离

MyCAT中间件服务实现读写分离

实验架构

 

 

系统环境：

cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

服务器地址：

Mycat：192.168.118.189

Mysql_Master:192.168.118.25

Mysql_Master:192.168.118.43

Mysql主从环境搭建

1.安装mysql数据库

 yum install mariadb-server -y

2.修改master与slave的配置文件

vim /etc/my.cnf
#Master
[mysqld]
server-id=25
log-bin
#Slave
[mysqld]
server-id=43
​
#配置完成后重启数据库
systemctl restart mariadb

3.Master上创建复制用户

[root@master ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)
​
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      245 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
​
MariaDB [(none)]> grant replication slave on *.* to repluser@'192.168.118.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
​
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
​
MariaDB [(none)]> show master status;
+--------------------+----------+--------------+------------------+
| File               | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------------+----------+--------------+------------------+
| mariadb-bin.000001 |      476 |              |                  |
+--------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
​
​

4.Slave上执行

[root@slave ~]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 2
Server version: 5.5.68-MariaDB MariaDB Server
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
MariaDB [(none)]> change master to MASTER_HOST='192.168.118.25',MASTER_USER='repluser',MASTER_PASSWORD='123456',MASTER_PORT=3306,MASTER_LOG_FILE='mariadb-bin.000001',MASTER_LOG_POS=245;
Query OK, 0 rows affected (0.01 sec)
​
MariaDB [(none)]>
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.00 sec)
​
MariaDB [(none)]> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.118.25
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mariadb-bin.000001
          Read_Master_Log_Pos: 476
               Relay_Log_File: mariadb-relay-bin.000002
                Relay_Log_Pos: 762
        Relay_Master_Log_File: mariadb-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 476
              Relay_Log_Space: 1058
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 25
1 row in set (0.00 sec)
​
​

在Mysql代理服务器上安装Mycat

1.安装mycat

[root@mycat ~]# yum install -y java mariadb
#确认安装成功
[root@mycat ~]# java -version
openjdk version "1.8.0_302"
OpenJDK Runtime Environment (build 1.8.0_302-b08)
OpenJDK 64-Bit Server VM (build 25.302-b08, mixed mode)
#下载mycat并安装
[root@mycat ~]# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
--2021-09-02 04:37:22--  http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
Resolving dl.mycat.org.cn (dl.mycat.org.cn)... 210.51.26.184
Connecting to dl.mycat.org.cn (dl.mycat.org.cn)|210.51.26.184|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 21760812 (21M) [application/octet-stream]
Saving to: ‘Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz’
​
100%[=============================================================================================>] 21,760,812  2.10MB/s   in 8.9s
​
2021-09-02 04:37:31 (2.33 MB/s) - ‘Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz’ saved [21760812/21760812]
​
[root@mycat ~]# mkdir /apps
[root@mycat ~]# tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
mycat/bin/mycat
mycat/bin/wrapper-linux-ppc-64
mycat/bin/wrapper-linux-x86-32
mycat/bin/wrapper-linux-x86-64
mycat/lib/annotations-13.0.jar
mycat/lib/asm-4.0.jar
mycat/lib/commons-collections-3.2.1.jar
mycat/lib/commons-lang-2.6.jar
mycat/lib/curator-client-2.11.0.jar
mycat/lib/curator-framework-2.11.0.jar
mycat/lib/curator-recipes-2.11.0.jar
mycat/lib/disruptor-3.3.4.jar
mycat/lib/dom4j-1.6.1.jar
mycat/lib/druid-1.0.26.jar
mycat/lib/ehcache-core-2.6.11.jar
mycat/lib/fastjson-1.2.58.jar
mycat/lib/guava-19.0.jar
mycat/lib/hamcrest-core-1.3.jar
mycat/lib/hamcrest-library-1.3.jar
mycat/lib/jline-0.9.94.jar
mycat/lib/joda-time-2.9.3.jar
mycat/lib/jsr305-2.0.3.jar
mycat/lib/kotlin-stdlib-1.3.50.jar
mycat/lib/kotlin-stdlib-common-1.3.50.jar
mycat/lib/kryo-2.10.jar
mycat/lib/leveldb-0.7.jar
mycat/lib/leveldb-api-0.7.jar
mycat/lib/libwrapper-linux-ppc-64.so
mycat/lib/libwrapper-linux-x86-32.so
mycat/lib/libwrapper-linux-x86-64.so
mycat/lib/log4j-1.2-api-2.5.jar
mycat/lib/log4j-1.2.17.jar
mycat/lib/log4j-api-2.5.jar
mycat/lib/log4j-core-2.5.jar
mycat/lib/log4j-slf4j-impl-2.5.jar
mycat/lib/mapdb-1.0.7.jar
mycat/lib/minlog-1.2.jar
mycat/lib/mongo-java-driver-3.11.0.jar
mycat/lib/Mycat-server-1.6.7.4-release.jar
mycat/lib/mysql-binlog-connector-java-0.16.1.jar
mycat/lib/mysql-connector-java-5.1.35.jar
mycat/lib/netty-3.7.0.Final.jar
mycat/lib/netty-buffer-4.1.9.Final.jar
mycat/lib/netty-common-4.1.9.Final.jar
mycat/lib/objenesis-1.2.jar
mycat/lib/okhttp-4.2.2.jar
mycat/lib/okio-2.2.2.jar
mycat/lib/reflectasm-1.03.jar
mycat/lib/sequoiadb-driver-1.12.jar
mycat/lib/slf4j-api-1.6.1.jar
mycat/lib/univocity-parsers-2.2.1.jar
mycat/lib/velocity-1.7.jar
mycat/lib/wrapper.jar
mycat/lib/zookeeper-3.4.6.jar
mycat/conf/wrapper.conf
mycat/conf/
mycat/conf/zkconf/
mycat/conf/zkdownload/
mycat/conf/auto-sharding-long.txt
mycat/conf/auto-sharding-rang-mod.txt
mycat/conf/autopartition-long.txt
mycat/conf/cacheservice.properties
mycat/conf/dbseq - utf8mb4.sql
mycat/conf/dbseq.sql
mycat/conf/ehcache.xml
mycat/conf/index_to_charset.properties
mycat/conf/migrateTables.properties
mycat/conf/myid.properties
mycat/conf/partition-hash-int.txt
mycat/conf/partition-range-mod.txt
mycat/conf/rule.xml
mycat/conf/schema.xml
mycat/conf/sequence_conf.properties
mycat/conf/sequence_db_conf.properties
mycat/conf/sequence_distributed_conf.properties
mycat/conf/sequence_http_conf.properties
mycat/conf/sequence_time_conf.properties
mycat/conf/server.xml
mycat/conf/sharding-by-enum.txt
mycat/conf/zkconf/auto-sharding-long.txt
mycat/conf/zkconf/auto-sharding-rang-mod.txt
mycat/conf/zkconf/autopartition-long.txt
mycat/conf/zkconf/cacheservice.properties
mycat/conf/zkconf/ehcache.xml
mycat/conf/zkconf/index_to_charset.properties
mycat/conf/zkconf/partition-hash-int.txt
mycat/conf/zkconf/partition-range-mod.txt
mycat/conf/zkconf/rule.xml
mycat/conf/zkconf/schema.xml
mycat/conf/zkconf/sequence_conf.properties
mycat/conf/zkconf/sequence_db_conf.properties
mycat/conf/zkconf/sequence_distributed_conf-mycat_fz_01.properties
mycat/conf/zkconf/sequence_distributed_conf.properties
mycat/conf/zkconf/sequence_time_conf-mycat_fz_01.properties
mycat/conf/zkconf/sequence_time_conf.properties
mycat/conf/zkconf/server-mycat_fz_01.xml
mycat/conf/zkconf/server.xml
mycat/conf/zkconf/sharding-by-enum.txt
mycat/conf/zkdownload/auto-sharding-long.txt
mycat/version.txt
mycat/conf/log4j2.xml
mycat/bin/dataMigrate.sh
mycat/bin/init_zk_data.sh
mycat/bin/rehash.sh
mycat/bin/startup_nowrap.sh
mycat/logs/
mycat/catlet/
[root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' >/etc/p
pam.d/          pkcs11/         pm/             postfix/        printcap        protocols
passwd          pki/            polkit-1/       ppp/            profile         python/
passwd-         plymouth/       popt.d/         prelink.conf.d/ profile.d/
[root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' >/etc/profile.d/mycat.sh
[root@mycat ~]# source /etc/profile.d/mycat.sh
[root@mycat ~]# ss -ntl
State       Recv-Q Send-Q                      Local Address:Port                                     Peer Address:Port
LISTEN      0      128                                     *:22                                                  *:*
LISTEN      0      100                             127.0.0.1:25                                                  *:*
LISTEN      0      128                                  [::]:22                                               [::]:*
LISTEN      0      100                                 [::1]:25                                               [::]:*
[root@mycat ~]# file /apps/mycat/bin/mycat
/apps/mycat/bin/mycat: POSIX shell script, ASCII text executable
[root@mycat ~]# mycat start
Starting Mycat-server...
[root@mycat ~]# ss -ntl
State       Recv-Q Send-Q                      Local Address:Port                                     Peer Address:Port
LISTEN      0      128                                     *:22                                                  *:*
LISTEN      0      100                             127.0.0.1:25                                                  *:*
LISTEN      0      1                               127.0.0.1:32000                                               *:*
LISTEN      0      50                                   [::]:45321                                            [::]:*
LISTEN      0      100                                  [::]:9066                                             [::]:*
LISTEN      0      50                                   [::]:44019                                            [::]:*
LISTEN      0      128                                  [::]:22                                               [::]:*
LISTEN      0      100                                 [::1]:25                                               [::]:*
LISTEN      0      50                                   [::]:1984                                             [::]:*
LISTEN      0      100                                  [::]:8066                                             [::]:*
[root@mycat ~]# tail  /apps/mycat/logs/wrapper.log
STATUS | wrapper  | 2021/09/02 04:40:52 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/09/02 04:40:52 | Launching a JVM...
INFO   | jvm 1    | 2021/09/02 04:40:53 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/09/02 04:40:53 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/09/02 04:40:53 |
INFO   | jvm 1    | 2021/09/02 04:40:55 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]# mysql -uroot -p123456 -h192.168.118.189 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)
​
MySQL [(none)]>
​

2.修改server.xml文件

[root@mycat ~]# vim /apps/mycat/conf/server.xml
        <user name="root" defaultAccount="true">
                <property name="password">123456</property>
                <property name="schemas">TESTDB</property>
                <property name="defaultSchema">TESTDB</property>
                <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
​
                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>
​

3.修改schema.xml

 vi /apps/mycat/conf/schema.xml
 
 <mycat:schema xmlns:mycat="http://io.mycat/">
​
        <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" randomDataNode="dn1">
        </schema>
        <dataNode name="dn1" dataHost="localhost1" database="mycat" />
                <dataHost name="localhost1" maxCon="1000" minCon="10" balance=1
                                        writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
                        <heartbeat>select user()</heartbeat>
                        <writeHost host="host1" url="192.168.118.25:3306" user="root" password="123456">
                        <readHost host="host2" url="192.168.118.43:3306" user="root" password="123456"/>
                        </writeHost>
        </dataHost>
</mycat:schema>
​
​
 

3.在后端服务器创建用户并对mycat授权

MariaDB [(none)]> create database db1;
Query OK, 1 row affected (0.00 sec)
​
MariaDB [(none)]> create database mycat;
Query OK, 1 row affected (0.01 sec)
​
MariaDB [(none)]> grant all on *.* to 'root'@'192.168.118.%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
​
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| mycat              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
6 rows in set (0.00 sec)
​

4.测试读写分离是否正常

#登录mycat代理的mysql
[root@mycat conf]# mysql -uroot -p123456 -h192.168.118.189 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
​
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
​
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
​
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)
​
MySQL [(none)]> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
​
Database changed
MySQL [TESTDB]> show tables;
+-----------------+
| Tables_in_mycat |
+-----------------+
| student_tbl     |
| teacher_tbl     |
+-----------------+
2 rows in set (0.00 sec)
​
#插入一条数据，查看master与slave通用日志（查看发现slvae也有insert语句执行，原因是主从同步）
MySQL [TESTDB]> INSERT INTO teacher_tbl(name,age,sex) VALUES('aa',24,'M');
Query OK, 1 row affected (0.01 sec)
#查询表，查看master与slave通用日志，发现只有slave有执行查询语句，读写分离实现
MySQL [TESTDB]> select * from teacher_tbl;
+------+------+------+
| name | age  | sex  |
+------+------+------+
| aa   |   24 | M    |
| aa   |   24 | M    |
| aa   |   24 | M    |
| aa   |   24 | M    |
| aa   |   24 | M    |
| aa   |   24 | M    |
+------+------+------+
6 rows in set (0.01 sec)
​
MySQL [TESTDB]>
​
#master通用日志
[root@master mysql]# tail -f  master.log
210902  9:33:17    13 Query     show variables like  'general_log'
                    9 Query     select user()
210902  9:33:27    10 Query     select user()
210902  9:33:28    13 Query     show variables like  'general_log_file'
210902  9:33:37     3 Query     select user()
210902  9:33:45    13 Quit
210902  9:33:47    11 Query     select user()
210902  9:33:57     8 Query     select user()
210902  9:34:07     4 Query     select user()
210902  9:34:17     6 Query     select user()
210902  9:34:27    12 Query     select user()
210902  9:34:37     7 Query     select user()
210902  9:34:47     5 Query     select user()
210902  9:34:57     9 Query     select user()
210902  9:35:07    10 Query     select user()
210902  9:35:17     3 Query     select user()
210902  9:35:27    11 Query     select user()
210902  9:35:37     8 Query     select user()
210902  9:35:47     4 Query     select user()
210902  9:35:57     6 Query     select user()
210902  9:36:07    12 Query     select user()
210902  9:36:13     7 Query     SET names utf8
                    7 Field List        student_tbl
                    5 Query     SET names utf8
                    5 Field List        teacher_tbl
210902  9:36:17     9 Query     select user()
210902  9:36:27    10 Query     select user()
210902  9:36:37     3 Query     select user()
210902  9:36:47    11 Query     select user()
210902  9:36:57     8 Query     select user()
210902  9:37:01     4 Query     SET names utf8;INSERT INTO teacher_tbl(name,age,sex) VALUES('aa',24,'M')
210902  9:37:07     6 Query     select user()
210902  9:37:17    12 Query     select user()
210902  9:37:27     9 Query     select user()
210902  9:37:37    10 Query     select user()
210902  9:37:47     3 Query     select user()
​
#slave通用日志
[root@slave ~]# tail -f /var/lib/mysql/slave.log
210902  9:33:17     5 Query     select user()
210902  9:33:27     6 Query     select user()
210902  9:33:37     4 Query     select user()
210902  9:33:47     7 Query     select user()
210902  9:33:57     5 Query     select user()
210902  9:34:07     6 Query     select user()
210902  9:34:17     4 Query     select user()
210902  9:34:27     7 Query     select user()
210902  9:34:34     8 Quit
210902  9:34:37     5 Query     select user()
210902  9:34:47     6 Query     select user()
210902  9:34:57     4 Query     select user()
210902  9:35:07     7 Query     select user()
210902  9:35:17     5 Query     select user()
210902  9:35:27     6 Query     select user()
                   10 Connect   root@192.168.118.189 as anonymous on mycat
                    9 Connect   root@192.168.118.189 as anonymous on mycat
210902  9:35:37     4 Query     select user()
210902  9:35:47     7 Query     select user()
210902  9:35:57     5 Query     select user()
210902  9:35:59     6 Query     SET names utf8;show dataabses
210902  9:36:07     9 Query     select user()
210902  9:36:13    10 Query     SET names utf8;show tables
210902  9:36:17     4 Query     select user()
210902  9:36:18     7 Query     SET names utf8;show tables
210902  9:36:27     5 Query     select user()
210902  9:36:37     6 Query     select user()
210902  9:36:47     9 Query     select user()
210902  9:36:57    10 Query     select user()
210902  9:37:01     1 Query     BEGIN
                    1 Query     INSERT INTO teacher_tbl(name,age,sex) VALUES('aa',24,'M')
                    1 Query     COMMIT /* implicit, from Xid_log_event */
210902  9:37:07     4 Query     select user()
210902  9:37:17     7 Query     select user()
210902  9:37:27     5 Query     select user()
210902  9:37:30     6 Query     select * from teacher_tbl
210902  9:37:37     9 Query     select user()
210902  9:37:47    10 Query     select user()
​
​