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

#三台服务器
centos7  mycat-server  10.0.0.152  内存2G以上
centos8  mysql-master  10.0.0.150  mariadb 10.3
centos8  mysql-master  10.0.0.160  mariadb 10.3

#1）创建数据库主从
[root@centos8 ~]#yum install -y mariadb-server
#master和slave修改配置文件
#master：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]#
server-id=1
log-bin

#slave：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=2

#修改配置文件后重启服务使生效
[root@centos8 ~]#systemctl start mariadb

#master：创建复制账号
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log 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)]> grant replication slave on *.* to 'repluser'@'%' identified by 'replpass';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name           | File_size |
+--------------------+-----------+
| mariadb-bin.000001 |       645 |
+--------------------+-----------+
1 row in set (0.000 sec)


#slave：连接主数据库
MariaDB [(none)]> change master to master_host='10.0.0.150',master_user='repluser',master_password='replpass',master_log_file='mariadb-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> show slave status \G;
			......
             Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
			......省略
			
#2）在mysql代理服务器10.0.0.152上安装mycat并启动
#安装jdk
[root@localhost ~]# yum install -y java
[root@localhost ~]# java -version
openjdk version "1.8.0_312"
OpenJDK Runtime Environment (build 1.8.0_312-b07)
OpenJDK 64-Bit Server VM (build 25.312-b07, mixed mode)

#下载并安装mycat
[root@localhost ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

或者#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

[root@localhost ~]# mkdir /apps
[root@localhost ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz  -C /apps

#配置环境变量
[root@localhost ~]# echo 'PATH=$PATH:/apps/mycat/bin'>/etc/profile.d/mycat.sh
[root@localhost ~]# source /etc/profile.d/mycat.sh

#启动mycat
[root@localhost ~]# ss -ntl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN     0      128               *:111                           *:*
LISTEN     0      128               *:22                            *:*
LISTEN     0      100       127.0.0.1:25                            *:*
LISTEN     0      128            [::]:111                        [::]:*
LISTEN     0      128            [::]:22                         [::]:*
LISTEN     0      100           [::1]:25                         [::]:*
[root@localhost ~]# mycat start
Starting Mycat-server...
[root@localhost ~]# ss -ntl
State      Recv-Q Send-Q Local Address:Port               Peer Address:Port
LISTEN     0      1         127.0.0.1:32000                         *:*
LISTEN     0      128               *:111                           *:*
LISTEN     0      128               *:22                            *:*
LISTEN     0      100       127.0.0.1:25                            *:*
LISTEN     0      50             [::]:1984                       [::]:*
LISTEN     0      128            [::]:8066                       [::]:*
LISTEN     0      50             [::]:43650                      [::]:*
LISTEN     0      128            [::]:9066                       [::]:*
LISTEN     0      128            [::]:111                        [::]:*
LISTEN     0      128            [::]:22                         [::]:*
LISTEN     0      100           [::1]:25                         [::]:*
LISTEN     0      50             [::]:34334                      [::]:*

#可以看到启动了多个端口，其中8066用于连接myscat
[root@localhost ~]# tail /apps/mycat/logs/wrapper.log
可以看到启动成功了

#用默认密码123456来连接mycat
[root@localhost ~]# mysql -uroot -p123456 -h10.0.0.152 -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.6-release-20210303094759 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)

#3）在mycat服务器上修改server.xml文件配置mycat的连接信息
[root@localhost ~]# vim /apps/mycat/conf/server.xml
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
或者删除注释，并修改下面的8066为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连
接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property 
name="processors">32</property> #--> 删除#后面此部分
 .....
<user name="root">                                       #连接Mycat的用户名
   <property name="password">magedu</property>          #连接Mycat的密码
   <property name="schemas">TESTDB</property>           #数据库名要和schema.xml相
对应


#这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。

#4）修改schema.xml实现读写分离策略
[root@localhost ~]# vim /apps/mycat/conf/schema.xml
#最终文件内容
[root@mycat ~]#cat /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
 <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
 </schema>
 <dataNode name="dn1" dataHost="localhost1" database="hellodb" />
 <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="10.0.0.18:3306" user="mycat"
   password="123456">
         <readHost host="host2" url="10.0.0.28:3306" user="mycat"
password="123456" />
 </writeHost>
   </dataHost>
</mycat:schema>

#重新启动mycat
root@localhost ~]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@localhost ~]# tail /apps/mycat/logs/wrapper.log
INFO   | jvm 1    | 2021/11/09 22:49:04 |       at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO   | jvm 1    | 2021/11/09 22:49:04 |       at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO   | jvm 1    | 2021/11/09 22:49:04 |       ... 13 more
STATUS | wrapper  | 2021/11/09 22:49:06 | <-- Wrapper Stopped
STATUS | wrapper  | 2021/11/09 22:53:37 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2021/11/09 22:53:38 | Launching a JVM...
INFO   | jvm 1    | 2021/11/09 22:53:38 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2021/11/09 22:53:38 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2021/11/09 22:53:38 |
INFO   | jvm 1    | 2021/11/09 22:53:41 | MyCAT Server startup successfully. see logs in logs/mycat.log


#5）在10.0.0.150的后端数据库创建用户并授权

MariaDB [(none)]> grant all privileges on *.* to 'mycat'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> exit
Bye
[root@centos8 ~]#mysql -h10.0.0.150 -umycat -p123456

#6）连接并测试，可以看到进入逻辑数据库TESTDB，查到我后端hellodb数据库里面的内容，成功
[root@localhost ~]# mysql -uroot -pmagedu -h10.0.0.152 TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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.6-release-20210303094759 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 [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| tbl1              |
+-------------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select * from tbl1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | tanliang |
+------+----------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select @@hostname;
+--------------------+
| @@hostname         |
+--------------------+
| centos8.magedu.org |
+--------------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

#7)通过日志确认实现读写分离
show variables like 'general_log';  #查看日志是否开启
set global general_log=on;    #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置

#在主和从服务器分别开启通用日志，查看读写分离
#操作：
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| tbl1              |
+-------------------+
1 row in set (0.00 sec)

MySQL [TESTDB]> select * from tbl1;
+------+----------+
| id   | name     |
+------+----------+
|    1 | tanliang |
+------+----------+
1 row in set (0.01 sec)

MySQL [TESTDB]> insert tbl1 value(2,'liang');
Query OK, 1 row affected (0.01 sec)

主：
[root@master mysql]#tail -f centos8.log
211109 23:03:41     20 Query    select user()
                    24 Connect  mycat@10.0.0.152 as anonymous on hellodb
211109 23:03:51     17 Query    select user()
211109 23:04:01     16 Query    SET names utf8;insert tbl1 value(2,'liang')
                    23 Query    select user()
^C
从：
211109 23:02:04     16 Query    SET names utf8;show tables
211109 23:02:11     15 Query    select user()
211109 23:02:21     13 Query    select user()
211109 23:02:31     12 Query    select user()
211109 23:02:41     14 Query    select user()
211109 23:02:51     11 Query    select user()
211109 23:03:01     16 Query    select user()
211109 23:03:11     15 Query    select user()
211109 23:03:12     13 Query    select * from tbl1
211109 23:03:21     12 Query    select user()
211109 23:03:31     14 Query    select user()
211109 23:03:41     11 Query    select user()
                    18 Connect  mycat@10.0.0.152 as anonymous on hellodb
211109 23:03:51     16 Query    select user()
211109 23:04:01     10 Query    BEGIN
                    10 Query    insert tbl1 value(2,'liang')
                    10 Query    COMMIT /* implicit, from Xid_log_event */
                    15 Query    select user()
#MyCAT对后端服务器的健康性检查方法select user()
#停止从节点，MyCAT自动调度读请求至主节点，#停止主节点，MyCAT不会自动调度写请求至从节点
slave上停止mariadb
[root@slave1 mysql]#systemctl stop mariadb

[root@localhost ~]# mysql -uroot -pmagedu -h10.0.0.152 TESTDB
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)