A guide to installing MySql in ubuntu 20.04 LTS with tips and some workarounds to fix some installation issues.
Many applications and systems have the functionality to read, modify, add and delete data.
These behave like the blood flowing through the veins of an organism.
Without data, it is difficult for a program to improve our daily lives.
Before using them, it is important for a software to store our data in an organized and structured way so that it can be retrieved more easily for our needs.
To perform these actions, we need a type of specialized system called a database management system (DBMS) or database manager.
In this article we will apprendre how to install one of the database management software that I like to use to start developing a project.
This is MySql.
How to install Mysql in ubuntu 20.04 lts
Installation of Mysql database manager is very easy on linux.
The difficulty that I encountered and that pushed me to write this mini tutorial is at the level of the system configuration.
Installation is easy, but configuration is tricky.
Let's start with the easiest point which is how to install mysql .
Installing MySQL
Installation is like any other software in ubuntu. In the script that will follow we will install mysql-server after updating our package manager:
sudo apt update
sudo apt install mysql-server
The installation of our database ends here. The most delicate actions take place in the following point.
How to configure Mysql in ubuntu
After learning how to install mysql in ubuntu, now is the time to tackle the trickier part of this tutorial. This is the configuration of your database.
The configuration of mysql takes place by executing a specific command. Everything will happen in the terminal with a single command. Start the command:
sudo mysql_secure_installation
1- Creation of the administrator password
When it is launched, you will be asked a series of questionnaires to configure your mysql database server. The most important is setting the password for your root account. Your terminal should look like this:
Securing the MySQL server deployment. Connecting to MySQL using a blank password. VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. It checks the strength of password and allows the users to set only those passwords which are secure enough. Would you like to setup VALIDATE PASSWORD component? Press y | Y for Yes, any other key for No: Y There are three levels of password validation policy: LOW Length> = 8 MEDIUM Length> = 8, numeric, mixed case, and special characters STRONG Length> = 8, numeric, mixed case, special characters and dictionary file Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
Before creating your password you will need to choose a password validation font. Depending on your needs, it is recommended to choose the last font (ie 2) if you want to use Mysql in production.
Once your choice is validated, you will need to create your administrator password for the database manager. You should see the following content in your terminal:
Please set the password for root here. New password: Re-enter new password:
After creating the password we will move on to the next section which is the creation of a user account.
2- Creation of a user account
I like to create database access accounts for every app and website project I take on. This is also a recommended practice for security reasons.
We can use mysql administrator account to connect our applications to their database.
The problem is that the administrator account has full privileges on all present and future databases. This production configuration created a flaw very dangerous for the database management system.
a - Why you should not use an administrator account
If one of the applications using administrator access is attacked by a hacker, the latter could have control over all the databases of the other applications and services.
It will even be able to change the Mysql database manager administrator password.
It is at this point that it is important to create a user account for each database of a digital application or service.
b - Creation of a user account
To start run the following commands in the terminal:
sudo mysql
To create a user account, just use the following command template:
mysql> CREATE USER 'username' @ 'host' IDENTIFIED BY 'password';
For example by using the account name “test” and the password “test @ pass” we can execute the following command to create an account.
mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'test @ pass';
It's not over, you have to grant appropriate privileges to the account you just created. Generally I grant all privileges on a single database without the ability to create another user account.
This way, this account will only have control over the database associated with it.
The privilege agreement template with Mysql is:
mysql> GRANT PRIVILEGE ON database.table TO 'username' @ 'host';
Usually several privileges are granted instead of just one depending on the needs. In our case we can apply the following example:
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on BASE_DE_DONNEES TO 'test' @ 'localhost' WITH GRANT OPTION;
In this example, we have granted the “CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD” privileges on the “DATABASE” database to the “test@localhost” account.
We have also given the possibility to this account to grant its privileges to sub-accounts.
Other privilege options can be found at the mysql site.
3 - Connection and test
Now that we have finished with the configuration of the administrator account and the creation of a test account, we will now test the connection to the mysql database. To begin with, we will exit from the Mysql cli command.
mysql> FLUSH PRIVILEGES;
mysql> exit;
Now we will try to connect to our test account:
mysql -u test -p
The password to type if you used our example commands should be test @ pass .
The test may fail with a type error mbind: Operation not permitted - mysql . Please feel free to follow the link to understand and resolve the issue.
How to install Mysql in Docker
These last sections may seem vague but I promise to update it as needed.
To develop applications we tend to install many tools and prerequisites over time.
The work computer or laptop becomes overloaded with tools that we used and forgot to uninstall.
We end up with an overloaded computer at startup (even under linux) with a lot of protocol and port occupied unnecessarily.
To solve this type of problem I recently decided to adopt a type of virtualization software that solves the problem mentioned above very well so far.
It is a software virtualization system which is Docker.
With docker you can easily install MySql without going through all the configurations mentioned above. We just need to download the official Mysql image and start it in our container.
This will be the subject of a separate section but for the moment I am taking a little break.
Installation of MySql clients – Optional
Now that you've installed your MySQL DBMS, your work is probably not done yet.
You will find that a lot of the work of creating tables, schema or query execution will be done by commands in your terminal. Over time, it can quickly become tedious.
To have a broader view of the contents of your database management system, it is important to install a type of application called in computer jargon, client.
These clients will allow you to go straight to the point in database management by saving you many steps of typing commands to obtain information about your databases.
I use as my favorite client phpmyadmin web app et mysql workbench as heavy software .
Conclusion
This article is a guide in the making to learn how to install Mysql in a linux system like ubuntu.
Although this tutorial contains many details from my personal experience with this system, you may encounter installation problems that I am unaware of.
