MyCAT实现MySQL读写分离

最新推荐文章于 2024-04-04 20:37:17 发布

一直在努力学习的菜鸟

最新推荐文章于 2024-04-04 20:37:17 发布

阅读量1.2k

点赞数

分类专栏： Linux运维文章标签： linux 运维

本文链接：https://blog.csdn.net/weixin_51867896/article/details/123159587

版权

Linux运维专栏收录该内容

245 篇文章 15 订阅

订阅专栏

MyCAT实现MySQL读写分离

环境准备：

服务器	操作系统及IP	数据库版本及说明
mycat-server	CentOS7.9-10.0.0.27	内存建议2G以上
mysql-master	CentOS7.9-10.0.0.7	MariaDB-10.4.22 写节点
mysql-slave	CentOS7.9-10.0.0.17	MariaDB-10.4.22 读节点

关闭SELinux和防火墙

systemctl stop firewalld
setenforce 0
时间同步

一、主节点

1.1修改master主节点的配置

[root@master ~]#vim /etc/my.cnf
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin

1.2 建立二进制文件存放目录，修改所有者和所属组

[root@master ~]#mkdir /data/logbin/ -p
[root@master ~]#chown -R mysql.mysql /data/logbin/

1.3 重启数据库服务

[root@master ~]#systemctl restart mysqld

1.4 查看二进制位置

MariaDB [mysql]> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       328 |
+------------------+-----------+

1.5 创建复制用户并授权

MariaDB [mysql]> grant replication slave on *.* to repluser@'10.0.0.%' identified by '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;

1.6 导入hellodb数据库

MariaDB [mysql]> source /root/hellodb_innodb.sql

1.7 在后端主服务器创建用户并对mycat授权

MariaDB [mysql]> GRANT ALL PRIVILEGES ON *.* TO root@'10.0.0.%' IDENTIFIED BY '123456';
MariaDB [mysql]> FLUSH PRIVILEGES;

1.8 启用通用日志，查看读写分离

MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+------------+
| Variable_name    | Value      |
+------------------+------------+
| general_log      | ON         |
| general_log_file | master.log |
+------------------+------------+

#永久保存需要写到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON

[root@master ~]#tail -f /data/mysql/master.log

#确认主节点写
220227  0:44:58	   325 Query	update teachers set age=@@server_id where tid=4

#停止从节点后，确认主节点开始读
220227  1:19:02	   331 Query	select * from students

1.9 停止主节点

[root@master ~]#systemctl stop mysqld

二、从节点

2.1 修改slave从节点的配置

[root@slave ~]#vim /etc/my.cnf
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin

2.2 建立二进制文件存放目录，修改所有者和所属组

[root@slave ~]#mkdir /data/logbin/ -p
[root@slave ~]#chown -R mysql.mysql /data/logbin/

2.3 重启数据库服务

[root@slave ~]#systemctl restart mysqld

2.4 确定同步master主节点的数据

MariaDB [mysql]> CHANGE MASTER TO
    -> MASTER_HOST='10.0.0.7',
    -> MASTER_USER='repluser',
    -> MASTER_PASSWORD='123456',
    -> MASTER_PORT=3306,
    -> MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=328;

2.5开启slave从节点进程，开始复制

MariaDB [mysql]> start slave;

2.6查看slave从节点备份状态

MariaDB [mysql]> show slave status\G
*************************** 1. row ***************************
                Slave_IO_State: Waiting for master to send event
                   Master_Host: 10.0.0.7
                   Master_User: repluser
                   Master_Port: 3306
                 Connect_Retry: 60
               Master_Log_File: mysql-bin.000001
           Read_Master_Log_Pos: 656
                Relay_Log_File: centos7-relay-bin.000002
                 Relay_Log_Pos: 883
         Relay_Master_Log_File: mysql-bin.000001
              Slave_IO_Running: Yes
             Slave_SQL_Running: Yes
               Replicate_Do_DB:
           Replicate_Ignore_DB:
            Replicate_Do_Table:
        Replicate_Ignore_Table:
       Replicate_Wild_Do_Table:
   Replicate_Wild_Ignore_Table:
                    Last_Errno: 0
                    Last_Error:
                  Skip_Counter: 0
           Exec_Master_Log_Pos: 656
               Relay_Log_Space: 1194
               Until_Condition: None
                Until_Log_File:
                 Until_Log_Pos: 0
            Master_SSL_Allowed: No
            Master_SSL_CA_File:
            Master_SSL_CA_Path:
               Master_SSL_Cert:
             Master_SSL_Cipher:
                Master_SSL_Key:
         Seconds_Behind_Master: 0
 Master_SSL_Verify_Server_Cert: No
                 Last_IO_Errno: 0
                 Last_IO_Error:
                Last_SQL_Errno: 0
                Last_SQL_Error:
   Replicate_Ignore_Server_Ids:
              Master_Server_Id: 7
                Master_SSL_Crl:
            Master_SSL_Crlpath:
                    Using_Gtid: No
                   Gtid_IO_Pos:
       Replicate_Do_Domain_Ids:
   Replicate_Ignore_Domain_Ids:
                 Parallel_Mode: conservative
                     SQL_Delay: 0
           SQL_Remaining_Delay: NULL
       Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
              Slave_DDL_Groups: 2
Slave_Non_Transactional_Groups: 0
    Slave_Transactional_Groups: 0

2.7 查看master主节点导入的hellodb数据库

MariaDB [hellodb]> show databases;
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+

2.8 启用通用日志，查看读写分离

MariaDB [mysql]> set global general_log=on;
MariaDB [mysql]> show variables like 'general%';
+------------------+-----------+
| Variable_name    | Value     |
+------------------+-----------+
| general_log      | ON        |
| general_log_file | slave.log |
+------------------+-----------+

#永久保存需要写到配置文件里
[root@master ~]#vim /etc/my.cnf
[mysqld]
general_log=ON


[root@slave ~]#tail -f /data/mysql/slave.log

#确认从节点读
220227  0:34:48	   447 Query	select * from teachers

2.9 停止从节点

[root@slave ~]#systemctl stop mysqld

3.0 启动从节点

[root@slave ~]#systemctl start mysqld

三、mycat节点

3.1环境准备

[root@mycat ~]#yum -y install java
[root@mycat ~]#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@mycat ~]#mkdir /apps
[root@mycat ~]#[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 ~]#. /etc/profile.d/mycat.sh

3.2.启动mycat

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

#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@mycat ~]#mycat start
Starting Mycat-server...

#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@mycat ~]#ss -ntlp
State      Recv-Q Send-Q              Local Address:Port                             Peer Address:Port
LISTEN     0      1                       127.0.0.1:32000                                       *:*users:(("java",pid=2478,fd=4))
LISTEN     0      128                             *:22                                          *:*users:(("sshd",pid=1293,fd=3))
LISTEN     0      100                     127.0.0.1:25                                          *:*users:(("master",pid=1387,fd=13))
LISTEN     0      50                           [::]:1984                                     [::]:*users:(("java",pid=2478,fd=70))
LISTEN     0      128                          [::]:8066                                     [::]:*users:(("java",pid=2478,fd=94))
LISTEN     0      50                           [::]:40998                                    [::]:*users:(("java",pid=2478,fd=69))
LISTEN     0      128                          [::]:9066                                     [::]:*users:(("java",pid=2478,fd=90))
LISTEN     0      50                           [::]:37942                                    [::]:*users:(("java",pid=2478,fd=71))
LISTEN     0      128                          [::]:22                                       [::]:*users:(("sshd",pid=1293,fd=4))
LISTEN     0      100                         [::1]:25                                       [::]:*users:(("master",pid=1387,fd=14))

#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper  | 2022/02/26 17:17:47 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/02/26 17:17:47 | Launching a JVM...
INFO   | jvm 1    | 2022/02/26 17:17:49 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/02/26 17:17:49 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/02/26 17:17:49 |
INFO   | jvm 1    | 2022/02/26 17:17:59 | MyCAT Server startup successfully. see logs in logs/mycat.log

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

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

#删除注释“<!-- -->”,并修改下面行的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" defaultAccount="true">                #连接Mycat的用户名
        <property name="password">MyCAT2022.</property>  #连接Mycat的密码
        <property name="schemas">TESTDB</property>       #数据库名要和schema.xml相对应

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

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

[root@mycat ~]#vim /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.7:3306" user="root"
                   password="123456">
         <readHost host="host2" url="10.0.0.17:3306" user="root"
password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>

3.5 重新启动mycat

[root@mycat ~]#mycat restart
#查看日志，确定成功
[root@mycat ~]#tail -f /apps/mycat/logs/wrapper.log
INFO   | jvm 1    | 2022/02/26 21:22:21 | 	at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO   | jvm 1    | 2022/02/26 21:22:21 | 	at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO   | jvm 1    | 2022/02/26 21:22:21 | 	... 13 more
STATUS | wrapper  | 2022/02/26 21:22:23 | <-- Wrapper Stopped
STATUS | wrapper  | 2022/02/26 21:24:23 | --> Wrapper Started as Daemon
STATUS | wrapper  | 2022/02/26 21:24:23 | Launching a JVM...
INFO   | jvm 1    | 2022/02/26 21:24:25 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO   | jvm 1    | 2022/02/26 21:24:25 |   Copyright 1999-2006 Tanuki Software, Inc.  All Rights Reserved.
INFO   | jvm 1    | 2022/02/26 21:24:25 |
INFO   | jvm 1    | 2022/02/26 21:24:28 | MyCAT Server startup successfully. see logs in logs/mycat.log

#连接MyCAT的端口已改为3306
[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                             *:22                                          *:*
LISTEN     0      100                     127.0.0.1:25                                          *:*
LISTEN     0      50                           [::]:1984                                     [::]:*
LISTEN     0      100                          [::]:3306                                     [::]:*
LISTEN     0      100                          [::]:9066                                     [::]:*
LISTEN     0      50                           [::]:43274                                    [::]:*
LISTEN     0      50                           [::]:37134                                    [::]:*
LISTEN     0      128                          [::]:22                                       [::]:*
LISTEN     0      100                         [::1]:25                                       [::]:*

3.6在Mycat服务器上连接并测试

[root@mycat ~]#mysql -uroot -pMyCAT2022. -h127.0.0.1 -DTESTDB
MySQL [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
MySQL [TESTDB]>  use TESTDB;
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+
MySQL [TESTDB]> select @@hostname;
+------------+
| @@hostname |
+------------+
| slave      |
+------------+

3.7 在主和从服务器分别启用通用日志后，查看读写分离

MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
+-----+---------------+-----+--------+

MySQL [TESTDB]> update teachers set age=@@server_id where tid=4;

#确认主节点写
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |   7 | F      |
+-----+---------------+-----+--------+

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

MySQL [TESTDB]> select * from students;

#停止从节点后，确认主节点开始读
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+

#启动从节点后，确认从节点读
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+

3.9 停止主节点，MyCAT不会自动调度写请求至从节点

MySQL [TESTDB]> update teachers set age=@@server_id where tid=3;
ERROR 1184 (HY000): java.net.ConnectException: Connection refused

一直在努力学习的菜鸟

关注

0
点赞
踩
4

收藏

觉得还不错? 一键收藏
打赏
0
评论
MyCAT实现MySQL读写分离

MyCAT实现MySQL读写分离环境准备：服务器操作系统及IP数据库版本及说明mycat-serverCentOS7.9-10.0.0.27内存建议2G以上mysql-masterCentOS7.9-10.0.0.7MariaDB-10.4.22 写节点mysql-slaveCentOS7.9-10.0.0.17MariaDB-10.4.22 读节点关闭SELinux和防火墙systemctl stop firewalldsetenforce 0
复制链接

扫一扫