5 Quick and Simple Tips to Better Secure your MySQL Server

Tip #1 - Disable Remote Access

If you web server and database server run on the same physical box, as is often the case, you shouldn't allow remote access to your MySQL server.  In most cases, you'll want to configure your router, firewall, or other security appliance to deny incoming traffic on MySQL's default port, 3306.  If you don't have a router, firewall, or appliance that can filter traffic by port, and you're using Linux, you might be interested in learning about iptables.  Iptables is used to set up, maintain, and inspect the tables of IP packet filter rules in the Linux kernel.  You can use iptables to deny incoming traffic on port 3306, from any client other than localhost (127.0.0.1).  As root, run:

(root)/> iptables -A INPUT -s 127.0.0.1 -p tcp --dport 3306 -j ACCEPT

(root)/> iptables -A INPUT -p tcp --dport 3306 -j DROP

The first command adds a rule to the INPUT chain that says "let 127.0.0.1 access port 3306".  The second command adds another rule to the INPUT chain that says "deny access to port 3306 from any other client." 


Tip #2 - Change the Default Admin Username and Password

Sadly, many MySQL server installations are using the default admin username and password configuration.  By default, the administrative username for MySQL is "root" and the password is empty (no-password).  Anyone with root access to your MySQL server can log into your database, create new users, change or grant privileges, add or drop tables, etc.  So, let's change the root username to something less obvious, and while were at it, let's change the root password too:

(root)/> mysql -u root mysql

mysql> UPDATE user SET user = "mydbadmin" WHERE user = "root";
mysql> FLUSH PRIVILEGES;

Replace "mydbadmin" with whatever you've chosen as the new administrative username.  Now, let's change the default administrative password:

mysql> SET PASSWORD FOR mydbadmin@localhost = PASSWORD('newpassword');
mysql> FLUSH PRIVILEGES;

Replace "mydbadmin" with whatever you've chosen as the new administrative username.  Replace "newpassword" with whatever you've chosen as the new administrative password.


Tip #3 - Delete Default Users

On occasion, several default database user accounts are created when you install MySQL.  You really shouldn't need these for any reason, so let's remove them:

(root)/> mysql -u mydbadmin -p mysql

mysql> DELETE FROM user WHERE NOT (host = "localhost" AND user = "mydbadmin");
mysql> FLUSH PRIVILEGES;

When prompted for a password, use the new administrative password you setup in Tip #2.  This command series will remove all of the non-administrative user accounts on your MySQL server.


Tip #4 - Create Separate User Accounts for Each Application

Now, let's create a single MySQL account per application that needs access to your database server.  Some database administrators provide a single account that has global access to all MySQL databases and tables.  This is bad for a number of reasons, but mainly because if one application using the database is compromised, a hacker can then access all other tables and databases running on the server.

For the sake of this example, say your MySQL database will be used by a blog and a guestbook.  The blog and guestbook are separate web-applications, and therefore, should have their own MySQL username and password.  The idea is that the blog MySQL account shouldn't have access to the guestbook database and vice versa.  If a hacker compromises the username and password used to access the blog database, they won't be able to access the guestbook database.  So, let's create a separate database and user account for the blog and guestbook:

(root)/> mysql -u mydbadmin -p

mysql> CREATE DATABASE blog;
mysql> CREATE DATABASE guestbook;

mysql> CREATE USER 'blgu'@'localhost' IDENTIFIED BY 'somepass';
mysql> CREATE USER 'gbu'@'localhost' IDENTIFIED BY 'anotherpass';

mysql> GRANT ALL PRIVILEGES ON blog.* TO 'blgu'@'localhost' WITH GRANT OPTION;
mysql> GRANT ALL PRIVILEGES ON guestbook.* TO 'gbu'@'localhost' WITH GRANT OPTION;

mysql> FLUSH PRIVILEGES;

Be sure to use good passwords when creating your new MySQL user accounts.  If you need a quick one-liner to generate good random passwords, please see my post on Generating Good Random Passwords With /dev/urandom.


Tip #5 - Delete the Sample Databases

As a general rule of thumb, you should delete or disable anything you don't explicitly need.  This includes the sample databases created by MySQL at install time.  On most MySQL installations, the sample/test database is named "test".  Let's remove it since it's just another component we don't need:

(root)/> mysql -u mydbadmin -p

mysql> DROP DATABASE test;
mysql> quit;

That's it!  At this point, you should should have reasonably secure installation of MySQL upon which to build your applications.  Enjoy!