How to Install MySQL on Ubuntu 22.04

Introduction

Before we begin talking about how to install MySQL on Ubuntu 22.04, let's briefly understand - What is MySQL?

MySQL is a popular open-source database management system used to store and manage vast amounts of data. It provides a reliable, scalable, and secure platform for businesses and individuals to organize and retrieve information efficiently.

MySQL supports various programming languages and offers robust features like data replication and high-performance indexing. With its user-friendly interface and widespread community support, MySQL is a trusted choice for powering dynamic websites, applications, and data-driven solutions.

In this tutorial, you will learn how to install MySQL on Ubuntu 22.04. We will also address a few FAQs on how to install MySQL on Ubuntu 22.04.

Advantages of MySQL

1. Open-source: MySQL is free to use and widely supported by a vast community, allowing for cost-effective database management.
2. Scalability: MySQL can handle large amounts of data and scale easily to accommodate growing demands.
3. Performance: It offers high-speed data processing, efficient indexing, and caching mechanisms, ensuring optimal performance.
4. Reliability: MySQL provides robust data protection, backup, and recovery mechanisms, ensuring data integrity and reliability.
5. Flexibility: It supports multiple programming languages and platforms, making it adaptable to various application development needs.

Prerequisites to Install MySQL on Ubuntu 22.04

• Ubuntu 22.04 64-bit Operating System
• A non-root user account with sudo privileges
• A Firewall

Step 1 – Install MySQL

Keep in mind that on Ubuntu 22.04, only the latest version of MySQL is present in the APT package repository by default. While writing this, it's MySQL 5.7

To install it, you need to update the package index on your server with apt using the following command:

sudo apt update

After that, install the default package.

sudo apt install mysql-server

Although this will install MySQL, it will not prompt you to set a password, and neither will it make any other configuration changes. As this leaves the installation of your MySQL insecure, we will see next what can be done about it.

Step 2 - Configuring MySQL

To freshly install, you'll need to run the present security script. This modifies some less secure default options for things like remote root logins and sample users. If you recall, on older versions of MySQL, you have to initialize the data directory manually, but this one is done automatically now.

Let's now run the security script.

sudo mysql_secure_installation

The above command will take you through a series of prompts wherein you can make some edits to your MySQL installation security options. Coming the first prompt, it will ask if you would like to set up the Validate Password Plugin, which you can use to test your MySQL password strength. However, regardless of your choice, the next prompt allows you to set a password for the MySQL root user. Enter a new password and confirm a secure password according to your choice.

From there on, you can press Y and then ENTER to accept the defaults for all the questions that follow. This will make you remove some anonymous users and the test database, disable remote root login, and load these new rules so that MySQL immediately respects the changes you have made.

To initialize the MySQL data directory, you would use mysql_install_db versions before 5.7.6, and mysqld --initialise for 5.7.6 and later. However, if you installed MySQL from the Debian distribution, as described in Step 1, the data directory was initialized automatically and there is no need for you to do anything. When you still try to run the command, you’ll see the following error:

Output

mysqld: Can't create directory '/var/lib/mysql/' (Errcode: 17 - File exists)
. . .
2018-04-23T13:48:00.572066Z 0 [ERROR] Aborting

Note that even though you’ve set a password for the root MySQL user, this user is not configured to authenticate with a password when connecting to the MySQL shell. If you want, you can adjust this setting by following Step 3.

Step 3 - (Optional) Modifying User Authentication and Privileges

Keep in mind that in Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. Thus, this allows for some greater security and usability in most cases, but it can also complicate things when you are required to allow an external program (e.g. phpMyAdmin) to access the user.

In order for you to use a password to connect to MySQL as root, you will be required to switch its authentication method from auth_socket to mysql_native_password. In order to do this, open up the MySQL prompt from your terminal:

sudo mysql

As a next step, check which authentication method each of your MySQL user accounts uses with the following command:

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             |                                           | auth_socket           | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

As you can see in this example, the root user authenticates using the auth_socket plugin. In order to configure the root account to authenticate with a password, run the following ALTER USER command. Do make sure to change the password to a strong password of your choosing, and note that this command will change the root password you set in Step 2.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Now, run FLUSH PRIVILEGES which tells the server to reload the grant tables and put your new changes into effect.

FLUSH PRIVILEGES;

Verify the authentication methods employed by each of your users again in order to confirm that the root no longer authenticates using the auth_socket plugin.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Output

+------------------+-------------------------------------------+-----------------------+-----------+
| user             | authentication_string                     | plugin                | host      |
+------------------+-------------------------------------------+-----------------------+-----------+
| root             | *3636DACC8616D997782ADD0839F92C1571D6D78F | mysql_native_password | localhost |
| mysql.session    | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| mysql.sys        | *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE | mysql_native_password | localhost |
| debian-sys-maint | *CC744277A401A7D25BE1CA89AFF17BF607F876FF | mysql_native_password | localhost |
+------------------+-------------------------------------------+-----------------------+-----------+
4 rows in set (0.00 sec)

As you can see in this example output the root MySQL user now authenticates using a password, once you confirm this on your own server, you can exit the MySQL shell:

exit;

You can also see that some may find that it better suits their workflow to connect to MySQL with a dedicated user. In order to create such a user, open up the MySQL shell once again.

sudo mysql

From here on, create a new user and give it a strong password.

CREATE USER 'pwsuser'@'localhost' IDENTIFIED BY 'password';

After that, grant your new user the appropriate privileges. For instance, you could permit the user privileges to all tables within the database, as well as the permission to add, change, and remove user privileges, with the following command:

GRANT ALL PRIVILEGES ON *.* TO 'pwsuser'@'localhost' WITH GRANT OPTION;

Please note that, at this point, you do not need to run the FLUSH PRIVILEGES command again. This command is required only when you modify the grant tables using statements like INSERT, UPDATE, or DELETE. Since you created a new user, instead of modifying an existing one, FLUSH PRIVILEGES is not required here.

After this, exit the MySQL shell:

exit;

As a final step, let’s test the MySQL installation.

Step 4 – Testing MySQL

Now, regardless of how you have installed it, MySQL should have started running automatically. To test this, check its status.

systemctl status mysql.service

You’ll see output similar to this:

Output

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Wed 2018-04-23 21:21:25 UTC; 30min ago
 Main PID: 3754 (mysqld)
    Tasks: 28
   Memory: 142.3M
      CPU: 1.994s
   CGroup: /system.slice/mysql.service
           └─3754 /usr/sbin/mysqld

In case MySQL is not running, you can begin with sudo systemctl start mysql.

If an additional check is required, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For instance, this command asks you to connect to MySQL as root (-u root), prompt for a password (-p), and return the version.

sudo mysqladmin -p -u root version

Note that the output will show up something like this:

Output 

mysqladmin  Ver 8.42 Distrib 5.7.21, for Linux on x86_64
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version      5.7.21-1ubuntu1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         30 min 54 sec

Threads: 1  Questions: 12  Slow queries: 0  Opens: 115  Flush tables: 1  Open tables: 34  Queries per second avg: 0.006

Voila! Your MySQL is up and running.

FAQs to Install MySQL on Ubuntu 22.04

Conclusion

We hope that this tutorial has helped you understand how to install MySQL on Ubuntu 22.04. Click here to know more about MySQL and its features.

If you have any further queries or concerns regarding MySQL installation, do leave a comment below, and we'll respond to them for sure.