## Module 2: Creating a Replication Task

### Step 1. SSH to PD1

  ```bash
  ./ssh-to-pd1.sh
  ```

  ```output
  ...
  Last login: Sun Oct 15 21:44:34 2023
  ...
  ```

### Step 2. Start MySQL server on PD1

  ```bash
  sudo service mysqld start
  ```

### Step 3. Login to MySQL

+ Login MySQL server on port 3306 as `root@'localhost'` with the password `q1w2e3R4_`.

  ```bash
  mysql -u root -p -h 127.0.0.1 -P 3306 -pq1w2e3R4_
  ```

### Step 4. Create a user for the change capture task

  ```sql
  CREATE USER cdc_user@'%' IDENTIFIED BY 'q1w2e3R4_';
  ```

  ```output
  mysql> CREATE USER cdc_user@'%' IDENTIFIED BY 'q1w2e3R4_';
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  GRANT ALL PRIVILEGES on *.* to cdc_user@'%';
  ```

  ```output
  mysql> GRANT ALL PRIVILEGES on *.* to cdc_user@'%';
  Query OK, 0 rows affected (0.00 sec)
  ```

  ```sql
  EXIT;
  ```

  ```output
  mysql> EXIT;
  ```

### Step 5. Load timezone for upstream MySQL database

  ```bash
  mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql -pq1w2e3R4_
  ```

  ```output
  mysql: [Warning] Using a password on the command line interface can be insecure.
  Warning: Unable to load '/usr/share/zoneinfo/iso3166.tab' as time zone. Skipping it.
  Warning: Unable to load '/usr/share/zoneinfo/leapseconds' as time zone. Skipping it.
  Warning: Unable to load '/usr/share/zoneinfo/tzdata.zi' as time zone. Skipping it.
  Warning: Unable to load '/usr/share/zoneinfo/zone.tab' as time zone. Skipping it.
  Warning: Unable to load '/usr/share/zoneinfo/zone1970.tab' as time zone. Skipping it.
  ```

### Step 6. Create schema for the downstream MySQL

+ Connect to MySQL with port 3306 as a downstream database, and create a new database called `test`, with a table called `T1`. The `T1` table should have a primary key column called `id` and a `VARCHAR(20)` column called `name`. And then exit the mysql client.

  ```bash
  mysql -u root -h 127.0.0.1 -P 3306 -pq1w2e3R4_
  ```

  ```sql
  DROP DATABASE IF EXISTS test;
  CREATE DATABASE test;
  CREATE TABLE test.T1(id INT PRIMARY KEY, name VARCHAR(20));
  SELECT * FROM test.T1;
  ```

  ```sql
  EXIT;
  ```

### Step 7. Open a second terminal (Terminal 2), and create schema on TiDB

+ Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab.

  ```bash
  mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot
  ```

  ```sql
  DROP DATABASE IF EXISTS test;
  CREATE DATABASE test;
  CREATE TABLE test.T1(id INT PRIMARY KEY, name VARCHAR(20));
  SELECT * FROM test.T1;
  EXIT;
  ```

### Step 8. In Terminal 2, create a replication task

+ In [Terminal 2](ssh://monitor1-ssh#2) on `monitor1`, execute `cdc:v${LAB:TIDB_VERSION} cli` command to create a replication task. Pay attention to the `cdc:v${LAB:TIDB_VERSION}` version, if you omit the version tag, the most up-to-date version might not be compatible with our TiDB cluster. You might receive a warning message, stating that some tables are not eligible to replicate, because they do not have a primary key or a not-null unique key. Those are the tables left over from previous labs. Simply choose `Y` to ignore those tables.

+ Here are some detail explanations of the commands
  + `--pd=http://${HOST_PD1_PRIVATE_IP}:2379`: IP address of one of the PD nodes.
  + `--sink-uri="mysql://cdc_user:q1w2e3R4_@${HOST_PD1_PRIVATE_IP}:3306/"`: The downstream address of the replication task: ${HOST_PD1_PRIVATE_IP}, and the port is: 3306
  + `--changefeed-id="replication-task-1" --sort-engine="unified"`: The ID of the replication task.

  ```bash
  tiup cdc:v${LAB:TIDB_VERSION} cli changefeed create --pd=http://${HOST_PD1_PRIVATE_IP}:2379 \
     --sink-uri="mysql://cdc_user:q1w2e3R4_@${HOST_PD1_PRIVATE_IP}:3306/" \
     --changefeed-id="replication-task-1" --sort-engine="unified"
  ```

  ```output
  tiup cdc:v${LAB:TIDB_VERSION} cli changefeed create --pd=http://${HOST_PD1_PRIVATE_IP}:2379 \

  > --sink-uri="mysql://cdc_user:q1w2e3R4_@${HOST_PD1_PRIVATE_IP}:3306/" \
  > --changefeed-id="replication-task-1" --sort-engine="unified"
  Starting component `cdc`: /home/ec2-user/.tiup/components/cdc/v${LAB:TIDB_VERSION}/cdc cli changefeed create --pd=http://10.80.0.224:2379 --sink-uri=mysql://cdc_user:q1w2e3R4_@10.80.0.224:3306/ --changefeed-id=replication-task-1 --sort-engine=unified
  Create changefeed successfully!
  ID: replication-task-1
  Info: {"upstream-id":0,"sink-uri":"mysql://cdc_user:q1w2e3R4_@10.80.0.224:3306/","opts":{},"create-time":"2023-02-13T12:54:53.673149259Z","start-ts":439430124303286275,"target-ts":0,"admin-job-type":0,"sort-engine":"unified","sort-dir":"","config":{"case-sensitive":true,"enable-old-value":true,"force-replicate":false,"check-gc-safe-point":true,"filter":{"rules":["*.*"],"ignore-txn-start-ts":null},"mounter":{"worker-num":16},"sink":{"dispatchers":null,"protocol":"","column-selectors":null,"schema-registry":""},"cyclic-replication":{"enable":false,"replica-id":0,"filter-replica-ids":null,"id-buckets":0,"sync-ddl":false},"consistent":{"level":"none","max-log-size":64,"flush-interval":1000,"storage":""}},"state":"normal","error":null,"sync-point-enabled":false,"sync-point-interval":600000000000,"creator-version":"v6.1.0"}
  ```

### Step 9. In Terminal 2, query the replication task list

  ```bash
  tiup cdc:v${LAB:TIDB_VERSION} cli changefeed list --pd=http://${HOST_PD1_PRIVATE_IP}:2379
  ```

  ```output
  Starting component `cdc`: /home/ec2-user/.tiup/components/cdc/v${LAB:TIDB_VERSION}/cdc cli changefeed list --pd=http://10.80.0.224:2379
  [
  {
     "id": "replication-task-1",
     "summary": {
     "state": "normal",
     "tso": 439430205069852673,
     "checkpoint": "2023-02-13 13:00:01.713",
     "error": null
     }
  }
  ]
  ```

### Step 10. In Terminal 2, query the specific replication task

  ```bash
  tiup cdc:v${LAB:TIDB_VERSION} cli changefeed query --pd=http://${HOST_PD3_PRIVATE_IP}:2379 --changefeed-id=replication-task-1
  ```

  ```output
  Starting component `cdc`: /home/ec2-user/.tiup/components/cdc/v${LAB:TIDB_VERSION}/cdc cli changefeed query --pd=http://10.80.0.12:2379 --changefeed-id=replication-task-1
  {
     "upstream_id": 7231813390813770262,
     "namespace": "default",
     "id": "replication-task-1",
     "sink_uri": "mysql://cdc_user:xxxxx@10.90.3.188:3306/",
     "config": {
     "memory_quota": 1073741824,
     "case_sensitive": true,
     "enable_old_value": true,
     "force_replicate": false,
     "ignore_ineligible_table": false,
     "check_gc_safe_point": true,
     "enable_sync_point": false,
     "bdr_mode": false,
     "sync_point_interval": 600000000000,
     "sync_point_retention": 86400000000000,
     "filter": {
        "rules": [
           "*.*"
        ],
        "event_filters": null
     },
     "mounter": {
        "worker_num": 16
     },
     "sink": {
        "protocol": "",
        "schema_registry": "",
        "csv": {
           "delimiter": ",",
           "quote": "\"",
           "null": "\\N",
           "include_commit_ts": false
        },
        "column_selectors": null,
        "transaction_atomicity": "",
        "encoder_concurrency": 16,
        "terminator": "\r\n",
        "date_separator": "none",
        "enable_partition_separator": false
     },
     "consistent": {
        "level": "none",
        "max_log_size": 64,
        "flush_interval": 2000,
        "storage": ""
     }
     },
     "create_time": "2023-05-11 07:09:04.259",
     "start_ts": 441395168967655437,
     "resolved_ts": 441395195247853571,
     "target_ts": 0,
     "checkpoint_tso": 441395195247853571,
     "checkpoint_time": "2023-05-11 07:10:44.372",
     "sort_engine": "unified",
     "state": "normal",
     "error": null,
     "error_history": null,
     "creator_version": "",
     "task_status": [
     {
        "capture_id": "4399c858-e811-4d9e-9b08-fb5690c097ec",
        "table_ids": [],
        "table_operations": null
     },
     {
        "capture_id": "5982fa7d-c755-409a-88d5-03527c8a0fcd",
        "table_ids": [],
        "table_operations": null
     },
     {
        "capture_id": "1c15e075-1435-464c-af66-92ba0286a7fe",
        "table_ids": [
           80
        ],
        "table_operations": null
     }
     ]
  }
  ```

### Step 11. In Terminal 2, insert data into the upstream TiDB

  ```bash
  mysql -h${HOST_DB1_PRIVATE_IP} -P4000 -uroot
  ```

  ```sql
  SELECT * FROM test.T1;
  ```

  ```output
  mysql> SELECT * FROM test.T1;
  Empty set (0.01 sec)
  ```

  ```sql
  INSERT INTO test.T1 VALUES(1, 'Tom');
  ```

  ```output
  mysql> INSERT INTO test.T1 VALUES(1, 'Tom');
  Query OK, 1 row affected (0.00 sec)
  ```

  ```sql
  SELECT * FROM test.T1;
  ```

  ```output
  mysql> SELECT * FROM test.T1;
  +----+------+
  | id | name |
  +----+------+
  |  1 | Tom  |
  +----+------+
  1 row in set (0.00 sec)
  ```

  ```sql
  EXIT;
  ```

### Step 12. Check the results in the downstream MySQL

- In [Terminal 1](ssh://monitor1-ssh#2), execute the following command.

  ```bash
  mysql -u cdc_user -h 127.0.0.1 -P 3306 -pq1w2e3R4_
  ```

  ```sql
  SELECT * FROM test.T1;
  ```

  ```output
  mysql> SELECT * FROM test.T1;
  +----+------+
  | id | name |
  +----+------+
  |  1 | Tom  |
  +----+------+
  1 row in set (0.00 sec)
  ```

  ```sql
  EXIT;
  ```

  ```output
  mysql> EXIT;
  ```

+ Exit from PD1 and return to Control Machine.

  ```bash
  exit
  ```