Mycat实现MySQL读写分离

Mycat:数据库中间件，开源、面向企业的数据库集群。高可用集群方式有：

  • keepalived + mycat + mysql
  • keepalived + lvs + mycat + mysql
  • keepalived + haproxy + mycat + mysql

实验：mycat + mysql实现数据库集群（mycat 1.6.7.6 release 版本）

mycat

安装数据库环境：

1
2
3
4
5
6
7
8
[root@Rocky8-2 ~]# systemctl  status firewalld.service
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
[root@Rocky8-2 ~]# getenforce 
Disabled
[root@Rocky8-2 ~]# yum install -y mysql-server

修改 master 和 slave 上的配置，配置主从数据库：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
##  master
[root@Rocky8-2 ~]# mkdir /binlog
[root@Rocky8-2 ~]# chown mysql. /binlog/
[root@Rocky8-2 ~]# vim /etc/my.cnf.d/mysql.cnf
[mysqld]
server-id=12
log-bin=/binlog/mysql-bin

## 完全备份
[root@Rocky8-2 ~]# mkdir /data
[root@Rocky8-2 ~]# mysqldump -A -F --master-data=1 --single-transaction > /data/all.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
[root@Rocky8-2 ~]# ll /data/
total 1264
-rw-r--r-- 1 root root 1292872 Jul 11 14:33 all.sql

[root@Rocky8-2 ~]# scp /data/all.sql  10.0.0.13:/data/

## 创建用户与授权
mysql> create user repluser@'10.0.0.%' identified by "123456";
Query OK, 0 rows affected (0.02 sec)

mysql>  grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)



## slave
[root@Rocky8-3 ~]# mkdir /binlog
[root@Rocky8-3 ~]# chown mysql. /binlog/
[root@Rocky8-3 ~]# vim /etc/my.cnf.d/mysql.cnf
[mysqld]
server-id=13
log-bin=/binlog/mysql-bin
read-only

## 修改备份文件
[root@Rocky8-12 ~]# vim /data/all.sql
CHANGE MASTER TO 
    MASTER_HOST='10.0.0.12',
    MASTER_PORT=3306,
    MASTER_USER='repluser',
    MASTER_PASSWORD='123456',
    MASTER_LOG_FILE='mysql-bin.000003', 
    MASTER_LOG_POS=157;
    
## 还原数据库
[root@Rocky8-12 ~]# mysql 
mysql> set sql_log_bin=0;
mysql> set sql_log_bin=1;
mysql> start slave;

## 查看主从状态
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for source to send event
                  Master_Host: 10.0.0.12
                  Master_User: repluser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000003
          Read_Master_Log_Pos: 682
               Relay_Log_File: Rocky8-3-relay-bin.000002
                Relay_Log_Pos: 851
        Relay_Master_Log_File: mysql-bin.000003
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
......

安装mycat 1.6.7.6

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
## 安装java环境
[root@Rocky8-1 ~]# yum install -y java
[root@Rocky8-1 ~]# java -version
openjdk version "1.8.0_372"
OpenJDK Runtime Environment (build 1.8.0_372-b07)
OpenJDK 64-Bit Server VM (build 25.372-b07, mixed mode)

## 下载准备 mycat
[root@Rocky8-1 ~]# wget http://dl.mycat.org.cn/1.6.7.6/20220524101549/Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz
[root@Rocky8-1 ~]# mkdir /apps
[root@Rocky8-1 ~]# tar xvf Mycat-server-1.6.7.6-release-20220524173810-linux.tar.gz -C /apps/

## 准备环境变量
[root@Rocky8-1 ~]# echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@Rocky8-1 ~]# source /etc/profile.d/mycat.sh

## 启动mycat 
[root@Rocky8-1 ~]# ll /apps/mycat/bin/mycat
-rwxr-xr-x 1 root root 15678 May 24  2022 /apps/mycat/bin/mycat
[root@Rocky8-1 ~]# mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@Rocky8-1 ~]# mycat start
Starting Mycat-server...

## 查看监听端口与日志
[root@Rocky8-1 ~]# ss -ntlp
State         Recv-Q        Send-Q               Local Address:Port                Peer Address:Port       Process                                                         
LISTEN        0             128                        0.0.0.0:22                       0.0.0.0:*           users:(("sshd",pid=874,fd=3))                                  
LISTEN        0             1                        127.0.0.1:32000                    0.0.0.0:*           users:(("java",pid=2239,fd=4))                                 
LISTEN        0             128                        0.0.0.0:111                      0.0.0.0:*           users:(("rpcbind",pid=804,fd=4),("systemd",pid=1,fd=37))       
LISTEN        0             50                               *:37425                          *:*           users:(("java",pid=2239,fd=78))                                
LISTEN        0             128                           [::]:22                          [::]:*           users:(("sshd",pid=874,fd=4))                                  
LISTEN        0             50                               *:1984                           *:*           users:(("java",pid=2239,fd=77))                                
LISTEN        0             2048                             *:8066                           *:*           users:(("java",pid=2239,fd=104))                               
LISTEN        0             50                               *:34085                          *:*           users:(("java",pid=2239,fd=76))                                
LISTEN        0             2048                             *:9066                           *:*           users:(("java",pid=2239,fd=102))                               
LISTEN        0             128                           [::]:111                         [::]:*           users:(("rpcbind",pid=804,fd=6),("systemd",pid=1,fd=39))       

[root@Rocky8-1 ~]# tail /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2023/07/11 16:58:28 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2023/07/11 16:58:28 | Launching a JVM...
INFO   | jvm 1    | 2023/07/11 16:58:29 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/07/11 16:58:29 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/07/11 16:58:29 | 
INFO   | jvm 1    | 2023/07/11 16:58:29 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary.
INFO   | jvm 1    | 2023/07/11 16:58:31 | MyCAT Server startup successfully. see logs in logs/mycat.log

观察是否出现  MyCAT Server startup successfully. see logs in logs/mycat.log

停止mycat ,修改/apps/mycat/conf/server.xml服务端口号为3306

1
[root@Rocky8-1 conf]# vim /apps/mycat/conf/server.xml

端口号：复制注释的配置行，修改端口号为3306：

20230712110202

创建 mycat 连接到 mysql 数据库的用户名和密码（在mysql 主服务器上创建账号并授权）

1
2
3
4
5
mysql> create user admin@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.23 sec)

mysql> grant all on hellodb.* to admin@'10.0.0.%';
Query OK, 0 rows affected (0.03 sec)

修改/apps/mycat/conf/schema.xml 配置文件：

1
[root@Rocky8-1 conf]# vim /apps/mycat/conf/schema.xml

20230712114436

启动mycat ,查看端口和日志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[root@Rocky8-1 conf]# mycat start 
Starting Mycat-server...

[root@Rocky8-1 conf]# ss -ntlp
State         Recv-Q        Send-Q               Local Address:Port                Peer Address:Port       Process                                                         
LISTEN        0             128                        0.0.0.0:22                       0.0.0.0:*           users:(("sshd",pid=874,fd=3))                                  
LISTEN        0             1                        127.0.0.1:32000                    0.0.0.0:*           users:(("java",pid=2760,fd=4))                                 
LISTEN        0             128                        0.0.0.0:111                      0.0.0.0:*           users:(("rpcbind",pid=804,fd=4),("systemd",pid=1,fd=37))       
LISTEN        0             50                               *:41649                          *:*           users:(("java",pid=2760,fd=76))                                
LISTEN        0             50                               *:37107                          *:*           users:(("java",pid=2760,fd=78))                                
LISTEN        0             128                           [::]:22                          [::]:*           users:(("sshd",pid=874,fd=4))                                  
LISTEN        0             50                               *:1984                           *:*           users:(("java",pid=2760,fd=77))                                
LISTEN        0             2048                             *:3306                           *:*           users:(("java",pid=2760,fd=107))                               
LISTEN        0             2048                             *:9066                           *:*           users:(("java",pid=2760,fd=103))                               
LISTEN        0             128                           [::]:111                         [::]:*           users:(("rpcbind",pid=804,fd=6),("systemd",pid=1,fd=39))

[root@Rocky8-1 conf]# cat /apps/mycat/logs/wrapper.log 
STATUS | wrapper  | 2023/07/12 11:48:34 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2023/07/12 11:48:35 | Launching a JVM...
INFO   | jvm 1    | 2023/07/12 11:48:37 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2023/07/12 11:48:37 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2023/07/12 11:48:37 | 
INFO   | jvm 1    | 2023/07/12 11:48:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@Rocky8-1 conf]#

连接 mycat ,验证服务

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[root@Rocky8-3 ~]# mysql -uroot -p123456 -h 10.0.0.11
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> 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> SHOW TABLES;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+

验证读写分离，开启 mysql 主、从节点的通用用志，并跟踪目志

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> select @@general_log;
+---------------+
| @@general_log |
+---------------+
|             0 |
+---------------+
1 row in set (0.01 sec)

mysql> set global general_log=ON;
Query OK, 0 rows affected (0.03 sec)

## 主节点
[root@Rocky8-2 ~]# tail -f /var/lib/mysql/Rocky8-2.log 

## 从节点
[root@Rocky8-3 ~]# tail -f /var/lib/mysql/Rocky8-3.log

查询数据库表在从节点看到

1
2
3
4
[root@Rocky8-3 ~]# tail -f /var/lib/mysql/Rocky8-3.log 
2023-07-12T04:46:04.218262Z	   21 Query	select user()
2023-07-12T04:45:56.918773Z	   23 Query	SELECT * FROM teachers
2023-07-12T04:46:04.218262Z	   21 Query	select user()

添加记录在主节点看到

1
2
3
4
[root@Rocky8-2 ~]# tail -f /var/lib/mysql/Rocky8-2.log 
2023-07-12T04:49:44.219452Z	   19 Query	select user()
2023-07-12T04:49:45.114385Z	   17 Query	insert teachers(name,age,gender) values('B1',33,'M')
2023-07-12T04:49:54.217873Z	   23 Query	select user()

注：select user() 为 mycat 服务检测数据库存活性产生的记录，通过select user()指令，查询当前用户身份

1
2
3
4
5
6
7
mysql> select user() ;
+----------------+
| USER()         |
+----------------+
| root@10.0.0.13 |
+----------------+
1 row in set (0.01 sec)

注：停止从节点，自动将读操作转到主节点；停止主节点，不会将写操作转到从节点。

MySQL
#MariaDB #init #MySQL #Cluster #Mycat
Mycat实现MySQL读写分离
https://www.xcjyc.top/2023/07/11/Mycat实现MySQL读写分离/
作者
XCJYC
发布于
2023年7月11日
许可协议