# 本次实验 共四台机器
# A: 10.0.0.11 为client
# B: 10.0.0.12 为主节点
# C: 10.0.0.13 为从节点1
# D: 10.0.0.14 为从节点2
# 首先配置BCD主从复制 （这里不做重复描写，参考我前面的文档）
# 开始安装mycat相关包，以及数据库客户端
[root@centos8 ~]#yum -y install java mariadb
#确认安装成功
[root@centos8 ~]#java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)
#下载并安装
[root@centos8 ~]#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@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
-C /apps
#配置环境变量
[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]#source /etc/profile.d/mycat.sh
#启动mycat
[root@centos8 ~]#mycat start
Starting Mycat-server...
# 在wrapper.log能查询到相关信息
STATUS | wrapper | 2021/06/26 23:28:36 | --> Wrapper Started as Daemon
STATUS | wrapper | 2021/06/26 23:28:36 | Launching a JVM...
INFO | jvm 1 | 2021/06/26 23:28:39 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2021/06/26 23:28:39 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2021/06/26 23:28:39 |
INFO | jvm 1 | 2021/06/26 23:28:40 | MyCAT Server startup successfully. see logs in logs/mycat.log
# 配置server.xml，改变serverPort和root的登录密码
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
<user name="root">
<property name="password">123456</property>
<property name="schemas">TESTDB</property>
<property name="defaultSchema">TESTDB</property>
#配置schema.xml
[08:43:34 root@centos8 conf]#cat schema.xml
<?xml version="1.0" encoding="UTF-8"?>
<!--?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">
<!--table name="students" dataNode="jdbc_dn1" rule="rule1" /-->
</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.12:3306" user="admin" password="123456">
<readHost host="host2" url="10.0.0.13:3306" user="admin" password="123456" />
<readHost host="host3" url="10.0.0.14:3306" user="admin" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
# schema.xml中配置有admin账号，因此需要在主从节点建立admin账号，并给予权限GRANT ALL ON *.* TO 'admin'@'10.0.0.%' IDENTIFIED BY '123456' WITH GRANT
OPTION;
MariaDB [(none)]> select user,host from mysql.user;
+----------------+-----------+
| user | host |
+----------------+-----------+
| admin | 10.0.0.% |
# 重启mycat服务
[08:25:05 root@centos8 ~]#mycat restart
[08:25:12 root@centos8 ~]#mycat console
Running Mycat-server...
Mycat-server is already running.
[08:48:36 root@centos8 ~]#mysql -uroot -p123456 -h127.0.0.1 -P3306 -DTESTDB
MySQL [TESTDB]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.001 sec)
MySQL [TESTDB]> use TESTDB;
Database changed
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.012 sec)
MySQL [TESTDB]> create table t1(id int);
Query OK, 0 rows affected (0.014 sec)
# 读的话，只会在C，D节点循环
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 14 |
+-------------+
1 row in set (0.001 sec)
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 13 |
+-------------+
1 row in set (0.001 sec)
MySQL [TESTDB]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 14 |
+-------------+
# 新增的id为主节点，证明其写已经分离
MySQL [TESTDB]> insert t1(id) values(@@server_id);
Query OK, 1 row affected (0.003 sec)
MySQL [TESTDB]> select * from t1;
+------+
| id |
+------+
| 12 |
+------+
1 row in set (0.006 sec)
# 用通用日志在主从节点观察
# 主节点
[08:28:34 root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
210627 8:27:44 16 Query select user()
210627 8:27:54 12 Query select user()
210627 8:30:23 12 Query SET names utf8;create table t1(id int)
210627 8:30:24 21 Query select user()
210627 8:30:34 24 Query select user()
210627 8:33:28 12 Query insert t1(id) values(@@server_id)
210627 8:33:34 21 Query select user()
210627 8:33:44 24 Query select user()
210627 8:33:54 23 Query select user()
# 从节点C
[08:29:18 root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
210627 8:28:01 11 Query show variables like '%general_log%'
210627 8:28:03 12 Query select user()
210627 8:28:13 15 Query select user()
210627 8:28:23 13 Query select user()
210627 8:30:23 10 Query create table t1(id int)
13 Query select user()
210627 8:30:33 14 Query select user()
210627 8:30:43 12 Query select user()
210627 8:30:53 17 Query select user()
210627 8:31:03 16 Query select user()
210627 8:31:13 15 Query select user()
210627 8:31:23 13 Query select user()
210627 8:31:33 14 Query select user()
210627 8:31:43 12 Query select user()
210627 8:31:50 17 Query SET names utf8;select @@server_id
210627 8:31:52 16 Query SET names utf8;select @@server_id
15 Query SET names utf8;select @@server_id
210627 8:33:03 12 Query select user()
210627 8:33:13 17 Query select user()
210627 8:33:23 16 Query select user()
210627 8:33:28 10 Query BEGIN
10 Query COMMIT /* implicit, from Xid_log_event */
210627 8:33:33 15 Query select user()
210627 8:33:43 13 Query select user()
210627 8:33:46 14 Query SET names utf8;select * from t1
210627 8:33:53 12 Query select user()
210627 8:34:03 17 Query select user()
210627 8:34:13 16 Query select user()
210627 8:34:23 15 Query select user()
# 从节点D
[08:29:29 root@centos8 ~]#tail -f /var/lib/mysql/centos8.log
210627 8:28:24 13 Query select user()
210627 8:28:34 12 Query select user()
210627 8:28:44 14 Query select user()
210627 8:28:54 13 Query select user()
210627 8:31:34 15 Query select user()
210627 8:31:44 16 Query SET names utf8;select @@server-id
14 Query select user()
210627 8:31:46 13 Query SET names utf8;select @@server_id
210627 8:31:52 12 Query SET names utf8;select @@server_id
210627 8:31:54 15 Query SET names utf8;select @@server_id
16 Query select user()
210627 8:32:04 14 Query select user()
210627 8:32:14 13 Query select user()
210627 8:32:54 13 Query select user()
210627 8:33:04 12 Query select user()
210627 8:33:14 15 Query select user()
210627 8:33:24 16 Query select user()
210627 8:33:28 10 Query BEGIN
10 Query COMMIT /* implicit, from Xid_log_event */
210627 8:33:34 14 Query select user()
210627 8:33:44 13 Query select user()
210627 8:33:54 12 Query select user()
210627 8:34:04 15 Query select user()
# 报错分析：重启mycat报错
#1> 标签问题，出现如下面这个类似的，一般是标签问题，这是XML语法检查，每个大标签应该有自己的结束标签
INFO | jvm 1 | 2021/06/26 23:43:36 | Caused by: org.xml.sax.SAXParseException; lineNumber: 33; columnNumber: 4; The element type "writeHost" must be terminated by the matching end-tag "</writeHost>".
#2> 有两个解决办法
INFO | jvm 1 | 2021/06/27 00:05:16 | Caused by: io.mycat.config.util.ConfigException: schema TESTDB didn't config tables,so you must set dataNode property!
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> #方法1 把之前的randomDataNode改成databNode
<!--table name="students" dataNode="jdbc_dn1" rule="rule1" /--> # 方法2，加上table
</schema>
#3> 这个出现system标签问题，那就需要去查看server.xml，明显是中间system标签中间有不是小标签的内容，类似中间有不是xml的语法，但是未加<>这些
INFO | jvm 1 | 2021/06/27 08:09:19 | Caused by: io.mycat.config.util.ConfigException: org.xml.sax.SAXParseException; lineNumber: 89; columnNumber: 11; The content of element type "system" must match "(property)*".
#4> 如果出现类似空指针的问题，请先检查文件格式，博主遇到很多都是因为windows格式传到linux出问题
浙公网安备 33010602011771号