Linux-Mycat实现MySQL的读写分离
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
<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> 修改mycat用户root登录面膜为atech
<user name="root" defaultAccount="true"> <property name="password">atech</property> <property name="schemas">TESTDB</property> <property name="defaultSchema">TESTDB</property> <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
重启观察日志是否成功
[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 修改内容
<?xml version=”1.0″?>
<!DOCTYPE mycat:schema SYSTEM “schema.dtd”>
<mycat:schema xmlns:mycat=”//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.28:3306″ user=”root”
password=”123456″>
<readHost host=”host2″ url=”10.0.0.38:3306″ user=”root”
password=”123456″ />
</writeHost>
</dataHost>
</mycat:schema>
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)
