This tutorial introduces you to MySQL installation and initial setup, a leading open-source database system. We’ll cover essential aspects like installation verification, initial security setup, and basic operations within the MySQL environment.
You’ll gain practical experience in interacting with the system, including exploring pre-defined databases and their tables.
Why use MySQL database?
MySQL is a popular database system for several reasons:
- Open-Source and Free: MySQL is open-source, meaning it’s freely available and can be modified and distributed. This makes it accessible and cost-effective, especially for smaller projects and startups.
- Widely Used: MySQL is incredibly popular, leading to a large community of users and developers. This means abundant resources, tutorials, and support are readily available.
- Relatively Easy to Learn: While database concepts can have a learning curve, MySQL is generally considered easier to learn and use compared to some other database systems.
- Fast and Efficient: MySQL is known for its speed and efficiency, making it suitable for handling large datasets and high-traffic applications.
- Versatile: MySQL can be used for a wide range of applications, from small websites and blogs to complex e-commerce platforms and social media sites.
- Scalable: MySQL can scale to handle growing data volumes and user traffic, making it suitable for businesses of all sizes.
- Cross-Platform Compatibility: MySQL runs on various operating systems, including Windows, Linux, and macOS, providing flexibility in deployment.
These factors contribute to MySQL’s widespread adoption and make it a strong choice for many database-driven projects.
Verify MySQL Installation
On a standard Ubuntu environment, you would install MySQL with the following commands:
sudo apt update
sudo apt install mysql-server -y
To ensure you have the most up-to-date package information, run apt update. Then, install the MySQL server using apt install mysql-server -y. The -y flag automatically confirms any installation prompts.
To begin, let’s verify MySQL’s installation and check its current status. Open a terminal window by clicking on the terminal icon on your desktop.
Within the terminal environment, execute the following command to determine the status of the MySQL service.
sudo service mysql status
This command utilizes sudo to gain administrative privileges, necessary for system-level tasks. The service mysql status component queries the system for the current operational status of the MySQL service.
The output should show that MySQL is currently stopped, which is expected since the service hasn’t been started yet.
sudo service mysql start
Allow a few seconds for the service to initialize, and then re-run the status command.
Accessing MySQL Shell
Now, we’ll access the MySQL shell to perform some basic tasks.
To do this, execute the following command:
sudo mysql -u root
This command uses sudo to gain administrative privileges, essential for most system-level actions. mysql is used to access the MySQL shell, and -u root specifies that you’re connecting as the root user.
Since our VM’s MySQL installation does not permit root login without a password, you need to define a password.
If the connection is successful, you’ll be greeted with a welcome message and then presented with 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 within the MySQL shell, where you can execute SQL commands. The MariaDB [(none)]> prompt signifies that you’re connected to MariaDB (a version of MySQL) and not currently using a specific database.
While you’re seeing MariaDB instead of MySQL, don’t worry! MariaDB is fully compatible with MySQL, making it suitable for this lab.
MariaDB was created as an open-source alternative to MySQL, offering high compatibility and even some performance enhancements.
For this lab, treat MariaDB as MySQL. All commands will work identically.
MariaDB is often preferred for its speed and lightweight nature, making it an excellent learning platform.
Many Linux distributions, including some Ubuntu versions, use MariaDB by default.
Essentially, for this lab, consider “MariaDB” as synonymous with “MySQL.” The skills you acquire here will be directly applicable to both in real-world situations.
Examine System Databases
Now, we’ll examine the system databases. These databases are included by default with MySQL and hold essential information about the server itself.
In the MySQL shell, run the following command:
SHOW DATABASES;
Important: Don't forget the semicolon (;) at the end of each SQL command.
This command provides a list of all the databases currently present on your MySQL server.
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
Let’s explore the purpose of these databases:
information_schema: This database acts as a central repository for metadata about the entire database system, including tables, columns, and user privileges.mysql
: This database contains critical information that the MySQL server itself requires to function correctly.performance_schema: This database enables real-time monitoring and analysis of the server’s internal operations, providing valuable insights into its performance.sys: This database complements performance_schema by offering a set of objects that simplify the interpretation of performance data for database administrators and developers.
For this lab, we’ll focus on the mysql database. Let’s switch to it:
USE mysql;
Now, we will determine which tables exist within this database.
SHOW TABLES;
You’ll see an extensive list of tables. These tables store a variety of information crucial for MySQL’s configuration and operation.
Data Examination in System Tables
Now that we have a list of the system tables, let’s delve deeper by examining the data within the user table. This table specifically stores information related to MySQL user accounts.
Run the following command to see the structure of the user table:
DESCRIBE user;
The output will display a complete list of all columns present in the user table.
As expected, this table contains a wealth of detailed information about every MySQL user.
Now, let’s shift our attention to specific data entries within this table, concentrating on a few critical columns.
SELECT User, Host, Password_expired FROM user;
This query selects four important columns from the user table:
User: The username of the MySQL accountHost: The host from which this user is allowed to connectPassword_expired: Whether the password has expired
You can expect to see output that resembles this:
+-------------+-----------+------------------+
| User | Host | password_expired |
+-------------+-----------+------------------+
| mariadb.sys | localhost | Y |
| root | localhost | N |
| mysql | localhost | N |
+-------------+-----------+------------------+
3 rows in set (0.001 sec)
This output provides a list of all MySQL user accounts on the system. The root user holds the highest level of administrative privileges, whereas the remaining accounts are utilized by the MySQL server for various internal purposes.
To exit the MySQL shell, type:
EXIT;
This will return you to your regular terminal prompt.
Summary
Throughout this tutorial, we’ve covered the fundamental steps involved in MySQL installation and initial setup within a Dockerized environment.
We successfully verified the MySQL installation, implemented initial security measures, gained access to the MySQL shell, explored the pre-installed system databases, and examined the data contained within these system tables.
Key learning outcomes include the ability to:
- Check and manage the status of the MySQL service.
- Access the MySQL shell effectively.
- View and identify the system databases.
- Explore the tables that reside within each system database.
- Query and retrieve data from the system tables.
These foundational skills are crucial for more advanced database management. Continue building upon these basics by creating your own databases, writing complex queries, and integrating MySQL into applications.
Consistent practice is key to mastering database management, so experiment with various commands and explore MySQL’s capabilities further.
