Cyberithub

How to Install MySQL on Ubuntu 20.04 LTS (Focal Fossa)

Advertisements

In this article, I will take you through the steps to install MySQL on Ubuntu 20.04 LTS (Focal Fossa). MySQL is a very popular, most widely used free and open-source relational database management system. It is well suited for both small and large scale applications usage. MySQL is currently available for diverse number of platforms including Windows, Linux and macOS. It comes in both community as well as in enterprise edition. The community version is completely free to use. Here we will see the installation of this version only on Ubuntu 20.04 LTS Server. More on official website.

 

How to Install MySQL on Ubuntu 20.04 LTS (Focal Fossa)

How to Install MySQL on Ubuntu 20.04 LTS (Focal Fossa)

Also Read: Easy Steps to Backup and Restore MariaDB Database on RHEL/CentOS 7/8

Advertisements

Step 1: Prerequisites

a) You should have a running Ubuntu 20.04 LTS Server.

b) You should have sudo or root access to run privileged commands.

Advertisements

c) You should have apt or apt-get utility available in your Server.

 

Step 2: Update Your Server

It is always a good practice to keep your installed packages updated and upgraded to the latest version by using sudo apt update && sudo apt upgrade command as shown below.

Advertisements
cyberithub@ubuntu:~$ sudo apt update && sudo apt upgrade
[sudo] password for cyberithub:
Hit:1 https://brave-browser-apt-release.s3.brave.com stable InRelease
Hit:2 https://download.docker.com/linux/ubuntu focal InRelease
Hit:3 https://dl.winehq.org/wine-builds/ubuntu focal InRelease
Hit:4 https://dl.google.com/linux/chrome/deb stable InRelease
Hit:5 http://ppa.launchpad.net/ubuntu-toolchain-r/test/ubuntu focal InRelease
Hit:6 http://deb.anydesk.com all InRelease
Hit:7 https://repo.nordvpn.com//deb/nordvpn/debian stable InRelease
Ign:8 https://storage.googleapis.com/download.dartlang.org/linux/debian stable InRelease
Get:9 http://security.ubuntu.com/ubuntu focal-security InRelease [114 kB]
Hit:10 http://in.archive.ubuntu.com/ubuntu focal InRelease
Hit:11 http://ppa.launchpad.net/wireshark-dev/stable/ubuntu focal InRelease
Hit:12 https://storage.googleapis.com/download.dartlang.org/linux/debian stable Release
Get:13 http://in.archive.ubuntu.com/ubuntu focal-updates InRelease [114 kB]
Hit:14 https://apt.boltops.com stable InRelease
...................................................

 

Step 3: Install MySQL Server

You can install MySQL Server community version from default Ubuntu repo by using sudo apt install mysql-server command as shown below. This will install the mysql server package along with all its dependencies.

cyberithub@ubuntu:~$ sudo apt install mysql-server
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following packages were automatically installed and are no longer required:
libvkd3d-shader1 libvkd3d-shader1:i386 libvkd3d1 libvkd3d1:i386 vkd3d-compiler
Use 'sudo apt autoremove' to remove them.
The following additional packages will be installed:
libaio1 libevent-core-2.1-7 libevent-pthreads-2.1-7 libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
mysql-client-core-8.0 mysql-server-8.0 mysql-server-core-8.0
Suggested packages:
libipc-sharedcache-perl mailx tinyca
The following NEW packages will be installed:
libaio1 libevent-core-2.1-7 libevent-pthreads-2.1-7 libhtml-template-perl libmecab2 mecab-ipadic mecab-ipadic-utf8 mecab-utils mysql-client-8.0
mysql-client-core-8.0 mysql-server mysql-server-8.0 mysql-server-core-8.0
0 upgraded, 13 newly installed, 0 to remove and 0 not upgraded.
Need to get 36.1 MB of archives.
After this operation, 317 MB of additional disk space will be used.
Do you want to continue? [Y/n] Y
...........................................................

 

Step 4: Check MySQL Service

After successful installation, you can check the status of mysql service by using systemctl status mysql command as shown below. If it shows not active, then you can start or restart the service by using sudo systemctl start mysql or sudo systemctl restart mysql command as shown below.

Advertisements
cyberithub@ubuntu:~$ systemctl status mysql
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2022-11-15 21:45:35 IST; 20s ago
Main PID: 52250 (mysqld)
Status: "Server is operational"
Tasks: 39 (limit: 2287)
Memory: 361.9M
CGroup: /system.slice/mysql.service
└─52250 /usr/sbin/mysqld

Nov 15 21:45:34 ubuntu systemd[1]: Starting MySQL Community Server...
Nov 15 21:45:35 ubuntu systemd[1]: Started MySQL Community Server.

 

Step 5: Connect MySQL

Now that service is running, you can connect the MySQL prompt by using sudo mysql command as shown below.

cyberithub@ubuntu:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.31-0ubuntu0.20.04.1 (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>

 

Step 6: Check Databases

To check all MySQL default databases, you need to use show databases query as shown below.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.11 sec)

 

Step 7: Using MySQL DB

To perform operation on some specific database, you need to first run use <db_name> to change to that database and then perform your operation by running mysql queries. For example to use mysql database, you need to do use mysql query as shown below.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Then to check all the tables in mysql database, you need to use show tables query as shown below.

mysql> show tables;
+------------------------------------------------------+
| Tables_in_mysql                                      |
+------------------------------------------------------+
| columns_priv                                         |
| component                                            |
| db                                                   |
| default_roles                                        |
| engine_cost                                          |
| func                                                 |
| general_log                                          |
| global_grants                                        |
| gtid_executed                                        |
| help_category                                        |
| help_keyword                                         |
| help_relation                                        |
| help_topic                                           |
| innodb_index_stats                                   |
| innodb_table_stats                                   |
| password_history                                     |
| plugin                                               |
| procs_priv                                           |
| proxies_priv                                         |
| replication_asynchronous_connection_failover         |
| replication_asynchronous_connection_failover_managed |
| replication_group_configuration_version              |
| replication_group_member_actions                     |
| role_edges                                           |
| server_cost                                          |
| servers                                              |
| slave_master_info                                    |
| slave_relay_log_info                                 |
| slave_worker_info                                    |
| slow_log                                             |
| tables_priv                                          |
| time_zone                                            |
| time_zone_leap_second                                |
| time_zone_name                                       |
| time_zone_transition                                 |
| time_zone_transition_type                            |
| user                                                 |
+------------------------------------------------------+
37 rows in set (0.00 sec)

 

Step 8: Setting Root User Login

Before setting root password for first time login, it is important to check if the root user is currently set to auth_socker plugin. By default, root user uses this plugin so if you have not set the root user to use the mysql_native_password plugin then setting root password will not help if you are login it for the first time. So it is important to set the root user to use mysql_native_password plugin first and then set the password for login. To do that, you need to first login to MySQL using sudo mysql command as shown below.

NOTE:

Please note that if you are using mysql_secure_installation script to set the root password then there is a chance that you might end up with error and it will further take you in endless loop which can only be ended with the close of the terminal.
cyberithub@ubuntu:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 8.0.31-0ubuntu0.20.04.1 (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>

Then change the DB to MySQL using use mysql query as shown below.

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Now update the root user plugin to mysql_native_password using below update query.

mysql> UPDATE user SET plugin='mysql_native_password' WHERE User='root';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0

To make all the database changes visible you need to run flush privileges query as shown below.

mysql> flush privileges;
Query OK, 0 rows affected (0.02 sec)

Lastly you need to exit the prompt.

mysql> exit
Bye

Once the changes are done, restart the mysql service by using sudo systemctl restart mysql command as shown below.

cyberithub@ubuntu:~$ sudo systemctl restart mysql

Now again login back to MySQL DB using sudo mysql command as shown below.

cyberithub@ubuntu:~$ sudo mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.31-0ubuntu0.20.04.1 (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>

Here you need to alter the root user password and set it to World@123$ as you can see below. Similarly, you can also set your user's password.

mysql> alter user 'root'@'localhost' identified by 'World@123$';
Query OK, 0 rows affected (0.06 sec)

Again, to make all the database changes visible you need to run flush privileges query as shown below.

mysql> flush privileges;
Query OK, 0 rows affected (0.04 sec)

Finally, exit the prompt.

mysql> exit
Bye

Now try to login to MySQL DB using root user as shown below. You will notice that you will be able to login to MySQL DB.

cyberithub@ubuntu:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.31-0ubuntu0.20.04.1 (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>

 

Step 9: Creating a User

To create a user, you can use create user '<user_name>'@'<host_name>' identified by <password> query syntax. For example, here we are creating a user cyberithub by using create user 'cyberithub'@'localhost' identified by 'Cyber@123$' query as shown below.

mysql> create user 'cyberithub'@'localhost' identified by 'Cyber@123$';
Query OK, 0 rows affected (0.05 sec)

 

Step 10: Grant Privileges

You can grant all the privileges to user cyberithub using below GRANT query.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'cyberithub'@'localhost' WITH GRANT OPTION;
Query OK, 0 rows affected (0.04 sec)

Then to make all the database changes visible, run flush privileges query as shown below.

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

And, exit the prompt.

mysql> exit
Bye

Leave a Comment