Cantech Knowledge Base

Your Go-To Hosting Resource

  1. Linux
  2. How to Install MySQL on Ubuntu 24.04?

How to Install MySQL on Ubuntu 24.04?

MySQL stands as a prevalent open-source relational database management system (RDBMS) which provides users with tools to efficiently store, manage and retrieve data. MySQL emerged in 1995 as a database system which through its evolution attained global popularity owing to its exceptional speed and reliability combined with user-friendly features. In this blog, we will guide you on how to install MySQL on Ubuntu 24.04.

Prerequisites

Before installing MySQL, ensure the following:

  • Ubuntu 24.04 installed on your system
  • Sudo privileges for executing administrative commands
  • A stable internet connection for downloading packages

Installation of MySQL

Ubuntu 24’s standard APT repositories include MySQL. 04 and through PPA repositories you have the option to install specific release versions. Execute the subsequent steps to update your server’s package index before proceeding to install the newest version of the MySQL database server on your system.

Step 1: Update System Packages

Ensure all existing packages are up-to-date:

sudo apt update && sudo apt upgrade -y

Step 2: Install MySQL Server

Install the MySQL server package:

sudo apt install mysql-server -y

This command installs the latest MySQL server available in Ubuntu’s default repositories.

Step 3: View the installed MySQL version.

This below command displays the version of the MySQL client installed on your system:

mysql --version

Output

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

Step 4: Secure MySQL Installation

The process of securing MySQL servers represents a critical measure to defend databases against access by unauthorized users. The MySQL database console remains accessible by default to privileged users like root. Execute the below commands to establish a new MySQL root user password while deactivating unsafe defaults to protect your database server.

Enhance the security of your MySQL server:

sudo mysql_secure_installation

During this process, you’ll be prompted to:

  • Set a root password.
  • Remove anonymous users.
  • Disallow root login remotely.
  • Remove the test database.
  • Reload privilege tables to apply changes.
  • It’s advisable to answer ‘yes’ to these prompts to ensure a secure setup.

To secure your MySQL database server by setting up a new root password, removing anonymous users, disabling remote root login, and removing test databases, you can use the following SQL script:

-- 1. Set a new password for the root user
ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewSecurePassword';

-- 2. Remove anonymous users
DELETE FROM mysql.user WHERE User='';

-- 3. Disallow remote root login
UPDATE mysql.user SET Host='localhost' WHERE User='root' AND Host='%';

-- 4. Remove test databases
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

-- 5. Apply changes
FLUSH PRIVILEGES;

Step 5: Restart the MySQL service to enable your configuration changes.

sudo systemctl restart mysqld

Management of MySQL System Service

The mysql system service manages database server processes and runtime operations for MySQL on Ubuntu 24. 04 server. Perform the subsequent steps to activate the mysql systemd service for boot time initiation and check its status to control server database operations.

To configure MySQL to start automatically at boot time on your system, follow the steps corresponding to your operating system:

1. Enable the MySQL Service: Use the systemctl command to enable MySQL to start at boot

sudo systemctl enable mysql

This command creates the necessary symbolic links to ensure MySQL starts automatically on system boot.

2. Verify the Service Status: Check the status of the MySQL service to confirm it’s active

sudo systemctl status mysql

A successful output indicates that MySQL is running and enabled:

Output

mysql.service - MySQL Community Server  
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)  
Active: active (running) since Wed 2025-02-19 19:15:28 IST; 1min ago

Access MySQL

1. To log in to the MySQL database server as the root user, you can use the following command

sudo mysql -u root -p

2. To create a new database in MySQL, you can use the CREATE DATABASE statement. for example, my_database.

CREATE DATABASE my_database;

3. Create a New User with a Strong Password

Once logged in, execute the following command to create a new user:

CREATE USER 'my_user'@'localhost' IDENTIFIED BY 'MyStr0ngP@ssw0rd!';

4. To grant all privileges on the my_database database to the user my_user

Execute the following SQL statement within the MySQL command-line interface:

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

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

FLUSH PRIVILEGES;

6. Exit the MySQL database console.

To exit the MySQL command-line interface, you can use any of the following commands at the mysql> prompt:

exit;
quit;
\q

EXIT;

Create a Sample MySQL Database

By default, MySQL database servers grant non-privileged users CREATE privileges. Through the subsequent sections, construct an example database with your restricted sample user my_user and insert fresh records into the database tables.

1. Log in to MySQL

mysql -u your_username -p
  • Replace your_username with your MySQL username.
  • You’ll be prompted to enter your password.

2. Show All Databases

Once inside the MySQL shell, run:

SHOW DATABASES;

This will display a list of all databases the logged-in user has access to.

Example Output:

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

Create a New Sample table

1. Select the Database

Choose the database where you want to create the table:

USE sample_db;

2. Create the Sample Table

Create a table named sample_table with three columns, each storing a different data type:

CREATE TABLE sample_table (
    id INT AUTO_INCREMENT PRIMARY KEY,  -- Integer data type
    name VARCHAR(100) NOT NULL,         -- String data type
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- Date/Time data type
);

3. Insert Sample Data

Add sample records to the table:

INSERT INTO sample_table (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Since id is auto-incremented and created_at has a default timestamp, you don’t need to insert values for those columns.

4. Verify the Inserted Data

Check if the data has been added correctly:

SELECT * FROM sample_table;

Example Output

| id | name    | created_at          |
|----|---------|---------------------|
| 1  | Alice   | 2025-02-20 12:30:00 |
| 2  | Bob     | 2025-02-20 12:31:00 |
| 3  | Charlie | 2025-02-20 12:32:00 |

Conclusion

You have successfully set up MySQL on your Ubuntu 24.04 server and implemented essential security measures to ensure safe and authenticated access. With MySQL’s robust integration capabilities, you can use it as a standalone remote database server or as part of a dynamic application stack like LAMP. To explore advanced configurations and best practices, refer to the official MySQL documentation.

March 19, 2025