Photo by Rubaitul Azad on Unsplash
How to Set up a MySQL Server on Ubuntu 20.04
4 min read
MySQL, a Relational Database Management System (RDBMS), is one of the four open-source building blocks (LAMP) used to set up most web applications. It makes use of the Structured Query Language to manipulate, process, and organise data into useful information.
In this article, I will be highlighting steps to install and set up a MySQL server on a Linux Virtual Machine (VM), an Ubuntu 20.04 droplet on Digital Ocean.
To carry out this task, you should have set up an Ubuntu 20.04 machine with a
root user account or a user with
Setting up the MySQL Server
1. Updating Cache
Update the packages repository with the following command:
sudo apt update && sudo apt upgrade -y
2. Installing MySQL
Install the MySQL server with:
sudo apt install mysql-server -y
You can confirm the successful installation of the server with the following commands:
mysql --version OR sudo systemctl status mysql
You will have an image as shown below:
3. Securing MySQL Installation:
After installing the MySQL server, the default user created is the
root and this user gains access to the server through the
auth_socket authentication plugin. This method of authentication does not require a password.
To set up the server for more secure and effective usage, you would have to run the Database Management System's (DBMS) security script to change some default less secure options. However, to successfully carry out the
MySQL_secure_installation step, you will have to log into the server, set a password for the root user, and change the default authentication method from
mysql_native_password with the following commands:
sudo mysql ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'mynewpassword'; exit
sudo mysql will take you into the mysql interface and
b. You need to change
mynewpassword in the code above into a password that you can easily remember.
exit command takes you out of the MySQL interface and back to the shell.
At this point, logging into the server with
sudo mysql will give you an error.
Next is to secure the MySQL server with the command:
You will be asked to enter the password you set for the root user and if you would like to change it. Pick
y to answer
yes to the series of other questions you will be asked till you get
All done!. These steps help to secure the server by removing anonymous users, removing access to test databases, and disallowing root login remotely, as shown below:
After you have successfully secured the server, log back into the server with the password you created earlier whenever you are prompted for it. This step will help to revert to the default authentication method:
mysql -u root -p ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket; exit
Here, you can now gain access to the MySQL server once again with
Creating Database, User with
sudo privileges, and their Password.
With the following commands you can add a Database and a User to your setup:
sudo mysql CREATE DATABASE newdatabasename; CREATE USER 'username'@'localhost' IDENTIFIED BY 'setapassword'; GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' WITH GRANT OPTION; FLUSH PRIVILEGES; EXIT
Note that you are to replace
setapassword with your preferred names.
To log into the server using the newly created user, you will type:
mysql -u username -p
And when you are prompted for the password, you will input the password that you set when creating the user.
Setting up the MySQL server may seem like a bit of a difficult task, especially when trying to run the
mysql_secure_installation step. Without first changing the default root authentication method and setting a password for the user, you may end up in a recursive loop whereby the only way out will be to close the terminal.
It is thereby essential that you carry out the following steps highlighted above to have a seamless installation and use of the MySQL server.
For more helpful information, visit the links below: