MySQL Installation, Configuration, and Basic Operations Lab

Introduction

In this lab, we will explore the basic configuration and usage of MySQL, one of the most popular open-source relational database management systems (RDBMS). MySQL is widely used in many database-driven web applications and popular websites. By the end of this lab, you will have gained hands-on experience in verifying MySQL installation, securing its initial setup, accessing the MySQL shell, and examining system databases and tables.

Verify MySQL Installation

MySQL has been pre-installed on your system for convenience. However, it's important to understand the installation process. In a typical Ubuntu environment, you would use the following commands to install MySQL:

The following commands are for informational purposes only. You do not need to run them, as MySQL is already installed in your environment.

## DO NOT RUN THESE COMMANDS
sudo apt update
sudo apt install mysql-server -y

The apt update command updates the package list to ensure that you have the latest information about available packages. The apt install mysql-server -y command installs the MySQL server package. The -y flag is used to automatically answer "yes" to any prompts that may appear during the installation process.

Let's start by verifying the MySQL installation and checking its status.

First, open a terminal window by clicking on the terminal icon on the desktop:

Once in the terminal, run the following command to check the status of MySQL:

sudo service mysql status

This command uses sudo to run with administrative privileges, which is often required for system-level operations. The service mysql status part asks the system to report on the current state of the MySQL service.

You should see output indicating that MySQL is stopped. This is expected, as we haven't started the MySQL service yet.

sudo service mysql start

Wait a few seconds for the service to start, then run the status command again:

Access MySQL Shell

Now, let's access the MySQL shell to perform some basic operations.

To access the MySQL shell, use the following command:

sudo mysql -u root

This command uses sudo to run with administrative privileges, which is often required for system-level operations. The mysql command is used to access the MySQL shell, and the -u root flag specifies that you're connecting as the root user.

There's no need to provide a password in this case because the MySQL installation on LabEx VM is configured to allow the root user to log in without a password. This is common in development environments but should not be done in production systems.

If successful, you'll see a welcome message and the MySQL prompt:

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 52
Server version: 10.6.18-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

You're now in the MySQL shell, where you can execute SQL commands.

The MariaDB [(none)]> prompt indicates that you're connected to MariaDB (a fork of MySQL) and not currently using any specific database.

It's important to note that you're seeing MariaDB instead of MySQL. Don't worry - this won't affect your ability to complete this lab or learn MySQL commands. Here's why:

MariaDB is a fork of MySQL, created by the original developers of MySQL. It's designed to be a drop-in replacement for MySQL, meaning it's fully compatible with MySQL syntax and operations.
MariaDB was created to ensure that a truly open-source version of MySQL would always be available. It maintains high compatibility with MySQL while also offering some unique features and improvements.
For the purposes of this lab and most basic to intermediate MySQL operations, you can treat MariaDB exactly as you would MySQL. All the commands we'll use in this lab work identically in both MariaDB and MySQL.
MariaDB is often considered more lightweight and faster than MySQL, which makes it an excellent choice for learning environments like this lab. You might notice quicker response times, which can make your learning experience smoother.
Many Linux distributions, including some versions of Ubuntu, now use MariaDB as their default MySQL-compatible database system due to its open-source nature and performance benefits.

So, when you see "MariaDB" in the prompt or output, just remember that for the purposes of this lab, you can think of it as MySQL. All the skills you learn here will be directly applicable to both MariaDB and MySQL in real-world scenarios.

Explore System Databases

Now that we're in the MySQL shell, let's explore the system databases. These are databases that come pre-installed with MySQL and contain important information about the MySQL server itself.

In the MySQL shell, run the following command:

SHOW DATABASES;

Note: Don't forget the semicolon (;) at the end of the command. SQL commands must end with a semicolon.

This command lists all the databases present in your MySQL server. You should see output similar to this:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

Let's break down what these databases are:

information_schema: This is a database that provides access to database metadata.
mysql: This database contains information required by the MySQL server for its operation.
performance_schema: This database provides a way to inspect internal execution of the server at runtime.
sys: This database contains a set of objects that help DBAs and developers interpret data collected by the performance_schema.

For this lab, we'll focus on the mysql database. Let's switch to it:

USE mysql;

Now, let's see what tables are in this database:

SHOW TABLES;

You'll see a long list of tables. These tables store various configuration and operational data for MySQL.

Examine Data in System Tables

Now that we've seen the system tables, let's examine the data in one of them. The user table in the mysql database contains information about MySQL user accounts.

Run the following command to see the structure of the user table:

DESCRIBE user;

This will show you all the columns in the user table. You'll see a lot of columns, as this table contains detailed information about each MySQL user.

Now, let's look at some of the data in this table. We'll focus on a few key columns:

SELECT User, Host, Password_expired FROM user;

This query selects four important columns from the user table:

User: The username of the MySQL account
Host: The host from which this user is allowed to connect
Password_expired: Whether the password has expired

You should see output similar to this:

+-------------+-----------+------------------+
| User        | Host      | password_expired |
+-------------+-----------+------------------+
| mariadb.sys | localhost | Y                |
| root        | localhost | N                |
| mysql       | localhost | N                |
+-------------+-----------+------------------+
3 rows in set (0.001 sec)

This output shows us the MySQL user accounts that exist on the system. The root user is the main administrative account, while the others are system accounts used by MySQL for various purposes.

To exit the MySQL shell, type:

EXIT;

This will return you to your regular terminal prompt.

Summary

In this lab, we've covered the essential steps of working with MySQL in a Docker environment. We verified the MySQL installation, secured the initial setup, accessed the MySQL shell, explored system databases, and examined data in system tables.

We learned how to:

Check and manage MySQL service status
Access the MySQL shell
View system databases
Explore tables within system databases
Query data from system tables

These fundamental skills form the foundation for more advanced database management tasks. As you continue your journey with MySQL, you'll build upon these basics to create your own databases, write sophisticated queries, and integrate MySQL with various applications. Remember, practice is key in mastering database management, so don't hesitate to experiment with different commands and explore MySQL's capabilities further.

Installation and Basic Configuration of MySQL