How to Install MySQL on Ubuntu 24.04

Introduction

MySQL is an open-source relational database management system (RDBMS) based on the Structured Query Language (SQL). It allows users to store, manage, and retrieve data efficiently, making it a popular choice for web applications, data storage, and complex data management. Installing MySQL on Ubuntu 24.04 provides a stable Linux environment for building, securing, and managing databases effectively. Whether you're a developer, database administrator, or system administrator, understanding the installation process is crucial for setting up a reliable and scalable database solution.

In this article, we'll walk you through the steps to install MySQL on Ubuntu 24.04, covering everything from updating your system to configuring MySQL for secure access. Whether you're setting up a new server or configuring a development environment, this guide will help you get your MySQL database up and running efficiently. If you're also interested in database management on other Linux distributions, you can check out our article on installing MySQL on Debian 12 for a similar approach.

Prerequisites

Before you begin:

• Deploy an Ubuntu 24.04 server instance on Vultr.

• Access the server using SSH as a non-root user with sudo privileges.

• Update the server.

Install MySQL on Ubuntu 24.04

MySQL is available in the default APT repositories on Ubuntu 24.04, and you can install a specific release version using PPA repositories. To install MySQL server on Ubuntu 24.04, follow the steps to update the server package index and install the latest MySQL database server version on your server.

1. Update the server package index.

   CONSOLE

   Copy

   $ sudo apt update

   Explain Code

   Powered by Vultr AgentBeta

2. Install the MySQL server package.

   CONSOLE

   Copy

   $ sudo apt install mysql-server -y

   Explain Code

   Powered by Vultr AgentBeta

3. View the installed MySQL version on your server.

   CONSOLE

   Copy

   $ mysql --version

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   mysql  Ver 8.0.37-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))

Manage the MySQL System Service

MySQL uses the mysql system service to control the database server processes and runtime on your Ubuntu 24.04 server. In the following steps, enable the mysql systemd service to start at boot time and verify the service status to manage the database processes on your server.

1. Enable MySQL to automatically start at boot time.

   CONSOLE

   Copy

   $ sudo systemctl enable mysql

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   Synchronizing state of mysql.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
   Executing: /usr/lib/systemd/systemd-sysv-install enable mysql

2. Start the MySQL database server.

   CONSOLE

   Copy

   $ sudo systemctl start mysql

   Explain Code

   Powered by Vultr AgentBeta

3. View the MySQL server status and verify that it's running.

   CONSOLE

   Copy

   $ sudo systemctl status mysql

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   mysql.service - MySQL Community Server
   Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: en>
   Active: active (running) since Tue 2024-06-04 15:57:55 EEST; 1h 11min ago
   Main PID: 7523 (mysqld)
   Status: "Server is operational"
   Tasks: 38 (limit: 7024)
   Memory: 375.5M (peak: 379.3M)
   CPU: 2min 32.531s
   CGroup: /system.slice/mysql.service
       \u2514\u25007523 /usr/sbin/mysqld

Secure the MySQL Server

Securing the MySQL server is an important step when protecting databases from unauthorized user access. Privileged users such as root can access the MySQL database console by default. In the following steps, create a new MySQL root user password and disable insecure defaults to secure your database server.

1. Run the following command to start the MySQL secure installation script.

   CONSOLE

   Copy

   $ sudo mysql_secure_installation

   Explain Code

   Powered by Vultr AgentBeta

   Follow the script prompts below to set up a new root user password, remove anonymous users, disallow remote root login, and remove test databases on your MySQL database server.

   • VALIDATE PASSWORD component: Enter Y and press Enter to enable password validation on your server.
   • Password Validation Policy: Enter 2 to enable the usage of strong passwords on your server.
   • New password: Enter a new strong password to assign the root database user.
   • Re-enter new password: Enter your password again to verify the root user password.
   • Do you wish to continue with the password provided?: Enter Y to apply the new user password.
   • Remove anonymous users?: Enter Y to revoke MySQL console access to unknown database users.
   • Disallow root login remotely?: Enter Y to disable remote access to the MySQL root user account on your server.
   • Remove test database and access to it?: Enter Y to delete the MySQL test databases.
   • Reload privilege tables now?: Enter Y to refresh the MySQL privilege tables and apply your new configuration changes.

2. Restart the MySQL service to apply your configuration changes.

   CONSOLE

   Copy

   $ sudo systemctl restart mysqld

   Explain Code

   Powered by Vultr AgentBeta

Access MySQL

1. Log in to the MySQL database server as root.

   CONSOLE

   Copy

   $ sudo mysql -u root -p

   Explain Code

   Powered by Vultr AgentBeta

   Enter the root user password you set earlier when prompted.

2. Create a new sample MySQL database. For example, my_database.

   SQL

   Copy

   mysql> CREATE DATABASE my_database;

   Explain Code

   Powered by Vultr AgentBeta

3. Create a new MySQL database user with a strong password. For example, my_user and replace my_password with your desired password depending on your password strength policy.

   SQL

   Copy

   mysql> CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password'; 

   Explain Code

   Powered by Vultr AgentBeta

4. Grant the database user my_user full privileges to your sample database my_database.

   SQL

   Copy

   mysql> GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';

   Explain Code

   Powered by Vultr AgentBeta

5. Refresh the MySQL privilege tables to apply the new user changes.

   SQL

   Copy

   mysql> FLUSH PRIVILEGES;

   Explain Code

   Powered by Vultr AgentBeta

6. Exit the MySQL database console.

   SQL

   Copy

   mysql> Exit

   Explain Code

   Powered by Vultr AgentBeta

Create a Sample MySQL Database

Non-privileged MySQL users have CREATE privileges on the database server by default. In the following sections, create an example database using your unprivileged sample user my_user and add new records to the database tables.

1. Log in to the MySQL database console using the database user my_user you created earlier.

   CONSOLE

   Copy

   $ mysql -u my_user -p

   Explain Code

   Powered by Vultr AgentBeta

   Enter the my_user password you set earlier when prompted.

2. List all databases available to the MySQL user

   CONSOLE

   Copy

   mysql> SHOW DATABASES;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   +--------------------+
   | Database           |
   +--------------------+
   | information_schema |
   | my_database        |
   | mysql              |
   | performance_schema |
   | sys                |
   +--------------------+

3. Create another sample MySQL database. For example, bookstore_db.

   SQL

   Copy

   mysql> CREATE DATABASE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

4. Switch to the new database.

   SQL

   Copy

   mysql> USE example_vultr;

   Explain Code

   Powered by Vultr AgentBeta

5. Create a new sample table with the 3 columns to store different data types.

   SQL

   Copy

   mysql> CREATE TABLE sample_table (
            id INT AUTO_INCREMENT PRIMARY KEY,
            name VARCHAR(255) NOT NULL,
            description TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            );

   Explain Code

   Powered by Vultr AgentBeta

6. Insert sample data into the sample_table table.

   SQL

   Copy

   mysql> INSERT INTO sample_table (name, description) VALUES 
          ('Sample Item 1', 'This is the first sample item.'),
          ('Sample Item 2', 'This is the second sample item.'),
          ('Sample Item 3', 'This is the third sample item.');

   Explain Code

   Powered by Vultr AgentBeta

7. Select all table records to verify that the new table data is available.

   SQL

   Copy

   mysql> SELECT * FROM sample_table;

   Explain Code

   Powered by Vultr AgentBeta

   Output:

   +----+--------------+-------------------------------+---------------------+
   | id | name         | description                   | created_at          |
   +----+--------------+-------------------------------+---------------------+
   |  1 | Sample Item 1| This is the first sample item.| 2024-06-19 10:00:00 |
   |  2 | Sample Item 2| This is the second sample item.| 2024-06-19 10:00:00|
   |  3 | Sample Item 3| This is the third sample item. | 2024-06-19 10:00:00|
   +----+--------------+-------------------------------+---------------------+

Conclusion

You have installed MySQL on your Ubuntu 24.04 server and secured the database server to allow authenticated access for all valid users. MySQL integrates with modern web applications and you can either use your server as a dedicated remote database server or the database backend using a dynamic application stack such as LAMP. For more information about MySQL, visit the official documentation.