MyCat实现数据库读写分离
架构
| 主机名 | IP | 角色 |
|---|---|---|
| mysql-master | 192.168.139.31 | 主MySQL |
| mysql-slave | 192.168.139.32 | 从MySQL |
| mycat | 192.168.139.33 | Mycat |
1）主从的搭建
1.1）配置主
[root@mysql-master ~]# cat /etc/my.cnf
[mysql]
port = 3306
socket = /data/mysql/data/mysql.sock
[mysqld]
server-id = 31 #重要
port = 3306
mysqlx_port = 33060
log-bin = /data/mysql/logbin/mysql-bin #重要
mysqlx_socket = /data/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/data/mysql.sock
pid-file = /data/mysql/data/mysqld.pid
log-error = /data/mysql/log/error.log
default-authentication-plugin = mysql_native_password
log_timestamps = SYSTEM
[root@mysql-master ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-master ~]# mysql -uroot -p123456
# 查看二进制位置
mysql> show master logs;
+------------------+-----------+-----------+
| Log_name | File_size | Encrypted |
+------------------+-----------+-----------+
| mysql-bin.000001 | 180 | No |
| mysql-bin.000002 | 486 | No |
| mysql-bin.000003 | 157 | No |
+------------------+-----------+-----------+
3 rows in set (0.00 sec)
# 创建账号
mysql> create user 'stangj'@'%' identified by 'stj@123';
# 授权账号复制权限
mysql> grant replication slave on *.* to 'stangj'@'%';
# 刷新权限
mysql> FLUSH PRIVILEGES;
1.2）配置从
# CHANGE MASTER TO
# MASTER_HOST='192.168.139.31',
# MASTER_USER='stangj',
# MASTER_PASSWORD='stj@123',
# MASTER_PORT=3306,
# MASTER_LOG_FILE='mysql-bin.000003',
# MASTER_LOG_POS=157;
[root@mysql-slave ~]# cat /etc/my.cnf
[mysql]
port = 3306
socket = /data/mysql/data/mysql.sock
[mysqld]
server-id = 32 # 重要
relay_log=/data/mysql/logbin #重要
read-only # 重要
port = 3306
mysqlx_port = 33060
log-bin = /data/mysql/logbin/mysql-bin #重要
mysqlx_socket = /data/mysql/data/mysqlx.sock
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/data/mysql.sock
pid-file = /data/mysql/data/mysqld.pid
log-error = /data/mysql/log/error.log
default-authentication-plugin = mysql_native_password
log_timestamps = SYSTEM
[root@mysql-slave ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
[root@mysql-slave ~]# mysql -uroot -p123456
# 连接同步主mysql
mysql> CHANGE MASTER TO
MASTER_HOST='192.168.139.31',
MASTER_USER='stangj',
MASTER_PASSWORD='stj@123',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000003',
MASTER_LOG_POS=157;
# 启动服务
mysql> start slave;
1.3）查看主从是否配置成功
# 验证主从状态
mysql> show slave status\G
...............................
...............................
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 这两个值为YES则为主从配置成功
...............................
...............................
# 查看是否已经同步了stangj用户
mysql> select user from mysql.user;
+------------------+
| user |
+------------------+
| stangj |
| mysql.infoschema |
| mysql.session |
| mysql.sys |
| root |
+------------------+
2）配置MyCat
2.1）安装
`下载jdk`
[root@mycat ~]# yum -y install java-1.8.0-openjdk
`下载mycat`
[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 ~]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@mycat ~]# echo 'PATH=/apps/mycat/bin:$PATH' >> /etc/profile
[root@mycat ~]# source /etc/profile
`启动mycat`
[root@mycat ~]# mycat start
Starting Mycat-server...
`查看是否成功启动`
[root@mycat ~]# cat /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2023/12/29 21:06:52 | --> Wrapper Started as Daemon
STATUS | wrapper | 2023/12/29 21:06:52 | Launching a JVM...
INFO | jvm 1 | 2023/12/29 21:06:53 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2023/12/29 21:06:53 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2023/12/29 21:06:53 |
INFO | jvm 1 | 2023/12/29 21:06:53 | MyCAT Server startup successfully. see logs in logs/mycat.log
``
[root@mysql-master ~]# mysql -uroot -p123456 -h192.168.139.33 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
2.3）修改mycat配置
[root@mycat ~]# vim /apps/mycat/conf/server.xml
...............
<user name="root" defaultAccount="true"> # 账号
<property name="password">stj123456</property> # 登录密码
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
...................
[root@mycat ~]# vim /apps/mycat/conf/schema.xml
[root@mycat ~]# cat /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="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="192.168.139.31:3306" user="root" password="123456"> # 写服务器指主mysql
<readHost host="host2" url="192.168.139.32:3306" user="root" password="123456" /> # 读服务器指从mysql
</writeHost>
</dataHost>
</mycat:schema>
2.4）重启mycat
[root@mycat ~]# mycat restart
[root@mycat ~]# tail -f /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2023/12/29 22:03:36 | --> Wrapper Started as Daemon
STATUS | wrapper | 2023/12/29 22:03:36 | Launching a JVM...
INFO | jvm 1 | 2023/12/29 22:03:36 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2023/12/29 22:03:36 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2023/12/29 22:03:36 |
INFO | jvm 1 | 2023/12/29 22:03:57 | MyCAT Server startup successfully. see logs in logs/mycat.log
3）在mysql中创建hellodb数据库并配置用户
[root@mysql-master ~]# mysql -uroot -p123456
# 创建数据库
mysql> create database hellodb;
# 切换到hellodb数据库
mysql> use hellodb;
# 创建表
mysql> CREATE TABLE student (
id int UNSIGNED AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(20) NOT NULL,
age tinyint UNSIGNED,
gender ENUM('M','F') default 'M'
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
# 插入三条数据
mysql> insert student (name,age)values('xiaoming',20);
mysql> insert student (name,age)values('wangwu',36);
mysql> insert student (name,age)values('tanghao',18);
# 授权账号
# 创建账号
mysql> GRANT ALL ON *.* TO 'root'@'192.168.139.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
# 查看信息
mysql> select user,host from mysql.user;
+------------------+---------------+
| user | host |
+------------------+---------------+
| stangj | % |
| root | 192.168.139.% |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+---------------+
GRANT ALL ON *.* TO 'root'@'192.168.139.%' IDENTIFIED BY '123456' WITH GRANT OPTION;
4）连接mycat验证数据
[root@mysql-master ~]# mysql -uroot -pstj123456 -h192.168.139.33 -P8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
mysql [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| student |
+-------------------+
mysql [TESTDB]> select * from student;
+----+----------+------+--------+
| id | name | age | gender |
+----+----------+------+--------+
| 10 | xiaoming | 20 | M |
| 11 | wangwu | 36 | M |
| 12 | tanghao | 18 | M |
+----+----------+------+--------+
5）验证是否是实现了读写分离
5.1）启用通用日志
-
主从都要启用（我演示一个）
[root@mysql-master/slave ~]# mysql -uroot -proot
`启动通用日志`
mysql > set global general_log=on;
`查看是否启用`
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+
mysql> show variables like 'general_log_file';
+------------------+------------------+
| Variable_name | Value |
+------------------+------------------+
| general_log_file | mysql-master.log |
+------------------+------------------+
5.2）主MySQL追踪通用日志
[root@mysql-master ~]# cd /var/lib/mysql/
[root@mysql-master mysql]# tail -f mysql-master.log
5.3）从MySQL追踪通用日志
[root@mysql-slave ~]# cd /var/lib/mysql/
[root@mysql-slave mysql]# tail -f mysql-slave.log
5.4）在mycat执行查数据操作
[root@mysql-master ~]# mysql -uroot -pstj123456 -h 192.168.139.33 -P8066
...............
mysql [TESTDB]> select * from student;
`从MySQL查看`
[root@mysql-slave mysql]# tail -f mysql-slave.log
........
231229 23:25:49 6 Query select user()
231229 23:25:59 13 Query select user()
231229 23:26:09 10 Query select user()
231229 23:26:19 14 Query select user()
231229 23:26:27 11 Query SET names utf8;select * from student # 日志很明显
5.5）在mycat执行写数据操作
[root@mysql-master ~]# mysql -uroot -pstj123456 -h 192.168.139.33 -P8066
...............
mysql> insert student (name,age)values('stj',21);
`主MySQL查看`
[root@mysql-master mysql]# tail -f mysql-master.log
231229 23:28:59 10 Query select user()
231229 23:29:09 14 Query select user()
231229 23:29:19 11 Query select user()
231229 23:29:29 8 Query select user()
231229 23:29:39 12 Query select user()
231229 23:29:49 9 Query select user()
231229 23:29:51 5 Query insert student (name,age)values('stj',21)