mysql 一主一从 读写分离
schema.xml配置
主从balance 策略
读写分离 首先需要配置 主从复制 ，主从复制 请查看
主库 备库 需要创建 itcast 数据库
目前读写分离是使用的mycat 需要配置 /usr/local/mycat/conf/schema.xml （注意格式缩进）
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="ITCAST_RW" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn7">
</schema>
<dataNode name="dn7" dataHost="dhost7" database="itcast" />
<dataHost name="dhost7" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master" url="jdbc:mysql://118.81.122.205:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="un15^ares">
<readHost host="slave" url="jdbc:mysql://123.222.33.28:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="root" password="MNwPass4!%#">
</readHost>
</writeHost>
</dataHost>
</mycat:schema>
mycat 的 /usr/local/mycat/conf/server.xml 配置文件更改
<user name="root" defaultAccount="true"> <property name="password">un15^ares</property> <property name="schemas">DB01,ITCAST_RW</property> <!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 --> <!-- 表级 DML 权限设置 --> <!-- <privileges check="false"> <schema name="TESTDB" dml="0110" > <table name="tb01" dml="0000"></table> <table name="tb02" dml="1111"></table> </schema> </privileges> --> </user>
然后重启 mycat
./mycat stop
./mycat start
然后查看日志
[root@VM-4-11-centos logs]# more wrapper.log
STATUS | wrapper | 2024/02/17 14:55:14 | --> Wrapper Started as Daemon
STATUS | wrapper | 2024/02/17 14:55:14 | Launching a JVM...
INFO | jvm 1 | 2024/02/17 14:55:15 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2024/02/17 14:55:15 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2024/02/17 14:55:15 |
INFO | jvm 1 | 2024/02/17 14:55:15 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally
unnecessary.
INFO | jvm 1 | 2024/02/17 14:55:15 |
INFO | jvm 1 | 2024/02/17 14:55:15 | WrapperSimpleApp: Encountered an error running main: java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2024/02/17 14:55:15 | java.lang.ExceptionInInitializerError
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.MycatStartup.main(MycatStartup.java:53)
INFO | jvm 1 | 2024/02/17 14:55:15 | at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO | jvm 1 | 2024/02/17 14:55:15 | at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
INFO | jvm 1 | 2024/02/17 14:55:15 | at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
INFO | jvm 1 | 2024/02/17 14:55:15 | at java.lang.reflect.Method.invoke(Method.java:498)
INFO | jvm 1 | 2024/02/17 14:55:15 | at org.tanukisoftware.wrapper.WrapperSimpleApp.run(WrapperSimpleApp.java:240)
INFO | jvm 1 | 2024/02/17 14:55:15 | at java.lang.Thread.run(Thread.java:750)
INFO | jvm 1 | 2024/02/17 14:55:15 | Caused by: io.mycat.config.util.ConfigException: SelfCheck### schema ITCAST refered by user root is not exist!
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.config.ConfigInitializer.selfChecking0(ConfigInitializer.java:142)
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.config.ConfigInitializer.<init>(ConfigInitializer.java:118)
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.config.MycatConfig.<init>(MycatConfig.java:72)
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.MycatServer.<init>(MycatServer.java:178)
INFO | jvm 1 | 2024/02/17 14:55:15 | at io.mycat.MycatServer.<clinit>(MycatServer.java:121)
INFO | jvm 1 | 2024/02/17 14:55:15 | ... 7 more
STATUS | wrapper | 2024/02/17 14:55:18 | <-- Wrapper Stopped
STATUS | wrapper | 2024/02/17 15:03:39 | --> Wrapper Started as Daemon
STATUS | wrapper | 2024/02/17 15:03:39 | Launching a JVM...
INFO | jvm 1 | 2024/02/17 15:03:40 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2024/02/17 15:03:40 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2024/02/17 15:03:40 |
INFO | jvm 1 | 2024/02/17 15:03:40 | Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally
unnecessary.
INFO | jvm 1 | 2024/02/17 15:03:41 | MyCAT Server startup successfully. see logs in logs/mycat.log
表示已经启动成功
主库操作
mysql> create table tb_user(
-> id int(11) primary key not null auto_increment,
-> name varchar(29) not null,
-> sex varchar(1)
-> )engine=innodb default charset=utf8mb4;
Query OK, 0 rows affected (0.05 sec)
mysql>
mysql> insert into tb_user values(null,'tmomca','1'),(null,'GSEnes','2'),(null,'fkaet','3');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
从库 也会有这个表数据
--备库操作 tb_user 表
insert into tb_user values(null,'tomcat','4'),(null,'dabao','5'),(null,'kafuka','6');
--主库操作
mysql> select * from tb_user;
+----+---------+------+
| id | name | sex |
+----+---------+------+
| 1 | tomcat4 | 1 |
| 2 | GSEnes | 2 |
| 3 | fkaet | 3 |
| 4 | tomcat | 4|
| 5 | dabao | 5 |
| 6 | kafuka | 6 |
+----+---------+------+
6 rows in set (0.00 sec)
如果从库修改 主库也可以查看到修改的数据，代表着 主从分离已经配置成功。
如果从库修改，从库的数据 跟主库不一致，代表这个 主从分离 未配置成功。
