Install MySQL 8 on Ubuntu 22.04

0
19

This guide provides a step-wise tutorial on how to install MySQL 8 on Ubuntu 22.04. MySQL is a fast, stable and true multi-user, multi-threaded SQL database server with its main goals being speed, robustness and ease of use. To see a comprehensive description of the features offered by MySQL 8, navigate to MySQL 8 Reference Manual.

Install MySQL 8 on Ubuntu 22.04

Ubuntu 22.04 ships with MySQL 8 on its default repositories. This makes the installation of MySQL 8 on Ubuntu 22.04 a seamless task.

Before you can proceed, update and upgrade your system packages.

apt update

Next, install MySQL 8 on Ubuntu 22.04 by executing the command below;

apt install mysql-server

The command installs MySQL 8 and all required package dependencies.

Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libprotobuf-lite23 libtimedate-perl
  liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
  libdata-dump-perl libipc-sharedcache-perl libbusiness-isbn-perl libwww-perl mailx tinyca
The following NEW packages will be installed:
  libcgi-fast-perl libcgi-pm-perl libclone-perl libencode-locale-perl libevent-pthreads-2.1-7 libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-parser-perl
  libhtml-tagset-perl libhtml-template-perl libhttp-date-perl libhttp-message-perl libio-html-perl liblwp-mediatypes-perl libmecab2 libprotobuf-lite23 libtimedate-perl
  liburi-perl mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0 mysql-client-core-8.0 mysql-common mysql-server mysql-server-8.0 mysql-server-core-8.0
0 upgraded, 28 newly installed, 0 to remove and 3 not upgraded.
Need to get 29.0 MB of archives.
After this operation, 240 MB of additional disk space will be used.
Do you want to continue? [Y/n] y

Secure MySQL 8 Installation on Ubuntu 22.04

MySQL ships with a security script called mysql_secure_installation that enables you to implement initial security of MySQL installation in the following ways:

  • You can set a password for root accounts.
  • You can remove root accounts that are accessible from outside the local host.
  • You can remove anonymous-user accounts.
  • You can remove the test database (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with test_.

The script can be simply execute by running;

mysql_secure_installation

When run, the script prompts you on whether you want to implement password complexity checks. Accept the choose the strength of the password;

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
...

Next, set the root password and accept other prompts to remove anonymous database users, disallow remote root login, remove test databases and reload privileges tables to effect the changes on MySQL.

Please set the password for root here.

New password: 

Re-enter new password: 

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Success.

By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
 - Dropping test database...
Success.

 - Removing privileges on test database...
Success.

Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.

All done!

Checking the Version of MySQL Installed

You can verify the version of MySQL installed by executing;

mysql -V
mysql  Ver 8.0.28-0ubuntu4 for Linux on x86_64 ((Ubuntu))

Logging in to MySQL 8

You can now connect to MySQL 8 as a root user with the password you just set above.

But it is good to note that MySQL 8 uses unix socket authentication plugin by default. This enables you to login to your MySQL server from the localhost as a root user or user with sudo rights without a password.

Therefore, running either of the commands below logs you in to MySQL server;

mysql
mysql -u root

Even when you run the command below, and press enter for empty password when prompted, you should still login.

mysql -u root -p

Once logged in to MySQL, you can as well check the version by executing the command;

mysql> SHOW VARIABLES LIKE "%version%";
+--------------------------+-----------------+
| Variable_name            | Value           |
+--------------------------+-----------------+
| admin_tls_version        | TLSv1.2,TLSv1.3 |
| immediate_server_version | 999999          |
| innodb_version           | 8.0.28          |
| original_server_version  | 999999          |
| protocol_version         | 10              |
| replica_type_conversions |                 |
| slave_type_conversions   |                 |
| tls_version              | TLSv1.2,TLSv1.3 |
| version                  | 8.0.28-0ubuntu4 |
| version_comment          | (Ubuntu)        |
| version_compile_machine  | x86_64          |
| version_compile_os       | Linux           |
| version_compile_zlib     | 1.2.11          |
+--------------------------+-----------------+
13 rows in set (0.02 sec)

Enable Password-Based on MySQL 8 Authentication

As mentioned above, MySQL 8 uses unix socket authentication plugin by default.

SELECT plugin from mysql.user where User='root';
+-------------+
| plugin      |
+-------------+
| auth_socket |
+-------------+
1 row in set (0.00 sec)

To enable password based authentication, you need to switch to MySQL native password plugin, mysql_native_password.

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root' AND plugin = 'auth_socket';

Once that is done, reset root user password;

ALTER USER [email protected] identified with mysql_native_password by '[email protected]';

Reload privileges tables;

flush privileges;

Verify the changes;

SELECT User,plugin from mysql.user where User='root';
+------+-----------------------+
| User | plugin                |
+------+-----------------------+
| root | mysql_native_password |
+------+-----------------------+
1 row in set (0.00 sec)

Exit the database connection and try to login as root again;

mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

There you go. You have disabled no password authentication for MySQL root user on your localhost.

That marks the end of our guide on how to install MySQL 8 on Ubuntu 22.04.

Related Tutorials

Install MySQL 8 on CentOS 8

Install MySQL 8 on Debian 10 Buster

Install MySQL 8 on FreeBSD 12

Install MySQL 8 on Debian 9

LEAVE A REPLY

Please enter your comment!
Please enter your name here