Installation and Basic Configuration of MySQL

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.

Skills Graph

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:

These commands are for your information only. You do not need to run them as MySQL is already installed in your environment.

sudo apt update
sudo apt install mysql-server -y

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

First, open a terminal window. In the Docker environment, you should already have a terminal open. If not, look for an icon that resembles a command prompt or terminal.

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 running. If it's not running, you can start it with:

sudo service mysql start

If MySQL is already running, you don't need to run this command. It's provided here in case you need to start MySQL in the future.

Secure MySQL Installation

Now that we've confirmed MySQL is installed and running, let's secure its initial setup. MySQL comes with a security script that helps us improve the security of our installation.

Run the following command to start the MySQL secure installation process:

sudo mysql_secure_installation

This script will guide you through several security-related questions. Here's how to respond:

1. When asked to enter the current password for root, just press Enter as there's no password set yet.
2. When asked if you want to set up the VALIDATE PASSWORD PLUGIN, type N and press Enter. This plugin can be useful in production environments, but for our learning purposes, we'll skip it.
3. When prompted to set a root password, type N. In this lab environment, we'll keep it simple without a password, but in a real-world scenario, you would set a strong password here.
4. For the remaining prompts, type Y and press Enter to agree to all the security recommendations. These include removing anonymous users, disallowing root login remotely, removing the test database, and reloading privilege tables.

This process enhances the security of your MySQL installation by removing potential vulnerabilities.

Access MySQL Shell

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

To access the MySQL shell, use the following command:

sudo mysql -u root

Note that we're not using the -p flag here because we didn't set a password in the previous step. In a production environment, you would include -p and enter a password when prompted.

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:

1. 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.
2. 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.
3. 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.
4. 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.
5. 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;

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, Account_locked, 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:

1. Check and manage MySQL service status
2. Secure a MySQL installation
3. Access the MySQL shell
4. View system databases
5. Explore tables within system databases
6. 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.