#三台服务器
centos7 mycat-server 10.0.0.152 内存2G以上
centos8 mysql-master 10.0.0.150 mariadb 10.3
centos8 mysql-master 10.0.0.160 mariadb 10.3
#关闭防火墙
systemctl stop firewalld
setenforce 0
时间同步
#1）创建数据库主从
[root@centos8 ~]#yum install -y mariadb-server
#master和slave修改配置文件
#master：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]#
server-id=1
log-bin
#slave：
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=2
#修改配置文件后重启服务使生效
[root@centos8 ~]#systemctl start mariadb
#master：创建复制账号
[root@centos8 ~]#mysql
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.28-MariaDB-log MariaDB Server
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> grant replication slave on *.* to 'repluser'@'%' identified by 'replpass';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> show master logs;
+--------------------+-----------+
| Log_name | File_size |
+--------------------+-----------+
| mariadb-bin.000001 | 645 |
+--------------------+-----------+
1 row in set (0.000 sec)
#slave：连接主数据库
MariaDB [(none)]> change master to master_host='10.0.0.150',master_user='repluser',master_password='replpass',master_log_file='mariadb-bin.000001',master_log_pos=645;
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> start slave;
Query OK, 0 rows affected (0.001 sec)
MariaDB [(none)]> show slave status \G;
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......省略
#2）在mysql代理服务器10.0.0.152上安装mycat并启动
#安装jdk
[root@localhost ~]# yum install -y java
[root@localhost ~]# 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)
#下载并安装mycat
[root@localhost ~]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
或者#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@localhost ~]# mkdir /apps
[root@localhost ~]# tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps
#配置环境变量
[root@localhost ~]# echo 'PATH=$PATH:/apps/mycat/bin'>/etc/profile.d/mycat.sh
[root@localhost ~]# source /etc/profile.d/mycat.sh
#启动mycat
[root@localhost ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
[root@localhost ~]# mycat start
Starting Mycat-server...
[root@localhost ~]# ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 1 127.0.0.1:32000 *:*
LISTEN 0 128 *:111 *:*
LISTEN 0 128 *:22 *:*
LISTEN 0 100 127.0.0.1:25 *:*
LISTEN 0 50 [::]:1984 [::]:*
LISTEN 0 128 [::]:8066 [::]:*
LISTEN 0 50 [::]:43650 [::]:*
LISTEN 0 128 [::]:9066 [::]:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 100 [::1]:25 [::]:*
LISTEN 0 50 [::]:34334 [::]:*
#可以看到启动了多个端口，其中8066用于连接myscat
[root@localhost ~]# tail /apps/mycat/logs/wrapper.log
可以看到启动成功了
#用默认密码123456来连接mycat
[root@localhost ~]# mysql -uroot -p123456 -h10.0.0.152 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
#3）在mycat服务器上修改server.xml文件配置mycat的连接信息
[root@localhost ~]# vim /apps/mycat/conf/server.xml
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面
或者删除注释，并修改下面的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"> #连接Mycat的用户名
<property name="password">magedu</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相
对应
#这里使用的是root，密码为magedu,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。
#4）修改schema.xml实现读写分离策略
[root@localhost ~]# 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="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="host1" url="10.0.0.18:3306" user="mycat"
password="123456">
<readHost host="host2" url="10.0.0.28:3306" user="mycat"
password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
#重新启动mycat
root@localhost ~]# mycat restart
Stopping Mycat-server...
Mycat-server was not running.
Starting Mycat-server...
[root@localhost ~]# tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2021/11/09 22:49:04 | at io.mycat.config.util.ConfigUtil.getDocument(ConfigUtil.java:115)
INFO | jvm 1 | 2021/11/09 22:49:04 | at io.mycat.config.loader.xml.XMLSchemaLoader.load(XMLSchemaLoader.java:111)
INFO | jvm 1 | 2021/11/09 22:49:04 | ... 13 more
STATUS | wrapper | 2021/11/09 22:49:06 | <-- Wrapper Stopped
STATUS | wrapper | 2021/11/09 22:53:37 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/11/09 22:53:38 | Launching a JVM...
INFO | jvm 1 | 2021/11/09 22:53:38 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/11/09 22:53:38 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/11/09 22:53:38 |
INFO | jvm 1 | 2021/11/09 22:53:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
#5）在10.0.0.150的后端数据库创建用户并授权
MariaDB [(none)]> grant all privileges on *.* to 'mycat'@'10.0.0.%' identified by '123456';
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)
MariaDB [(none)]> exit
Bye
[root@centos8 ~]#mysql -h10.0.0.150 -umycat -p123456
#6）连接并测试，可以看到进入逻辑数据库TESTDB，查到我后端hellodb数据库里面的内容，成功
[root@localhost ~]# mysql -uroot -pmagedu -h10.0.0.152 TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| tbl1 |
+-------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from tbl1;
+------+----------+
| id | name |
+------+----------+
| 1 | tanliang |
+------+----------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select @@hostname;
+--------------------+
| @@hostname |
+--------------------+
| centos8.magedu.org |
+--------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
1 row in set (0.00 sec)
#7)通过日志确认实现读写分离
show variables like 'general_log'; #查看日志是否开启
set global general_log=on; #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置
#在主和从服务器分别开启通用日志，查看读写分离
#操作：
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| tbl1 |
+-------------------+
1 row in set (0.00 sec)
MySQL [TESTDB]> select * from tbl1;
+------+----------+
| id | name |
+------+----------+
| 1 | tanliang |
+------+----------+
1 row in set (0.01 sec)
MySQL [TESTDB]> insert tbl1 value(2,'liang');
Query OK, 1 row affected (0.01 sec)
主：
[root@master mysql]#tail -f centos8.log
211109 23:03:41 20 Query select user()
24 Connect mycat@10.0.0.152 as anonymous on hellodb
211109 23:03:51 17 Query select user()
211109 23:04:01 16 Query SET names utf8;insert tbl1 value(2,'liang')
23 Query select user()
^C
从：
211109 23:02:04 16 Query SET names utf8;show tables
211109 23:02:11 15 Query select user()
211109 23:02:21 13 Query select user()
211109 23:02:31 12 Query select user()
211109 23:02:41 14 Query select user()
211109 23:02:51 11 Query select user()
211109 23:03:01 16 Query select user()
211109 23:03:11 15 Query select user()
211109 23:03:12 13 Query select * from tbl1
211109 23:03:21 12 Query select user()
211109 23:03:31 14 Query select user()
211109 23:03:41 11 Query select user()
18 Connect mycat@10.0.0.152 as anonymous on hellodb
211109 23:03:51 16 Query select user()
211109 23:04:01 10 Query BEGIN
10 Query insert tbl1 value(2,'liang')
10 Query COMMIT /* implicit, from Xid_log_event */
15 Query select user()
#MyCAT对后端服务器的健康性检查方法select user()
#停止从节点，MyCAT自动调度读请求至主节点，#停止主节点，MyCAT不会自动调度写请求至从节点
slave上停止mariadb
[root@slave1 mysql]#systemctl stop mariadb
[root@localhost ~]# mysql -uroot -pmagedu -h10.0.0.152 TESTDB
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)