You’re the system-slash-database admin and need to build a database management system (DBMS) for a new project. If you choose to install MySQL on Ubuntu, then you’re in luck. MySQL is fast, reliable, versatile, and free — a good choice!
This tutorial will walk you through installing a MySQL server on Ubuntu, make it secure, and create a dedicated user with limited permissions to avoid exposing the root account. By the end, you’ll have built a MySQL server that you can use in your project to host databases!
Prerequisites
This tutorial will be a hands-on demonstration. If you’d like to follow along, be sure you have an Ubuntu Server 20.04 or later with a non-root user account with sudo privileges.
Install MySQL on Ubuntu (From the Repository)
The Ubuntu APT package repository includes the MySQL package by default, making it convenient to install MySQL Ubuntu in a single command.
NOTE: The default Ubuntu APT repository may not always have the latest MySQL version.
To install MySQL Ubuntu, proceed as follows.
1. Open your preferred SSH client and log in to your Ubuntu server.
2. Ensure that your package list is recent by running the below command. This command updates the list of available packages in the APT package repository.
sudo apt update -y3. Now, install the mysql-server package by executing the following command. Wait for the installation to finish.
sudo apt install mysql-server -yAs you can see below, the latest MySQL version from the APT repository is 8.0.28.
4. Lastly, run the below command to check and confirm whether the mysql service is now running.
sudo systemctl status mysql
Installing MySQL from the Official Repository
If you don’t like waiting for your Linux distro repository to catch up, downloading and installing the DEB package is the best way to get the latest features and bug fixes.
The MySQL developers provide their software repository to install the latest version of MySQL, but Ubuntu doesn’t know where to find that repository (yet). Follow the below steps to download and install MySQL from the official repository.
1. Open the web browser on your computer and navigate to the MySQL APT Repository page.
2. Once on the download page, click the Download button, as you can see below.
3. On the next page, right-click the No thanks, just start my download link and click Copy link address.
4. Go back to your SSH terminal session and download the MySQL package to the /tmp directory home directory by running the curl command below. Make sure to change the URL value after the -OL parameter with the URL you copied in the previous step.
cd /tmp && curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.22-1_all.deb
5. After downloading the package, run the dpkg command below to install the MySQL package file.
sudo dpkg -i mysql-apt-config*6. On the configuration page, that shows up asking Which MySQL product do you wish to configure? – Press the down arrow until you select Ok, and press Enter.
7. Now, run the below command to update your package list so that APT can find the new MySQL packages.
sudo apt update -y8. Now that APT knows where to find the latest MySQL version, run the below command to install the mysql-server package.
sudo apt install mysql-server -y9. Type in the new strong password for the MySQL database root account on the configuration prompt and press Enter.
Re-enter the password and press Enter to confirm.
10. Select Use Strong Password Encryption (RECOMMENDED), and press Enter on the default authentication plugin selection.
Wait for the installation to complete. The screenshot below shows the MySQL server version you installed. In this example, the latest version is 8.0.28.
11. Run the systemctl command below to verify that the MySQL service is running.
sudo systemctl status mysqlYou will see the following output. This output shows that the service is active (running) and enabled.
Securing Your MySQL Server
The MySQL server default installation is not secured out-of-the-box, and you need to perform some additional steps to secure your MySQL server. Luckily, the MySQL developers have created a configuration script that does most of the work for you.
The security script is called mysql_secure_installation, which you can find under the /usr/bin directory. The script modifies less safe defaults, such as remote root logins and sample users.
1. Run the following command below to start the security script on the terminal. This script will ask you several Yes/No questions to configure the MySQL server security.
sudo mysql_secure_installationIf you installed MySQL from the default Ubuntu repository, you’d see the following message saying that you’re connecting to MySQL with a blank password! Don’t worry; you’ll be fixing that issue in the succeeding steps.
2. At the next prompt asking to set up the VALIDATE PASSWORD COMPONENT, type y and press Enter. This component validates whether MySQL user passwords comply with the complexity requirement.
3. Next, select the password validation policy level to implement. The options are:
LOW– requires a password with eight characters minimum.
MEDIUM– requires a password with eight or more characters, including numbers, mixed case, and special characters.
STRONG– same asMEDIUMbut checks the password against a dictionary file for validation.
In this example, type 2 to select STRONG and press Enter.
MySQL does not have a default dictionary file. As such, you’ll need to create a dictionary file and modify the validate_password_dictionary_file system variable value to the dictionary file path. Creating and configuring the password dictionary file is not in the scope of this tutorial.
4. To ensure that the root password complies with the password complexity requirements, set the root password again and re-enter. The script will then show you the password strength score. Press y to confirm saving the password and press Enter.
5. Next, the script asks if it should remove the anonymous user that the MySQL installation created. This user is safe to remove, so type y and press Enter to remove the anonymous user.
6. To further lock down MySQL, type y and press Enter to disallow remote login for root. After this step, the root account can only log in to MySQL from the server and not from remote clients.
7. Next, remove the test database. This database is only for testing and should not go into production. To remove (drop) the test database, type y, and press Enter.
8. Finally, reload the privilege tables for all the changes to take effect immediately. Type y and press Enter to reload.
You have now successfully secured your MySQL server.
Creating a New MySQL User and Granting Limited Permissions
At this point, there’s only one user account in your MySQL server, which is the root user. As the name implies, the root account has full access to the MySQL server and every resource within.
Constantly using the root user account is not a best practice and can be a security risk. You should reserve the root account for administrative tasks that need its permissions. Otherwise, you should be using an account with limited privileges, and below are the instructions for creating one.
1. Log in to MySQL as the root user on the terminal by executing the below command.
sudo mysql
2. Next, run the CREATE USER statement to create a new user. The below example creates a new user account named ata which can only log in from the localhost. Make sure to provide a strong password that complies with the password policy you configured earlier.
CREATE USER 'ata'@'localhost' IDENTIFIED BY 'password';Suppose you create a user who can log in from a specific remote computer, run the below command instead. This command replaces localhost with the remote computer’s ip_address.
CREATE USER 'ata'@'ip_address' IDENTIFIED BY 'password';To create a user that can log in to MySQL from anywhere, replace the localhost or ip_address with % instead.
CREATE USER 'ata'@'%' IDENTIFIED BY 'password';Always end your MySQL statements with a semi-colon (;). Also, enclosing the values in single quotes (‘) helps prevent SQL syntax errors.
The result below shows the successful user creation
3. Now that you’ve created the user run the GRANT statement next to grant permissions. There are many MySQL privileges that you can assign to the user. But in this example, the command will grant the following privileges to the user 'ata'@'localhost’ to all (*.*) databases and tables.
ALTER– gives the user permission to alter the structures of tables.
SELECT– gives the user access to theSELECTstatement, which allows reading the database contents.
CREATE– gives the user permission to create databases and tables.
INSERT– allows the user to insert rows into the table.
DELETE– allows the user to delete rows from the table.
UPDATE– the user can update rows on the table.
REFERENCES– enable foreign key creation.
RELOAD– the user can run theFLUSHstatement and commands.
GRANT CREATE, ALTER, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO ‘ata'@'localhost’;Refer to the Privileges Provided by MySQL documentation for a complete list of all privileges. You may add, remove, combine permissions as you wish.
The result below shows granting permissions was successful.
4. Next, run the below command to reload the privilege tables. Doing so will ensure that all the changes you have made so far immediately take effect.
FLUSH PRIVILEGES;5. Lastly, log out from MySQL.
exitTesting Your MySQL Server
So you’ve installed and secured your MySQL server and configured a new limited user account. Now’s the time to test whether everything works as you expected.
1. Log in to MySQL as the ata user. This way, you can test whether this limited user account works as planned. The -u flag specifies the account to log in, while the -p flag invokes the password prompt.
If you do not append the -p flag, the mysql account will try to log in without a password — which will fail since the login account has a password.
mysql -u ata -p
2. Next, test the user privilege by running the below CREATE statement to create a new database named ata_db.
CREATE DATABASE ata_db;You should see the Query OK message, indicating a successful database creation.
3. Run the show databases; statement to see a list of all the databases that are available to use.
show databases;You should see the ata_db database you created earlier, as shown below.
Conclusion
In this article, you have learned how to install the MySQL Ubuntu server and configure the server security settings. You also learned how to create a new user account with limited privileges and test the new user’s permissions by creating a new database.
At this point, you should have a fully functional MySQL Ubuntu server that you can use for your development and production needs. And if you prefer managing MySQL with a GUI, perhaps you could install a web front-end next?
