dble安装

环境

两个完全独立的mysql实例

依赖：

jdk1.8+

dble下载：https://github.com/actiontech/dble

• 安装

  # mkdir /opt/dble
  
  # 解压到指定地址
  # tar -xvf dble-3.22.01.1-20220615064550-java1.8.0_151-linux.tar.gz -C '/var/lib/'
  

• 配置

  # cd /var/lib/dble/conf/
  # dble集群配置文件
  cp cluster_template.cnf cluster.cnf
  
  cp bootstrap_template.cnf bootstrap.cnf
  # 后端对应的实际的数据库集群或者单实例的配置 需要根据配置修改
  cp db_template.xml db.xml
  # 外部链接dble的用户名密码 managerUser的管理用户 shardingUser 数据管理用户
  cp user_template.xml user.xml
  # 设置虚拟数据表的数据分片的逻辑 对应表的拆分逻辑 其中shardingTable的name对应实际的表明
  cp sharding_template.xml sharding.xml
  

  db.xml

  <!--
    ~ Copyright (C) 2016-2022 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="dbGroup1" rwSplitMode="2" delayThreshold="100">
          <heartbeat>show slave status</heartbeat>
          <dbInstance name="instanceM1" url="192.168.10.168:3306" user="mysql" password="mysql" maxCon="1000" minCon="10"
                      primary="true" readWeight="1" id="xx1">
              <property name="testOnCreate">true</property>
          </dbInstance>
          <!--<dbInstance name="instanceS1" url="ip3:3306" user="your_user" password="your_psw" maxCon="1000" minCon="10"  readWeight="2" disabled="true">-->
              <!--<property name="testOnCreate">false</property>-->
          <!--</dbInstance>-->
      </dbGroup>
  
      <dbGroup name="dbGroup2" rwSplitMode="0" delayThreshold="100" disableHA="true">
          <heartbeat errorRetryCount="1" timeout="10">show slave status</heartbeat>
          <dbInstance name="instanceM2" url="192.168.10.179:3306" user="mysql" password="mysql" maxCon="1000" minCon="10"
                      primary="true">
              <property name="testOnCreate">true</property>
          </dbInstance>
  
          <!-- can have multi read instances -->
          <!--<dbInstance name="instanceS2" url="ip4:3306" user="your_user" password="your_psw" maxCon="1000" minCon="10" usingDecrypt="true">-->
              <!--<property name="testOnCreate">true</property>-->
          <!--</dbInstance>-->
      </dbGroup>
      <dbGroup name="dbGroup3" rwSplitMode="0" delayThreshold="100" disableHA="true">
          <heartbeat errorRetryCount="1" timeout="10">show databases</heartbeat>
          <dbInstance name="instanceM2" url="192.168.10.179:9004" user="mysql" password="mysql" maxCon="1000" minCon="10"
   databaseType="clickhouse"
                      primary="true">
              <property name="testOnCreate">true</property>
          </dbInstance>
          <!-- can have multi read instances -->
          <!--<dbInstance name="instanceS2" url="ip4:9004" user="your_user" password="your_psw" maxCon="1000" minCon=
               "10" usingDecrypt="true" databaseType="clickhouse">-->
          <!--<property name="testOnCreate">true</property>-->
          <!--</dbInstance>-->
      </dbGroup>
      <!--for clickhouse-->
  </dble:db>
  

• 启动

  # cd /var/lib/dble/
  # bin/dble start
  

• 测试 启动成功

  # 管理用户
  # /usr/local/mysql/bin/mysql -h192.168.10.179 -P9066 -uman1 -p654321
  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.7.11-dble-3.22.01.1-6c90fba9b199f2829d8478cb7c6ce8ae2b922925-20220615064550 dble Server (ActionTech)
  
  Copyright (c) 2000, 2022, 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> 
  
  # 进入数据节点 
  # /usr/local/mysql/bin/mysql -h192.168.10.179 -P8066 -uroot -p123456
  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 2
  Server version: 5.7.11-dble-3.22.01.1-6c90fba9b199f2829d8478cb7c6ce8ae2b922925-20220615064550 dble Server (ActionTech)
  
  Copyright (c) 2000, 2022, 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 |
  +----------+
  | testdb   |
  +----------+
  1 row in set (0.01 sec)
  

• 建表

  具体的建表语句和实例在./conf/template_table.sql下 里面的表明对应的

• 登录dble管理端口

  mysql -p654321 -P9066 -h 127.0.0.1 -u man1
  

• 创建各个数据分片的数据库

  create database @@shardingnode='dn$1-6';
  

• 登录dble数据端口

  mysql -p123456 -P8066 -h 127.0.0.1 -u root
  

• 打开数据分片的general_log mysql实例上执行 不在dble执行

  SET GLOBAL log_output = 'TABLE';SET GLOBAL general_log = 'ON';
  

• 查询general_log

  SELECT * from mysql.general_log ORDER BY event_time DESC ;