OneProxy :: 动态修改和加载Sharding表分片,实现中间件零重起在线维护

在OneProxy 6.1.0版本中已经将大部份的配置都移到数据库中,以方便在线维护。在最新的6.1.3版本中,已经将分区表的分区变更操作在线化,通过使用带版本号的分区表定义的方法,完美地解决了Sharding表分片的在线更改。在OneProxy中定义如下分片表:

  {
     "table"      : "my_date",
     "version"    : 1,
     "pkey"       : "id",
     "type"       : "timestamp",
     "method"     : "range",
     "partitions":
        [
           { "suffix" : "_1601", "group": "server1", "value" : "2016/02/01" },
           { "suffix" : "_1602", "group": "server1", "value" : "2016/03/01" },
           { "suffix" : "_1603", "group": "server1", "value" : "2016/04/01" },
           { "suffix" : "_1604", "group": "server1", "value" : "2016/05/01" }
        ]
  }

在OneProxy的管理端口中可以查看分区表的定义,如下所示:

mysql> list tables;
+-----------+---------+------+-----------+--------+------------+----------+----------+
| TABLENAME | VERSION | KEY  | TYPE      | METHOD | PARTITIONS | KEYCACHE | TEMPLATE |
+-----------+---------+------+-----------+--------+------------+----------+----------+
| my_date   |       1 | id   | timestamp | range  |          4 |        0 | NULL     |
+-----------+---------+------+-----------+--------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> list partitions my_date;
+-----------+--------+--------------+---------+------------+--------+
| TABLENAME | PARTID | PARTITION    | GROUP   | VALUES     | STATUS |
+-----------+--------+--------------+---------+------------+--------+
| my_date   | 0      | my_date_1601 | server1 | 2016/02/01 | RW     |
| my_date   | 1      | my_date_1602 | server1 | 2016/03/01 | RW     |
| my_date   | 2      | my_date_1603 | server1 | 2016/04/01 | RW     |
| my_date   | 3      | my_date_1604 | server1 | 2016/05/01 | RW     |
+-----------+--------+--------------+---------+------------+--------+
4 rows in set (0.00 sec)

接下来更改分区表配置文件,新增一个分片,并更新版本号,如下所示:

  {
    "table"      : "my_date",
    "version"    : 2,
    "pkey"       : "id",
    "type"       : "timestamp",
    "method"     : "range",
    "partitions":
      [
         { "suffix" : "_1601", "group": "server1", "value" : "2016/02/01" },
         { "suffix" : "_1602", "group": "server1", "value" : "2016/03/01" },
         { "suffix" : "_1603", "group": "server1", "value" : "2016/04/01" },
         { "suffix" : "_1604", "group": "server1", "value" : "2016/05/01" },
         { "suffix" : "_1701", "group": "server1", "value" : "2017/02/01" }
      ]
  }

在OneProxy的管理端口中运行“load tables”命令,进行动态装载,如下所示:

mysql> load tables '/data/oneproxy/conf/date.txt';
Query OK, 0 rows affected (0.00 sec)

再次在OneProxy管理端口中验证分区表的定义,看看是否被更改,如下所示:

mysql> list tables;
+-----------+---------+------+-----------+--------+------------+----------+----------+
| TABLENAME | VERSION | KEY  | TYPE      | METHOD | PARTITIONS | KEYCACHE | TEMPLATE |
+-----------+---------+------+-----------+--------+------------+----------+----------+
| my_date   |       2 | id   | timestamp | range  |          5 |        0 | NULL     |
+-----------+---------+------+-----------+--------+------------+----------+----------+
1 row in set (0.00 sec)

mysql> list partitions my_date;
+-----------+--------+--------------+---------+------------+--------+
| TABLENAME | PARTID | PARTITION    | GROUP   | VALUES     | STATUS |
+-----------+--------+--------------+---------+------------+--------+
| my_date   | 0      | my_date_1601 | server1 | 2016/02/01 | RW     |
| my_date   | 1      | my_date_1602 | server1 | 2016/03/01 | RW     |
| my_date   | 2      | my_date_1603 | server1 | 2016/04/01 | RW     |
| my_date   | 3      | my_date_1604 | server1 | 2016/05/01 | RW     |
| my_date   | 4      | my_date_1701 | server1 | 2017/02/01 | RW     |
+-----------+--------+--------------+---------+------------+--------+
5 rows in set (0.00 sec)

可以在OneProxy管理端口中,执行SQL查询语句,以验证新配置是否生效,如下所示:

mysql> select * from my_date;
+------------------------------------+
| TABLES                             |
+------------------------------------+
| select * from my_date_1601 my_date |
| select * from my_date_1602 my_date |
| select * from my_date_1603 my_date |
| select * from my_date_1604 my_date |
| select * from my_date_1701 my_date |
+------------------------------------+
5 rows in set (0.00 sec)

需要注意的是,这里仅仅更改了OneProxy中的分表区定义,对底层的数据并没有任何操作,因此在更新分区表定义前后,需要注意前后数据分布的兼容性问题,手工创建新增的表分片,以及删除移除的表分表,这些都需要DBA进行手工操作。