How To Install MySQL 8 on Ubuntu 20.04
Publish Date: 2022-06-20
MySQL is a fast, multi-threaded, multi-user, and robust SQL database server. It is intended for mission-critical, heavy-load production systems and mass-deployed software. It is part of database management system provided by Oracle, and can be installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack.
In this tutorial, you will learn how to install and configure MySQL version 8.0 on Ubuntu 20.04. By completing it, you will have a working relational database that you can use to build your next application.
Step 1: Installing MySQL
first you have to update the package index on your machine if you’ve not done so recently, to ensure you are installing the latest MySQL release:
sudo apt update
currently At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.29. to install the mysql-server package, run this command(press Y when it ask to confirm):
sudo apt install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl
libhtml-template-perl libmecab2 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:
libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libcgi-fast-perl libcgi-pm-perl libevent-core-2.1-7 libevent-pthreads-2.1-7 libfcgi-perl
libhtml-template-perl libmecab2 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, 17 newly installed, 0 to remove and 53 not upgraded.
Need to get 31.2 MB of archives.
After this operation, 261 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
after the installation is done, you can check the version:
mysql –V
mysql Ver 8.0.29-0ubuntu0.20.04.3 for Linux on x86_64 ((Ubuntu))
Upon successfully installing MySQL, the MySQL service starts automatically. If you receive output like the following, that means the service is operational and running:
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 Sat 2022-05-07 10:47:40 CET; 2min 58s ago
Main PID: 40833 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 4632)
Memory: 357.9M
CGroup: /system.slice/mysql.service
└─40833 /usr/sbin/mysqld
ماي 07 10:47:37 ubuntu21 systemd[1]: Starting MySQL Community Server...
ماي 07 10:47:40 ubuntu21 systemd[1]: Started MySQL Community Server.
If it is not running for any reason , you can start it with this command:
sudo systemctl start mysql.service
Step 2: Creating Dedicated MySQL Users and Granting Privileges
The only option to connect to newly installed MySQL, is to use the MySQL root user account to manage your database, Which has full privileges over the MySQL server, that means complete control over every database, table, user, and so on. For best practices , it is best to avoid using this account outside of administrative functions. But because right now the only available user is root, we will use it, to create a new user accounts and grant privileges to them.
the root MySQL user is set to authenticate using the auth_socket plugin by default rather than with a password (In Ubuntu systems running MySQL 5.7 and later versions up to currently 8), 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. Because of that, if you try to access the MySQL shell without sudo privileges, you will receive error:
mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
so, you must invoke mysql with sudo privileges to gain access to the root MySQL user like the following command (even no password is assigned to mysql root user, you still have to enter the sudo password) :
sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.29-0ubuntu0.20.04.3 (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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
or using the usual command to access MySQL server shell ( by default, no password is assigned to Mysql root user, so just press Enter):
sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 8.0.29-0ubuntu0.20.04.3 (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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
After you have access to the MySQL shell, you can create a new user with a CREATE USER statement by follow this general syntax:
CREATE USER 'username'@'hostname' IDENTIFIED WITH authentication_plugin BY 'password';
so in this command you have to specify the 3 things:
- username@host : the ‘username’ + ‘@’ + ‘hostname’ from which this user will connect for example: ‘ahmed’@’localhost’ , ‘sara'@'192.168.152.124’, wrapping both the username and host in single quotes isn’t always necessary, but doing so can help to prevent errors.
- authentication plugin : there are multiples options to choose from:
- The auth_socket plugin: provides strong security without requiring valid users to enter a password to access the database. But it prevents remote connections, which can complicate things when external applications need to interact with MySQL.
- - The caching_sha2_password plugin: is The MySQL documentation recommendation for users who want to log in with a password due to its strong security features. And it is the default plugin used if you leave out the WITH portion of the syntax entirely.
- - The mysql_native_password plugin: the older one, but still secure, and you can use it as a base plugin or if you face problems working with some php versions.
- password : here you specify your password , which should be strong and satisfy minimum requirements of security policy.
Run the following commands to create and alter some users (all of them in localhost):
Example1: create user ahmed to login from localhost using caching_sha2_password plugin and set him a password of value = ahmed*123A :
CREATE USER 'ahmed'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'ahmed*123A';
Query OK, 0 rows affected (0.01 sec)
Example2: alter user root to login from localhost using auth_socket plugin and set him a password of value = root*123R:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'root*123R';
Query OK, 0 rows affected (0.02 sec)
Example3: create a user: ali that can login from localhost, use mysql_native_password plugin to authenticate with the password = ali*123A:
CREATE USER 'ali'@'localhost' IDENTIFIED WITH mysql_native_password by 'ali*123A';
Query OK, 0 rows affected (0.04 sec)
After successfully creating users, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:
GRANT PRIVILEGE ON Database.Table TO 'username'@'host';
in this syntax, you have to define a couple of parameters:
- – privileges: actions allowed for a user to perform, You can grant multiple privileges to the same user in one command by separating each with a comma. you should only grant users the permissions they need. You can find the full list of available privileges in the official MySQL documentation
- – database and table: which the privileges will be applied to. you can also grant a user privileges globally by entering asterisks (*) in place of the database and table names (* represent “all” databases or tables ).
- – 'username'@'host' : here you have to specify which user will benefit from these privileges and from which location
Let’s grant permission to 2 (two) existing users: ahmed as admin (instead of relying to Mysql root user all the time) as in example 1, and ali as a user with limited privileges as in example 2.
Example4: grant the MySQL user ahmed the ALL PRIVILEGES privilege on all databases and tables at localhost, which will provide broad superuser privileges akin to the root user’s privileges,plus the ability to grant these permissions to other users as well, like so:
GRANT ALL PRIVILEGES ON *.* TO 'ahmed'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)
Example5: let's create a database test_db1, then grant limited privileges to user ali at localhost on all tables but only on database test_db1:
create database test_db1;
Query OK, 0 rows affected (0.01 sec)
GRANT CREATE, INSERT, UPDATE, SELECT on test_db1.* TO 'ali'@'localhost';
Query OK, 0 rows affected (0.01 sec)
After assigning privileges to users , you should run the FLUSH PRIVILEGES command, which it will free up any memory that the server cached as a result of the preceding CREATE USER and GRANT statements:
FLUSH PRIVILEGES;
now, you can exit the MySQL client:
exit;
Step 3 — Testing MySQL
Next, let’s test the newly created MySQL users functions. you can test to log in as your new MySQL user by using a command as follows:
- The -u flag to specify which username to be used to connect to Mysql server.
- The -p flag will cause the MySQL client to prompt you for your MySQL user’s password in order to authenticate):
mysql -u ahmed -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 23
Server version: 8.0.29-0ubuntu0.20.04.3 (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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
you no longer need to specify sudo privilege to use MySQL root user, just type like follows:
mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 24
Server version: 8.0.29-0ubuntu0.20.04.3 (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.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
show databases;
+-----------------------+
| Database |
+-----------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| test_db1 |
+-----------------------+
5 rows in set (0.02 sec)
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. You should see output similar to this:
mysqladmin -p -u ahmed version
Enter password:
mysqladmin Ver 8.0.29-0ubuntu0.20.04.3 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.29-0ubuntu0.20.04.3
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 51 min 38 sec
Threads: 2 Questions: 30 Slow queries: 0 Opens: 169 Flush tables: 3 Open tables: 88 Queries per second avg: 0.009
This means MySQL is up and running.
You can restart the mysql service any time if it is not running:
sudo service mysql restart
A good starting point for troubleshooting problems is the systemd journal, which can be accessed at the terminal prompt with this command:
sudo journalctl -u mysql
-- Logs begin at Fri 2022-05-06 13:44:46 CET, end at Sat 2022-05-07 12:08:49 CET. --
ماي 06 15:09:26 ubuntu21 systemd[1]: Starting MySQL Community Server...
ماي 06 15:09:27 ubuntu21 systemd[1]: Started MySQL Community Server.
ماي 06 16:42:55 ubuntu21 systemd[1]: Stopping MySQL Community Server...
ماي 06 16:42:58 ubuntu21 systemd[1]: mysql.service: Succeeded.
ماي 06 16:42:58 ubuntu21 systemd[1]: Stopped MySQL Community Server.
-- Reboot --
ماي 07 08:20:11 ubuntu21 systemd[1]: Starting MySQL Community Server…
……..
ماي 07 10:47:37 ubuntu21 systemd[1]: Starting MySQL Community Server...
ماي 07 10:47:40 ubuntu21 systemd[1]: Started MySQL Community Server.
The network status of the MySQL service can also be checked by running the ss command at the terminal prompt:
sudo ss -tap | grep mysql
LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* users:(("mysqld",pid=40833,fd=22))
LISTEN 0 151 127.0.0.1:mysql 0.0.0.0:* users:(("mysqld",pid=40833,fd=24))
Step 4: Securing MySQL
by default in fresh installations of MySQL, no password is configured at the installation time, which leave MySQL server in an insecure situation. to address that ,you’ll want to run the DBMS’s included security script. This script changes some less secure default options, for things like remote root logins and sample users.
The following command will take you through a series of prompts where you can make some changes to your MySQL installation’s security options:
sudo mysql_secure_installation
The first prompt will ask you 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:
Securing the MySQL server deployment.
Enter password for user root:
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
If you select to set up the Validate Password Plugin, any MySQL user you create that authenticates with a password, will be required to have a password that satisfies the policy you select:
- - Low. A password containing at least 8 characters.
- - Medium. A password containing at least 8 characters, including numeric, mixed case characters, and special characters.
- - Strong. A password containing at least 8 characters, including numeric, mixed case characters, and special characters, and compares the password to a dictionary file.
Enter 0, 1, or 2 depending on the password strength you want to set:
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: 1
Regardless of whether you choose to set up the Validate Password Plugin, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice
Note: If you already applied example 2 where we alter user root, here instead it will ask you if you want to change the password:
Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
Either way, the script estimates the strength of your password and requires confirmation to continue. Press Y if you are happy with the password or any other key if you want a different one:
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
after successfully set password to root, and from there, you can press Y and then ENTER to accept the defaults for all the subsequent following security features:
- Remove anonymous users?
- Disallow root login remotely?
- Remove test database and access to it?
- Reload privilege tables now?
The recommended answer to all these questions is Y. However, if you want a different setting for any reason, enter any other key.
For example, if you need the remote login option for your server, enter any key other than Y for that prompt.
Once the script completes, your MySQL installation will be secured.
Step 5: Remove MySQL
If you want to disable mysql secure utility along with deleting mysql you can follow these steps:
sudo apt-get remove --purge mysql-server mysql-client mysql-common -y
clean and remove any leftover during the installation of mysql:
sudo apt-get autoremove -y
sudo apt-get autoclean
remove all existing installation files of mysql on the ubuntu machine :
sudo rm -rf /etc/mysql
sudo find / -iname 'mysql*' -exec rm -rf {} \;
Conclusion
You should now have a fully functioning MySQL server installed on your machine. Start exploring the vast possibilities of MySQL and test the various functions it has to offer.