The MySQL binary installation package takes care of most of the things automatically but we still need to do some extra manual steps to make sure that our MySQL database is safe from outside attacks as it is going to be on a server directly connected to the internet.

Copy the example configuration file from the MySQL source, ‘/usr/local/mysql/support-files/my-medium.cnf‘, to ‘/etc/my.cnf‘ and set the appropriate permissions:

chmod 644 /etc/my.cnf

Next we need to remove all accounts except the local root account because we don’t need them and they pose a security risk as everyone knows they are there to abuse. Please note that you should only do this if you install a new instance and not if you are upgrading or you will loose all your current users.

Start mysql from the Terminal and enter the following commands:

mysql> use mysql;
mysql> delete from db;
mysql> delete from user where User = '';
mysql> flush privileges;

This will prevent the database from establishing anonymous connections.

To change the password for the ‘root’ account you can use the following commands from the MySQL terminal:

mysql> update user set Password=PASSWORD('new_password')
where User='root';
mysql> flush privileges;

Don’t forget to flush privileges; to make the privileges take effect.
It is also recommended to change the default name of administrator’s account (root), to a different, harder to guess one. Such a change will make it difficult to perform brute-force and dictionary attacks on the administrator’s password. In this case the intruder will have to guess not only the password, but first and foremost, the name of the administrator’s account.

mysql> update user set user="mydbadmin" where user="root";
mysql> flush privileges;

Another change applies to the 3306/tcp port, on which MySQL listens by default. Because, according to the initial assumptions, the database will be only used by locally installed PHP applications, we can freely disable listening on that port. This will limit possibilities of attacking the MySQL database by direct TCP/IP connections from other hosts (This can also be accomplished by blocking this port using the Firewall). Local communication will be still possible through the mysql.sock socket. In order to disable listening on the mentioned port, the following parameter should be added in the [mysqld] section of ‘/etc/my.cnf‘:

skip-networking

If, for some reason, remote access to the database is still required (e.g. to perform remote data backup), the SSH protocol can be used to connect to the server and execute a command locally, for example to create a backup you could type:

otherhost$ ssh mysqlserver /usr/local/mysql/bin/mysqldump -A > backup

Administer your MySQL server

You will need to administer your server, you could use the Terminal commands but that is a bit tedious. There are nice administration tools available even from MySQL, or web based like phpMyAdmin. The installation of the MySQL administrator is just like a regular application, the installation of phpMyAdmin will be discussed elsewhere because you need to install PHP first.