MySQL is a mature open-source SQL-based relational database management system popularly used due to its higher speed and efficiency. It provides a stand-alone client from where users can directly interact with their databases using SQL. It was first introduced in 1995 and is currently owned by Oracle. MySQL is a significant part of the LAMP software stack, which means Linux, Apache (web server), MySQL, and PHP in short.
MySQL's attributes make it the best choice for data warehousing, e-commerce applications and web databases. It is used as a database engine for many web applications such as Drupal, Joomla, and WordPress. Many popular websites, including Facebook, Twitter, and YouTube, use MySQL. MySQL server source code is available on GitHub.
This guide will explain how to install MySQL in Ubuntu (20.04 LTS). There are ready-to-use MySQL binary packages available for various operating systems, such as MacOS X, Windows, and many Linux distributions. Two packages are available on Ubuntu, namely MySQL client and MySQL server software - MySQL client connects the MySQL server and the MySQL server software hosts several databases. We will cover the installation of both.
Installing MySQL on Ubuntu
As we will be covering MySQL client and MySQL server installation, let's first start with the MySQL client installation process.
- Installing MySQL Client on Ubuntu 20.04
Installing the MySQL client on the Ubuntu system enables the MySQL shell command. You can log in to remote MySQL servers using the command.
Step 1: Initialising installation
To start the installation, you first need to open a terminal using the command:
$ sudo apt install mysql-client
Step 2: Confirming MySQL client installation
You can confirm the MySQL client installation and see its version on your system with this command:
$ mysql –V
mysql Ver 8.0.19-0ubuntu4 for Linux on x86_64 ((Ubuntu))
Step 3: Connecting MySQL server
After the installation is successful, you will be able to connect to a remote MySQL server using the command:
$ mysql -u USERNAME -p PASSWORD -h HOST-OR-SERVER-IP
We are done with the MySQL client installation, let's move on to the MySQL server installation.
- Installing MySQL Server on Ubuntu 20.04
MySQL server hosts several MySQL databases on Ubuntu 20.04 system. With the MySQL server, you can access the database from the local machine or remote client as well, which are using the MySQL Client.
Step 1: Initialising installation
For installing MySQL Server, the following command is given in the terminal:
$ sudo apt install mysql-server
Step 2: Securing MySQL server
After the installation is completed, it is first needed to secure it using the following command:
$ sudo mysql_secure_installation
Step 3: Giving access
MySQL is accessible from the local PC by default. It is recommended to keep it the same way unless a remote user or an external web server connection wants to access it. Thus, it's necessary to change the MySQL configuration file line for allowing remote access. Use the below command to open the file:
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Now, change the bind-address line to 0.0.0 inplace of 127.0.0.1. Save the changes and close the file.
Step 4: Restarting MySQL
For the changes to reflect, as explained above, you need to restart MySQL. The following command does the task:
$ sudo systemctl restart mysql
After restarting, you can also enable MySQL to start up after the system reboots automatically using the command:
$ sudo systemctl enable mysql
Step 5: Listening to incoming connections
MySQL listens to port 3306 connections by default. You can make the MySQL service listen to incoming connections on all interfaces on 0.0.0.0 using this command:
$ ss -ltn
Step 6: Adding exception
It is important to ensure that your server's firewall is not blocking the incoming on port 3306 connections. You can do this by adding an exception in default Ubuntu's firewall with this command:
$ sudo ufw allow from any to any port 3306 proto tcp
Step 7: Logging in
Now that we have learned how to install, secure, start/restart the MySQL server on Ubuntu 20.04 LTS. Now, let's learn the commands used to log in as a root (admin) user:
mysql -u root -p
mysql -u USER -h host –p
To display the version and other information, use the command
STATUS;
- Creating a New MySQL Database
After the installation, creating a MySQL database to access the data for retrieving and modification.
- A new database 'wpblog' can be created using the command:
CREATE DATABASE wpblog;
- A new user name 'wpuser' for our database is created using the command:
CREATE USER 'wpuser'@'%' IDENTIFIED BY 'Your_ Password';
- Commands for granting permissions:
GRANT SELECT, INSERT, UPDATE, DELETE ON wpblog.* TO 'wpuser'@'%';
- You can also grant all the privileges as follows:
GRANT ALL PRIVILEGES ON wpblog.* TO 'wpuser'@'%';
- Command to know MySQL users and the permissions they have been granted:
SELECT user,host FROM mysql.user;
SHOW GRANTS for wpuser;
- You can test the user access using the commands:
mysql -u wpuser -p wpblog
mysql -u wpuser -h localhost -p wpblog
Here,
Ø -u wpblog: Login user
Ø -h localhost: For connect to localhost
Ø -p : Password prompt
Ø wpblog: For connecting to database 'wpblog'
Conclusion
At the end of this important installation, we hope that we could give you a comprehensive knowledge of the MySQL installation, both server and client, with the procedure given above. We have also covered the MySQL database's creation and installation, which is the primary function of this RDBMS on the Ubuntu 20.04 LTS server.