利用 Mycat 实现 MySQL 的读写分离-李佳程的个人主页

1、创建 MySQL 主从数据库

# 修改配置文件
# master上的my.cnf
[root@centos79-mysql01 ~]# vim /etc/my.cnf
[mysqld]
server-id=1
log-bin

# slave上的my.cnf
[root@centos79-mysql02 ~]# vim /etc/my.cnf
[mysqld]
server-id=2

#  Master上创建复制用户
[root@centos79-mysql01 ~]# mysql -uroot -p123456
mysql> grant replication slave on *.* to 'copyuser'@'192.168.%' identified by '123456';
mysql> flush privileges;
mysql> show master status;
+-----------------------------+----------+--------------+------------------+-------------------+
| File                        | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------------------+----------+--------------+------------------+-------------------+
| centos79-mysql01-bin.000002 |      861 |              |                  |                   |
+-----------------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

# Slave上配置复制
[root@centos79-mysql02 ~]# mysql -uroot -p123456
mysql> CHANGE MASTER TO MASTER_HOST='192.168.1.21', MASTER_USER='copyuser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='centos79-mysql01-bin.000002',  MASTER_LOG_POS=861;
mysql> start slave;
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.21
                  Master_User: copyuser
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: centos79-mysql01-bin.000002
          Read_Master_Log_Pos: 861
               Relay_Log_File: centos79-mysql02-relay-bin.000002
                Relay_Log_Pos: 331
        Relay_Master_Log_File: centos79-mysql01-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

2、在MySQL代理服务器192.168.1.23安装mycat并启动

# 安装java
[root@mycat ~]# yum install -y java-1.8.0-openjdk.x86_64
[root@mycat ~]# java -version
openjdk version "1.8.0_352"
OpenJDK Runtime Environment (build 1.8.0_352-b08)
OpenJDK 64-Bit Server VM (build 25.352-b08, mixed mode)

# 安装mycat
[root@mycat ~]# wget https://github.com/MyCATApache/Mycat-Server/releases/download/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

[root@mycat ~]# mkdir /apps
[root@mycat ~]# tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/

# 配置环境变量
[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                                   *: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                                             [::]:*

# 启动mycat
[root@mycat ~]# mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@mycat ~]# mycat start

# 查看端口
[root@mycat ~]# 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      100                                [::]:8066                                           [::]:*
LISTEN      0      50                                 [::]:46824                                          [::]:*
LISTEN      0      100                                [::]:9066                                           [::]:*
LISTEN      0      128                                [::]:111                                            [::]:*
LISTEN      0      128                                [::]:22                                             [::]:*
LISTEN      0      100                               [::1]:25                                             [::]:*
LISTEN      0      50                                 [::]:43805                                          [::]:*

# 查看日志
[root@mycat ~]# tail /apps/mycat/logs/wrapper.log
STATUS | wrapper  | 2022/11/21 09:18:51 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/11/21 09:18:51 | Launching a JVM...
INFO   | jvm 1    | 2022/11/21 09:18:51 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/11/21 09:18:51 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/11/21 09:18:51 |
INFO   | jvm 1    | 2022/11/21 09:18:52 | MyCAT Server startup successfully. see logs in logs/mycat.log

# 连接mycat
[root@centos79-mysql01 ~]# mysql -uroot -p123456 -h192.168.1.23 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address          |
| travelrecord     |
+------------------+
2 rows in set (0.00 sec)

mysql> select * from address;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid DataSource:0

3、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

[root@mycat ~]# vim /apps/mycat/conf/server.xml

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

4、修改schema.xml实现读写分离策略

[root@mycat ~]# vim /apps/mycat/conf/schema.xml

上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是192.168.1.21为主库，192.168.1.22为从库。

要保证能使用root/123456权限成功登录192.168.1.21和192.168.1.22机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

# 重启mycat
[root@mycat ~]# mycat restart
[root@mycat ~]# tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper  | 2022/11/21 09:59:03 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/11/21 09:59:03 | Launching a JVM...
INFO   | jvm 1    | 2022/11/21 09:59:03 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/11/21 09:59:03 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/11/21 09:59:03 |
INFO   | jvm 1    | 2022/11/21 09:59:04 | MyCAT Server startup successfully. see logs in logs/mycat.log

# 查看端口
[root@mycat ~]# 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      50                                 [::]:35137                                          [::]:*
LISTEN      0      100                                [::]:3306                                           [::]:*
LISTEN      0      100                                [::]:9066                                           [::]:*
LISTEN      0      128                                [::]:111                                            [::]:*
LISTEN      0      128                                [::]:22                                             [::]:*
LISTEN      0      50                                 [::]:39575                                          [::]:*
LISTEN      0      100                               [::1]:25                                             [::]:*

5、在后端主服务器修改root访问限制

mysql> create database mycat;
mysql> update mysql.user set host='192.168.%' where user='root';
mysql> grant all on *.* to 'root'@'192.168.%';
mysql> flush privileges;

6、连接并测试

[root@centos79-mysql01 ~]# mysql -uroot -p123456 -h192.168.1.23
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

mysql> use TESTDB;
Database changed

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

mysql> create table t1(id int);
Query OK, 0 rows affected (0.00 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| t1                |
| teachers          |
| toc               |
+-------------------+
8 rows in set (0.01 sec)

mysql> select @@hostname;
+------------------+
| @@hostname       |
+------------------+
| centos79-mysql02 |
+------------------+
1 row in set (0.00 sec)

7、通过通用日志确认实现读写分离

# 主从服务器都配置
[root@centos79-mysql01 ~]# vim /etc/my.cnf
[mysqld]
general_log=ON

[root@centos79-mysql01 ~]# service mysqld restart

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

mysql> show variables like 'general_log_file';
+------------------+----------------------------------+
| Variable_name    | Value                            |
+------------------+----------------------------------+
| general_log_file | /data/mysql/centos79-mysql01.log |
+------------------+----------------------------------+

# 使用查询和新建表测试是否读写分离
[root@centos79-mysql01 ~]# tail -f /data/mysql/centos79-mysql01.log
[root@centos79-mysql02 ~]# tail -f /data/mysql/centos79-mysql02.log

8、停止从节点，MyCAT自动调度读请求至主节点

[root@centos79-mysql02 ~]# service mysqld stop
[root@centos79-mysql01 ~]# mysql -uroot -p123456 -h192.168.1.23
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

9、MyCAT对后端服务器的健康性检查方法select user()

[root@centos79-mysql01 ~]# tail -f /data/mysql/centos79-mysql01.log
2022-11-21T03:09:14.389118Z	    2 Query	select user()
2022-11-21T03:09:24.389430Z	    5 Query	select user()
2022-11-21T03:09:34.388904Z	    4 Query	select user()

[root@centos79-mysql02 ~]# tail -f /data/mysql/centos79-mysql02.log
2022-11-21T03:07:04.389410Z	    6 Query	select user()
2022-11-21T03:07:14.388952Z	    7 Query	select user()
2022-11-21T03:07:24.389308Z	    4 Query	select user()