DBScale快速入门手册

本手册旨在帮助用户快速搭建DBScale集群,熟悉DBScale如何管理MySQL主从架构以及如何进行数据分片。

DBScale简介

DBScale 是一款分布式数据库软件,其设计初衷就是为了解决大数据量、高负载下的数据分布问题。

它运行于客户端程序和数据库服务器之间,通过截取客户端程序发往数据库服务器的信息,按照需要进行修改等分析,然后转发给相应的数据库服务器;再从服务端接受返回的数据,转发给对应的客户端程序。

对于客户端程序而言,DBScale 就像是真正的数据库服务器,而没有必要去知道真正的数据存储位置 ,这就极大地提高了数据库系统的扩展性:当由于业务负载增大而增加数据库服务器时,只需要修改 DBScale 的配置,而没有必要重新部署客户端程序。

除了对数据分布进行处理外,DBScale 还可以对各种故障进行处理,比如网络故障、后台数据库服务器 不可用等,保证整个业务系统的稳定运行。

DBScale架构

dbscale架构

DBScale主从集群

DBScale主从集群docker镜像,在一个镜像中,包含了一组MySQL主从实例,以及一个DBScale实例。

1.下载docker镜像

$ docker pull louishust/dbscale:all_in_one_replication
$ docker run -it louishust/dbscale:all_in_one_replication /bin/bash

2.启动MySQL主从实例

$ cd /root/sandboxes/rsandbox_5_7_20
$ find master/data -type f -exec touch {} \;
$ find node1/data -type f -exec touch {} \;
$ ./start_all

3.启动DBScale

$ service dbscale start
$ service dbscale status

4.登录DBScale集群

  • 查看集群版本
$ /root/5.7.20/bin/mysql -h127.0.0.1 -udbscale -pdbscale -P3306
mysql> dbscale show version;
+-------------------+
| DBScale version   |
+-------------------+
| DBScale v2.0.2363 |
+-------------------+
1 row in set (0.01 sec)

5. 查看集群状态

  • 查看后端MySQL实例
mysql> dbscale show dataservers\G
mysql> dbscale show dataservers;
+------------+-----------+-------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+
| servername | host      | port  | username | password | status        | master_backup | extrenal_load | local_script | external_script | remote_user | remote_password | remote_port | max_needed_connection/max_mysql_connection |
+------------+-----------+-------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+
| master     | 127.0.0.1 | 20795 | dbscale  | xxxxx    | Server normal | 1             | 0             |              |                 | root        | xxxxx           | 22          | 4100/151                                   |
| slave      | 127.0.0.1 | 20796 | dbscale  | xxxxx    | Slave normal  | 1             | 0             |              |                 | root        | xxxxx           | 22          | 4100/151                                   |
+------------+-----------+-------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+

dbscale show dataservers: 查看当前集群管理的MySQL实例.

可以看出当前集群定义了两个MySQL实例,masterslave. master对应端口20795的mysql实例,slave对应端口20796的实例。status: 表示server状态, Server normal表示master状态正常,Slave normal表示slave状态正常。

  • 查看主从关系状态
mysql> dbscale show datasource type=replication;
+------+-------------+--------------+---------------------+--------------+--------------------+--------------+--------------------+-----------------+-------------------+--------------+-------------------+
| Name | Type        | SourceStatus | LoadBalanceStrategy | MasterSource | MasterSourceStatus | MasterServer | MasterServerStatus | SlaveSource     | SlaveSourceStatus | SlaveServers | SlaveServerStatus |
+------+-------------+--------------+---------------------+--------------+--------------------+--------------+--------------------+-----------------+-------------------+--------------+-------------------+
| auth | Replication | Working      | MASTER-SLAVES       | auth_master  | Working            | master       | Server normal      | auth_read_slave | Working           | slave        | Slave normal      |
| ms   | Replication | Working      | MASTER-SLAVES       | ms_master    | Working            | master       | Server normal      | ms_read_slave   | Working           | slave        | Slave normal      |
+------+-------------+--------------+---------------------+--------------+--------------------+--------------+--------------------+-----------------+-------------------+--------------+-------------------+
2 rows in set (0.00 sec)

dbscale show datasource type=replication: 查看主从状态情况

可以看出当前replication集群的主是master server, 从是slave server.

MasterServer: 表示当前replication集群的master server是哪一个.
MasterServerStatus: 表示当前replication集群的master server的状态.
SlaveServers: 表示当前replication集群的slave server是哪一个.
SlaveServerStatus: 表示当前replication集群的slave server的状态.

6.执行简单操作

mysql> use mydb;
Database changed
mysql> create table t1(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t1;
+------+
| c1   |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

7.故障演练

模拟master server宕机的情况,看dbscale是否仍然可用。

  • 停止master server
$ /root/sandboxes/rsandbox_5_7_20/master/stop
  • 查看集群状态
mysql> dbscale show datasource type=replication;
+------+-------------+--------------+---------------------+-----------------+--------------------+--------------+--------------------+-------------+-------------------+--------------+-------------------+
| Name | Type        | SourceStatus | LoadBalanceStrategy | MasterSource    | MasterSourceStatus | MasterServer | MasterServerStatus | SlaveSource | SlaveSourceStatus | SlaveServers | SlaveServerStatus |
+------+-------------+--------------+---------------------+-----------------+--------------------+--------------+--------------------+-------------+-------------------+--------------+-------------------+
| auth | Replication | Working      | MASTER-SLAVES       | auth_read_slave | Working            | slave        | Server normal      | auth_master | Stop              | master       | Server down       |
| ms   | Replication | Working      | MASTER-SLAVES       | ms_read_slave   | Working            | slave        | Server normal      | ms_master   | Stop              | master       | Server down       |
+------+-------------+--------------+---------------------+-----------------+--------------------+--------------+--------------------+-------------+-------------------+--------------+-------------------+
2 rows in set (0.00 sec)

可以看到replication的拓扑发生了变化,MasterServer变成了slave server,SlaveServers变成了master server,SlaveServerStatus变成了Server down,表示master server停止工作了。

  • 执行简单操作
mysql> use mydb;
Database changed
mysql> insert into t1 values(2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1;
+------+
| c1   |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

可以看出,虽然master server宕机了,DBScale自动进行了故障切换,将slave server提升为replication组的master,继续对外提供服务。

DBScale分片集群

这一节用户手动启动各个不同角色的docker实例,完成部署。

1.下载docker镜像

$ docker pull mysql:5.7.21
$ docker pull louishust/dbscale:dbscale
$ export MYSQL_ROOT_PASSWORD=dbscale

2.创建网络

$ docker network create dbscale-cluster-net

3.创建认证节点

docker run --name=auth_master --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=1
docker run --name=auth_slave --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=2

4.创建数据节点

创建两个分片,每个分片由一主一从组成,共四个MySQL实例.

docker run --name=part1_master --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=3
docker run --name=part1_slave --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=4
docker run --name=part2_master --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=5
docker run --name=part2_slave --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=6

5.创建DBScale节点

docker run -it --name=dbscale --network=dbscale-cluster-net  louishust/dbscale:dbscale /bin/bash
# service dbscale start

此容器不可退出.

6.连接DBScale

docker exec -it part1_master mysql -hdbscale -P3306 -uroot -pdbscale --skip-ssl mysql
mysql> dbscale show version;
+-----------------+
| DBScale version |
+-----------------+
| DBScale v2.0.0  |
+-----------------+
1 row in set (0.00 sec)
mysql> dbscale show dataservers;
+--------------+------------+------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+
| servername   | host       | port | username | password | status        | master_backup | extrenal_load | local_script | external_script | remote_user | remote_password | remote_port | max_needed_connection/max_mysql_connection |
+--------------+------------+------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+
| auth_master  | 172.18.0.2 | 3306 | root     | xxxxx    | Server normal | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
| auth_slave   | 172.18.0.3 | 3306 | root     | xxxxx    | Slave normal  | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
| part1_master | 172.18.0.4 | 3306 | root     | xxxxx    | Server normal | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
| part1_slave  | 172.18.0.5 | 3306 | root     | xxxxx    | Slave normal  | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
| part2_master | 172.18.0.6 | 3306 | root     | xxxxx    | Server normal | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
| part2_slave  | 172.18.0.7 | 3306 | root     | xxxxx    | Slave normal  | 1             | 0             |              |                 | root        | xxxxx           | 22          | 300/151                                    |
+--------------+------------+------+----------+----------+---------------+---------------+---------------+--------------+-----------------+-------------+-----------------+-------------+--------------------------------------------+
6 rows in set (0.00 sec)
mysql> dbscale show datasource type=replication;
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
| Name     | Type        | SourceStatus | LoadBalanceStrategy | MasterSource          | MasterSourceStatus | MasterServer | MasterServerStatus | SlaveSource               | SlaveSourceStatus | SlaveServers | SlaveServerStatus |
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
| auth     | Replication | Working      | MASTER-SLAVES       | auth_auth_master      | Working            | auth_master  | Server normal      | auth_read_auth_slave      | Working           | auth_slave   | Slave normal      |
| ds_part1 | Replication | Working      | MASTER-SLAVES       | ds_part1_part1_master | Working            | part1_master | Server normal      | ds_part1_read_part1_slave | Working           | part1_slave  | Slave normal      |
| ds_part2 | Replication | Working      | MASTER-SLAVES       | ds_part2_part2_master | Working            | part2_master | Server normal      | ds_part2_read_part2_slave | Working           | part2_slave  | Slave normal      |
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
3 rows in set (0.01 sec)

7.创建分片表

  • 查看分片策略
mysql> dbscale show partition_scheme;
+-------------+------+----------+----------------------------+---------------+
| scheme_name | type | is_shard | partitions                 | relate_tables |
+-------------+------+----------+----------------------------+---------------+
| part        | HASH | 1        | ds_part1(*3) ds_part2(*3)  |               |
+-------------+------+----------+----------------------------+---------------+
1 row in set (0.01 sec)

part分片策略包含两个分片数据源ds_part1ds_part2.

  • 创建分片表

首先需要创建库策略:

mysql> dbscale dynamic add schema dataspace mydb datasource=ds_part1;
Query OK, 0 rows affected (0.01 sec)
mysql> dbscale dynamic add partition_table dataspace mydb.t1 partition_key=c1 partition_scheme=part;

上面的语句首先指定的mydb库的默认存储位置为ds_part1,然后对mydb中的t1表添加了分片策略,分片列使用c1列,分片scheme使用part.

然后可以创建库以及表。

mysql> create database mydb;
Query OK, 6 rows affected (0.01 sec)

mysql> create table mydb.t1(c1 int ,c2 int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into mydb.t1 values(1,1);
Query OK, 1 row affected (0.02 sec)

mysql> insert into mydb.t1 values(2,2);
Query OK, 1 row affected (0.00 sec)

mysql> dbscale show partition table mydb.t1 shard map;
+-------------------------+
| option                  |
+-------------------------+
| [partition-scheme part] |
| ds_part1:0:2:4          |
| ds_part2:1:3:5          |
+-------------------------+

可以看出mydb.t1表分布在part1_masterpart2_master机器上. 每个机器上内部其实维护了三个分片子表, ds_part1上为0,2,4, ds_part2上为1,3,5.

8.添加新的数据节点

这里通过为part分片策略添加一个主从分片数据源ds_part3。

首先创建一对MySQL节点:

docker run --name=part3_master --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=7
docker run --name=part3_slave --network=dbscale-cluster-net -e MYSQL_ROOT_PASSWORD="dbscale" -d mysql --log-bin --server-id=8

然后将这组主从作为新的datasource ds_part3:

mysql> dbscale dynamic add dataserver server_name=part3_master,server_host=part3_master,server_port=3306,server_user=root,server_password="dbscale";
Query OK, 0 rows affected (0.00 sec)

mysql> dbscale dynamic add dataserver server_name=part3_slave,server_host=part3_slave,server_port=3306,server_user=root,server_password="dbscale";
Query OK, 0 rows affected (0.01 sec)

mysql> dbscale dynamic add replication datasource ds_part3 master=part3_master-4-20-8-10 slave=part3_slave-4-20-8-10 load_balance_strategy=master_slaves;
Query OK, 0 rows affected (0.00 sec)

mysql> dbscale show datasource type=replication;
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
| Name     | Type        | SourceStatus | LoadBalanceStrategy | MasterSource          | MasterSourceStatus | MasterServer | MasterServerStatus | SlaveSource               | SlaveSourceStatus | SlaveServers | SlaveServerStatus |
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
| auth     | Replication | Working      | MASTER-SLAVES       | auth_auth_master      | Working            | auth_master  | Server normal      | auth_read_auth_slave      | Working           | auth_slave   | Slave normal      |
| ds_part1 | Replication | Working      | MASTER-SLAVES       | ds_part1_part1_master | Working            | part1_master | Server normal      | ds_part1_read_part1_slave | Working           | part1_slave  | Slave normal      |
| ds_part2 | Replication | Working      | MASTER-SLAVES       | ds_part2_part2_master | Working            | part2_master | Server normal      | ds_part2_read_part2_slave | Working           | part2_slave  | Slave normal      |
| ds_part3 | Replication | Working      | MASTER-SLAVES       | ds_part3_part3_master | Working            | part3_master | Server normal      | ds_part3_read_part3_slave | Working           | part3_slave  | Slave normal      |
+----------+-------------+--------------+---------------------+-----------------------+--------------------+--------------+--------------------+---------------------------+-------------------+--------------+-------------------+
4 rows in set (0.01 sec)

可以看到datasource ds_part3创建成功。

9.迁移mydb.t1表到新的数据分片

mysql> dbscale show partition table mydb.t1 shard map;
+-------------------------+
| option                  |
+-------------------------+
| [partition-scheme part] |
| ds_part1:0:2:4          |
| ds_part2:1:3:5          |
+-------------------------+
3 rows in set (0.00 sec)

可以看出目前mydb.t1分布在ds_part1ds_part2上。

现在将mydb.t1表的部分数据迁移到ds_part3上:

mysql> dbscale migrate shard mydb.t1 from ds_part1 to ds_part3;
Query OK, 0 rows affected (4.03 sec)

mysql> dbscale show partition table mydb.t1 shard map;
+----------------------------------------------------------------+
| option                                                         |
+----------------------------------------------------------------+
| [partition-scheme part:ds_part1:0:2-ds_part2:1:3:5-ds_part3:4] |
| ds_part1:0:2                                                   |
| ds_part2:1:3:5                                                 |
| ds_part3:4                                                     |
+----------------------------------------------------------------+
4 rows in set (0.01 sec)

mysql> dbscale migrate shard mydb.t1 from ds_part2 to ds_part3;
Query OK, 0 rows affected (4.04 sec)

mysql> dbscale show partition table mydb.t1 shard map;
+----------------------------------------------------------------+
| option                                                         |
+----------------------------------------------------------------+
| [partition-scheme part:ds_part1:0:2-ds_part2:1:3-ds_part3:4:5] |
| ds_part1:0:2                                                   |
| ds_part2:1:3                                                   |
| ds_part3:4:5                                                   |
+----------------------------------------------------------------+
4 rows in set (0.00 sec)

经过两次migrate命令,可以将ds_part1ds_part2上的部分数据迁移到ds_part3上。