Explore MySQL System Tables

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.

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                                                 |
+------------------------------------------------------+

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.