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 ~]
[mysqld]
server_id=7
binlog_format=row
log-bin=/data/logbin/mysql-bin
1.2 建立二进制文件存放目录，修改所有者和所属组
[root@master ~]
[root@master ~]
1.3 重启数据库服务
[root@master ~]
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 ~]
[mysqld]
general_log=ON
[root@master ~]
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 ~]
二、从节点
2.1 修改slave从节点的配置
[root@slave ~]
[mysqld]
server_id=17
binlog_format=row
read_only=on
log-bin=/data/logbin/mysql-bin
2.2 建立二进制文件存放目录，修改所有者和所属组
[root@slave ~]
[root@slave ~]
2.3 重启数据库服务
[root@slave ~]
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 ~]
[mysqld]
general_log=ON
[root@slave ~]
220227 0:34:48 447 Query select * from teachers
2.9 停止从节点
[root@slave ~]
3.0 启动从节点
[root@slave ~]
三、mycat节点
3.1环境准备
[root@mycat ~]
[root@mycat ~]
[root@mycat ~]
[root@mycat ~]
[root@mycat ~]
[root@mycat ~]
3.2.启动mycat
[root@mycat ~]
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump }
[root@mycat ~]
Starting Mycat-server...
[root@mycat ~]
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 ~]
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 ~]
<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>
<user name="root" defaultAccount="true">
<property name="password">MyCAT2022.</property>
<property name="schemas">TESTDB</property>
3.4 修改schema.xml实现读写分离策略
[root@mycat ~]
[root@mycat ~]
<?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 ~]
[root@mycat ~]
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
[root@mycat ~]
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 [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
|