《童虎学习笔记》16分钟ShardingSphere搭建MySQL分库分表

本文章配套视频	https://www.ixigua.com/7094648549387797005
本专栏全部文章	https://blog.csdn.net/tonghu_note/category_11716546.html
总目录	https://blog.csdn.net/tonghu_note/article/details/124333034

来我的dou音 aa10246666， 看配套视频

---

一、实战环境

机器名	用途	版本	IP地址
Shard1_node1	第1个分片	mysql  Ver 8.0.28	10.211.55.9
Shard2_node2	第2个分片	mysql  Ver 8.0.28	10.211.55.4
ShardingSphere_Proxy_node3	中间件	5.1.0	10.211.55.6
client_node4	客户端测试	mysql  Ver 8.0.28	10.211.55.7

---

二、 Shard1_node1和Shard2_node2上准备环境

1、创建ShardingSphere连接MySQL所使用的数据库和帐号

Shard1_node1上创建数据库 shard1和帐号appuser

mysql> create database shard1;
Query OK, 1 row affected (0.01 sec)

mysql> create user appuser@'%' identified with mysql_native_password by '111';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on shard1.* to appuser@'%';
Query OK, 0 rows affected (0.00 sec) 

Shard2_node2上创建数据库 shard2和帐号appuser

mysql> create database shard2;
Query OK, 1 row affected (0.01 sec)

mysql> create user appuser@'%' identified with mysql_native_password by '111';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on shard2.* to appuser@'%';
Query OK, 0 rows affected (0.00 sec) 

---

三、ShardingSphere-Proxy上准备环境

1、安装ShardingSphere-Proxy

安装及配置jdk(jdk的版本要依据你cpu来选择，我用的是mac虚出来的虚机)

tar xzf jdk-8u202-linux-arm64-vfp-hflt.tar.gz

mv jdk1.8.0_202 /usr/local/

/etc/profile最后添加如下配置

PATH=/usr/local/jdk1.8.0_202/bin:$PATH

source /etc/profile

 安装及配置ShardingSphere-Proxy

tar xzf apache-shardingsphere-5.1.0-shardingsphere-proxy-bin.tar.gz

mv apache-shardingsphere-5.1.0-shardingsphere-proxy-bin /usr/local/

2、下载mysql jdbc驱动并配置

到官网下载适合你的驱动 https://dev.mysql.com/downloads/connector/j/

我是debian系统，以下是我的安装方式，大家可以根据自已的操作系统环境来安装

root@node3:~# dpkg -i mysql-connector-java_8.0.29-1debian11_all.deb

root@node3:~# cd /usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib

root@node3:/usr/local/apache-shardingsphere-5.1.0-shardingsphere-proxy-bin/lib# cp /usr/share/java/mysql-connector-java-8.0.29.jar . 

 3、修改配置文件 server.yaml

rules:
  - !AUTHORITY
    users:
      - root@%:123
    provider:
      type: ALL_PRIVILEGES_PERMITTED

props:
  sql-show: true

4、修改配置文件 config-sharding.yaml

schemaName: sharding_db

dataSources:
  ds_0:
    url: jdbc:mysql://10.211.55.9:3306/shard1?serverTimezone=UTC&useSSL=false
    username: appuser
    password: 111
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMill