Table of Contents
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)
Also Read: Easy Steps to Backup and Restore MariaDB Database on RHEL/CentOS 7/8
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.
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.
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.
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:
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