MySQL is one of the most popular and open-source database management systems. It was released in 2016 with an open-source license. If you are a Linux user or Admin and want to use MySQL with Linux then it is a secure and stable operating system which is also open source and is used by a number of organizations. MySQL is commonly installed as part of LAMP stack i.e Linux, Apache, MySQL, PHP/Perl/Python and is used to manage data.
Today, here in this post we will discuss the installation steps of MySQL on Ubuntu Server 18.04 that is a version of the Linux operating system itself.
There are two ways to install MySQL on Ubuntu, as per the first method the user or Ubuntu admin can simply update the package index and install MySQL-server package. After this installation the user can run the security script that is given below:
- $ sudo apt update
- $ sudo apt install MySQL-server
- $ sudo mysql_secure_installation
If you have already installed the older version of MySQL than you can upgrade it to the newer version i.e. MySQL 5.7, else you can refer to this article for a fresh installation.
As you are going to install MySQL on Ubuntu server, so as a prerequisite, firstly you will have to set up Ubuntu 18.04 server, including non-root user with a firewall and sudo privileges.
Step 1 – MySQL Installation
With Ubuntu 18.04 Server the latest MySQL version is available in the APT package repository by default. It can be either MySQL5.7 or the latest one.
In order to install MySQL, you will have to update the package index on a server with apt. For this you can use the following command:
$ sudo apt update
Then you can install the default package with the following command:
$ sudo apt install MySQL-server
This way MySQL will be installed, but you will not be prompted to set the password or to make any change in the configuration. As by doing this your MySQL may become insecure, we would discuss this in detail later in the article.
Step 2: MySQL Configuration
For the fresh installation of MySQL, you may be required to run the included security script. In this way, some of the less secure default options like sample users or remote root logins may get changed. In the earlier versions of MySQL, the user must initialize the data directory manually, while in the latest versions they get updated automatically.
Now at this point, you can run the security script through the following code:
$ sudo mysql_secure_installation
During this installation, you will be prompted for a series of options through which you can make some changes to your MySQL configuration. Here you may be asked for password validation through a plugin, that can test the strength of the MySQL password. In the next step, you will be asked to set the password for root. Here just enter the password of your choice and confirm it.
After that, you can press Y to continue with the rest of the default settings. After this step, some of the test database, anonymous users, remote root logins and load will be removed and some of the new rules will be set to respect the immediate MySQL changes.
Step 3: To Adjust User Privilege and Authentication
In Ubuntu Server, the root login can check the user authenticity through auth_socket plugin by default rather than by using a password. This way they can get greater security and usability but at the same time, it can also complicate some of the things like authenticating any external program to access the server.
If you want to get the authentication to be done through password rather than authentication method, then you can use
mysql_native_password rather than
auth_socket. To do this just open the MySQL prompt and type following command:
$ sudo myself
Now you can check the authentication method for each of the MySQL user accounts through the following command:
mysql> SELECT user, authentication_string,plugin,host FROM mysql.user;
Above command can have the following output:
Here in the above result, the root user has used auth_socket for authentication plugin. To update the method through password rather than socket you should run ALTER USER command and don’t forget to change the password to a strong password, it can be done through the following command:
mysql>ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY “password’;
Now run FLUSH PRIVILEGES that tells the server to load again grant tales and put new changes into effect:
mysql> FLUSH PRIVILEGES;
Check authentication method that is employed by each user to make sure that root no longer can authenticate through an auth_socket plugin. It can be done through the following command:
mysql> SELECT user,authentication_syring,plugin.host FROM mysql.user.
The command will give the following output:
You can notice that the authentication has been changed to the mysql_native_password. Now you can exit from the MySQL shell through exit command.
As an alternate option, you can find better solutions and options to get connected to MySQL with a dedicated user. You can create such user through following MySQL command:
$ sudo myself;
If you have enabled a password authentication then you can use the following command to access MySQL shell, that can be done just through normal privileges:
$ mysql –u root –p
Now you can create a new user and give it a strong password: through the following command:
Mysql> CREATE USER ‘romil’@’localhost’ IDENTIFIED BY ‘password’;
Now you can assign new privileges to this newly create a user and add power to this user by making new changes.
mysql > GRANT ALL PRIVILEGES ON *.* TO ‘romil’ @ ‘localhost’ WITH GRANT OPTION;
Now you can exit from MySQL installation.
After MySQL installation, you should check the running status of MySQL as it should now run automatically. For his you can use the following command:
$ systemctl status myself.service;
Above command will give the following output:
In case if MySQL is not be running then you can start it with the “sudo systemctl start MySQL” command. You can also use mysqladmin tool to connect, that help you in running administrative commands. For the following command, you should get the shown output that can ensure that you have successfully installed MySQL.
$ sudo mysqladmin –p –u root version
Through the above-listed steps, you can make sure that you have installed MySQL. This is a step by step procedure that can be used to install MySQL and you can make sure that it is running successfully just through the standard commands.