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) that enables users to store, manage and retrieve data efficiently for use with a variety of applications. This guide explains how to install MySQL on Ubuntu 24.04 and secure the server to enable authenticated access to the database console.
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.
Install MySQL
MySQL is available in the default APT repositories on Ubuntu 24.04 and you can install a specific release version using PPA repositories. In the following steps, update the server package index and install the latest MySQL database server version on your server.
Update the server package index.
console$ sudo apt update
Install the MySQL server package.
console$ sudo apt install mysql-server -y
View the installed MySQL version on your server.
console$ mysql --version
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.
Enable MySQL to automatically start at boot time.
console$ sudo systemctl enable mysql
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
Start the MySQL database server.
console$ sudo systemctl start mysql
View the MySQL server status and verify that it's running.
console$ sudo systemctl status mysql
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.
Run the following command to start the MySQL secure installation script.
console$ sudo mysql_secure_installation
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.
Restart the MySQL service to apply your configuration changes.
console$ sudo systemctl restart mysqld
Access MySQL
Log in to the MySQL database server as
root
.console$ sudo mysql -u root -p
Enter the root user password you set earlier when prompted.
Create a new sample MySQL database. For example,
my_database
.sqlmysql> CREATE DATABASE my_database;
Create a new MySQL database user with a strong password. For example,
my_user
and replacemy_password
with your desired password depending on your password strength policy.sqlmysql> CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'my_password';
Grant the database user
my_user
full privileges to your sample databasemy_database
.sqlmysql> GRANT ALL PRIVILEGES ON my_database.* TO 'my_user'@'localhost';
Refresh the MySQL privilege tables to apply the new user changes.
sqlmysql> FLUSH PRIVILEGES;
Exit the MySQL database console.
sqlmysql> Exit
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.
Log in to the MySQL database console using the database user
my_user
you created earlier.console$ mysql -u my_user -p
Enter the
my_user
password you set earlier when prompted.List all databases available to the MySQL user
consolemysql> SHOW DATABASES;
Output:
+--------------------+ | Database | +--------------------+ | information_schema | | my_database | | mysql | | performance_schema | | sys | +--------------------+
Create another sample MySQL database. For example,
bookstore_db
.sqlmysql> CREATE DATABASE example_vultr;
Switch to the new database.
sqlmysql> USE example_vultr;
Create a new sample table with the 3 columns to store different data types.
sqlmysql> CREATE TABLE sample_table ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255) NOT NULL, description TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
Insert sample data into the
sample_table
table.sqlmysql> 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.');
Select all table records to verify that the new table data is available.
sqlmysql> SELECT * FROM sample_table;
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.