This article shows how to configure MySQL on your Ubuntu 18.04 server. It also shows you how to enable authentication, followed by service management. In the end, you will also learn how to test the service to verify the successful configuration.
Step 1: MySQL Client Installation
Install the mysql-client to remotely connect with the server:
Check the client version to verify if the installation was successful:
Output:
Now you can use the following command to establish a remote connection with the MySQL server:
Step 2: MySQL Server Installation
Before installing MySQL, make sure the Ubuntu server is properly installed and configured. The Ubuntu 18.04 server, by default, contains the latest MySQL version 5.7 in the repositories. Use the apt command to update the system packages from the repository as follows:
Now use the following command to install the MySQL server package:
Step 3: MySQL Configuration
In this era of persistent cybersecurity threats, it is a standard to change default options after successful server installation. This section guides you on MySQL server configuration to eliminate insecure default options such as remote root logins, default user accounts, etc. MySQL eases this task by automatically making all the changes with the help of running a simple security script.
The script prompts multiple options requesting Yes or No answers to change the MySQL default security. For instance, the first prompt requests if you want to set up a plugin to validate passwords, answer Yes and continue.
The next prompt asks to set the MySQL root user account password. You can notice that enabling the Validate Password plugin allows setting password strength against three levels of password security policy and length.
Enter the number you want to choose to set the password strength. The system will then ask to set the new password and re-type it for confirmation, as follows:
You can notice that after setting the password, it displays the strength and asks if you want to continue further.
Now it will ask for subsequent questions:
To remove anonymous test user Disable remote login from the root user Remove the test databases Reload privileges table to save all the changes
Type Y to continue with the default settings to set security rules.
Older versions of MySQL (before 5.7.6) require you to manually initialize the database directory. While for versions after that, run the following command:
Step 4: MySQL User Authentication Adjustments
Irrespective of establishing a password for a MySQL user account, the default authentication setting disables the use of passwords during connection establishment. Instead, it automatically authenticates the user with the help of the auth_socket plugin. The plugin is a great feature for usability, however, it is not practical if you want to access your database from a remote client.
As database access without a password complicates the working process when accessed by an external program, this section summarizes two ways to authenticate the user at connection establishment:
1. Root User Authentication With Password
To ensure user authentication via password, you can change the plugin from “auth_socket” to “mysql_native_password.” For this purpose, open the MySQL prompt screen by using the sudo mysql command and verify the plugin that is in use for user authentication, as follows:
The above screenshot displays that the root client authenticates itself by using the “auth_socket” plugin. To initialize root client authentication with a password, use the ALTER USER statement to set the “mysql_native_password” plugin.
Save the above changes as follows:
Now to verify the authentication strategies utilized by each user and ensure that the “auth_socket” plugin is not used by the root client, re-run the command:
Now the root user can connect with the server by authenticating via the following command:
2. Create a Dedicated User
Another way to enable authentication without using mysql_native_password is to create a dedicated user as follows:
Create a new account and grant all privileges to assign administrative level control to the new user. Then, exit the MySQL prompt. Run the following SQL queries one by one to achieve this:
Manage and Test the MySQL Service With systemctl
Set the service to run at the Ubuntu server startup by using the systemctl enable command as follows:
Besides, you can test the server to be up and running by typing:
Or
The service runs automatically after MySQL installation, however, if it does not, you can initiate the service using the following command:
Getting Started With MySQL Server on Ubuntu
MySQL is an open-source, user-friendly, scalable, and robust database management system. Besides, it is an integral part of the LAMP/LEMP stack model or web application technologies. This relational database management system (RDBMS) offers simple installation and easy configuration on its recent versions.
This article guides you in building a basic MySQL setup that will help you get started with learning how MySQL works. It also covers some initial important security measures in its configuration to avoid the loopholes in the default setup. You can learn more about securing MySQL by following some advanced security tips.
