MySQL Tutorial: Exploring System Tables and Databases

Introduction

In this challenge, you'll practice starting the MySQL service and explore the system databases and tables. Understanding how to manage the MySQL service and navigate the system tables is a crucial skill for any database administrator or developer working with MySQL.

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL mysql(("MySQL")) -.-> mysql/BasicKeywordsandStatementsGroup(["Basic Keywords and Statements"]) mysql(("MySQL")) -.-> mysql/DatabaseFunctionsandDataTypesGroup(["Database Functions and Data Types"]) mysql(("MySQL")) -.-> mysql/SystemManagementToolsGroup(["System Management Tools"]) mysql/BasicKeywordsandStatementsGroup -.-> mysql/use_database("Database Selection") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/database("DB Function - Info Retrieval") mysql/DatabaseFunctionsandDataTypesGroup -.-> mysql/version("DB Version Check") mysql/SystemManagementToolsGroup -.-> mysql/show_status("Status Overview") mysql/SystemManagementToolsGroup -.-> mysql/show_variables("Configuration Overview") subgraph Lab Skills mysql/use_database -.-> lab-391702{{"Explore MySQL System Tables"}} mysql/database -.-> lab-391702{{"Explore MySQL System Tables"}} mysql/version -.-> lab-391702{{"Explore MySQL System Tables"}} mysql/show_status -.-> lab-391702{{"Explore MySQL System Tables"}} mysql/show_variables -.-> lab-391702{{"Explore MySQL System Tables"}} end

Start MySQL and Explore System Tables

In this challenge, you will practice starting the MySQL service, connecting to the MySQL server, and exploring system databases and tables. These are fundamental skills for working with MySQL databases.

Tasks

Start the MySQL service
Connect to MySQL as the root user
List all databases in the system
Select and explore the mysql system database
List all tables in the mysql database
Save the list of tables to a file named system_tables.txt in the ~/project directory, which has been created for you.

Requirements

All commands must be executed in the terminal within the ~/project directory
Use sudo when necessary to execute commands with elevated privileges
Connect to MySQL using the command line interface
Use SQL commands to list databases and tables
The output must be saved to a file named system_tables.txt in the ~/project directory

Hints

Double-click the system_tables.txt file in the Desktop to open it in the text editor, then copy and paste the output from the terminal.

Desktop icon → project → right-click to create a new file → system_tables.txt → double-click to open in text editor → copy and paste the output from the terminal → save and close the file

Example

After executing your commands and saving the results, you can verify the output:

cat ~/project/system_tables.txt

+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| column_stats                                         |
| columns_priv                                         |
| db                                                   |
| event                                                |
| func                                                 |
| general_log                                          |
| gtid_slave_pos                                       |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| host                                                 |
| index_stats                                          |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| plugin                                               |
| proc                                                 |
| procs_priv                                           |
| proxies_priv                                         |
| roles_mapping                                        |
| servers                                              |
| slow_log                                             |
| table_stats                                          |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+

✨ Check Solution and Practice

Summary

In this challenge, you practiced starting the MySQL service, connecting to the MySQL server, and exploring system databases and tables. These are fundamental skills for working with MySQL databases. The challenge reinforces the importance of being able to manage the MySQL service and navigate the system's structure.