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.
