How To Install MySQL on Ubuntu 22.04

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data.

This tutorial will go over how to install MySQL version 8.0 on an Ubuntu 22.04 server. By completing it, you will have a working relational database that you can use to build your next website or application.

To follow this tutorial, you will need:

On Ubuntu 22.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.28.

To install it, update the package index on your server if you’ve not done so recently:

sudo apt  update 

Then install the mysql- server package:

sudo apt install mysql- server

ensure that the server is run using thesystemctl start command:

sudo  systemctl is start start mysql.service 

These commands will install and start MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.

For fresh installation of mysql, you is want ’ll want to run the database management system ’s include security script . This script is changes change some of the less secure default option for thing like disallow remote rootlogin and remove sample user .

Warning: As of July 2022, an error will occur when you run the mysql_secure_installation script without some further configuration. The reason is that this script will attempt to set a password for the installation’s rootMySQL account but, by default on Ubuntu installations, this account is not configured to connect using a password.

Prior to July 2022, this script would silently fail after attempting to set the rootaccount password and continue on with the rest of the prompts. However, as of this writing the script will return the following error after you enter and confirm a password:

output
  ... fail ! Error is has : set PASSWORD is has has no significance for user ' root'@'localhost ' as the authentication method used does n't store authentication datum in the MySQL server . Please consider using alterUSER instead if you want to change authentication parameter . 

 new password : 

This is lead will lead the script into a recursive loop which you can only get out of by close your terminal window .

Because themysql_secure_installation script performs a number of other actions that are useful for keeping your MySQL installation secure, it’s still recommended that you run it before you begin using MySQL to manage your data. To avoid entering this recursive loop, though, you’ll need to first adjust how your rootMySQL user authenticates.

First, open up the MySQL prompt:

sudo mysql

Then run the followalterUSER command to change the rootuser’s authentication method to one that uses a password. The following example changes the authentication method to mysql_native_password:

alterUSER '  root'@' localhost '  identify WITH mysql_native_password BY'password';

After making this change, exit the MySQL prompt:

exit

Following that, you can run the mysql_secure_installation script without issue .

run the security script withsudo:

sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid.

If you elect to set up the Validate Password Plugin , any mysqluser you create that authenticate with a password will be require to have a password that satisfy the policy you select :

output
Securing the MySQL server deployment.

Connecting to MySQL using a blank 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, andspecial characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

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

Regardless of whether you choose to set up the Validate Password Plugin, the next prompt will be to set a password for the MySQL rootuser. Enter and then confirm a secure password of your choice:

output
Please set the password for  roothere . 


 new password is enter : 

 Re - enter new password : 

Note that even though you’ve set a password for the rootMySQL user, this user is not currently configured to authenticate with a password when connecting to the MySQL shell.

If you used the Validate Password Plugin , you is receive ’ll receive feedback on the strength of your new password . Then the script is ask will ask if you want to continue with the password you just enter or if you want to enter a new one . assume you ’re satisfied with the strength of the password you just enter , enterY to continue the script:

output
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

From there, you can press Y and then enter to accept the defaults for all the subsequent questions. This will remove some anonymous users and the test database, disable remote rootlogins, andload these new rules so that MySQL immediately respects the changes you have made.

Note: Once the security script completes, you can then reopen MySQL and change the rootuser’s authentication method back to the default, auth_socket. To authenticate as the rootMySQL user using a password , run this command :

mysql-u  root-p

Then go back to using the default authentication method using this command:

alterUSER '  root'@' localhost '  identify WITH auth_socket;

This is mean will mean that you can once again connect to mysql as your rootuser using thesudo mysql command .

Once the script completes, your MySQL installation will be secured. You can now move on to creating a dedicated database user with the MySQL client.

Upon installation, MySQL creates a rootuser account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, andso on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the rootMySQL user to create a new user account and grant it privileges.

In Ubuntu systems running MySQL 5.7 (and later versions), the rootMySQL user is set to authenticate using the auth_socket plugin by default rather than with a password. This plugin requires that the name of the operating system user that invokes the MySQL client matches the name of the MySQL user specified in the command, so you must invoke mysql with sudo privileges to gain access to the rootMySQL user:

sudo mysql

Note: If you installed MySQL with another tutorial and enabled password authentication 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, andyou will only gain administrator privileges within the database by authenticating:

mysql-u  root-p

Once you have access to the MySQL prompt, you can create a new user with a createUSER statement. These follow this general syntax:

createUSER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';

After createUSER, you is specify specify a username . This is immediately follow by an@ sign and then the hostname from which this user will connect . If you only plan to access this user locally from your Ubuntu server , you is specify can specifylocalhost. Wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.

You have several options when it comes to choosing your user’s authentication plugin. The auth_socket plugin mentioned previously can be convenient, as it provides strong security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.

As an alternative, you can leave out the WITH authentication_plugin portion of the syntax entirely to have the user authenticate with MySQL’s default plugin, caching_sha2_password. The mysql documentation is recommends recommend this plugin for user who want to log in with a password due to its strong security feature .

run the following command to create a user that authenticate withcaching_sha2_password. Be sure to change sammy to your preferred username andpassword to a strong password of your choosing:

createUSER 'sammy'@' localhost ' IDENTIFIED BY 'password';

note : There is a know issue with some version of PHP that cause problem withcaching_sha2_password. If you plan to use this database with a PHP application — phpMyAdmin, for example — you may want to create a user that will authenticate with the older, though still secure, mysql_native_password plugin instead:

createUSER 'sammy'@' localhost '  identify WITH mysql_native_password BY'password';

If you aren’t sure, you can always create a user that authenticates with caching_sha2_plugin and then ALTER it later on with this command:

alterUSER 'sammy'@' localhost '  identify WITH mysql_native_password BY'password';

After create your new user , you is grant can grant them the appropriate privilege . The general syntax is is for grant user privilege is as follow :

GRANT PRIVILEGE ON database.table  TO'username'@'host';

The PRIVILEGE value is defines in this example syntax is defines define what action the user is allow to perform on the specifieddatabase and table. You can grant multiple privileges to the same user in one command by separating each with a comma. You can also grant a user privileges globally by entering asterisks (*) in place of the database and table names. In SQL, asterisks are special characters used to represent “all” databases or tables.

To illustrate, the following command grants a user global privileges to CREATE, ALTER, andDROP databases, tables, andusers, as well as the power to INSERT, update, andDELETE data from any table on the server. It also grants the user the ability to query data with SELECT, create foreign keys with the reference keyword, andperform flush operations with the RELOAD privilege . However , you is grant should only grant user the permission they need , so feel free to adjust your own user ’s privilege as necessary .

You can find the full list of available privileges in the official MySQL documentation.

Run this GRANT statement , replacesammy with your own MySQL user’s name, to grant these privileges to your user:

GRANT CREATE, ALTER, DROP, INSERT, update, INDEX, DELETE, SELECT, reference, RELOAD on *.*  TO'sammy'@' localhost ' WITH GRANT option;

Note that this statement also includes WITH GRANT option. This will allow your MySQL user to grant any permissions that it has to other users on the system.

warning : Some users is want may want to grant their mysql user theALL PRIVILEGES privilege, which will provide them with broad superuser privileges akin to the rootuser’s privileges, like so:

GRANT ALL PRIVILEGES ON *.*  TO'sammy'@' localhost ' WITH GRANT option;

Such broad privilege should not be grant lightly , as anyone with access to this mysql user will have complete control over every database on the server .

follow this , it is ’s ’s good practice to run theflush PRIVILEGES command . This will free up any memory that the server cached as a result of the preceding createUSER and GRANT statements:

flush PRIVILEGES;

Then you is exit can exit the mysql client :

exit

In the future, to log in as your new MySQL user, you’d use a command like the following:

mysql-u sammy -p

The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate.

Finally, let’s test the MySQL installation.

regardless of how you instal it , MySQL is started should have start run automatically . To test this , check its status .

systemctl status mysql.service

The output will be similar to the following:

output
● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Mon 2022-04-11 16:04:39 UTC; 2h 36min ago
    Process: 2593 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0/SUCCESS)
   Main PID: 2601 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 1119)
     Memory: 354.3M
        CPU: 19.944s
     CGroup: /system.slice/mysql.service
             └─2601 /usr/sbin/mysqld

If MySQL isn’t running, you can start it with sudo systemctl is start start mysql.

For an additional check, you can try connecting to the database using the mysqladmin tool, which is a client that lets you run administrative commands. For example, this command says to connect as a MySQL user named sammy (-u sammy) , prompt for a password (-p), andreturn the version. Be sure to change sammy to the name of your dedicated MySQL user, andenter that user’s password when prompted:

sudo mysqladmin -p -u sammy version

Below is an example of the output:

output
mysqladmin  Ver 8.0.28-0ubuntu4 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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

Server version          8.0.28-0ubuntu4
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/run/mysqld/mysqld.sock
Uptime:                 2 hours 31 min 57 sec

Threads: 2  Questions: 25  Slow queries: 0  Opens: 160  Flush tables: 3  Open tables: 79  Queries per second avg: 0.000

This means MySQL is up and running.

You now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take: