Vitess - 手动将非分库表迁移至分库表

首先根据官方文档说明，准备好本地 Vitess 集群。

一键拉起集群和 commerce 库

$ ./101_initial_cluster.sh

准备好目标库：customer

$ ./201_customer_tablets.sh

给 customer 生成表

$ vtctlclient ApplySchema -sql-file create_commerce_schema.sql customer

sharding customer 库

$ ./301_customer_sharded.sh

查看分库 tablets:

$ vtctlclient ListShardTablets customer/0

zone1-0000000200 customer 0 master Pin-Ubuntu:15200 Pin-Ubuntu:17200 []
zone1-0000000201 customer 0 replica Pin-Ubuntu:15201 Pin-Ubuntu:17201 []
zone1-0000000202 customer 0 rdonly Pin-Ubuntu:15202 Pin-Ubuntu:17202 []

创建 vreplication

$ vtctlclient VReplicationExec zone1-200 'insert into _vt.vreplication(workflow, db_name, source, pos, max_tps, max_replication_lag, tablet_types, time_updated, transaction_timestamp, state) values ("testworkflow", "vt_customer", "keyspace:\"commerce\" shard:\"0\" filter:<rules:<match:\"customer\" filter:\"select * from customer\" > >", "", 99999, 99999, "master", 0, 0, "Running")'

登陆 zone1-200 查看 _vt.vreplication 表，可以看到记录：

# __your_vitess_data_root__： 默认是示例目录下的 vtdataroot 子目录
$ mysql -uroot --socket=__your_vitess_data_root__/vt_0000000200/mysql.sock
mysql> select id, CAST(workflow AS CHAR(100)), CAST(db_name AS CHAR(100)) from _vt.vreplication;
+----+-----------------------------+----------------------------+
| id | CAST(workflow AS CHAR(100)) | CAST(db_name AS CHAR(100)) |
+----+-----------------------------+----------------------------+
|  1 | testworkflow                | vt_customer                |
+----+-----------------------------+----------------------------+
1 row in set (0.00 sec)

插入测试数据

$ mysql -uroot -h127.0.0.1 -P 15306
mysql> use commerce;
mysql> insert into customer values (1, '1@qq.com');
mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | 0x314071712E636F6D |
+-------------+--------------------+
1 row in set (0.00 sec)

验证 customer 数据同步

$ mysql -uroot --socket=__your_vitess_data_root__/vt_0000000200/mysql.sock
mysql> use vt_customer;
Database changed
mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | 0x314071712E636F6D |
+-------------+--------------------+
1 row in set (0.00 sec)

切换 rdonly 读

$ vtctlclient SwitchReads -tablet_type=rdonly customer.testworkflow
I0715 14:12:32.538276 3128967 trace.go:151] successfully started tracing with [noop]
I0715 14:12:32.542847 3128967 main.go:64] I0715 06:12:32.542358 traffic_switcher.go:368] Migration ID for workflow testworkflow: 6740760173349126720
I0715 14:12:32.548737 3128967 main.go:64] I0715 06:12:32.548590 traffic_switcher.go:1202] Saving routing rules map[commerce.customer@rdonly:[customer.customer] customer.customer@rdonly:[customer.customer] customer@rdonly:[customer.customer]]

打开 vtctld 管理界面 http://__your_vitess_ip:15000，可以看到路由规则：

Node Data
rules: <
  from_table: "customer@rdonly"
  to_tables: "customer.customer"
>
rules: <
  from_table: "customer.customer@rdonly"
  to_tables: "customer.customer"
>
rules: <
  from_table: "commerce.customer@rdonly"
  to_tables: "customer.customer"
>

插入新数据至 customer.customer

通过切换 keyspace，往不同数据表中插入数据，并验证 switch reads 是否生效：

$ mysql -uroot -h127.0.0.1 -P 15306
mysql> use customer;
Database changed
mysql> insert into customer (customer_id, email) values (2, '2@qq.com');
Query OK, 1 row affected (0.02 sec)

mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | 0x314071712E636F6D |
|           2 | 0x324071712E636F6D |
+-------------+--------------------+
2 rows in set (0.00 sec)
# 切换至 commerce，只有一条数据
mysql> use commerce;
Database changed
mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | 0x314071712E636F6D |
+-------------+--------------------+
1 row in set (0.01 sec)

验证 switch reads 路由

# 切换至 commerce@rdonly，验证路 switch reads
mysql> use commerce@rdonly
Database changed
mysql> select * from customer;
+-------------+--------------------+
| customer_id | email              |
+-------------+--------------------+
|           1 | 0x314071712E636F6D |
|           2 | 0x324071712E636F6D |
+-------------+--------------------+
2 rows in set (0.00 sec)

SwitchReads & SwitchWrites

$ vtctlclient SwitchReads -tablet_type=replica customer.testworkflow
$ vtctlclient SwitchWrites customer.testworkflow

查看路由：

Node Data
rules: <
  from_table: "customer"
  to_tables: "customer.customer"
>
rules: <
  from_table: "commerce.customer"
  to_tables: "customer.customer"
>

有意思的是，这个时候源数据库的 tablet，也会创建一个 vreplication，用于从目标库反向同步数据回来：

# 100 是 commerce 的 master tablet
$ mysql -uroot --socket=__your_vitess_data_root__/vt_0000000100/mysql.sock
mysql> select id, CAST(workflow AS CHAR(100)), CAST(db_name AS CHAR(100)) from _vt.vreplication;
+----+-----------------------------+----------------------------+
| id | CAST(workflow AS CHAR(100)) | CAST(db_name AS CHAR(100)) |
+----+-----------------------------+----------------------------+
|  1 | testworkflow_reverse        | vt_commerce                |
+----+-----------------------------+----------------------------+
1 row in set (0.00 sec)

完整步骤总结

• 创建好目标库 tablets
• 准备好 schema 和 vshema
• 手动创建 vreplication
• SwithReads
• SwitchWrites
• 清理源数据

考虑到应用服务需要上线，需要在清理源数据之前修改代码访问的 keyspace 并上线。