MySQL is the most popular open-source database server in the world. It is simple and very fast, plus you can easily install it on Ubuntu Linux.
Despite its simplicity, MySQL is a very powerful relational database management system. It is highly scalable as well, and this makes it a popular choice in organizations of all sizes across the world.
You should note that this tutorial works for Ubuntu 18.04 and 20.04 installations and on both the server and desktop versions. Of course, you can always use the Ubuntu Software Center to search for and install MySQL, but it is better this way.
MySQL Installation Steps
Follow these steps to install MySQL on Ubuntu
1. Check your Software Versions
You need to know what you are working with, so it is important that you first check your software versions. If you are installing on Ubuntu 18.04, then keep in mind that it comes with MySQL version 5.7.
For Ubuntu 20.04 installations, you’ll be dealing with MySQL version 8. Both versions are similar to install and use, but version 8.0 offers more speed, security, and forward compatibility.
If you are on Ubuntu 18.04 and wish to try out MySQL v. 8, the safest way is to upgrade your installation to Ubuntu 20.04. On the other hand, you could directly download MySQL’s repository files for version 8 and try installing it.
You can get the latest Debian package at the apt repository and then use curl or wget to download it for installation. See an example below:
wget -c https://dev.mysql.com/get/mysql-apt-config_0.8.11-1_all.deb
Install the repository by typing
sudo dpkg -i mysql-apt-config_0.8.16-1_all.deb
You will be asked to select a release from a dialog box. Choose and continue. After the configuration, you will need to update your Ubuntu system before beginning the MySQL installation.
2. Update the System
The recommendation is to always update your Ubuntu repositories before installing any new software. This helps to make sure that you are getting all the necessary updates to avoid breaking your system.
To make sure your system is up to date before starting the installation, call up a terminal using Ctrl + Alt + T and enter the command below:
sudo apt-get update
Sudo will ask for your admin password, which you will need to provide before continuing. Then wait a moment while apt-get refreshes your system’s repository cache. This is an important step for installing both versions 5.7 and 8 on both Ubuntu 18.04 and 20.04 versions.
3. Install MySQL Server
Once this update is over, it is now time to install the MySQL server package. You do it using the following command:
sudo apt-get install mysql-server
Apt-get will install all the necessary files, including the database common files and a shell client to administer the database.
Keep in mind that you need to enter a root password during installation. This password is strictly for the database and has nothing to do with your Ubuntu box. So feel free to use any password.
You can also leave the root-password field blank during installation. This allows you to set a password later, during configuration. In any case, make sure you remember the password!
4. Test the Installation
MySQL server gets started automatically once the installation is over. So, to make sure the installation is a success, you can issue a few commands to the server and confirm that it responds. Here’s an example:
sudo systemctl status mysql
This command checks the status of the server and prints basic information such as its status, PID (Process ID), and memory usage. Once you have a reply from your MySQL installation, this shows that it installed correctly, and it is now time to configure it.
Other commands that make it possible to stop, start, and restart the server, respectively, include:
sudo systemctl stop mysql
sudo systemctl start mysql
sudo systemctl restart mysql
5. Configuring the Server
Once you confirm that the installation is a success, you’ll need to secure it to prevent security issues down the line. Luckily for us, MySQL includes a tool to do that. All you need to do is enter the command below:
This tool includes a series of questions to aid the setup, as well as a guide for password strength. In a nutshell, you need 8 characters at least for a Low-strength password. You can also go for a Medium or Strong password by following the plugin’s recommendations.
Further asked questions include if you want to remove the anonymous user, restrict root’s access to the machine, or remove the test database?. It is best to answer ‘yes’ to all these questions.
6. Add Users
Finally, it is time to set up the database server to your needs, such as adding users to access it through programs like phpMyAdmin and other environments. And unless you know what you are doing, it is recommended to leave the root@localhost password alone.
There are many access levels on MySQL, so it is left for you to decide which one to create. You can give a user global access to all parts of the server, or you can give him access to only a database, or even allow only specific operations on a database or table.
For instance, you could create three new users on your new server. One with global access, one with limited access to the customers’ database, and one with access to only customers’ phone numbers on the server with the following commands:
mysql> CREATE USER ‘admin’@’SkyBank.org’ IDENTIFIED BY ‘password’;
mysql> GRANT ALL PRIVILEGES ON * . * TO ‘admin’@’skybank.org’;
mysql> CREATE USER ‘bob’@’SkyBank.org’ IDENTIFIED BY ‘password’;
mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP ON customers.* TO ‘bob’@’skybank.org’;
mysql> CREATE USER ‘vera’@’SkyBank.org’ IDENTIFIED BY ‘password’;
mysql> GRANT SELECT,INSERT,UPDATE ON customers.telephone TO ‘vera’@’skybank.org’;
mysql> FLUSH PRIVILEGES;
The last command reloads the user privileges on the server to update it according to your new rules.
Conclusion – A Final Test
After setting up your database users and flushing the privileges, it is often good advice to try and log in as the new user you created, just to make sure everything is working beautifully.
Logging in to the shell is simple, with:
mysql -u user -p
Then you will receive the prompt to enter your password. You can try creating a database or a table and populate them, then delete rows, drop tables, and so on.