Linux-Mycat实现MySQL的读写分离
阅读原文时间:2022年05月16日阅读:1

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

3306
9066
300000
15000
0.0.0.0
300000
4096 32

修改mycat用户root登录面膜为atech

atech
TESTDB
TESTDB

重启观察日志是否成功

[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 修改内容



select user()

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)