# Connecting to TiDB

### 概要
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 following shows the deployment topology diagram of TiDB cluster.

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/TiDBClusterDeploymentTopologyExercise_aa_007_20220914.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
### Connecting to TiDB Using MySQL Client and Executing SQL Statements

### 概要
+ In this exercise, you connect to TiDB and execute SQL statements. You will perform all tasks on Control Machine (monitor1_address).

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

### タスク
+ 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.

### 手順
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. 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
   ```

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)
   ```


5. 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)
   ```

6. 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)
   ```

7. 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
   ```


8. 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)
   ```


9. 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)
   ```

10. 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)
    ```

11. 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)
    ```

12. Log out of the client and close all open terminal windows.
## 演習 2
### Connecting to TiDB Using GUI Tool

### 概要
+ In this exercise, you connect to the TiDB using the `MySQL Workbench` on macOS.

### Note  
+ If your operating system is Windows or Linux, etc., the *Solution* may be different with this exercise.

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

### タスク
+ Go to https://dev.mysql.com/downloads/workbench/, download `Workbench`, and install it on your local machine.
+ Connect to TiDB using `Workbench`.

### 手順

1. GO to https://dev.mysql.com/downloads/workbench/. Select your operating system and choose an installation package, then click `Download`.

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


2. On the next page, you can create a new account or login to an existing one or skip this by clicking the `No thanks, just start my download` button at the bottom.

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

   
3. After downloading the installation package, install `MySQL Workbench`.

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/InstallMySQLWorkbenchen_aa_001_20221010.png)
   
4. Open `MySQL Workbench`, then click `+` in the `MySQL Workbench` home window.  

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/AddMySQLWorkbenchenConnection_aa_001_20221010.png)
   
5. On the `Setup New Connection` windows, add the `Connection Name`. In the `Parameters` section, enter `${HOST_DB1_PUBLIC_IP}` in the `Hostname` field, and configure `Port`, `Username` and `Password` of `tidb` user. Then click `OK`.

   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/SetupNewConnection_aa_001_20221010.png)   
   
6. In the `MySQL Workbench` home window, click the new instance to connect to TiDB.
   ![60](https://pingcap-edu.s3.us-west-2.amazonaws.com/quick-demo/ConnectingTiDBUsingGUI_aa_001_20221010.png)      

## 演習終わり