How to Install MySQL Server on Ubuntu 20.04 Tutorial (Step by Step)

How to Install MySQL Server on Ubuntu 20.04. In this tutorial we will introduce what MySQL is and move onto installation phase on Ubuntu 20.04. We will learn how to create a database and user in MySQL, create a table in MySQL and how to remove a table, database and user. Let’s get started. 

What is MySQL

MySQL is a free, open source  and one of the most popular relational database management systems. It is used to create, store and manipulate the data for many web based applications including data warehousing, e-commerce and logging applications. It is developed by Michael Widenius and is based on Structured Query Language (SQL). It is a simple but high performance database and is easier to set up and administer than larger systems. MySQL server  is the major component of the LAMP and LEMP stack, which is a software stack to deploy web applications and websites.

MySQL Features

  • Free and open source.
  • Dual password support.
  • JSON document validation.
  • Query language support.
  • Secure and portable.
  • Ease of use.
  • Support for large databases.
  • Quick and reliable.

Follow this post below and we will show you how to install MySQL Server on Ubuntu 20.04.

Install MySQL Server on Ubuntu 20.04

At the time of writing this tutorial, the latest version of MySQL is MySQL 8. By default, it is available in the Ubuntu 20.04 default repository. You can install it by just running the following command:

				
					apt-get install mysql-server -y
				
			

After installing the MySQL server, start the MySQL service and enable it to start at system reboot:

				
					systemctl start mysql
systemctl enable mysql
				
			

Now, check the status of the MySQL server using the following command:

				
					systemctl status mysql
				
			

Sample output:

				
					● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Thu 2021-11-04 07:13:07 UTC; 14s ago
   Main PID: 1560 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 2353)
     Memory: 354.3M
     CGroup: /system.slice/mysql.service
             └─1560 /usr/sbin/mysqld

Nov 04 07:13:06 ubuntu2004 systemd[1]: Starting MySQL Community Server...
Nov 04 07:13:07 ubuntu2004 systemd[1]: Started MySQL Community Server.

				
			

You can also verify the MySQL server version using the following command:

				
					mysql -V
				
			

You should see the MySQL version in the following output:

				
					mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))
				
			

To check the detailed information of the MySQL server package, run the following command:

				
					apt show mysql-server
				
			

Sample output:

				
					Package: mysql-server
Version: 8.0.27-0ubuntu0.20.04.1
Priority: optional
Section: database
Source: mysql-8.0
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 113 kB
Depends: mysql-server-8.0
Homepage: http://dev.mysql.com/
Task: lamp-server
Download-Size: 9,548 B
APT-Manual-Installed: yes

				
			

To check the MySQL server log for any error, run the following command:

				
					tail -f /var/log/mysql/error.log
				
			

Secure the MySQL Installation

By default, the MySQL server is not secured. So it is recommended to secure MySQL and set a root password. You can do it by running the following script:

				
					mysql_secure_installation
				
			

You will be asked to set a MySQL root password as shown below:

				
					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: 
Please set the password for root here.

New password: 

Re-enter new password: 

				
			

Set your MySQL root password and press Enter. You will be asked to remove the anonymous users:

				
					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
				
			

Press Y and hit Enter. You will be asked to disallow root login remotely:

				
					Success.


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.
				
			

Press Y and hit Enter. You will be asked to remove the test database:

				
					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
				
			

Press Y and hit Enter. You will be asked to reload the privileges tables:

				
					 - 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
				
			

Press Y and hit Enter to complete the script.

Next, log in to MySQL using a root user:

				
					mysql -u root -p
				
			

Once you are log in, you will get the following output:

				
					Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.27-0ubuntu0.20.04.1 (Ubuntu)

Copyright (c) 2000, 2021, 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> 
				
			

Now, check the MySQL status using the following command:

				
					mysql> STATUS;
				
			

Sample output:

				
					--------------
mysql  Ver 8.0.27-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

Connection id:		10
Current database:	
Current user:		root@localhost
SSL:			Not in use
Current pager:		stdout
Using outfile:		''
Using delimiter:	;
Server version:		8.0.27-0ubuntu0.20.04.1 (Ubuntu)
Protocol version:	10
Connection:		Localhost via UNIX socket
Server characterset:	utf8mb4
Db     characterset:	utf8mb4
Client characterset:	utf8mb4
Conn.  characterset:	utf8mb4
UNIX socket:		/var/run/mysqld/mysqld.sock
Binary data as:		Hexadecimal
Uptime:			3 min 25 sec

Threads: 2  Questions: 14  Slow queries: 0  Opens: 130  Flush tables: 3  Open tables: 49  Queries per second avg: 0.068
--------------
				
			

To exit from the MySQL shell, run the following command:

				
					mysql> EXIT;
				
			

Create MySql Database and User

MySQL provides a command line interface that allows you to create a database and user, and manage them easily. First, log in to the MySQL interface using the following command:

				
					mysql -u root -p
				
			

Once you are log in, create a database named testdb using the following command:

				
					mysql> CREATE DATABASE testdb;
				
			

Please verify all MySQL databases using the following command:

				
					mysql> SHOW DATABASES;
				
			

Sample output:

				
					+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdb             |
+--------------------+
				
			

Next, create a new MySQL user named testuser and set a password using the following command:

				
					mysql> CREATE USER 'testuser'@'localhost' IDENTIFIED BY 'securepassword';
				
			

If you want to grant specific privileges on testdb database, run:

				
					mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON testdb.* TO 'testuser'@'localhost';
				
			

To grant all privileges on testdb database, run:

				
					mysql> GRANT ALL PRIVILEGES ON testdb.* TO 'testuser'@'localhost';
				
			

To list all MySQL users, run:

				
					mysql> SELECT user,host FROM mysql.user;
				
			

Sample output:

				
					+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| testuser         | localhost |
+------------------+-----------+
				
			

To display all granted privileges, run:

				
					mysql> SHOW GRANTS for testuser@localhost;
				
			

Sample output:

				
					+--------------------------------------------------------------+
| Grants for testuser@localhost                                |
+--------------------------------------------------------------+
| GRANT USAGE ON *.* TO `testuser`@`localhost`                 |
| GRANT ALL PRIVILEGES ON `testdb`.* TO `testuser`@`localhost` |
+--------------------------------------------------------------+
				
			

Create a Table in MySQL

To create a table in MySQL, you will need to switch to the database first. To switch the database to testdb, run:

				
					mysql> USE testdb;
				
			

Next, create a new table named students, run:

				
					mysql> CREATE table students

(
students_id int auto_increment primary key,
students_first_name varchar(500) NOT null,
students_last_name varchar(500) NOT null,
students_emailID varchar(500),
students_class_ID int default 9
);
				
			

Please insert some data in to students table using the following command:

				
					mysql> INSERT INTO students (students_first_name, students_last_name, students_emailid, students_class_id) values ('Milan','Shah','milanshah@gmail.com','01');
mysql> INSERT INTO students (students_first_name, students_last_name, students_emailid, students_class_id) values ('Jay','Shah', 'jayshah@gmail.com', '01');
mysql> INSERT INTO students (students_first_name, students_last_name, students_emailid, students_class_id) values ('Raj','Shah','rajshah@gmail.com','01');
				
			

To display all data from the table, run:

				
					mysql> SELECT * FROM students;
				
			

You will get the following output:

				
					+-------------+---------------------+--------------------+---------------------+-------------------+
| students_id | students_first_name | students_last_name | students_emailID    | students_class_ID |
+-------------+---------------------+--------------------+---------------------+-------------------+
|           1 | Milan               | Shah               | milanshah@gmail.com |                 1 |
|           2 | Jay                 | Shah               | jayshah@gmail.com   |                 1 |
|           3 | Raj                 | Shah               | rajshah@gmail.com   |                 1 |
+-------------+---------------------+--------------------+---------------------+-------------------+
				
			

Remove a Table, Database and User in MySQL

In order to delete a table, database and user in MySQL, you can use the DROP statement.

To delete a table, run the following command:

				
					mysql> DROP TABLE students;
				
			

To delete a database, run the following command:

				
					mysql> DROP DATABASE testdb;
				
			

To delete a user, run the following command:

				
					mysql> DROP USER testuser@localhost;
				
			

Uninstall MySQL

If you want to remove the MySQL database server from your server, run the following command:

				
					apt-get remove mysql-server --purge
				
			

After removing the MySQL database, some other unwanted dependencies are still persisting in your system. You can remove them by running the following command:

				
					apt-get autoremove
				
			

Next, remove all package cache with the following command:

				
					apt-get clean 
				
			

Great work! You have followed all the steps and learned how to Install MySQL Server on Ubuntu 20.04.

How to Install MySQL Server on Ubuntu 20.04 Conclusion

In the above guide, we explained how to install MySQL server on Ubuntu 20.04. We also explained how to create a user, database and table in MySQL. I hope you have now enough knowledge to interact with the MySQL database.

Avatar for Hitesh Jethva
Hitesh Jethva

I am a fan of open source technology and have more than 10 years of experience working with Linux and Open Source technologies. I am one of the Linux technical writers for Cloud Infrastructure Services.

0 0 votes
Article Rating
Subscribe
Notify of
0 Comments
Most Voted
Newest Oldest
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x