Ubuntu Tips n Tricks: TechSource: How to Secure a MySQL Installation

Wednesday, January 9, 2013

TechSource: How to Secure a MySQL Installation

Posted: 08 Jan 2013 06:28 PM PST

MySQL is the world's most popular database management system and it is being used in many projects. Be it a simple media player or a huge server that logs thousands of users, this open-source database is known for its stability and performance. MySQL was named after co-founder Michael Widenius' daughter, My and as for the SQL part, the phrase stands for Structured Query Language. If you are using MySQL on a server, it is highly important that you secured the installation in order to avoid any loopholes. If left unsecured, an intruder might hack into the database causing havoc to the stored data.

To get started, simply login to your MySQL installation using the following command:

mysql -u root -p

Enter the password (Press enter if the root password is blank).

Step 1: Removing test databases

MySQL, when installed by default, comes with sample databases for testing purposes. Keeping them might pose a security risk, and hence we need to remove them. Enter the following commands to do that:

mysql> drop database test;
mysql> use mysql;
mysql> delete from db;
mysql> delete from user where not (host="localhost" and user="root");
mysql> flush privileges;

This will remove all other databases that are not root.

Step 2: Set a strong root password (Root password is empty by default)

Usually, MySQL password for root is empty by default. This is a huge security flaw. In order to set a root password, issue the following commands in the MySQL prompt:

mysql> UPDATE mysql.user SET Password = PASSWORD('astrongpassword')
-> WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

This will set a password to the root account. Do make sure you remember it well.

Step 3: Disable remote access to MySQL

We need to disable access to 3306 port, to which MySQL listens to. This is to avoid any remote attacks to the server. To do that, simply locate your my.cnf file. The configuration file is usually located in either of the following paths:

/etc/my.cnf
/etc/mysql/my.cnf
~/.my.cnf

Open the file and add the following line to the section that says [mysqld]:

skip-networking

You can still remotely access the database using ssh, so don't worry about it.

Additional security tips:

Set a strong root password: I cannot emphasize this enough. Setting a root password isn't everything. You need a password that is hard to decipher. Try generating a random password from the command line with the following code:

$ date | md5sum

Don't use MySQL as root: Create a separate user and then use it to test, modify and add databases. Avoid logging in as root as much as possible.

Lockdown the data directory: Change the permission of the directory where database is stored so that only selected users can access it. You can do that using chown and chmod commands.

Periodically backup MySQL data: Even though the server might be relatively immune to attacks, it's still a good idea to backup your databases. You can use the mysqldump command to do that.

Here's a sample of the command in action:

mysql --u [username] --password=[password] [database name] < [dump file]

Written by: Abhishek, a regular TechSource contributor and a long-time FOSS advocate.