How to install MySQL server on Ubuntu 20.04 LTS Linux

How do I install MySQL server 8.0 on Ubuntu 20.04 LTS Linux server? How can I add a new MySQL user and database on the newly set up Ubuntu server?

MySQL version 8.0 is a free and open-source database system used by most web applications and sites on the Internet. Typically MySQL is part of the LAMP (Linux, Apache, MySQL, Perl/Python/PHP) stack. Popular opensource software such as WordPress, MediaWiki, and others heavily used by MySQL as a database storage engine. Let us see how to install MySQL server version 8.x on Ubuntu 20.04 LTS Linux server for your web apps or blog system.

How to install MySQL server on Ubuntu 20.04 LTS server

The procedure is as follows. First, open the terminal application and log in to your server using the ssh command and then update Ubuntu repo to apply security updates and fixes on Ubuntu Linux:
sudo apt update
sudo apt upgrade
## Reboot Linux system if a new Linux kernel installed ##
sudo reboot

Step 1 – Installing MySQL server

Let us search for package:
apt search mysql-server
Get information about the mysql-server version and then install the mysql server:
sudo apt info mysql-server
Outputs:

Package: mysql-server
Version: 8.0.21-0ubuntu0.20.04.4
Priority: optional
Section: database
Source: mysql-8.0
Origin: Ubuntu
Maintainer: Ubuntu Developers 
Original-Maintainer: Debian MySQL Maintainers 
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 111 kB
Depends: mysql-server-8.0
Homepage: http://dev.mysql.com/
Task: lamp-server
Download-Size: 9552 B
APT-Sources: http://archive.ubuntu.com/ubuntu focal-updates/main amd64 Packages
Description: MySQL database server (metapackage depending on the latest version)
 This is an empty package that depends on the current "best" version of
 mysql-server (currently mysql-server-8.0), as determined by the MySQL
 maintainers. Install this package if in doubt about which MySQL
 version you need. That will install the version recommended by the
 package maintainers.
 .
 MySQL is a fast, stable and true multi-user, multi-threaded SQL database
 server. SQL (Structured Query Language) is the most popular database query
 language in the world. The main goals of MySQL are speed, robustness and
 ease of use.

Install it on Ubuntu 20.04 LTS:
sudo apt install mysql-server

Step 2 – Securing MySQL server

We need to secure our new MySQL server installation by running the following command:
sudo mysql_secure_installation
You need to set up a secure password:

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, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
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

Of course, we must delete an anonymous user too:

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.

Do not allow root login over the LAN or Internet. It is a security feature:

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.

Finally, delete a Oracle MySQL database called test and reload privilege:

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! 

Step 3 – Enabling MySQL server at boot time

Make sure our server start when Ubuntu 20.04 LTS boots:
sudo systemctl is-enabled mysql.service
If not enabled, type the following command to enable the server:
sudo systemctl enable mysql.service
Verify server status by typing the following systemctl command:
sudo systemctl status mysql.service

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Fri 2020-10-23 09:20:17 UTC; 16min ago
   Main PID: 1129 (mysqld)
     Status: "Server is operational"
      Tasks: 40 (limit: 4680)
     Memory: 338.6M
     CGroup: /system.slice/mysql.service
             └─1129 /usr/sbin/mysqld

Oct 23 09:20:16 ubuntu-pdb systemd[1]: Starting MySQL Community Server...
Oct 23 09:20:17 ubuntu-pdb systemd[1]: Started MySQL Community Server.

Step 4 – Starting/Stopping/Restarting MySQL server

We can control the MySQL server on Ubuntu using the command line option itself. Let us start it with
sudo systemctl start mysql.service
Stop the MySQL server, enter:
sudo systemctl stop mysql.service
Restart the MySQL server as follows:
sudo systemctl restart mysql.service
We can view the MySQL service log as follows using the journalctl command:
sudo journalctl -u mysql.service -xe
sudo tail -f /var/log/mysql/error.log
Sample log file:

2020-10-23T09:20:13.285531Z 0 [Warning] [MY-011810] [Server] Insecure configuration for --pid-file: Location '/tmp' in the path is accessible to all OS users. Consider choosing a different directory.
2020-10-23T09:20:13.309795Z 7 [System] [MY-013172] [Server] Received SHUTDOWN from user boot. Shutting down mysqld (Version: 8.0.21-0ubuntu0.20.04.4).
2020-10-23T09:20:15.758296Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.21-0ubuntu0.20.04.4)  (Ubuntu).
2020-10-23T09:20:16.780030Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.21-0ubuntu0.20.04.4) starting as process 1129
2020-10-23T09:20:16.789608Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2020-10-23T09:20:17.046682Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2020-10-23T09:20:17.151840Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock
2020-10-23T09:20:17.220152Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2020-10-23T09:20:17.220347Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2020-10-23T09:20:17.242986Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21-0ubuntu0.20.04.4'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  (Ubuntu).

Step 5 – Login into MySQL server for testing purpose

So far, we learned how to install, set up, secure, and start/stop the MySQL server version 8 on Ubuntu 20.04 LTS. It is time to log in as a root (admin) user. The syntax is:
mysql -u root -p
mysql -u USER -h host -p
The STATUS command displays the version and other info:
STATUS;
We can see MySQL version as follows:
SHOW VARIABLES LIKE "%version%";

Step 6 – Creating a new MySQL database and user/password

Let create a new database called wpblog, type:
CREATE DATABASE wpblog;
Next, I’m going to create a new user named ‘wpuser’ for our database called wpblog as follows:
CREATE USER 'wpuser'@'%' IDENTIFIED BY 'Your_Super_Secret_Password';
Finally, give permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON wpblog.* TO 'wpuser'@'%';
Of course, I can grant ALL PRIVILEGES too as follows:
GRANT ALL PRIVILEGES ON wpblog.* TO 'wpuser'@'%';
See MySQL users and their grants:
SELECT user,host FROM mysql.user;
SHOW GRANTS for wpuser;
Test it as follows:
mysql -u wpuser -p wpblog
mysql -u wpuser -h localhost -p wpblog

Where,

• -u wpuser : User for login
• -h localhost : Connect to host named localhost
• -p : Prompt for password
• wpblog : Connect to database named wpblog

Conclusion

And there you have it, Oracle MySQL server version 8.x set up and running correctly on Ubuntu Linux 20.04 LTS server. Further, you learned how to add a new database, user, and password for your project. See Oracle MySQL database docs for SQL and other commands.

Category	List of Unix and Linux commands
File Management	cat
Firewall	Alpine Awall • CentOS 8 • OpenSUSE • RHEL 8 • Ubuntu 16.04 • Ubuntu 18.04 • Ubuntu 20.04
Network Utilities	dig • host • ip • nmap
OpenVPN	CentOS 7 • CentOS 8 • Debian 10 • Debian 8/9 • Ubuntu 18.04 • Ubuntu 20.04
Package Manager	apk • apt
Processes Management	bg • chroot • cron • disown • fg • jobs • killall • kill • pidof • pstree • pwdx • time
Searching	grep • whereis • which
User Information	groups • id • lastcomm • last • lid/libuser-lid • logname • members • users • whoami • who • w
WireGuard VPN	Alpine • CentOS 8 • Debian 10 • Firewall • Ubuntu 20.04