How to install and configure MySQL

How to install and configure MySQL

Introduction

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better called as SQL) to manage its data.

This tutorial will re-examine a way to install MySQL version 8.0 on an Ubuntu 20.04 server. By completing it, you’ll have a working relational database that you just can use to make your next website or application.

Prerequisites

To follow this tutorial, you’ll need:

A single Ubuntu 20.04 server with a non-root administrative account and a UFW firewall. To line this up, follow our initial server setup guide for Ubuntu 20.04.

Installing MySQL

The APT package repository will be used to install MySQL on Ubuntu 20.04. The version of MySQL accessible in the default Ubuntu repository at the time of writing is version 8.0.27.
If you haven’t done so recently, update the package index on your server to install it.

sudo apt update

Then install the mysql-server package:

$ sudo apt install mysql-server

This will install MySQL, but won’t prompt you to line a password or make the other configuration changes. Because this leaves your installation of MySQL insecure, we’ll address this next.

Configuring MySQL

You should run the DBMS’s inbuilt security script on new MySQL installations. For things like remote root logins and sample users, this script modifies a few of the less secure default parameters.

Run the security script with sudo:

$ sudo mysql_secure_installation

This will take you thro a series of prompts where you’ll be able to make some changes to your MySQL installation’s security options. The first step will ask if you’d want to install the Validate Password Plugin, which may be used to assess the strength of recent MySQL users’ passwords before judging them genuine.

If you choose to line up the Validate Password Plugin, any MySQL user you create that authenticates with a password are going to be required to possess a password that satisfies the policy you choose. The strongest policy level — which you’ll be able to select by entering 2 — would force passwords to be a minimum of eight characters long and include a mixture of uppercase, lowercase, numeric, and special characters:

Regardless of whether you use the Validate Password Plugin or not, the next step will be to create a password for the MySQL root account. Choose a safe password, then enter and confirm it.

Note that although you’ve set a password for the root MySQL user, this user isn’t currently configured to authenticate with a password when connecting to the MySQL shell.

You’ll get feedback on the strength of your new password if you utilised the Validate Password Plugin. The script will then ask you if you want to keep the password you just entered or if you want to change it. Assuming you’re satisfied with the strength of the password you simply entered, enter Y to continue the script

From there, you’ll press Y and then ENTER to simply accept the defaults for all the following questions. This can remove some anonymous users and also the test database, disable remote root logins, and load these new rules so that MySQL immediately respects the changes you’ve got made.

Once the script completes, your MySQL installation are going to be secured. You’ll be able to now progress to making an avid database user with the MySQL client.

Creating and Granting Privileges to a Dedicated MySQL User

Upon installation, MySQL creates a root user account which you’ll use to manage your database. This user has full authority over the MySQL server, which means it has access to every database, table, user, and so on. As a result, it’s advisable not to use this account for anything other than administrative purposes. This step outlines the way to use the root MySQL user to create a new user account and grant it privileges.

In Ubuntu systems running MySQL 5.7 (and later versions), the root MySQL user is about to authenticate using the auth_socket plugin by default instead of with a password. The name of the operating system user who launches the MySQL client must match the name of the MySQL user supplied in the command for this plugin to work, so you must invoke mysql with sudo privileges to realise access to the root MySQL user:

$ sudo mysql

Note: If you installed MySQL with another tutorial and enabled password authentication for root, you may have to use a unique command to access the MySQL shell. The subsequent will run your MySQL client with regular user privileges, and you may only gain administrator privileges within the database by authenticating:

$ mysql –u root -p

Once you have access to the MySQL prompt, execute the Establish USER statement to create a new user. These are written in the following format:

>Create user ‘username’@’host’ identified with authentication_plugin by ‘password’;

After CREATE USER, you specify a username. This can be immediately followed by an @ sign so the hostname from which this user will connect. You can specify localhost if you simply want to access this user locally from your Ubuntu server. It’s not always required to wrap the username and host in single quotes, although it can assist prevent issues.

You have several options when it involves choosing your user’s authentication plugin. The previously stated auth socket plugin may be useful because it provides high security without forcing valid users to enter a password in order to access the database. However, it disables remote connections, which might make things more complicated when external programmes need to interface with MySQL.

As an alternate, you’ll hope over the WITH authentication_plugin portion of the syntax entirely to own the user authenticate with MySQL’s default plugin, caching_sha2_password. Because of its robust security features, the MySQL documentation recommends this plugin for users who want to log in using a password.

To create a user who authenticates using caching sha2 password, run the following command. Take care to vary shan to your preferred username and password to a strong password of your choosing:

>create user ‘shan’@’localhost’ identified by ‘password’;

After you’ve created your new user, you can give them the permissions they need. The following is the basic syntax for granting user privileges:

>grant privilege on database.table to ‘shan’@’localhost’;

For example, the following command gives a user global capabilities, including the ability to CREATE, ALTER, and DROP databases, tables, and users, as well as the ability to INSERT, UPDATE, and DELETE data from any table on the server. It also gives the user the ability to utilise SELECT to query data, REFERENCES to build foreign keys, and the RELOAD privilege to perform FLUSH operations. You should only give people the access they require, therefore feel free to change your own user’s privileges as needed.

In the official MySQL documentation, you’ll find a complete list of accessible privileges.

Run this GRANT statement, replacing sammy with your own MySQL user’s name, to grant these privileges to your user:

>GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'shan'@'localhost' WITH GRANT OPTION;

It’s worth noting that WITH GRANT OPTION is included in this statement. This will allow your MySQL user to grant any permission that it has to other users on the system.

Warning: Some users may wish to offer their MySQL user the ALL Rights privilege, which will grant them superuser privileges similar to those granted to the root user, as follows:

GRANT ALL PRIVILEGES ON *.* TO 'shan'@'localhost' WITH GRANT OPTION;

Anyone with access to this MySQL user will have complete control over every database on the server, therefore such broad privileges should not be granted lightly.
After that, it’s a good idea to run the FLUSH PRIVILEGES command. This clears any memory cached by the server as a result of the previous CREATE USER and GRANT statements:

>FLUSH PRIVILEGES;

Then you can exit the MySQL client:

exit;

You’ll use a command like this in the future to log in as your new MySQL user:

$ mysql –u shan –p

In order to authenticate, the -p switch causes the MySQL client to prompt you for your MySQL user’s password.

Follow us Twitter, Facebook, LinkedIn

Open Source Listing