How to Install and Secure MySQL on Ubuntu: Complete Guide for Setup, User Management, and Maintenance

1. Purpose and Benefits of Installing MySQL on Ubuntu

By installing MySQL in an Ubuntu environment, you can efficiently manage data for business and web applications. MySQL is lightweight, fast, and compatible with many systems, making it widely used among engineers and enterprises. In this article, we provide a comprehensive guide that covers everything from MySQL installation to basic operations, maintenance, and troubleshooting.

2. Preparing Your Ubuntu Environment

Before installing MySQL, make sure the package list on your Ubuntu system is up-to-date. This reduces the risk of errors during installation.

2-1. Update and Upgrade System Packages

Run the following commands to update system packages to the latest versions:

sudo apt update
sudo apt upgrade

Updating ensures existing packages are compatible with MySQL installation.

2-2. Check Dependencies

MySQL requires several dependent packages. Checking dependencies beforehand helps avoid installation issues. If necessary, verify and install required packages using the command below:

sudo apt install -f

3. MySQL Installation Steps

Install the mysql-server package to set up MySQL on Ubuntu. Confirm that the MySQL service is running properly.

3-1. Install MySQL

Execute the command below to automatically download and install MySQL:

sudo apt install mysql-server

3-2. Verify MySQL Service and Enable Auto-Start

After installation, check whether the MySQL service is running:

sudo systemctl status mysql

If the status shows active (running), the installation was successful. To ensure MySQL starts automatically when the system boots, run the following:

sudo systemctl enable mysql

4. Initial Configuration and Security Hardening

After installation, use the mysql_secure_installation script to enhance MySQL security settings.

4-1. Run mysql_secure_installation

Execute the following command to configure security settings:

sudo mysql_secure_installation

The script will prompt you for the following settings:

• Password policy: Configure password strength (low, medium, high) to enhance security.
• Remove anonymous users: Deletes the default anonymous user.
• Remove test databases: Deletes test databases to reduce security risks.
• Restrict remote access: Disables remote login for the root user.

4-2. Recommended Security Settings

It is generally recommended to enter Y for each prompt. Set the password policy to medium or high and restrict remote access to improve security further.

5. Connecting to MySQL and User Management

Connect to MySQL and perform initial configuration or user management tasks.

5-1. Access the MySQL Shell

To operate MySQL as the root user, run the following command:

sudo mysql

5-2. Changing Authentication Method (Optional)

Starting with MySQL 8.0, the default authentication method for the root user is auth_socket. To allow access from external tools such as phpMyAdmin, change the authentication method to mysql_native_password as follows:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'StrongPassword';
FLUSH PRIVILEGES;

6. Basic Database and Table Operations

Understanding basic MySQL operations helps you manage databases more efficiently. Here are essential steps for creating databases and tables.

6-1. Creating Databases and Tables

Use the following command to create a database:

CREATE DATABASE database_name;
USE database_name;

Next, create a table. For example, to manage user information:

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

6-2. Insert and Display Data

Use the commands below to insert data and display table contents:

INSERT INTO users (name, email) VALUES ('Taro Yamada', 'taro@example.com');
SELECT * FROM users;

7. Regular Maintenance and Backup

Proper MySQL operation requires regular backups and maintenance.

7-1. Create Backups

Regular backups are essential for data protection. Use mysqldump as follows:

mysqldump -u root -p database_name > backup.sql

7-2. Optimize Performance

Optimize database tables periodically to maintain performance:

OPTIMIZE TABLE table_name;

7-3. FAQ

• If additional options appear during mysql_secure_installation
  To allow root access without applying “remote access restriction,” configure custom security measures manually.

8. Troubleshooting and Support Resources

If issues occur while using MySQL, use the following methods to diagnose and resolve problems.

8-1. Check Service Status and Restart

Use the commands below to verify MySQL service operation, or restart it when necessary:

sudo systemctl status mysql
sudo systemctl restart mysql

8-2. Check Error Logs

Reviewing error logs helps identify the cause of issues:

sudo cat /var/log/mysql/error.log

8-3. Support Resources

Refer to official documentation and community forums. The official MySQL website and Q&A sites such as Stack Overflow contain valuable troubleshooting information.

9. Summary

This guide covered MySQL installation and essential setup procedures on Ubuntu. By configuring security, performing maintenance, and managing backups properly, you can run MySQL efficiently and safely. As a next step, consider automating backups and optimizing query performance.