# Lab 6: Importing Data into TiDB Using TiDB Lightning ### Overview 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) ### Notes + 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 lab. + Commands and SQL statements presented on this page are clickable for easy copy and paste. + 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. ### Assumptions + You have downloaded and secured the TiUP Control Machine private key (for example the file is saved under `~/.ssh/` folder locally): [chmod 400 ~/.ssh/pe-class-key-oregon.pem](lab-controller/class_303_get_ec2_key_for_lab.php?region=oregon) + Windows users please refer to this [guide](https://github.com/pingcap-inc/tidb-course-labs/blob/master/README_303_class_setup_guide_for_student_lab_control_en.md) for private key preparation. ## Exercise 1 ### Subject: Importing CSV File into TiDB ### Overview + In this exercise, you import a CSV file into TiDB using `TiDB Lightning`. You will perform all tasks on node Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 30 minutes to complete. ### Tasks + Install `TiDB Lightning` using `TiUP`. + Prepare the data. + Create schema in TiDB. + Config `TiDB Lightning`. + Import data into TiDB using `TiDB Lightning`. ### Solution 1. Login as user ec2-user on monitor1_address. Enter the following command at the terminal prompt and receive the results shown. If you do not possess the credentials to connect, kindly review the Assumptions section under the OVERVIEW tab. If prompted, answer `yes`. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem 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 ``` 4. Extract the data directories archive from `tidb-admin-dataset-v2.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-v2.zip -d stage/ ``` ```output Archive: tidb-admin-dataset-v2.zip creating: TiDB-Administration-exercise-data-v2/ ... ``` 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-v2/exercise-lightning/Importing-CSV-File ``` ```output 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-v2/exercise-lightning/Importing-CSV-File/emp.sal.000000000.csv ``` ```output "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-v2/exercise-lightning/Importing-CSV-File/emp.sal.000000000.csv ``` ```output 275696 /home/ec2-user/stage/TiDB-Administration-exercise-data-v2/exercise-lightning/Importing-CSV-File/emp.sal.000000000.csv ``` 7. Read the `solution-lightning-csv.toml`. The IP addresses shown below will be different from yours. ```command $ cd ``` ```command $ cat solution-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-csv.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-v2/exercise-lightning/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 = "" 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 = "10.90.2.84: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 Enter to make ` tidb-lighting` work in the background): ```command $ nohup tiup tidb-lightning -config solution-lightning-csv.toml > nohup.out & ``` ```output [1] 17481 $ nohup: ignoring input and redirecting stderr to stdout ... [1]+ Done nohup tiup tidb-lightning -config solution-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-csv.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-csv.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 ``` ## Exercise 2 ### Subject: Importing Data into TiDB in Parallel ### Overview + In this exercise, you import multiple tables into TiDB using `TiDB Lightning` in parallel. 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) ### Duration + This exercise should take you approximately 30 minutes to complete. ### Tasks + Import the data into TiDB by starting two `TiDB Lightning` processes on two PDs at nearly the same time. ### Solution 1. List the contents of the data files directory. Enter the following command at the terminal prompt and receive the results shown: ```command $ ls ~/stage/TiDB-Administration-exercise-data-v2/exercise-lightning/Importing-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-v2/exercise-lightning/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-v2/exercise-lightning/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-v2/exercise-lightning/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 login as user ec2-user on monitor1_address. ```command $ ssh -i ~/.ssh/pe-class-key-oregon.pem 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/ ``` 4. Go to `PD1`. If prompted, say `yes`. ```command $ ./ssh-to-pd1.sh ``` ```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/ ... ``` 5. In terminal 2, extract the data. ```command $ unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ```command $ ls ~/stage/TiDB-Administration-exercise-data-v2/exercise-lightning/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 ``` 6. 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-v2/exercise-lightning/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-v2/exercise-lightning/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-v2/exercise-lightning/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'); ... ``` 7. In terminal 1, view `solution-lightning-p1.toml`. The IP addresses shown below will be different from yours. ```command $ cat solution-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-v2/exercise-lightning/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 = "" 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 = "10.90.2.84:2379" ``` 8. In terminal 2, install `TiUP`, and install `Lightning`: ```command $ ./01-install-tiup.sh ``` ```command $ source .bash_profile ``` ```command $ tiup mirror set tidb-community-server-7.5.1-linux-amd64 $ tiup install tidb-lightning ``` 9. In terminal 1, copy the file `solution-lightning-p2.toml` from `monitor1` to `pd1`: ```command $ scp -i ~/.ssh/pe-class-key-oregon.pem solution-lightning-p2.toml ec2-user@${HOST_PD1_PRIVATE_IP}:/home/ec2-user/ ``` ```output solution-lightning-p2.toml 100% 720 678.5KB/s 00:00 ``` 10. 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 ``` 11. Use a `nohup` command to start the `tidb-lightning` process (Press Enter to make `tidb-lighting` work in the background). ```command $ nohup tiup tidb-lightning -config solution-lightning-p1.toml > nohup.out & ``` ```output [1] 4256 # nohup: ignoring input and redirecting stderr to stdout ... [1]+ Done nohup tidb-lightning -config solution-lightning-p1.toml > nohup ``` 12. 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 -config solution-lightning-p2.toml > nohup.out & ``` ```output [1] 4256 # nohup: ignoring input and redirecting stderr to stdout ... [1]+ Done nohup tidb-lightning -config solution-lightning-p2.toml > nohup ``` 13. 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-p1.log` file to confirm the cause of the error. ```command $ cat tidb-lightning-p1.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 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-p2.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-p2.log` file to confirm the cause of the error. ```command $ cat tidb-lightning-p2.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 ``` 15. 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) ``` 16. Log out of the client and close all open terminal windows. ## End of Exercises