mysql利用dble做读写分离

avatar 2024年6月6日18:33:46 评论 182 次浏览

dble是基于开源mycat的基础上开发的,所以dble也是在mycat的基础上做了改进,另外也修复了一些mycat的bug,功能和效率相比mycat更高了。这里针对,mycat和dble的区别和优劣势就不过多的描述了,需要了解的可以看看官网的描述,这里主要聊一下dble的用法。同样dble也是基于java环境的,上一章针对mycat的用法已经介绍过可以参考: https://www.wulaoer.org/?p=3084

 [root@clinent ~]# wget https://github.com/actiontech/dble/releases/download/3.23.08.0%2Ftag/dble-3.23.08.0-20231102044353-linux.tar.gz
 [root@clinent ~]# tar -zxf dble-3.23.08.0-20231102044353-java1.8.0_352-linux.tar.gz
 [root@clinent ~]# mv dble /usr/local/

下载好了dble,并且放到指定的目录下了,下面看一下dble的配置文件和配置方法。在dble中有一个conf目录,这个目录就是存放配置文件的。

 [root@clinent dble]# ls conf/
 bootstrap.dynamic.cnf    cluster_template.cnf  ehcache.xml         partition-number-range.txt  sequence_db_conf.properties  user_template.xml
 bootstrap_template.cnf   dbseq.sql             log4j2.xml          partition-pattern.txt       sharding_template.xml
 cacheservice.properties  db_template.xml       partition-enum.txt  sequence_conf.properties    template_table.sql

在conf目录下,其中

 [root@clinent conf]# cp cluster_template.cnf cluster.cnf  #集群配置文件
 [root@clinent conf]# cp db_template.xml db.xml        #数据库配置文件
 [root@clinent conf]# cp bootstrap_template.cnf bootstrap.cnf  #dble配置文件
 [root@clinent conf]# cp user_template.xml user.xml      #用户配置文件

这里我们不是部署的dble集群,所以集群配置文件不需要修改即可,这里主要是db.xml和user.xml两个文件。因为dble也是基于java环境的,所以我们需要安装一下java环境,然后在配置dble看看。

 [root@client ~]# vim /etc/profile
 ...................
 export JAVA_HOME=/usr/local/jdk
 export CLASSPATH=.:${JAVA_HOME}/jre/lib/rt.jar:${JAVA_HOME}/lib/dt.jar:${JAVA_HOME}/lib/tools.jar
 export PATH=$PATH:${JAVA_HOME}/bin
 [root@client ~]# yum -y install java-1.8.0-openjdk java-1.8.0-openjdk-devel

下面看一下dble的配置,因为我的mysql是8.0的,dble的默认配置文件是5.7的,所以需要修改bootstrap.cnf

 [root@client ~]# cd /usr/local/dble/
 [root@client dble]# vim conf/bootstrap.cnf
 .....................
 -DfakeMySQLVersion=8.0.33
 ....................
 [root@client dble]# cat conf/db.xml
 <?xml version="1.0"?>
 <!--
   ~ Copyright (C) 2016-2023 ActionTech.
   ~ License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher.
   -->
 
 <dble:db xmlns:dble="http://dble.cloud/" version="4.0">
     <dbGroup name="dw_dble" rwSplitMode="1" delayThreshold="100"> #rwSplitMode等于0是不做负载均衡直接发到主实例,1是读操作在所有从实例中均衡,当所有实例不可用时报错,在v3.20.10版本之前会发往主实例。2读操作在所有实例中均衡
         <heartbeat>show slave status</heartbeat>
         <dbInstance name="instanceM1" url="192.168.6.73:3306" user="root" password="root" maxCon="1000" minCon="10" primary="true"/>
         <dbInstance name="instanceS1" url="192.168.6.74:3306" user="root" password="root" maxCon="1000" minCon="10" readWeight="1"/>
     </dbGroup>
 </dble:db>
 [root@client dble]# cat conf/user.xml
 <?xml version="1.0" encoding="UTF-8"?>
 <!--
   ~ Copyright (C) 2016-2023 ActionTech.
   ~ License: http://www.gnu.org/licenses/gpl.html GPL version 2 or higher.
   -->
 
 <!-- - - Licensed under the Apache License, Version 2.0 (the "License");
   - you may not use this file except in compliance with the License. - You
   may obtain a copy of the License at - - http://www.apache.org/licenses/LICENSE-2.0
   - - Unless required by applicable law or agreed to in writing, software -
   distributed under the License is distributed on an "AS IS" BASIS, - WITHOUT
   WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - See the
   License for the specific language governing permissions and - limitations
   under the License. -->
 <dble:user xmlns:dble="http://dble.cloud/" version="4.0">
     <managerUser name="man1" password="654321" maxCon="100"/>
     <rwSplitUser name="rwsu1" password="123456" dbGroup="dw_dble"  maxCon="20"/>
 </dble:user>

注意,我这里的73是主服务,74是从服务,也就是mysql的一主一从读写分离。如果多从直接增加dbInstance即可,可以使用同一个group引用,后面的user就是dble的管理用户,rwSplitUser是管理数据库的,managerUser是管理dble的。下面我们启动一下dble,看看是否可以查询到数据库,并写入数据;

 [root@client dble]# ./bin/dble start
 Starting dble-server...
 dble-server is already running.
 [root@client dble]# mysql -uman1 -p -h127.0.0.1 -P9066
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 5
 Server version: 8.0.33-dble-3.23.08.0-375c9961af1c54ffb9122b21c5eb5434d39bbd64-20231102044353 dble Server (ActionTech)
 
 Copyright (c) 2000, 2024, 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> show @@dbinstance;
 +----------+------------+--------------+------+------+--------+------+------+-----------+------------+----------+
 | DB_GROUP | NAME       | HOST         | PORT | W/R  | ACTIVE | IDLE | SIZE | READ_LOAD | WRITE_LOAD | DISABLED |
 +----------+------------+--------------+------+------+--------+------+------+-----------+------------+----------+
 | dw_dble  | instanceS1 | 192.168.6.74 | 3306 | R    |      0 |   10 | 1000 |         3 |          0 | false    |
 | dw_dble  | instanceM1 | 192.168.6.73 | 3306 | W    |      0 |   10 | 1000 |         4 |          0 | false    |
 +----------+------------+--------------+------+------+--------+------+------+-----------+------------+----------+
 2 rows in set (0.01 sec)
 [root@client dble]# mysql -urwsu1 -p -h127.0.0.1 -P8066
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 6
 Server version: 8.0.33-dble-3.23.08.0-375c9961af1c54ffb9122b21c5eb5434d39bbd64-20231102044353 MySQL Community Server - GPL
 
 Copyright (c) 2000, 2024, 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> show databases;
 +--------------------+
 | Database           |
 +--------------------+
 | information_schema |
 | mysql              |
 | performance_schema |
 | sys                |
 | wolf               |
 | wulaoer            |
 +--------------------+
 7 rows in set (0.00 sec)

下面验证一下读写分离的效果,我们通过dble往数据库里创建一个表,然后往表里插入数据,然后通过dble进行查看。

 [root@client dble]# mysql -urwsu1 -p -h127.0.0.1 -P8066
 Enter password:
 Welcome to the MySQL monitor.  Commands end with ; or \g.
 Your MySQL connection id is 6
 Server version: 8.0.33-dble-3.23.08.0-375c9961af1c54ffb9122b21c5eb5434d39bbd64-20231102044353 MySQL Community Server - GPL
 
 Copyright (c) 2000, 2024, 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> use wulaoer
 Database changed
 mysql> CREATE TABLE test_table (
     ->     id INT AUTO_INCREMENT PRIMARY KEY,
     ->     value VARCHAR(255) NOT NULL
     -> );
 Query OK, 0 rows affected (0.03 sec)
 
 mysql> DELIMITER $$
 mysql>
 mysql> CREATE PROCEDURE insert_data()
     -> BEGIN
     ->     DECLARE i INT DEFAULT 1;
     ->     WHILE i <= 1000 DO
     ->         INSERT INTO test_table (value) VALUES (CONCAT('Value ', i));
     ->         SET i = i + 1;
     ->     END WHILE;
     -> END $$
 Query OK, 0 rows affected (0.01 sec)
 
 mysql>
 mysql> DELIMITER ;
 mysql>
 mysql> CALL insert_data();
 Query OK, 1 row affected (1.20 sec)
 mysql> SELECT COUNT(*) FROM test_table;
 +----------+
 | COUNT(*) |
 +----------+
 |     1000 |
 +----------+
 1 row in set (0.01 sec)
 
 mysql> SELECT * FROM test_table LIMIT 10;
 +----+----------+
 | id | value    |
 +----+----------+
 |  1 | Value 1  |
 |  2 | Value 2  |
 |  3 | Value 3  |
 |  4 | Value 4  |
 |  5 | Value 5  |
 |  6 | Value 6  |
 |  7 | Value 7  |
 |  8 | Value 8  |
 |  9 | Value 9  |
 | 10 | Value 10 |
 +----+----------+
 10 rows in set (0.00 sec)

然后看一下73和74节点查看试一下数据是否一致,这里我就不验证了。这里在刚开始部署的时候发现,选择的是这个版本dble-3.22.07.0-20220915063524-java1.8.0_151-linux.tar.gz,但是启动的时候一直提示错误"These properties in bootstrap.cnf or bootstrap.dynamic.cnf are not recognized: java.specification.maintenance.version",但是我的配置文件bootstrap.cnf中就没有“ java.specification.maintenance.version”的配置,所以一直以为是我的配置错误,后来换到最新的版本同样的配置就可以正常启动了。好了,就先写到这吧。

avatar

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: