How to set up MySQL on Ubuntu 20.04 server
Updated: Feb 28, 2022
MySQL is a widely used relational database management system (RDBMS).
It is free and open-source. MySQL is ideal for both small and large applications
In this guide, we will go over how to set up MySQL on Ubuntu 20.04 server.
A server with ubuntu installed.
Step 1: Installing MySQL
You can install MySQL using the apt package repository.
You can update the package index of your server by typing:
$ sudo apt update
Then install the mysql-server package by typing:
$ sudo apt install mysql-server
This will install MySQL but will not ask you to set a password or make any configuration changes.
To make MySQL secure you will have to run a security script with sudo:
$ sudo mysql_secure_installation
This script will prompt you with some boolean choices and according to your responses, MySQL will make necessary changes to your installation’s security options.
Once the script completes, your MySQL installation will be secured.
Step 2: Creating a new MySQL user and Granting Privileges
MySQL installation will by default create a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over the server. Because of this, it is best to avoid using this root user account.
You can create a new user and grant required permissions to it.
To login into MySQL prompt you can type:
$ sudo mysql -u root -p
This command will ask for the password of the root user.
You will have to enter the password that you set up during the mysql_secure_installation script.
Once you are logged in, you can create a new user with CREATE USER statement.
Syntax: CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;
The host is the hostname from which this user will connect. If you only plan to access this user locally from your Ubuntu server you can specify localhost.
mysql> CREATE USER 'test_user'@'localhost' IDENTIFIED BY 'testpass@123';
This will create a user with the username test_user and have the password testpass@123.
After creating your new user, you can grant them the appropriate privileges.
Syntax: GRANT PRIVILEGE ON database.table TO ‘username’@’host’;
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION
This will grant test_user CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT
Privileges on all tables in all databases with the ability to grant privileges to other users.
You can also grant all privileges by typing:
mysql>GRANT ALL PRIVILEGES ON *.* TO 'test_user'@'localhost' WITH GRANT OPTION;
However, this is not advisable.
You can exit the MySQL client by typing:
To log in as new test_user you can type:
$ mysql -u test_user -p
MySQL client will prompt you for password and then you will be logged in.
Step 3: Testing MySQL
MySQL should have started running automatically after installation. To test this we can check its status by typing:
$ systemctl status mysql.service
If MySQL isn’t running, you can start it manually with the command:
$ sudo systemctl start mysql
You now have a basic MySQL setup installed on your ubuntu server.
If you’d like to learn how to set up PostgreSQL in ubuntu 20.04, we encourage you to check out the following guide: