# Importing the Data into TiDB Using TiDB Lightning

### 概要
+ In these exercises, you import the data into TiDB using `TiDB Lightning`.
+ The following shows the deployment topology diagram of TiDB cluster.

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercisewithLighting_aa_003_20230301.png)

### 注意
+ Click the private key file [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) to download it. It is required to log into the Control Machine for this demo.
+ Commands and SQL statements presented on this page are clickable for easy copy and paste.
+ The required IP addresses for specific commands will be substituted with the actual ones automatically once you have set up the lab environment after Step 1.
+ Your environment may be different with these exercises, including but not limited to IP, port, username, password and directory. Example output is displayed in a gray background in this guide.
+ The `$` in your terminal window is an OS prompt. The `tidb>` or `mysql>` in your terminal window is SQL command prompt.


### 前提条件
+ You have downloaded and secured the TiUP Control Machine private key: `chmod 400 [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)`
+ The `ssh-agent` is running on your laptop. Windows users please refer to this [guide](https://github.com/pingcap/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md).
+ You have added identity to authentication agent: `ssh-add [pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon)`       

## 演習 1
### Importing CSV File into TiDB

### 概要
+ In this exercise, you import a CSV file into TiDB using `TiDB Lightning`. You will perform all tasks on node Control Machine (monitor1_address).

### 所要時間
+ This exercise should take you approximately 30 minutes to complete.

### タスク
+ Install `TiDB Lightning` using `TiUP`.
+ Prepare the data.
+ Create schema in TiDB.
+ Config `TiDB Lightning`.
+ Import data into TiDB using `TiDB Lightning`.

### 手順
1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. The returned message may be slightly different from what is displayed here:
   ```command
   $ ssh -A ec2-user@monitor1_address
   ```
   ```output
      ,     #_
      ~\_  ####_        Amazon Linux 2
     ~~  \_#####\
     ~~     \###|       
     ~~       \#/ ___
      ~~       V~' '->
       ~~~         /    A newer version of Amazon Linux is available!
         ~~._.   _/
            _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
          _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
   ```

2. Create a user for Lightning task:
   ```command
   $ source hosts-env.sh
   ```
   
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} -uroot -P4000
   ```

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

   ```command
   GRANT ALL PRIVILEGES ON *.* TO ltask@'%';
   ```
   ```output
   mysql> GRANT ALL PRIVILEGES ON *.* TO ltask@'%';
   Query OK, 0 rows affected (0.05 sec)
   ```

   ```command
   EXIT;
   ```
   ```output
   mysql> EXIT;
   ```

3. Install the `TiDB Lightning` on node `monitor1`. You are suggested to use **`TiUP`** to install the `TiDB Lightning`.
   ```command
   $ tiup install tidb-lightning:v6.5.1
   ```

4. Extract the data directories archive from `tidb-admin-dataset.zip` to `/home/ec2-user/stage/` on node `monitor1`. Enter the following command at the terminal prompt and receive the results shown: 
   ```command
   $ unzip -u stage/tidb-admin-dataset.zip -d stage/
   ```
   ```output
   Archive:  tidb-admin-dataset.zip
      creating: TiDB-Administration-exercise-data/
      creating: TiDB-Administration-exercise-data/exercise-12/
    ...
     inflating: TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_1/my_db.my_table_1.000000000.sql 
   ```

5. List the contents of the CSV data directory. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ ls ~/stage/TiDB-Administration-exercise-data/exercise-08/
   ```
   ```output
   Importing-CSV-File
   emp.sal.000000000.csv  emp.sal.000000002.csv  emp.sal.000000004.csv  emp.sal.000000006.csv  emp.sal.000000008.csv  emp.sal.000000010.csv
   emp.sal.000000001.csv  emp.sal.000000003.csv  emp.sal.000000005.csv  emp.sal.000000007.csv  emp.sal.000000009.csv  metadata
   ```

6. View the `emp.sal.000000000.csv` file to learn about its structure. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ head -n 10 ~/stage/TiDB-Administration-exercise-data/exercise-08/
   ```
   ```output
   Importing-CSV-File/emp.sal.000000000.csv
   "emp_no","salary","from_date","to_date"
   10001,60117,"1986-06-26","1987-06-26"
   10001,62102,"1987-06-26","1988-06-25"
   10001,66074,"1988-06-25","1989-06-25"
   10001,66596,"1989-06-25","1990-06-25"
   10001,66961,"1990-06-25","1991-06-25"
   10001,71046,"1991-06-25","1992-06-24"
   10001,74333,"1992-06-24","1993-06-24"
   10001,75286,"1993-06-24","1994-06-24"
   10001,75994,"1994-06-24","1995-06-24"
   ```
   
   ```command
   $ wc -l ~/stage/TiDB-Administration-exercise-data/exercise-08/
   ```
   ```output
   Importing-CSV-File/emp.sal.000000000.csv
   275696 /home/ec2-user/stage/TiDB-Administration-exercise-data/exercise-08/Importing-CSV-File/emp.sal.000000000.csv
   ```

7. Use a text editor to edit the `lightning-csv.toml` file. Modify `lightning-csv.toml` file to contain settings similar to those that follow. Enter the following command at the terminal prompt and receive the results shown. Or, you can use `solution-lightning-csv.toml` which had been provisioned for you:
   ```command
   $ cd
   ```

   ```command
   $ vi lightning-csv.toml
   ```
   ```output
   [lightning]

   # The concurrency number of data. It is set to the number of logical CPU
   # cores by default. When deploying together with other components, you can
   # set it to 75% of the size of logical CPU cores to limit the CPU usage.
   # region-concurrency = 1

   # Logging
   level = "info"
   file = "tidb-lightning.log"


   [tikv-importer]
   # Sets the backend to the "local" mode.
   backend = "local"
   # Sets the directory of temporary local storage.
   sorted-kv-dir = "/tmp/sorted-kv-dir"

   [mydumper]
   # Local source data directory
   data-source-dir = "/home/ec2-user/stage/TiDB-Administration-exercise-data/exercise-08/Importing-CSV-File"
   # Configures the wildcard rule. By default, all tables in the mysql, sys, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, METRICS_SCHEMA, and INSPECTION_SCHEMA system databases are filtered.
   # If this item is not configured, the "cannot find schema" error occurs when system tables are imported.
   filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*']

   [[mydumper.files]]
   # Import only 00001~05000 data files and ignore other files
   pattern = '(?i)(?:[/]*/)*emp\.sal\.(00000000[0-1]|000000001)\.csv'
   schema = "emp"
   table = "sal"
   type = "csv"

   [mydumper.csv]
   # Separator between fields. Must not be empty.
   separator = ','
   # Quoting delimiter. Empty value means no quoting.
   delimiter = '"'
   # Line terminator. Empty value means both "\n" (LF) and "\r\n" (CRLF) are line terminators.
   terminator = ""
   # Whether the CSV files contain a header.
   # If `header` is true, the first line will be skipped.
   header = true
   # Whether the CSV contains any NULL value.
   # If `not-null` is true, all columns from CSV cannot be NULL.
   not-null = false
   # When `not-null` is false (that is, CSV can contain NULL),
   # fields equal to this value will be treated as NULL.
   null = '\N'
   # Whether to interpret backslash escapes inside fields.
   backslash-escape = true
   # If a line ends with a separator, remove it.
   trim-last-separator = false

   [tidb]
   # Configuration of any TiDB server from the cluster.
   host = "<HOST_DB1_PRIVATE_IP>"
   port = 4000
   user = "ltask"
   password = "q1w2e3R4_"
   # Table schema information is fetched from TiDB via this status-port.
   status-port = 10080
   # Address of any PD server from the cluster.
   pd-addr = "<HOST_PD1_PRIVATE_IP>:2379"
   ```

8. Connect to the TiDB. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. 
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```

9. Create a new database called `emp`, with a table called `sal`. The `sal` table should have an `INT(11)` primary key column called `emp_no`, an `INT(11)` column called `salary`, a `DATE` primary key column called `from_date`, a `DATE` column called `to_date`. Then query the number of rows in the `sal` table. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   DROP DATABASE IF EXISTS emp;
   ```
   ```output
   mysql> DROP DATABASE IF EXISTS emp;
   Query OK, 0 rows affected (0.21 sec)
   ```

   ```command
   CREATE DATABASE emp;
   ```
   ```output
   mysql> CREATE DATABASE emp;
   Query OK, 0 rows affected (0.07 sec)
   ```

   ```command
   USE emp;
   ```
   ```output
   mysql> USE emp;
   Database changed
   ```

   ```command
   CREATE TABLE `sal` ( `emp_no` INT(11) NOT NULL, `salary` INT(11) NOT NULL, `from_date` DATE NOT NULL, `to_date` DATE NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
   ```
   ```output
   mysql> CREATE TABLE `sal` ( `emp_no` INT(11) NOT NULL, `salary` INT(11) NOT NULL, `from_date` DATE NOT NULL, `to_date` DATE NOT NULL, PRIMARY KEY (`emp_no`,`from_date`));
   Query OK, 0 rows affected (0.08 sec)
   ```

   ```command
   SELECT COUNT(*) FROM emp.sal;
   ```
   ```output
   mysql> SELECT COUNT(*) FROM emp.sal;
   +----------+
   | COUNT(*) |
   +----------+
   |        0 |
   +----------+
   1 row in set (0.00 sec)
   ```

   ```command
   EXIT;
   ```
   ```output
   mysql> EXIT;
   ```

10. Use a `nohup` command to start the `tidb-lightning` process. Enter the following command at the terminal prompt and receive the results shown (You can press <kbd>Enter</kbd> to make ` tidb-lighting` work in the background):
   ```command
   $ nohup tiup tidb-lightning:v6.5.1 -config lightning-csv.toml > nohup.out &
   ```
   ```output
   [1] 17481
   $ nohup: ignoring input and redirecting stderr to stdout

   $ 
   ...

   [1]+  Done      nohup tiup tidb-lightning:v6.5.1 -config tidb-lightning-csv.toml > nohup.out
   ```


11. View the contents of the `nohup.out` file to verify that `TiDB Lightning` is exited. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    $ cat nohup.out
    ```
    ```output
    Verbose debug logs will be written to tidb-lightning.log

       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  # | CHECK ITEM                                                                                                            | TYPE        | PASSED |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  1 | Source csv files size is proper                                                                                       | performance | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  2 | checkpoints are valid                                                                                                 | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  3 | table schemas are valid                                                                                               | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  4 | Cluster is available                                                                                                  | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  5 | Lightning has the correct storage permission                                                                          | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  6 | sorted-kv-dir:/tmp/sorted-kv-dir and data-source-dir:/home/ec2-user/sample/csv are in the same disk, may slow down performance | performance | false  |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  7 | local disk resources are rich, estimate sorted data size 20.53MiB, local available is 5.15GiB                         | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  8 | Cluster available is rich, available is 54.48GiB, we need 61.58MiB                                                    | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       |  9 | Cluster doesn't have too many empty regions                                                                           | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+
       | 10 | Cluster region distribution is balanced                                                                               | critical    | true   |
       +----+-----------------------------------------------------------------------------------------------------------------------+-------------+--------+

    tidb lightning exit successfully
    ```   
+ If any error occurs, view the contents of the `tidb-lightning-csv.log` file to confirm the cause of the error:
  ```command
  $ less tidb-lightning.log
  ```
+ You might need to delete lightning checkpoint before importing the data again. The following command assumes that the checkpoint is stored in `/tmp/`.
  ```command
  rm /tmp/tidb_lightning_checkpoint.pb
  ```

12.   Connect to the TiDB and query the count of the `sal` table, and then exit the session. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
      ```command
      $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
      ```

      ```command
      SELECT COUNT(*) FROM emp.sal;
      ```
      ```output
      mysql> SELECT COUNT(*) FROM emp.sal;
      +----------+
      | COUNT(*) |
      +----------+
      |   551395 |
      +----------+
      1 row in set (0.20 sec)
      ```

      ```command
      EXIT
      ```
      ```output
      mysql> EXIT;
      Bye
      ```

## 演習 2
### Importing Data into TiDB in Parallel

### 概要
+ In this exercise, you import multiple tables into TiDB using `TiDB Lightning` in parallel.
+ You use two terminal windows for this exercise.
+ The following diagram shows how to use `TiDB Lightning` to import sharded databases and tables into TiDB in parallel. In our example, `my_table_1` contains all the records with an odd ID number, and `my_table_2` has all the records with an even ID number.

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBLightningLabToplogy_aa_003_20220725.png)  


### 所要時間
+ This exercise should take you approximately 30 minutes to complete.

### タスク
1. Import the data into TiDB by starting two `TiDB Lightning` processes on two PDs at nearly the same time.

### 手順
1. Open a terminal window (terminal 1) and login as user ec2-user on monitor1_address. Then list the contents of the data files directory. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ ssh -A ec2-user@monitor1_address
   ```
   ```output
      ,     #_
      ~\_  ####_        Amazon Linux 2
     ~~  \_#####\
     ~~     \###|       
     ~~       \#/ ___
      ~~       V~' '->
       ~~~         /    A newer version of Amazon Linux is available!
         ~~._.   _/
            _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
          _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
   ```

   ```command
   $ ls ~/stage/TiDB-Administration-exercise-data/exercise-08Importing-Data-into-TiDB-in-Parallel/my_table_1
   ```
   ```output
   metadata  my_db.my_table_1.000000000.sql  my_db.my_table_1-schema.sql  my_db-schema-create.sql
   ```

2. View the `my_db-schema-create.sql`, `my_db.my_table_1-schema.sql` and `my_db.my_table_1.000000000.sql` files to learn about their structures. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_1/my_db-schema-create.sql
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
   ```

   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_1/my_db.my_table_1-schema.sql 
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   CREATE TABLE `my_table_1` (
   `id` int(11) NOT NULL,
   `name` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   ```

   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_1/my_db.my_table_1.000000000.sql
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   > INSERT INTO `my_table_1` VALUES
   (1,'tom'),
   (3,'frank');
   ...
   ```

3. Open a new terminal window (terminal 2) and log in as user ec2-user on PD1_address. Then view the contents of the data files directory on `PD1`. Enter `yes` if prompted by `ssh`. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ ssh -A ec2-user@pd1_address
   ```
   ```output
      ,     #_
      ~\_  ####_        Amazon Linux 2
     ~~  \_#####\
     ~~     \###|       
     ~~       \#/ ___
      ~~       V~' '->
       ~~~         /    A newer version of Amazon Linux is available!
         ~~._.   _/
            _/ _/       Amazon Linux 2023, GA and supported until 2028-03-15.
          _/m/'           https://aws.amazon.com/linux/amazon-linux-2023/
   ```

4. In terminal 2, extract the data.
   ```command
   $ unzip -u stage/tidb-admin-dataset.zip -d stage/
   ```
   ```command
   $ ls ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_2
   ```
   ```output
   metadata
   my_db.my_table_2.000000000.sql
   my_db.my_table_2-schema.sql
   my_db-schema-create.sql
   ```

5. In terminal 2, view the `my_db-schema-create.sql`, `my_db.my_table_2-schema.sql` and `my_db.my_table_2.000000000.sql` files to learn about their structures. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_2/my_db-schema-create.sql
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   CREATE DATABASE `my_db` /*!40100 DEFAULT CHARACTER SET latin1 */;
   ```

   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_2/my_db.my_table_2-schema.sql
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   CREATE TABLE `my_table_2` (
   `id` int(11) NOT NULL,
   `name` varchar(32) DEFAULT NULL,
   PRIMARY KEY (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   ```

   ```command
   $ cat ~/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_2/my_db.my_table_2.000000000.sql
   ```
   ```output
   /*!40101 SET NAMES binary*/;
   INSERT INTO `my_table_2` VALUES
   (2,'jack'),
   (4,'tony');
   ```

6. In terminal 1, use a text editor to edit the `lightning-p1.toml` file. Modify `lightning-p1.toml` file to contain settings similar to those that follow (replace the IP addresses with the correct ones assigned to you). Or, you can simply use the file `solution-lightning-p1.toml` which is provisioned for you:
   ```command
   $ vi lightning-p1.toml
   ```
   ```output
   [lightning]

   # The concurrency number of data. It is set to the number of logical CPU
   # cores by default. When deploying together with other components, you can
   # set it to 75% of the size of logical CPU cores to limit the CPU usage.
   # region-concurrency = 1

   # Logging
   level = "info"
   file = "tidb-lightning-p1.log"

   [tikv-importer]
   # Whether to allow importing data to tables with data. The default value is `false`.
   # When you use parallel import mode, you must set it to `true`, because multiple TiDB Lightning instances are importing the same table at the same time.
   incremental-import = true
   # Sets the backend to the "local" mode.
   backend = "local"
   # Sets the directory of temporary local storage.
   sorted-kv-dir = "/tmp/sorted-kv-dir"

   [mydumper]
   # Local source data directory
   data-source-dir = "/home/ec2-user/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_1"
   # Only import tables if these wildcard rules are matched. See the corresponding section for details.
   filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*']
   
   [[routes]]
   schema-pattern = "my_db"
   table-pattern = "my_table_*"
   target-schema = "my_db"
   target-table = "my_table"

   [tidb]
   # Configuration of any TiDB server from the cluster.
   host = "<HOST_DB1_PRIVATE_IP>"
   port = 4000
   user = "ltask"
   password = "q1w2e3R4_"
   # Table schema information is fetched from TiDB via this status-port.
   status-port = 10080
   # Address of any PD server from the cluster.
   pd-addr = "<HOST_PD1_PRIVATE_IP>:2379"                   
   ``` 
  
7. In terminal 2, install `TiUP`, and install `Lightning`:
   ```command
   $ ./01-install-tiup.sh
   ```
   ```command
   $ source .bash_profile
   ```
   ```command
   $ tiup install tidb-lightning:v6.5.1
   ```

8. In terminal 2, use a text editor to edit the `lightning-p2.toml` file. Modify `lightning-p2.toml` file to contain settings similar to those that follow (replace the IP addresses with the correct ones assigned to you). Or, you can simply copy the file `solution-lightning-p2.toml` from `monitor1` to `pd1` which is provisioned for you:
   
   (a) Create and modify the `lightning-2.toml` file.
   ```command
   $ vi tidb-lightning-2.toml
   ```
   ```output
   [lightning]

   # The concurrency number of data. It is set to the number of logical CPU
   # cores by default. When deploying together with other components, you can
   # set it to 75% of the size of logical CPU cores to limit the CPU usage.
   # region-concurrency = 1

   # Logging
   level = "info"
   file = "tidb-lightning-p2.log"

   [tikv-importer]
   # Whether to allow importing data to tables with data. The default value is `false`.
   # When you use parallel import mode, you must set it to `true`, because multiple TiDB Lightning instances are importing the same table at the same time.
   incremental-import = true
   # Sets the backend to the "local" mode.
   backend = "local"
   # Sets the directory of temporary local storage.
   sorted-kv-dir = "/tmp/sorted-kv-dir"

   [mydumper]
   # Local source data directory
   data-source-dir = "/home/ec2-user/stage/TiDB-Administration-exercise-data/exercise-08/Importing-Data-into-TiDB-in-Parallel/my_table_2"
   # Only import tables if these wildcard rules are matched. See the corresponding section for details.
   filter = ['*.*', '!mysql.*', '!sys.*', '!INFORMATION_SCHEMA.*', '!PERFORMANCE_SCHEMA.*', '!METRICS_SCHEMA.*', '!INSPECTION_SCHEMA.*']
   
   [[routes]]
   schema-pattern = "my_db"
   table-pattern = "my_table_*"
   target-schema = "my_db"
   target-table = "my_table"

   [tidb]
   # Configuration of any TiDB server from the cluster.
   host = "<HOST_DB2_PRIVATE_IP>"
   port = 4000
   user = "ltask"
   password = "q1w2e3R4_"
   # Table schema information is fetched from TiDB via this status-port.
   status-port = 10080
   # Address of any PD server from the cluster.
   pd-addr = "<HOST_PD1_PRIVATE_IP>:2379"             
   ```

   (b) Or, on terminal 1, copy the file `solution-lightning-p2.toml` from `monitor1` to `pd1`:
   ```command
   $ scp solution-lightning-p2.toml ${HOST_PD1_PRIVATE_IP}:/home/ec2-user/
   ```

9. In terminal 1, connect to the TiDB and drop the `my_db` database. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. Enter the following command at the terminal prompt and receive the results shown:
   ```command
   $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
   ```

   ```command
   DROP DATABASE IF EXISTS my_db;
   ```
   ```output
   mysql> DROP DATABASE IF EXISTS my_db;
   ```

   ```command
   EXIT
   ```
   ```output
   mysql> EXIT;
   Bye
   ```

10. Use a `nohup` command to start the `tidb-lightning` process (Press <kbd>Enter</kbd> to make `tidb-lighting` work in the background). Enter the following command at the terminal prompt and receive the results shown. Or, you can simply use the file `solution-lightning-p1.toml` which is provisioned for you::
   ```command
   $ nohup tiup tidb-lightning:v6.5.1 -config lightning-p1.toml > nohup.out &
   ```
   ```output
   [1] 4256
   # nohup: ignoring input and redirecting stderr to stdout 
   ...

   [1]+  Done                    nohup tidb-lightning -config lightning-p1.toml > nohup
   ```

11. Change to terminal 2, use a `nohup` command to start the `tidb-lightning` process. Terminal 1 and terminal 2 import data into TiDB in parallel. Check the output from terminal 1 and terminal 2.
    ```command
    $ nohup .tiup/bin/tiup tidb-lightning:v6.5.1 -config lightning-p2.toml > nohup.out &
    ```
    ```output
    [1] 4256
    # nohup: ignoring input and redirecting stderr to stdout
    ...

    [1]+  Done                    nohup tidb-lightning -config lightning-p2.toml > nohup
    ```

12. In terminal 1, view the contents of the `nohup.out` file to verify that `TiDB Lightning` was exited successfully. Enter the following command at the terminal prompt and receive the results shown, and type `:q!` to quit to the terminal: 
      ```command
      $ cat nohup.out
      ```
      ```output
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  # | CHECK ITEM                                                                                                                       | TYPE        | PASSED |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  1 | Source csv files size is proper                                                                                                  | performance | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  2 | checkpoints are valid                                                                                                            | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  3 | table schemas are valid                                                                                                          | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  4 | Cluster is available                                                                                                             | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  5 | Lightning has the correct storage permission                                                                                     | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  6 |sorted-kv-dir:/tmp/sorted-kv-dir and data-source-dir:/home/ec2-user/sample/sql/my_table_1 are in the same disk, may slow down performance  | performance | false  |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  7 | local disk resources are rich, estimate sorted data size 85B, local available is 5.149GiB                                        | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  8 | Cluster available is rich, available is 54.46GiB, we need 255B                                                                   | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      |  9 | Cluster doesn't have too many empty regions                                                                                      | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      | 10 | Cluster region distribution is balanced                                                                                          | critical    | true   |
      +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
      tidb lightning exit successfully
      ```

+ If any error occurs, view the contents of the `tidb-lightning.log` file to confirm the cause of the error.
  ```command
  cat tidb-lightning.log
  ```
+ You might need to delete lightning checkpoint before importing the data again. The following command assumes that the checkpoint is stored in `/tmp/`.
  ```command
  rm /tmp/tidb_lightning_checkpoint.pb
  ```

13. In terminal 2, view the contents of the `nohup.out` file to verify that `TiDB Lightning` is exited successfully. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    $ cat nohup.out
    ```
    ```output
    Verbose debug logs will be written to tidb-lightning.log

    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  # | CHECK ITEM                                                                                                                       | TYPE        | PASSED |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  1 | Source csv files size is proper                                                                                                  | performance | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  2 | checkpoints are valid                                                                                                            | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  3 | table schemas are valid                                                                                                          | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  4 | Cluster is available                                                                                                             | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  5 | Lightning has the correct storage permission                                                                                     | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  6 |sorted-kv-dir:/tmp/sorted-kv-dir and data-source-dir:/home/ec2-user/sample/sql/my_table_2 are in the same disk, may slow down performance  | performance | false  |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  7 | local disk resources are rich, estimate sorted data size 85B, local available is 7.173GiB                                        | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  8 | Cluster available is rich, available is 54.46GiB, we need 255B                                                                   | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    |  9 | Cluster doesn't have too many empty regions                                                                                      | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+
    | 10 | Cluster region distribution is balanced                                                                                          | critical    | true   |
    +----+----------------------------------------------------------------------------------------------------------------------------------+-------------+--------+

    tidb lightning exit successfully
    ```

+ If any error occurs, view the contents of the `tidb-lightning.log` file to confirm the cause of the error.
  ```command
  $ cat tidb-lightning.log
  ```
+ You might need to delete lightning checkpoint before importing the data again. The following command assumes that the checkpoint is stored in `/tmp/`.
  ```command
  rm /tmp/tidb_lightning_checkpoint.pb
  ```

14. In terminal 1, connect to the TiDB and query the count of the `emp.sal` table. Enter the following command at the terminal prompt and receive the results shown:
    ```command
    $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root
    ```
    ```command
    SELECT COUNT(*) FROM my_db.my_table;
    ```
    ```output
    mysql> SELECT COUNT(*) FROM my_db.my_table;
    +----------+
    | COUNT(*) |
    +----------+
    |    40156 |
    +----------+
    1 row in set (0.01 sec)
    ```

15. Log out of the client and close all open terminal windows.

## 演習終わり