# Lab 2: Connecting to TiDB ### Overview In these exercises, you connect to TiDB using MySQL client and GUI tool, and execute SQL statements on TiDB, and then check the connection status of the current database. The diagram below illustrates the deployment topology of the TiDB cluster. ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercise_aa_007_20220914.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. + 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. ### 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/tidb-course-201-lab/blob/master/README_303_v65_en_class_setup_guide_for_student.md) for private key preparation. ## Exercise 1 ### Subject: Connecting to TiDB Using MySQL Client and Executing SQL Statements ### Overview In this exercise, you connect to TiDB and execute SQL statements. You will perform all tasks on Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 20 minutes to complete. ### Tasks + Start a TiDB cluster and connect to it. + Create a new database called `tidb`. + Create a table called `tab_tidb`. The `tab_tidb` table should have an auto-incrementing primary key column called `id`, a `VARCHAR(20)` column called `name`, an `INT` column called `age` and a `VARCHAR(20)` column called `version`. + Insert some data into the `tab_tidb` table and query the data. + Execute the `SHOW PROCESSLIST` statement to view the session state. ### 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. The returned message may be slightly different from what is displayed here. 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. Start your TiDB Cluster. ```command $ ./start-cluster.sh ``` ```output tiup is checking updates for component cluster ... ... + [ Serial ] - UpdateTopology: cluster=tidb-test Started cluster `tidb-test` successfully Cluster type: tidb Cluster name: tidb-test Cluster version: v6.5.0 Deploy user: ec2-user SSH type: builtin Dashboard URL: http://35.90.88.156:2379/dashboard Grafana URL: http://10.90.4.217:3000 ID Role Host Ports OS/Arch Status Data Dir Deploy Dir -- ---- ---- ----- ------- ------ -------- ---------- 10.90.4.217:9093 alertmanager 10.90.4.217 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093 10.90.4.217:3000 grafana 10.90.4.217 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000 10.90.3.122:2379 pd 10.90.3.122 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.139:2379 pd 10.90.3.139 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.3.140:2379 pd 10.90.3.140 2379/2380 linux/x86_64 Up /tidb-data/pd-2379 /tidb-deploy/pd-2379 10.90.4.217:9090 prometheus 10.90.4.217 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090 10.90.1.164:4000 tidb 10.90.1.164 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.1.59:4000 tidb 10.90.1.59 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000 10.90.4.34:9000 tiflash 10.90.4.34 9000/8123/3930/20170/20292/8234 linux/x86_64 Up /tidb-data/tiflash-9000 /tidb-deploy/tiflash-9000 10.90.3.158:20160 tikv 10.90.3.158 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.211:20160 tikv 10.90.3.211 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 10.90.3.83:20160 tikv 10.90.3.83 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160 Total nodes: 12 ``` 3. Connect to TiDB using MySQL client. You should provide option `-p` if `root` has a password, otherwise you can omit the `-p` option. (if you don't know how to get the `${HOST_DB1_PRIVATE_IP}` please ask your instructor for help). Enter the following command at the terminal prompt and receive the results shown: ```command $ mysql -h ${HOST_DB1_PRIVATE_IP} --port 4000 -u root ``` 4. Query database version. Enter the following command at the terminal prompt and receive the results shown: ```command SELECT TIDB_VERSION()\G ``` ```output *************************** 1. row *************************** TIDB_VERSION(): Release Version: v6.5.0 Edition: Community Git Commit Hash: 1a89decdb192cbdce6a7b0020d71128bc964d30f Git Branch: heads/refs/tags/v6.5.0 UTC Build Time: 2022-06-05 05:15:11 GoVersion: go1.18.2 Race Enabled: false TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306 Check Table Before Drop: false 1 row in set (0.00 sec) ``` 6. Display the databases on TiDB. Enter the following command at the terminal prompt and receive the results shown: ```command SHOW DATABASES; ``` ```output +--------------------+ | Database | +--------------------+ | INFORMATION_SCHEMA | | METRICS_SCHEMA | | PERFORMANCE_SCHEMA | | mysql | | test | +--------------------+ 5 rows in set (0.00 sec) ``` 7. Create a new database called `tidb`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE DATABASE tidb; ``` ```output Query OK, 0 rows affected (0.08 sec) ``` 8. Change the current database to `tidb`. Enter the following command at the terminal prompt and receive the results shown: ```command USE tidb; ``` ```output Database changed ``` 9. Create a table called `tab_tidb`. The `tab_tidb` table should have an auto-incrementing primary key column called `id`, a `VARCHAR(20)` column called `name`, an `int(11)` column called `age` and a `VARCHAR(20)` column called `version`. Enter the following command at the terminal prompt and receive the results shown: ```command CREATE TABLE `tab_tidb` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) NOT NULL DEFAULT '', `age` INT(11) NOT NULL DEFAULT 0, `version` VARCHAR(20) NOT NULL DEFAULT '', PRIMARY KEY (`id`), KEY `idx_age` (`age`)); ``` ```output Query OK, 0 rows affected (0.09 sec) ``` 10. Insert a row into the `tab_tidb` table with `1` as the value for the `id` column, `TiDB` as the value for the `name` column, `6` as the value for the `age` column, `TiDB-v6.5.0` as the value for the `version` column. Enter the following command at the terminal prompt and receive the results shown: ```command INSERT INTO `tab_tidb` VALUES (1,'TiDB',6,'TiDB-v6.5.0'); ``` ```output Query OK, 1 row affected (0.01 sec) ``` 11. Query the data of the `tab_tidb` table. Enter the following command at the terminal prompt and receive the results shown: ```command SELECT * FROM tab_tidb; ``` ```output +----+------+-----+-------------+ | id | name | age | version | +----+------+-----+-------------+ | 1 | TiDB | 6 | TiDB-v6.5.0 | +----+------+-----+-------------+ 1 row in set (0.00 sec) ``` 12. Execute the `SHOW PROCESSLIST` statement to view the session state. Enter the following command at the terminal prompt and receive the results shown: ```command SHOW PROCESSLIST; ``` ```output +---------------------+------+-------------------+------+---------+------+------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +---------------------+------+-------------------+------+---------+------+------------+------------------+ | 6156387705266635215 | root | 172.31.0.106:4208 | tidb | Query | 0 | autocommit | show processlist | +---------------------+------+-------------------+------+---------+------+------------+------------------+ 1 row in set (0.00 sec) ``` 13. Log out of the client and close all open terminal windows. ## Exercise 2 ### Subject: Connecting to TiDB Using Java ### Overview In this exercise, you connect to TiDB using Java. You will perform all tasks on Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Read `JdbcConnect.java` and understand how Java connects to TiDB. + Run `JdbcConnect.java` and observe the results. ### Solution 1. Read `JdbcConnect.java` and understand how Java connects to TiDB. Pay attention to the lines where Java connects to TiDB. ```command cat JdbcConnect.java ``` ```output ... try { connection = DriverManager.getConnection( "jdbc:mysql://XX.XX.XX.XXX:4000/test", "root", ""); System.out.println("Connection established."); printResultSetStringString("SELECT TIDB_VERSION()", connection); } catch (SQLException e) { System.out.println("Error: " + e); } ... ``` 2. Run `JdbcConnect.java` by using `runJdbcConnect.sh`and observe the results. ```command ./RunJdbcConnect.sh ``` ```output ... Release Version: v6.5.0 ... ``` ## Exercise 3 ### Subject: Connecting to TiDB Using Python ### Overview In this exercise, you connect to TiDB using Python. You will perform all tasks on Control Machine (monitor1_address). ### Duration + This exercise should take you approximately 10 minutes to complete. ### Tasks + Read `PythonConnect.py` and understand how Python connects to TiDB. + Run `RunPythonConnect.sh` and observe the results. ### Solution 1. Read `PythonConnect.py` and understand how Python connects to TiDB. Pay attention to the lines where Python connects to TiDB. ```command cat PythonConnect.py ``` ```output ... conn = mysql.connector.connect( database="test", host="${HOST_DB1_PRIVATE_IP}", port=4000, user="root", password="", ) cursor = conn.cursor() ... ``` 2. Run `PythonConnect.py` by using `RunPythonConnect.sh`and observe the results. ```command ./RunPythonConnect.sh ``` ```output ('Release Version: v6.5.0\nEdition: Community\nGit Commit Hash: 706c3fa3c526cdba5b3e9f066b1a568fb96c56e3\nGit Branch: heads/refs/tags/v6.5.0\nUTC Build Time: 2022-12-27 03:50:44\nGoVersion: go1.19.3\nRace Enabled: false\nTiKV Min Version: 6.2.0-alpha\nCheck Table Before Drop: false\nStore: tikv',) ``` ## End of Exercises