Create a Data Migration Task

This document describes how to create a simple data migration task after the DM cluster is successfully deployed.

Sample scenario

Suppose that you create a data migration task based on this sample scenario:

  • Deploy two MySQL instances with binlog enabled and one TiDB instance locally
  • Use a DM-master of the DM cluster to manage the cluster and data migration tasks.

The information of each node is as follows.

InstanceServer AddressPort
MySQL1127.0.0.13306
MySQL2127.0.0.13307
TiDB127.0.0.14000
DM-master127.0.0.18261

Based on this scenario, the following sections describe how to create a data migration task.

Start upstream MySQL

Prepare 2 runnable MySQL instances. You can also use Docker to quickly start MySQL. The commands are as follows:

  1. docker run --rm --name mysql-3306 -p 3306:3306 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:5.7.22 --log-bin=mysql-bin --port=3306 --bind-address=0.0.0.0 --binlog-format=ROW --server-id=1 --gtid_mode=ON --enforce-gtid-consistency=true > mysql.3306.log 2>&1 &
  2. docker run --rm --name mysql-3307 -p 3307:3307 -e MYSQL_ALLOW_EMPTY_PASSWORD=true mysql:5.7.22 --log-bin=mysql-bin --port=3307 --bind-address=0.0.0.0 --binlog-format=ROW --server-id=1 --gtid_mode=ON --enforce-gtid-consistency=true > mysql.3307.log 2>&1 &

Prepare data

  • Write example data into mysql-3306:

    1. drop database if exists `sharding1`;
    2. create database `sharding1`;
    3. use `sharding1`;
    4. create table t1 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
    5. create table t2 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
    6. insert into t1 (id, uid, name) values (1, 10001, 'Gabriel García Márquez'), (2 ,10002, 'Cien años de soledad');
    7. insert into t2 (id, uid, name) values (3,20001, 'José Arcadio Buendía'), (4,20002, 'Úrsula Iguarán'), (5,20003, 'José Arcadio');

  • Write example data into mysql-3307:

    1. drop database if exists `sharding2`;
    2. create database `sharding2`;
    3. use `sharding2`;
    4. create table t2 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
    5. create table t3 (id bigint, uid int, name varchar(80), info varchar(100), primary key (`id`), unique key(`uid`)) DEFAULT CHARSET=utf8mb4;
    6. insert into t2 (id, uid, name, info) values (6, 40000, 'Remedios Moscote', '{}');
    7. insert into t3 (id, uid, name, info) values (7, 30001, 'Aureliano José', '{}'), (8, 30002, 'Santa Sofía de la Piedad', '{}'), (9, 30003, '17 Aurelianos', NULL);

Start downstream TiDB

To run a TiDB server, use the following command:

  1. wget https://download.pingcap.org/tidb-community-server-v7.1.5-linux-amd64.tar.gz
  2. tar -xzvf tidb-latest-linux-amd64.tar.gz
  3. mv tidb-latest-linux-amd64/bin/tidb-server ./
  4. ./tidb-server

Create a Data Migration Task - 图1

Warning

The deployment method of TiDB in this document do not apply to production or development environments.

Configure the MySQL data source

Before starting a data migration task, you need to configure the MySQL data source.

Encrypt the password

Create a Data Migration Task - 图2

Note

  • You can skip this step if the database does not have a password.
  • You can use the plaintext password to configure the source information in DM v1.0.6 and later versions.

For safety reasons, it is recommended to configure and use encrypted passwords. You can use dmctl to encrypt the MySQL/TiDB password. Suppose the password is “123456”:

  1. ./dmctl encrypt "123456"
  1. fCxfQ9XKCezSzuCD0Wf5dUD+LsKegSg=

Save this encrypted value, and use it for creating a MySQL data source in the following steps.

Edit the source configuration file

Write the following configurations to conf/source1.yaml.

  1. # MySQL1 Configuration.
  3. source-id: "mysql-replica-01"
  5. # Indicates whether GTID is enabled
  6. enable-gtid: true
  8. from:
  9.   host: "127.0.0.1"
  10.   user: "root"
  11.   password: "fCxfQ9XKCezSzuCD0Wf5dUD+LsKegSg="
  12.   port: 3306

In MySQL2 data source, copy the above configurations to conf/source2.yaml. You need to change name to mysql-replica-02 and change password and port to appropriate values.

Create a source

To load the data source configurations of MySQL1 into the DM cluster using dmctl, run the following command in the terminal:

  1. ./dmctl --master-addr=127.0.0.1:8261 operate-source create conf/source1.yaml

For MySQL2, replace the configuration file in the above command with that of MySQL2.

Create a data migration task

After importing prepared data, there are several sharded tables on both MySQL1 and MySQL2 instances. These tables have identical structure and the same prefix “t” in the table names; the databases where these tables are located are all prefixed with “sharding”; and there is no conflict between the primary keys or the unique keys (in each sharded table, the primary keys or the unique keys are different from those of other tables).

Now, suppose that you need to migrate these sharded tables to the db_target.t_target table in TiDB. The steps are as follows.

  1. Create the configuration file of the task:

    1. ---
    2. name: test
    3. task-mode: all
    4. shard-mode: "pessimistic"
    5. target-database:
    6.   host: "127.0.0.1"
    7.   port: 4000
    8.   user: "root"
    9.   password: "" # It is recommended to use password encrypted with dmctl if the password is not empty.
    11. mysql-instances:
    12.   - source-id: "mysql-replica-01"
    13.     block-allow-list:  "instance"  # This configuration applies to DM versions higher than v2.0.0-beta.2. Use black-white-list otherwise.
    14.     route-rules: ["sharding-route-rules-table", "sharding-route-rules-schema"]
    15.     mydumper-thread: 4
    16.     loader-thread: 16
    17.     syncer-thread: 16
    18.   - source-id: "mysql-replica-02"
    19.     block-allow-list:  "instance"  # This configuration applies to DM versions higher than v2.0.0-beta.2. Use black-white-list otherwise.
    20.     route-rules: ["sharding-route-rules-table", "sharding-route-rules-schema"]
    21.     mydumper-thread: 4
    22.     loader-thread: 16
    23.     syncer-thread: 16
    24. block-allow-list:  # This configuration applies to DM versions higher than v2.0.0-beta.2. Use black-white-list otherwise.
    25.   instance:
    26.     do-dbs: ["~^sharding[\\d]+"]
    27.     do-tables:
    28.     - db-name: "~^sharding[\\d]+"
    29.       tbl-name: "~^t[\\d]+"
    30. routes:
    31.   sharding-route-rules-table:
    32.     schema-pattern: sharding*
    33.     table-pattern: t*
    34.     target-schema: db_target
    35.     target-table: t_target
    36.   sharding-route-rules-schema:
    37.     schema-pattern: sharding*
    38.     target-schema: db_target

  2. To create a task using dmctl, write the above configurations to the conf/task.yaml file:

    1. ./dmctl --master-addr 127.0.0.1:8261 start-task conf/task.yaml
    1. {
    2.     "result": true,
    3.     "msg": "",
    4.     "sources": [
    5.         {
    6.             "result": true,
    7.             "msg": "",
    8.             "source": "mysql-replica-01",
    9.             "worker": "worker1"
    10.         },
    11.         {
    12.             "result": true,
    13.             "msg": "",
    14.             "source": "mysql-replica-02",
    15.             "worker": "worker2"
    16.         }
    17.     ]
    18. }

Now, you have successfully created a task to migrate the sharded tables from the MySQL1 and MySQL2 instances to TiDB.

Verify data

You can modify data in the upstream MySQL sharded tables. Then use sync-diff-inspector to check whether the upstream and downstream data are consistent. Consistent data means that the migration task works well, which also indicates that the cluster works well.