With  Nginx and PHP setup, now our server able to handle dynamic content. We also have mysql server installed so that we can store and manage some information for our site. But we still need to do some configuration to make it working properly and securely.

Setup mysq_secure_installation

To secure the installation, MySQL comes with a command that will ask whether we want to modify some insecure defaults. We can do that by running this command:

sudo mysql_secure_installation

This command will ask if you want to configure the VALIDATE PASSWORD PLUGIN. Answer Y for yes, or any other key to continue without enabling.

VALIDATE PASSWORD PLUGIN 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 plugin?

Press y|Y for Yes, any other key for No: Y

If you've answered Y, you will be asked to select a level of password validation.

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: 1

Next, you'll be asked to enter and confirm the password for the root:

Please set the password for root here.

New password:

Re-enter new password:

Next, you'll be asked to remove anonymous users. Hit y or yes to proceed or any other key to skip.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Next, you'll be asked to ignore the root user to login remotely. For me I need to login mysql remotely, so I'll hit n.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n

Next, you'll be asked to remove test database. Hit y to proceed.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

Last, you'll be asked to reload mysql privilege table. Hit y to proceed.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

All done!

Change Mysql root password

Since MySQL version 5.7, the root user is set to authenticate using auth_socket plugin by default. It allow us to be able to login to mysql without need to specify the user and password (still we need enter password for sudo).

This could be better for security and usability in many cases, but in other hand it can also make things complicated when we need to access mysql through external program like PhpMyAdmin or other tool.

If you prefer using auth_socket to access mysql, then you can skip this section. But if you want to use a password when accessing mysql using root account, then you will need to change the authentication plugin from auth_socket to mysql_native_password.

To do this, let's open mysql console by typing following command:

sudo mysql

Once you've entered mysql console, you can check which authentication plugin for each mysql user accounts by using this command:

mysql> SELECT user, authentication_string, plugin, host FROM mysql.user;

You can see that the root user has auth_socket plugin with empty authentication string. Now let's change the root account's plugin to use mysql_native_password like other users. To do that we can run ALTER USER command as follow:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';

Don't forget to run FLUSH PRIVILEGES to tells mysql to reload the privileges tables and take effect the changes.

mysql> FLUSH PRIVILEGES;

Now we can verify the changes by re-run this command.

SELECT user, authentication_string, plugin, host FROM mysql.user;

As you can see now the authentication_string column for root account no longer empty and the plugin has changed to mysql_native_password. Now we can exit from mysql console by typing \q or exit.

mysql> exit

And since now you can access mysql without sudo but you need to specify the user ( -u) and password ( -p) as follow.

mysql -u root -p

This will prompt you for the root password. Just enter the password and hit Enter.

Create new Database and User for Laravel App

It's a bad security practice to use the root user for our application's database because it has unlimited privileges. So, let's create a dedicated database as well as user for our Laravel application.

Let's start by creating new database. In my case I'll call it laravel_database.

mysql> CREATE DATABASE laravel_database;

Next, let's create new user account in our database. Here I'll call it laravel_user with password password.

mysql> CREATE USER 'laravel_user'@'localhost' IDENTIFIED BY 'password';

Now we can verify our new user by re-run this query.

SELECT user,authentication_string,plugin,host FROM mysql.user;

Next, we need to grant all privileges to laravel_user on laravel_database by execute this command.

mysql> GRANT ALL ON laravel_database.* TO 'laravel_user'@'localhost';

Last, let's refresh mysql privileges by running this command.

mysql> FLUSH PRIVILEGES;

Alright, now we have new database as well as user for our Laravel application. We can exit from mysql console by typing:

mysql> \q