Install and Configure MySQL 8 on Ubuntu: A Detailed A-Z Guide – ITFROMZERO

For over half a year, MySQL 8 has been the backbone for many web applications I’ve been developing and maintaining in production environments. From small systems to projects with large user bases, MySQL consistently stands as the top choice for storing and managing relational data. It’s not only powerful and reliable but also continuously improved with many new features, significantly boosting performance.

If you are a developer, DevOps engineer, or system administrator who needs to set up a database server on Ubuntu, mastering how to install and configure MySQL 8 is an essential foundational skill. I believe that after this article, you will confidently deploy MySQL 8 for your projects safely and efficiently.

Table of Contents

Core Concepts: What are MySQL and RDBMS?

Before diving into practice, let’s understand what MySQL is and how it works. Essentially, MySQL is an open-source Relational Database Management System (RDBMS) that uses Structured Query Language (SQL) to manage data.

The core of RDBMS is organizing data into tables that are related to each other via keys. For example, you might have a users table containing user information and a posts table storing articles. A column in the posts table would link to the user ID in the users table. This model helps keep data consistent and easy to manage.

MySQL operates on a client-server model. This means a server program runs on your system to manage the database. Application programs (clients) will connect to this server, send SQL commands, and receive results. Ubuntu, with its stability and large support community, is an ideal platform for running a MySQL server.

Hands-on: Installing and Configuring MySQL 8 on Ubuntu

This is the most crucial part, where I will guide you step-by-step to get MySQL 8 up and running on your Ubuntu system. I assume you are using Ubuntu Server 20.04 LTS or 22.04 LTS, but these steps are also similar for recent Ubuntu versions.

Step 1: Update the System

Always start by updating the system. This is an important step when deploying any service, ensuring you have the latest software packages and crucial security patches.


sudo apt update
sudo apt upgrade -y

Step 2: Install MySQL Server

After the system is updated, we can proceed with installing the MySQL server package. Ubuntu typically has MySQL 8 available in its official repositories.


sudo apt install mysql-server -y

During the installation process, you may be prompted to set a password for the MySQL root user. Choose a strong password and remember it carefully. If not prompted, you will set it up in the subsequent security configuration step.

Step 3: Initial Security Configuration with mysql_secure_installation

This step is extremely important to protect your MySQL server from unauthorized access. The mysql_secure_installation tool will guide you through a series of questions to enhance security.


sudo mysql_secure_installation

The tool will ask you the following:

VALIDATE PASSWORD COMPONENT? I usually choose Y to ensure that the password is set strongly enough.
- You will be able to choose the password strength level (Low, Medium, Strong). I recommend using Medium or Strong.
Change the password for root? If you already set the password in the previous installation step, choose N. Otherwise, choose Y and set a strong password.
Remove anonymous users? Always choose Y. Anonymous users can access the database without a password, which is very dangerous.
Disallow root login remotely? I always choose Y. Restricting root access to only localhost helps prevent brute-force attacks on the root account from external sources.
Remove test database and access to it? Choose Y. The test database is not necessary for production environments and can be exploited.
Reload privilege tables now? Always choose Y for security changes to take effect immediately.

Step 4: Check MySQL Server Status

After installation and security configuration, ensure that the MySQL server is running stably. You can check the status with the command:


systemctl status mysql

If you see active (running) status, it’s good. If it’s not running, you can restart it with the command:


sudo systemctl start mysql

And enable it to start automatically with the system:

sudo systemctl enable mysql

Step 5: Log in to MySQL and Check Version

Now you can log in to the MySQL client with the root user to check.


sudo mysql -u root -p

Enter the root password you set. After successful login, you will see the mysql> prompt. To check the version, run the command:


SELECT VERSION();

The output will display the MySQL version, for example: 8.0.36.

Step 6: Create a New User and Database for the Application

In a production environment, I never use the root account to connect from applications. This poses many risks. Instead, you should create a separate database and user with the necessary privileges. This is a safer approach.


-- Create a new database
CREATE DATABASE myapp_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create a new user and only allow access from localhost
CREATE USER 'myapp_user'@'localhost' IDENTIFIED BY 'YourVeryStrongPassword!';

-- Grant privileges to the new user on the myapp_db database
GRANT ALL PRIVILEGES ON myapp_db.* TO 'myapp_user'@'localhost';

-- Apply privilege changes
FLUSH PRIVILEGES;

Note: Replace YourVeryStrongPassword! with your own strong password. If the application needs to connect from another server (not localhost), replace 'localhost' with an IP address or '%' (allows from anywhere, but requires extreme caution regarding security).

Step 7: Configure Remote Access (Optional)

By default, the MySQL server only accepts connections from localhost. If you need applications from other servers to connect, you must change this configuration.

First, edit the MySQL configuration file:


sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Find the bind-address line and change it as follows:


[mysqld]
# ... other configurations ...
# bind-address = 127.0.0.1
bind-address = 0.0.0.0 # Allows connections from all IP addresses
# Or only allow from a specific IP, e.g.: bind-address = 192.168.1.100

Save and close the file (Ctrl+X, Y, Enter). Then, restart MySQL:


sudo systemctl restart mysql

Finally, you need to open the MySQL port (default is 3306) on Ubuntu's firewall (UFW).


sudo ufw allow 3306/tcp
sudo ufw status

Ensure that ufw is enabled and port 3306/tcp is allowed.

Important Warning: Opening ports for remote access increases security risks. Always ensure you have configured strong passwords and only grant access to trusted IP addresses.

Step 8: Basic Configuration Optimization from Real-world Experience

The default configuration of MySQL 8 is quite good for many cases. However, when your system starts experiencing high loads, optimization becomes unavoidable. I remember when I first worked on a project, as the users table approached 10 million rows, queries started to slow down terribly. This directly impacted user experience. That's when I realized the default MySQL configuration couldn't handle large loads, and optimizing indexes along with system parameters was critically important.

One of the most important parameters is innodb_buffer_pool_size. This is the cache memory area that InnoDB (MySQL 8's default storage engine) uses to store frequently accessed data and indexes. Allocating sufficient RAM for innodb_buffer_pool_size can significantly improve read performance. I usually set it to about 70-80% of the server's total RAM if it's a server running only MySQL (for example, a 4GB RAM server could set innodb_buffer_pool_size to 3GB).

To edit, you also go to the mysqld.cnf file:


sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Add or edit the following line under the [mysqld] section:


[mysqld]
# ... other configurations ...
innodb_buffer_pool_size = 2G # Example: set 2GB, adjust according to the server's actual RAM

Then, restart MySQL to apply the changes:


sudo systemctl restart mysql

MySQL 8 has removed the query_cache. Instead, you can consider caching mechanisms at the application layer or use tools like ProxySQL to optimize connections and query caching. Monitoring the slow query log is also a powerful tool for identifying SQL queries that need performance optimization.

Conclusion

With the detailed instructions above, you have successfully installed and configured a MySQL 8 server on Ubuntu. From initial security setup to creating users, databases, and basic optimization adjustments, you now have a solid foundation to deploy your applications.

Remember, installation is just the beginning. Maintaining, monitoring performance, and continuously optimizing MySQL is an ongoing process, especially as your system grows. Always prioritize security and don't hesitate to explore the other powerful features MySQL 8 offers. I wish you success with your projects using MySQL!