## Module 2: Importing Data into TiDB in Parallel + 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://lab-static.pingcap.com/quick-demo/TiDBLightningLabToplogy_aa_003_20220725.png) ### Step 1. List the contents of the data files directory ```bash 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 ``` ### Step 2. Check the data source files + 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. ```bash 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 */; ``` ```bash 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; ``` ```bash 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'); ... ``` ### Step 3. Open a new terminal (terminal 2) + Click here - [Terminal 2](ssh://monitor1-ssh#2) to open a new terminal connected to the virtual machine assigned for your hands-on lab. ### Step 4. In terminal 2, login to `PD1` ```bash ./ssh-to-pd1.sh ``` ```output ... ... ``` ### Step 5. In terminal 2, extract the data ```bash unzip -u stage/tidb-admin-dataset-v2.zip -d stage/ ``` ```bash 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 ``` ### Step 6. In terminal 2, check the data files ```bash 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 */; ``` ```bash 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; ``` ```bash 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'); ... ``` ### Step 7. In terminal 1, view `solution-lightning-p1.toml` + In [Terminal 1](ssh://monitor1-ssh#1), the IP addresses shown below will be different from yours. ```bash 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" ``` ### Step 8. In terminal 2, install `TiUP` and `Lightning` - Switch to [Terminal 2](ssh://monitor1-ssh#2), execute the following commands. ```bash ./01-install-tiup.sh ``` ```bash source .bash_profile ``` ```bash tiup mirror set tidb-community-server-7.5.1-linux-amd64 tiup install tidb-lightning ``` ### Step 9. In terminal 1, copy the file `solution-lightning-p2.toml` to `pd1` - Switch to [Terminal 1](ssh://monitor1-ssh#1), execute the following commands. ```bash scp -i ~/.ssh/pe-class-key-${LAB:REGION_NAME}.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 ``` ### Step 10. In terminal 1, connect to the TiDB and drop the `my_db` database ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```bash DROP DATABASE IF EXISTS my_db; ``` ```output mysql> DROP DATABASE IF EXISTS my_db; ``` ```bash EXIT ``` ```output mysql> EXIT; Bye ``` ### Step 11. In terminal 1, import the data using Lightning + Use a `nohup` command to start the `tidb-lightning` process (Press Enter to make `tidb-lighting` work in the background). + Please execute step 11 and step 12 at almost the same time ```bash 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 ``` ### Step 12. In terminal 2, import the data using Lightning - Switch to [Terminal 2](ssh://monitor1-ssh#2), execute the following commands. ```bash 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 ``` ### Step 13. In both terminals, view the Lightning output + In [Terminal 1](ssh://monitor1-ssh#1) , view the contents of the `nohup.out` file to verify that `TiDB Lightning` was exited successfully. , and type `:q!` to quit to the terminal: ```bash 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. ```bash 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/`. ```bash rm /tmp/tidb_lightning_checkpoint.pb ``` + In [Terminal 2](ssh://monitor1-ssh#2) , view the contents of the `nohup.out` file to verify that `TiDB Lightning` is exited successfully. ```bash 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 ``` ### Step 14. In terminal 1, connect to the TiDB and query the `emp.sal` table ```bash mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` ```bash 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) ```