Sharding-Proxy 配置读写分离

x33g5p2x  于2021-11-13 转载在 其他  
字(2.3k)|赞(0)|评价(0)|浏览(326)

一 创建三个数据库

二 修改 conf 里面配置文件,config-master-slave.yaml 

schemaName: master_slave_db

dataSources:
  master_ds:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_master?serverTimezone=UTC&useSSL=false
    username: root
    password: Mima123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_0:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_0?serverTimezone=UTC&useSSL=false
    username: root
    password: Mima123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50
  slave_ds_1:
    url: jdbc:mysql://127.0.0.1:3306/demo_ds_slave_1?serverTimezone=UTC&useSSL=false
    username: root
    password: Mima123456
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

masterSlaveRule:
  name: ms_ds
  masterDataSourceName: master_ds
  slaveDataSourceNames:
    - slave_ds_0
    - slave_ds_1

三 启动 Sharding-Proxy 服务

四 数据库操作

# l 连接到数据库
C:\Users\chengqiuming>mysql -P3307 -uroot -p
Enter password: **********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.4-Sharding-Proxy 4.0.0

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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        |
+-----------------+
| master_slave_db |
| sharding_db     |
+-----------------+
2 rows in set (0.02 sec)

mysql> use master_slave_db;
Database changed
# 创建数据库表
mysql> create table demo_ds_master.t_order(
    -> order_id bigint not null,
    -> user_id int not null,
    -> status varchar(50),
    -> primary key(order_id)
    -> );
Query OK, 0 rows affected (0.15 sec)

mysql> create table demo_ds_slave_0.t_order(
    -> order_id bigint not null,
    -> user_id int not null,
    -> status varchar(50),
    -> primary key(order_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

mysql> create table demo_ds_slave_1.t_order(
    -> order_id bigint not null,
    -> user_id int not null,
    -> status varchar(50),
    -> primary key(order_id)
    -> );
Query OK, 0 rows affected (0.04 sec)

# 插入数据库,数据只能插入到主库
mysql> insert into t_order(order_id,user_id,status) values(11,1,'init');
Query OK, 1 row affected (0.09 sec)

# 查询数据,数据只能从从库查询,此时从库无数据,所有查询结果为空
mysql> select * from t_order;
Empty set (0.06 sec)

相关文章