What is MySQL?
MySQL is an open-source database management system, commonly installed as part of the popular LAMP ( Linux, Apache, MySQL, PHP / Python / Perl ) stack. It uses a relational database and SQL (Structured Query Language) to manage its data.
This article will show you how to install and secure MySQL on an Ubuntu 18.04 machine.
1. Prerequisites
-> The operating system running Ubuntu 18.04 Linux
-> A root or non-root user with Sudo privileges
-> Has stable internet connection
-> Terminal window / Command line
2. Update Local Repositories
Updating the local package with apt-get command. Open a terminal window and run the following command:
sudo apt-get update
3. Install MySQL 5.7 Server
Please run the following command as sudo to install MySQL from APT-GET repositories.
sudo apt-get install mysql-server
The [Y / n] question to continue with the installation may appear during the installation process, press the Y key from the keyboard and hit Enter. This will install MySQL on the system.
4. Verify MySQL 5.7
After the installation, the MySQL server should start automatically. To check if it's running, run the following command:
mysql --version
5. Managing the MySQL Process
Now that you have your MySQL up and running, let's go over basic management commands.
To stop your MySQL, run this command:
sudo systemctl stop mysql
To start your MySQL, run this command:
sudo systemctl start mysql
To status your web server, run this command:
sudo systemctl status mysql
6. Configure MySQL Server
By default, MySQL lacks many basic and essential security features. Luckily, it comes with an installation script that walks you through the configuration. Use the mysql_secure_installation command:
sudo mysql_secure_installation
You will be asked to configure the VALIDATE PASSWORD PLUGIN which is used to test the strength of the MySQL user's passwords and improve the security. Type Y to start the Validate Password plugin and you will get the following prompt:
Enter the number for the password strength and press the Enter key:
On the next screen, enter and re-enter the password:
The system will then display the strength of the password you provided and also ask you if you want to continue with the password.
Type Y for Yes and press Enter.
In the next couple of steps, you will be prompted with the questions and, depending on the answers you provide, the security on your system will be set.
The first question is about removing anonymous test users.
Type Y for Yes and press Enter.
The second question is about forbidding root login from remote systems. It is recommended for a root user to allow the connection from the local system and deny it from the remote connections.
Type Y for Yes and press Enter.
The third question is about will be to remove the test database.
If you want to remove it, Press Y and the Enter key.
In the last step, you will be asked to reload privilege tables in order for the above changes to take effect.
Press Y and then Enter key and all security settings changes will be applied.
7.log in to Mysql And Configuring
Now, when all is set, let's log in to MySQL with the root user. In the terminal, run the following command:
mysql -u root -p
To check which authentication method each MySQL user uses, run the following command:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Change the authentication method from auth_socket to mysql_native_password. You can do that by running the following command:
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'YOUR_STRONG_PASSWORD';
After executing the ALTER USER command, run the following command:
mysql> FLUSH PRIVILEGES;
Now, if you recheck the authentication method for your MySQL user accounts using the following command, you see that your root user is now using the mysql_native_password plugin for authentication:
mysql> SELECT user,authentication_string,plugin,host FROM mysql.user;
Now, when trying to login to MySQL using the root user, you'll be logged with no problems.
Thank you for reading this blog.
Top comments (0)