部署sharding-proxy

部署sharding-proxy和分库分表参考另外一篇文章
https://www.jianshu.com/p/cb22a039795f

读写分离

1.把配置文件放到conf目录下

config-master-slave.yaml

schemaName: master_slave_db

dataSources:
  ds_master:
    url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_master_0?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds_slave0:
    url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_master_0_slave_0?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65
  ds_slave1:
    url: jdbc:mysql://sharding-sphere-mysql:3306/demo_ds_master_0_slave_1?serverTimezone=UTC&useSSL=false
    username: root
    password:
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 65

masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_master
  slaveDataSourceNames:
    - ds_slave0
    - ds_slave1

2.重启sharding-proxy

能同时识别多个配置，但是配置不能动态加载，所以需要重新启动一下sharding-proxy
重启好了之后，可以看到配置文件已经识别到了，proxy中显示了master_slave_db这个库

mysql> show databases;
+-----------------+
| Database        |
+-----------------+
| master_slave_db |
| sharding_db     |
+-----------------+

3.为master和slave分别创建表

可以看到下面有两次show tables操作，第一次没有查出来任何表，第二次查出来两张表，说明show tables操作是作用在了master上。

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0_slave_0.t_order_0 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0_slave_0.t_order_1 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.16 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0_slave_1.t_order_0 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.13 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0_slave_1.t_order_1 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0.t_order_0 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.20 sec)

mysql> CREATE TABLE IF NOT EXISTS demo_ds_master_0.t_order_1 (order_id BIGINT NOT NULL AUTO_INCREMENT, user_id INT NOT NULL, status VARCHAR(50), PRIMARY KEY (order_id));
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+---------------------------+
| Tables_in_master_slave_db |
+---------------------------+
| t_order_0                 |
| t_order_1                 |
+---------------------------+

4.插入数据（写）

#直接执行insert是把数据插入到了master中  （master节点写操作）
mysql>  INSERT INTO t_order_0 VALUES (null,1,'init');
Query OK, 1 row affected (0.02 sec)

#直接查询 t_order_0 查不到，因为查的是slave_0和slave_1的，随机选（slave节点读操作）
mysql> select * from t_order_0;
Empty set (0.01 sec)

#如果指定库查询可以看到，数据已经插入到了master中
mysql> select * from demo_ds_master_0.t_order_0;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | init   |
+----------+---------+--------+
1 row in set (0.01 sec)

5.查询数据（读）

往demo_ds_master_0_slave_0和demo_ds_master_0_slave_1中插入数据

mysql>  INSERT INTO demo_ds_master_0_slave_0.t_order_0 VALUES (null,1,'init');
Query OK, 1 row affected (0.03 sec)

mysql>  INSERT INTO demo_ds_master_0_slave_1.t_order_0 VALUES (null,1,'init');
Query OK, 1 row affected (0.04 sec)

多执行几次插入不同的数据，然后执行查询操作
可以发现不明确指定库名的话，select * from t_order_0查询出来的是两个slave节点中随机查询一个节点。但是不会有master节点的数据。

#查出来是slave0，的信息
mysql> select * from t_order_0;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | init   |
|        2 |       1 | init   |
|        3 |       1 | init   |
+----------+---------+--------+
3 rows in set (0.00 sec)

#查出来是slave1，的信息
mysql> select * from t_order_0;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | init   |
|        2 |       2 | init   |
|        3 |       1 | init   |
|        4 |       3 | init   |
+----------+---------+--------+
4 rows in set (0.00 sec)

#查出来是slave0，的信息
mysql> select * from t_order_0;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | init   |
|        2 |       1 | init   |
|        3 |       1 | init   |
+----------+---------+--------+
3 rows in set (0.00 sec)

#查出来是slave1，的信息
mysql> select * from t_order_0;
+----------+---------+--------+
| order_id | user_id | status |
+----------+---------+--------+
|        1 |       1 | init   |
|        2 |       2 | init   |
|        3 |       1 | init   |
|        4 |       3 | init   |
+----------+---------+--------+
4 rows in set (0.01 sec)

sharding-proxy 读写分离