## Module 1: MySQL Compatibility ![demo_203-tidb-x-mysql-compat.jpg 85%](https://lab-static.pingcap.com/images/2025/11/27/6302b6914af7014616941c0c3feec84cd3a8d3d1.jpg) In this module, we will connect to TiDB, create tables, import data, and examine MySQL 8.x features such as Window Functions and also see how unsupported features like Stored Procedures behave. For a comprehensive compatibility matrix, see: [https://docs.pingcap.com/tidb/stable/mysql-compatibility/](https://docs.pingcap.com/tidb/stable/mysql-compatibility/). ### Step 1. Log in to the assigned Ubuntu virtual machine - Click [Terminal 1](ssh://monitor1-ssh#1) to open a new terminal connected to the Ubuntu virtual machine assigned to your hands-on lab. You will use this terminal to access the TiDB Cloud Starter cluster. ### Step 2. Check the connectivity to the TiDB Cloud cluster - You can connect to TiDB in the same way as you would connect to a standard MySQL database. You will try to connect to the TiDB Cloud Starter cluster using the following three methods. (To exit the MySQL client, use `EXIT`.) - **A. Connection (with SSL):** Connect using the following command. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt ``` ```output mysql> ``` ```sql EXIT; ``` - **B. Connection (with SSL):** If you don't specify the `--ssl-mode` option, the default mode is `PREFERRED`, which means the MySQL client will first attempt to establish an SSL connection and fall back to an unencrypted connection if SSL fails. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} ``` ```output mysql> ``` ```sql EXIT; ``` - **C. Connection (without SSL):** Change to `--ssl-mode=DISABLED`. A connection error will occur. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=DISABLED ``` ```output mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1105 (HY000): Connections using insecure transport are prohibited. See https://docs.pingcap.com/tidbcloud/secure-connections-to-serverless-tier-clusters ``` > [!NOTE] > Although we use the public endpoint in this lab, remember that TiDB Cloud Starter also supports private network endpoints, which are recommended and much more commonly used in real-world scenarios. - Go to the [Clusters page](https://tidbcloud.com/project/clusters) in your TiDB Cloud console and click the name of your lab cluster. If you started the lab by choosing `Create New Cluster`, the cluster name will be `${LAB:SERVERLESS_CLUSTER_NAME}`. ![choose-starter-cluster-v2.jpg 70%](https://lab-static.pingcap.com/images/2025/11/24/968052d742cea1bce34cda07961dbffb8d309ebd.jpg) - Click `Connect`. ![starter-connect.jpg 80%](https://lab-static.pingcap.com/images/2025/11/18/dfb8f70aacdf6b738e74976a2d292f67a5417314.jpg) - You can see the `Private Endpoint` under `Connection Type`. ![starter-connect-methods.jpg 70%](https://lab-static.pingcap.com/images/2025/11/18/724569d619c6850877608947c662edff50462206.jpg) - After completing the workshop, you can visit this page to enable the private endpoint, which allows you to access the cluster from your organization's actual work network (for example, a VPC). For now, let's leave it unchanged and proceed to the next step. ### Step 3. Check basic features - In [Terminal 1](ssh://monitor1-ssh#1), connect to TiDB. ```bash mysql \ --comments -u '${LAB:SERVERLESS_CLUSTER_USERNAME}' -p'${LAB:SERVERLESS_CLUSTER_PASSWORD}' \ -h ${LAB:SERVERLESS_CLUSTER_HOST} -P ${LAB:SERVERLESS_CLUSTER_PORT} \ --ssl-mode=VERIFY_IDENTITY --ssl-ca=/etc/ssl/certs/ca-certificates.crt ``` ```output mysql> ``` - Create a new database called `${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}`. Use TiDB just like you would use MySQL. ```sql DROP DATABASE IF EXISTS ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE DATABASE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SHOW DATABASES\G ``` ```output *************************** 1. row *************************** Database: INFORMATION_SCHEMA *************************** 2. row *************************** Database: PERFORMANCE_SCHEMA *************************** 3. row *************************** Database: ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME} *************************** 4. row *************************** Database: mysql *************************** 5. row *************************** Database: test 5 rows in set (0.02 sec) ``` - Create a simple table. Use TiDB just like you would use MySQL. ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; CREATE TABLE color (id INT AUTO_INCREMENT, name TEXT, Point INT, PRIMARY KEY (id)); INSERT INTO color (name, Point) VALUES ('red', 1); INSERT INTO color (name, Point) VALUES ('orange', 1); INSERT INTO color (name, Point) VALUES ('yellow', 2); SELECT * FROM color; ``` ```output Database changed Query OK, 0 rows affected (0.14 sec) Query OK, 1 row affected (0.04 sec) Query OK, 1 row affected (0.01 sec) Query OK, 1 row affected (0.01 sec) +----+--------+-------+ | id | name | Point | +----+--------+-------+ | 1 | red | 1 | | 2 | orange | 1 | | 3 | yellow | 2 | +----+--------+-------+ 3 rows in set (0.01 sec) ``` - Check the default character set and collation. ```sql SHOW CREATE TABLE color\G ``` ```output *************************** 1. row *************************** Table: color Create Table: CREATE TABLE `color` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` text DEFAULT NULL, `Point` int(11) DEFAULT NULL, PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */ ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin AUTO_INCREMENT=30001 1 row in set (0.00 sec) ``` - Window function example. ```sql SELECT id, name, Point, RANK() OVER w as 'rank' FROM color WINDOW w AS (ORDER BY Point); ``` ```output +----+--------+-------+------+ | id | name | Point | rank | +----+--------+-------+------+ | 1 | red | 1 | 1 | | 2 | orange | 1 | 1 | | 3 | yellow | 2 | 3 | +----+--------+-------+------+ 3 rows in set (0.01 sec) ``` - TiDB offers strong compatibility with MySQL, but some features—such as stored procedures—are not supported. For example, the following attempt to create a stored procedure will fail. ```sql delimiter // CREATE PROCEDURE sample() BEGIN SELECT * FROM color; END // delimiter ; ``` ```output ERROR 8108 (HY000): Unsupported type *ast.ProcedureInfo ``` - Now that you have a rough understanding of how to connect and use TiDB, let's proceed to the next step and create a more complete sample schema. ### Step 4. Create the bookshop tables - In [Terminal 1](ssh://monitor1-ssh#1), execute the following command to briefly review the provided SQL script. This script creates 6 tables and populates data for a sample bookshop application. ```sql SYSTEM cat create_bookshop_schema_in_current_db.sql | egrep '(CREATE TABLE|INSERT INTO)'; ``` ```output CREATE TABLE `authors` ( INSERT INTO `authors` CREATE TABLE `book_authors` ( INSERT INTO `book_authors` CREATE TABLE `books` ( INSERT INTO `books` CREATE TABLE `orders` ( CREATE TABLE `ratings` ( INSERT INTO `ratings` CREATE TABLE `users` ( INSERT INTO `users` ``` - Execute the script to create and import the dataset into the `${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}` database. ```sql USE ${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}; SOURCE create_bookshop_schema_in_current_db.sql ``` ```output Query OK, 0 rows affected (0.01 sec) ... Query OK, 0 rows affected (0.01 sec) ``` ### Step 5. Query the sample data - Let's perform a single query to analyze the top-rated book genres, identify the top three books within each genre, and review their stock levels. While the following complex query isn't the most user-friendly, it demonstrates TiDB's support for advanced SQL operations like JOINs, GROUP BY, ORDER BY, aggregate functions, window functions, and nested subqueries—all within one statement. ```sql SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; ``` ```output +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | type | book_count | avg_score | top_3_rated_title | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | Comics | 64 | 3.5156 | Mrs Robinson,Be My Baby,On the Atchison | | Arts | 86 | 3.4535 | Give Me Everything,Georgia On My Mind,Besame Mucho | | Magazine | 59 | 3.3051 | Wishing Well,Stayin' Alive,Spanish Harlem | | Kids | 48 | 3.1875 | What Goes Around Comes Around,Gonna Make You Sweat (Everybody Dance Now),Hey There | | Life | 175 | 3.1657 | Play That Funky Music,Open Arms,Every Breath You Take | | Sports | 81 | 3.1605 | Waterfalls,The Hustle,My Sweet Lord | | Education & Reference | 65 | 3.1231 | Greatest Love of All,Addicted to Love,ABC | | Science & Technology | 149 | 3.0671 | Build Me Up Buttercup,Dilemma,Daydream Believer | | Humanities & Social Sciences | 78 | 2.9615 | Higher Love,12th Street Rag,Body & Soul | | Novel | 44 | 2.9318 | The Power of Love,Thrift Shop,Summer in the City | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ 10 rows in set (0.01 sec) ``` - Please do not spend too much time study the query in detail and proceed to the next step. ### Step 6. Use Python to query the sample data - Exit to OS prompt. ```sql EXIT; ``` - Create a Python virtual environment. ```bash python3 -m venv ~/.venv ~/.venv/bin/pip install mysql-connector-python prettytable dotenv ``` - Create a Python script called `top3books.py` for executing the analytical query. The script uses the `mysql.connector` Python module to connect to TiDB just like a MySQL database. ```bash cat > top3books.py <<'EOF' import mysql.connector import datetime from prettytable import PrettyTable config = { 'user': '${LAB:SERVERLESS_CLUSTER_USERNAME}', 'password': '${LAB:SERVERLESS_CLUSTER_PASSWORD}', 'host': '${LAB:SERVERLESS_CLUSTER_HOST}', 'database': '${LAB:SERVERLESS_CLUSTER_DATABASE_NAME}', 'raise_on_warnings': False, 'port': ${LAB:SERVERLESS_CLUSTER_PORT}, } def execQuery(query, queryName): print(queryName) print("Query execution start time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # Executing the query cursor.execute(query) print("Query execution end time: ", datetime.datetime.now().strftime("%H:%M:%S.%f")) # Fetching all rows from the last executed query results = cursor.fetchall() # Creating a PrettyTable object my_table = PrettyTable() # Adding field names (column names) to the table field_names = [i[0] for i in cursor.description] my_table.field_names = field_names # Adding rows to the table for row in results: my_table.add_row(row) # Printing the table print(my_table) print("==========================") try: # Establishing the connection conn = mysql.connector.connect(**config) # Creating a cursor object cursor = conn.cursor() # Your SQL query queryName= "■■■Query: Top three books within each genre■■■" query = """ SELECT b.type, COUNT(b.id) AS book_count, AVG(r.score) avg_score, v.top_3_rated_title FROM books b JOIN ratings r ON b.id = r.book_id JOIN (SELECT ov.type, GROUP_CONCAT(ov.title) top_3_rated_title FROM (SELECT iv.type, iv.title, RANK() OVER (PARTITION BY iv.type ORDER BY book_avg_score DESC) rank_in_type FROM (SELECT books.type, books.title, AVG(ratings.score) as book_avg_score FROM books as books JOIN ratings as ratings ON books.id = ratings.book_id GROUP BY books.type, books.title) iv ) ov WHERE rank_in_type < 4 GROUP BY ov.type ) v ON v.type = b.type GROUP BY type, top_3_rated_title ORDER BY avg_score DESC; """ execQuery(query, queryName) except mysql.connector.Error as err: print(f"Error: {err}") finally: # Closing the connection if conn.is_connected(): cursor.close() conn.close() print("MySQL connection is closed") EOF ls -l top3books.py ``` ```output -rw-r--r--. 1 ubuntu ubuntu 3636 Apr 28 02:22 top3books.py ``` - Run the Python script to get the report. ```bash ~/.venv/bin/python top3books.py ``` ```output ■■■Query: Top three books within each genre■■■ Query execution start time: 14:05:18.613932 Query execution end time: 14:05:18.629749 +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | type | book_count | avg_score | top_3_rated_title | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ | Comics | 64 | 3.5156 | Mrs Robinson,On the Atchison,Be My Baby | | Arts | 86 | 3.4535 | Give Me Everything,Georgia On My Mind,Besame Mucho | | Magazine | 59 | 3.3051 | Wishing Well,Stayin' Alive,Spanish Harlem | | Kids | 48 | 3.1875 | What Goes Around Comes Around,Gonna Make You Sweat (Everybody Dance Now),Hey There | | Life | 175 | 3.1657 | Play That Funky Music,Open Arms,Every Breath You Take | | Sports | 81 | 3.1605 | Waterfalls,The Hustle,My Sweet Lord | | Education & Reference | 65 | 3.1231 | Greatest Love of All,Addicted to Love,ABC | | Science & Technology | 149 | 3.0671 | Build Me Up Buttercup,Dilemma,Daydream Believer | | Humanities & Social Sciences | 78 | 2.9615 | Higher Love,12th Street Rag,Body & Soul | | Novel | 44 | 2.9318 | The Power of Love,Thrift Shop,Summer in the City | +------------------------------+------------+-----------+------------------------------------------------------------------------------------+ ========================== MySQL connection is closed ``` - In this module, we accessed and used TiDB just like MySQL, both with SQL and Python, except for a few minor compatibility differences. For details on accessing TiDB with other drivers or ORMs, refer to the [documentation](https://docs.pingcap.com/tidbcloud/dev-guide-choose-driver-or-orm/) or explore our [related labs](/labs?target=Developer). Now, let's proceed to the next module.