Linux-Mycat實現MySQL的讀寫分離

語言: CN / TW / HK

centos8

伺服器共三臺

client                  10.0.0.88        mariadb-10.4.24

mycat-server     10.0.0.18        #記憶體建立2G以上

mysql-master     10.0.0.28          mariadb-10.4.24

mysql-master      10.0.0.38       mariadb-10.4.24

關閉防火牆和SELinux

systemctl stop firewalld
setenforce 0
時間同步

1、實現主從複製

修改master和slave上配置檔案

master
[root@master ~]#cat /etc/my.cnf 
[mysqld]
datadir=/data/mysql/data
pid-file=/data/mysql/mysqll.pid
server-id=28                      新增ID編號
log-bin=/data/mysql/mysql-bin            開啟二進位制日誌並指定路徑
relay_log=/data/mysql/logs/mysql_relay.log
[client]
socket=/tmp/mysql.sock
[root@master ~]#cd /data/                         #mkdir /data/mysql
[root@master /data]#ll
total 0
drwxrwx--- 5 mysql mysql 59 May 15 20:23 mysql    #確認目錄屬性為    修改命令chown -R mysql.mysql /data/mysql
[root@master /data]#systemctl restart mysqld      #重啟服務
 

lave
[root@slave ~]#cat /etc/my.cnf 
[mysqld]
datadir=/data/mysql/data
pid-file=/data/mysql/mysqll.pid
server-id=38
relay_log=/data/mysql/logs/mysql_relay.log
[client]
socket = /tmp/mysql.sock
[root@slave ~]#systemctl restart mysqld

(2)master上建立複製使用者

[root@master ~]#mysql -uroot -p
Enter password:
MariaDB [(none)]> create user repluser@'10.0.0.%' identified by '123456';      建立複製使用者
Query OK, 0 rows affected (0.002 sec)

MariaDB [(none)]> grant replication slave on *.* to repluser@'10.0.0.%';       授權
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)
 
MariaDB [(none)]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       351 |
| mysql-bin.000002 |       915 |
+------------------+-----------+
2 rows in set (0.000 sec)
 

(3)slave上執行

MariaDB [(none)]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.28',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=915;
Query OK, 0 rows affected (0.006 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> show slave status\G;
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.28
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000002
           Read_Master_Log_Pos: 915
                Relay_Log_File: mysql_relay.000002
                 Relay_Log_Pos: 555
         Relay_Master_Log_File: mysql-bin.000002
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
.........................
    Slave_Transactional_Groups: 0
1 row in set (0.000 sec)

主從複製檢查

主節點載入資料庫

MariaDB [(none)]> source /root/hellodb_innodb.sql
MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

從節點同布

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)

2、在MySQL代理伺服器10.0.0.18安裝mycat並啟動

[root@mycat ~]#yum -y install java

#確認安裝成功

[root@mycat ~]#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)

(1)下載工具,建立目錄 , 解壓縮

[root@mycat ~]#wget http://dl.mycat.org.cn/1.6.7.6/20211221142218/Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz
--2022-05-15 21:59:05-- http://dl.mycat.org.cn/1.6.7.6/20211221142218/Mycat-server-1.6.7.6-release-20211221142218-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: 27570028 (26M) [application/octet-stream]
Saving to: ‘Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz’

Mycat-server-1.6.7.6-release- 100%[================================================>] 26.29M 9.72MB/s in 2.7s

2022-05-15 21:59:08 (9.72 MB/s) - ‘Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz’ saved [27570028/27570028]
[root@mycat ~]#mkdir /apps/
[root@mycat ~]#tar xf Mycat-server-1.6.7.6-release-20211221142218-linux.tar.gz -C /apps/

(2)配置環境變數

[root@mycat ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@mycat ~]#source /etc/profile.d/mycat.sh 

(3)啟動mycat

[root@mycat ~]#mycat start                       #啟動前內容調為大於2G,否則啟動不了
Starting Mycat-server...
[root@mycat ~]#

[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log       #檢視日誌,是否執行成功
STATUS | wrapper | 2022/05/15 22:02:30 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/05/15 22:02:30 | Launching a JVM...
INFO | jvm 1 | 2022/05/15 22:02:31 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/05/15 22:02:31 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/05/15 22:02:31 |
INFO | jvm 1 | 2022/05/15 22:02:32 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]#ss -tnl
State        Recv-Q       Send-Q               Local Address:Port                Peer Address:Port       Process       
LISTEN       0            128                        0.0.0.0:22                       0.0.0.0:*                        
LISTEN       0            1                        127.0.0.1:32000                    0.0.0.0:*                        
LISTEN       0            128                           [::]:22                          [::]:*                        
LISTEN       0            50                               *:43575                          *:*                        
LISTEN       0            50                               *:1984                           *:*                        
LISTEN       0            128                              *:8066   預設監聽8066埠          *:*                        
LISTEN       0            50                               *:43369                          *:*                        
LISTEN       0            128                              *:9066                           *:*                       

3、在mycat 伺服器上修改server.xml檔案配置Mycat的連線資訊

vim /apps/mycat/conf/server.xml 找到以下內容取消註釋

                <!--
                        <property name="serverPort">8066</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> -->

修改serverport埠為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>                         
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property>

修改mycat使用者root登入面膜為atech
<user name="root" defaultAccount="true">
<property name="password">atech</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 錯誤前會嘗試使用該schema作為schema,不設定則為null,報錯 -->

重啟觀察日誌是否成功

[root@mycat ~]#mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

備份檔案  schema.xml

[root@mycat ~]#cp /apps/mycat/conf/schema.xml{,.back}
[root@mycat ~]#vim /apps/mycat/conf/schema.xml
[root@mycat ~]#mycat restart    #重啟程式,注意觀察日誌是否成功
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2022/05/15 22:02:30 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/05/15 22:02:30 | Launching a JVM...
INFO   | jvm 1    | 2022/05/15 22:02:31 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/05/15 22:02:31 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/05/15 22:02:31 | 
INFO   | jvm 1    | 2022/05/15 22:02:32 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper  | 2022/05/15 22:08:53 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2022/05/15 22:08:54 | <-- Wrapper Stopped
STATUS | wrapper  | 2022/05/15 22:08:55 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/05/15 22:08:55 | Launching a JVM...
INFO   | jvm 1    | 2022/05/15 22:08:55 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/05/15 22:08:55 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/05/15 22:08:55 | 
INFO   | jvm 1    | 2022/05/15 22:08:56 | MyCAT Server startup successfully. see logs in logs/mycat.log
STATUS | wrapper  | 2022/05/15 22:11:37 | TERM trapped.  Shutting down.
STATUS | wrapper  | 2022/05/15 22:11:38 | <-- Wrapper Stopped
STATUS | wrapper  | 2022/05/15 22:11:39 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/05/15 22:11:39 | Launching a JVM...
INFO   | jvm 1    | 2022/05/15 22:11:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/05/15 22:11:40 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/05/15 22:11:40 | 
INFO   | jvm 1    | 2022/05/15 22:11:41 | MyCAT Server startup successfully. see logs in logs/mycat.log

wrapper.log 日誌觀察

vim編輯   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.28:3306" user="root"
   password="123456">
         <readHost host="host2" url="10.0.0.38:3306" user="root"
password="123456" />
 </writeHost>
   </dataHost>
</mycat:schema>

4、在後端主伺服器建立使用者並對mycat授權

master

MariaDB [hellodb]> create database mycat;
Query OK, 1 row affected (0.000 sec)
MariaDB [hellodb]> create user root@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> grant all on *.* to root@'10.0.0.%'
    -> ;
Query OK, 0 rows affected (0.000 sec)

從伺服器同步檢查

MariaDB [mysql]> select user,host from user;
+-------------+---------------------------+
| User        | Host                      |
+-------------+---------------------------+
| root        | 10.0.0.%                  |
| magedu      | 192.168.1.0/255.255.255.0 |
| mariadb.sys | localhost                 |
| mysql       | localhost                 |
| root        | localhost                 |
+-------------+---------------------------+
5 rows in set (0.001 sec)

5、主從節點都開啟通用日誌

MariaDB [mysql]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | OFF   |
+---------------+-------+
1 row in set (0.001 sec)

MariaDB [mysql]> set global general_log=on;  
Query OK, 0 rows affected (0.000 sec)

MariaDB [mysql]> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log   | ON    |
+---------------+-------+
1 row in set (0.000 sec)

6客戶端登入

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

MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| master.org |
+------------+
1 row in set (0.001 sec)

MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.001 sec)

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

MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| master.org |
+------------+
1 row in set (0.001 sec)

MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.002 sec)

MySQL [TESTDB]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name          | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
|     1 | Shi Zhongyu   |  22 | M      |       2 |         3 |
|     2 | Shi Potian    |  22 | M      |       1 |         7 |
|     3 | Xie Yanke     |  53 | M      |       2 |        16 |
|     4 | Ding Dian     |  32 | M      |       4 |         4 |
|     5 | Yu Yutong     |  26 | M      |       3 |         1 |
|     6 | Shi Qing      |  46 | M      |       5 |      NULL |
|     7 | Xi Ren        |  19 | F      |       3 |      NULL |
|     8 | Lin Daiyu     |  17 | F      |       7 |      NULL |
|     9 | Ren Yingying  |  20 | F      |       6 |      NULL |
|    10 | Yue Lingshan  |  19 | F      |       3 |      NULL |
|    11 | Yuan Chengzhi |  23 | M      |       6 |      NULL |
|    12 | Wen Qingqing  |  19 | F      |       1 |      NULL |
|    13 | Tian Boguang  |  33 | M      |       2 |      NULL |
|    14 | Lu Wushuang   |  17 | F      |       3 |      NULL |
|    15 | Duan Yu       |  19 | M      |       4 |      NULL |
|    16 | Xu Zhu        |  21 | M      |       1 |      NULL |
|    17 | Lin Chong     |  25 | M      |       4 |      NULL |
|    18 | Hua Rong      |  23 | M      |       7 |      NULL |
|    19 | Xue Baochai   |  18 | F      |       6 |      NULL |
|    20 | Diao Chan     |  19 | F      |       7 |      NULL |
|    21 | Huang Yueying |  22 | F      |       6 |      NULL |
|    22 | Xiao Qiao     |  20 | F      |       1 |      NULL |
|    23 | Ma Chao       |  23 | M      |       4 |      NULL |
|    24 | Xu Xian       |  27 | M      |    NULL |      NULL |
|    25 | Sun Dasheng   | 100 | M      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.002 sec)