MySQL for production

I’m starting a web project and I decided to save some money by hosting my MySQL database in a cheap instance in Digital Ocean. I was a little concerned about security so I did some research and found some ways to make my installation a little safer.

The first thing any installation must do is run:

1
sudo mysql_secure_installation

This step will allow you to set a root password if you haven’t already done so. This of course is something you must do if you want any kind of security. The script will also remove the default anonymous account, only allow root connections from localhost and remove the test database.

If you expect connections to your mysql database to come from a single host you can restrict this inside /etc/my.cnf by adding something like this:

1
bind-address = 127.0.0.1

This can be any valid IP address. If you want to allow connections from more than one IP addresses then you will have to do this at the network level.

MySQL allows you to load data from the local file system using a LOAD statement. If you are not using this statement, the best thing is to remove the access to local files altogether. You can do this by adding a line to your /etc/my.cnf file:

1
local-infile = 0

Verify the current installation

You can quickly check your installation security settings using this command:

1
SELECT user, host, password FROM mysql.user;

You will see something like this:

1
2
3
4
5
6
7
8
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C |
| root | 127.0.0.1 | *7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C |
| root | ::1       | *7F0C90A004C46C64A0EB9DDDCE5DE0DC437A635C |
| jose | %         |                                           |
+------+-----------+-------------------------------------------+

In the previous output we can see that the user jose has not password set. This can be fixed with this command:

1
UPDATE mysql.user SET password=PASSWORD("secret-password") WHERE user="jose";

This sets the password secret-password to the user jose. Another thing that should be fixed is the Host. Jose can currently log-in from anywhere(%). We can fix this also with this command:

1
UPDATE mysql.user SET host="localhost" WHERE user="jose";

Now you need to flush the privileges for the changes to take effect:

1
FLUSH PRIVILEGES;

Creating users

The root user should be used for administration, when you need applications to access your database you should create users with more restrictive permissions. You can create a user with this command:

1
CREATE USER "user-name"@"localhost" IDENTIFIED BY "secret";

A typical application will most likely need to SELECT and UPDATE records. You can grant these permissions:

1
GRANT SELECT, INSERT, UPDATE ON cooldb.* TO "user-name"@"localhost";

You should give as little permissions as you can. If you are not sure, it is better to fail on the side of security. If in the future you find you need more access you can always grant it. After changing any permissions, remember to flush the permissions:

1
FLUSH PRIVILEGES;

Another very important point to make a MySQL installation secure is securing the network. If possible you should communicate with your database over the same private network so nobody can sniff the traffic. If for some reason you can’t use a private network you should use an encrypted connection so traffic can’t be sniffed. I will write in another post a little about network security.