Jun 26, 2023 7 min read

How to Install MySQL on Ubuntu 22.04

Install MySQL on Ubuntu 22.04 with our step-by-step tutorial. It's a database management system used to manage vast amounts of data.

Install MySQL on Ubuntu 22.04
Table of Contents

Choose a different version or distribution

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
💡
If getting problems with mysql_secure_installation, just log in to MySQL sudo mysql then execute the query to set a password for the root ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOUR_PASSWORD', then again run the mysql_secure_installation and press n when prompt for change root password.

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
👉
If password authentication is enabled for root, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating: mysql -u root -p

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

How do I start the MySQL service after installation?

You can start the MySQL service by executing the command sudo service mysql start in the terminal.

How can I access the MySQL command-line interface?

To access the MySQL command-line interface, use the command mysql -u root -p and provide the root password when prompted.

How do I secure my MySQL installation?

MySQL provides a security script that can be executed with the command sudo mysql_secure_installation. It will guide you through the process of securing your installation by setting a root password, removing anonymous users, and more.

What is the default location of MySQL configuration file on Ubuntu 22.04?

The default location of the MySQL configuration file on Ubuntu 22.04 is /etc/mysql/mysql.conf.d/mysqld.cnf.

Can I install a specific version of MySQL on Ubuntu 22.04?

Yes, you can install a specific version of MySQL on Ubuntu 22.04 by using the package manager apt and specifying the version number, such as sudo apt install mysql-server-5.7.

How can I uninstall MySQL from Ubuntu 22.04?

To uninstall MySQL from Ubuntu 22.04, run the command sudo apt remove mysql-server followed by sudo apt autoremove to remove any remaining dependencies.

Is it necessary to install a separate MySQL client on Ubuntu 22.04?

No, the MySQL client is included with the MySQL server installation. You can access the MySQL command-line interface directly without installing a separate client package.

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.

Great! You’ve successfully signed up.
Welcome back! You've successfully signed in.
You've successfully subscribed to DevOps Tutorials - VegaStack.
Your link has expired.
Success! Check your email for magic link to sign-in.
Success! Your billing info has been updated.
Your billing was not updated.