Securing MySQL on a production server

There's a conumdrum for the authors / distributors of any open source server software that's likely to be used in a productions environment - should they send it out so that it's quick and easy to try but needs securing, or so that it's well secured but therefore calls for a bit more effort when you try it out.

The MySQL folks have always been "passed masters" at providing good distributions that install easily and test well ... but there have always been warnings about setting up passwords, getting rid of anonymous accounts and test databases, and limiting direct logins to localhost or a specific subnet.

These warnings remain, but in recent versions, the MySQL folks supply a script called mysql_secure_installation which takes you through each of the areas I have warned you about and lets you close the potential loopholes (some ARE just potential) on your to-be-production server.

Before you run the script, you should set up your path to include the MySQL binaries just installed:

  export PATH=/usr/local/mysql/bin:$PATH

  ./bin/mysql_secure_installation

and if you get "cannot connect to socket" messages, you should clean up / repeat the install, getting rid of the /var/lib/mysql directory before the reinstall!

What are the issues that the mysql secure installation deals with?

1. Setting the root password ensures that nobody can log into the MySQL root user without authentication (as shipped / unpacked, there is no root password set)

2. By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This account is limited in where it can log in from and what it can do, but never the less it should normally be removed.

3. Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network. If your server is behind a firewall, this may be less of a concern, but if you do need root access to MySQL from a remote site, you've probably got an ssh access set up to the server too and you should use that and run the mysql client on the server.

4.By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment. If you've removed the anonymous user already, there's less of an issue here as the door is already closed, but it IS sensible to remove the test database. If you leave both anonymous access and the test database easily accessed, dangers include unauthorised people stuffing your discs with gobs of data you don't want, and then running slow queries on it.

Even if you go through this securing script when installing MySQL, you still need to continue to consider security at all times. A login account to MySQL with a password the same as the user account name, or a password held in plain test in a publicly readable file can compromise the whole system. And if your database can hold data submitted by users, you'll need to have an acceptable user policy and a system in place to enforce that AUP.
(written 2009-10-09, updated 2009-10-12) 229a

Associated topics are indexed under

S153 - Sourcing, Running and Configuring MySQL
  [2458] Cant connect to local MySQL server through socket /tmp/mysql.sock - (2009-10-17)
  [2444] Potted MySQL installation - (2009-10-09)
  [2426] Which version of MySQL am I running? - (2009-09-26)
  [2209] Monitoring and Tuning your MySQL installation - (2009-05-31)
  [2085] MySQL - licensing issues, even with using the name - (2009-03-16)
  [1935] Summary of MySQL installation on a Linux system - (2008-12-11)
  [1771] More HowTo diagrams - MySQL, Tomcat and Java - (2008-08-24)
  [1731] Apache httpd, MySQL, PHP - installation procedure - (2008-08-01)
  [1689] Some sideways thoughts on the news - (2008-06-27)
  [1131] MySQL - Password security (authentication protocol) - (2007-04-02)
  [1123] mysqldump and mysqlrestore - (2007-03-30)
  [1095] Apache httpd , browser, MySQL and MySQL client downloads - (2007-02-28)
  [907] Browser -> httpd -> Tomcat -> MySQL. Restarting. - (2006-10-28)
  [591] Key facts - SQL and MySQL - (2006-02-04)
  [535] MySQL permissions and privileges - (2005-12-20)
  [515] MySQL - an FAQ - (2005-12-03)
  [489] Which MySQL server am I using? - (2005-11-07)
  [334] Symbolic links and hard links - (2005-06-02)
  [192] Current MySQL and PHP paths and upgrades - (2005-01-28)

Back to
Potted MySQL installation

Previous and next
or
Horse's mouth home

Forward to
Learn about MySQL stored procedures

Some other Articles

Four aspects - Chamber, Transport, Courses and Hotel
MySQL - efficiency and other topics
MySQL stored procedures / their use on the web from PHP
Learn about MySQL stored procedures
Securing MySQL on a production server
Contrasting Cambridge, Bristol and Wiltshire
Variable storage - Perl, Tcl and Python compared
Not your cup of tea?
Optional parameters to Python functions

4088 posts, page by page

Link to page ... 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82 at 50 posts per page

This is a page archived from The Horse's Mouth at http://www.wellho.net/horse/ - the diary and writings of Graham Ellis. Every attempt was made to provide current information at the time the page was written, but things do move forward in our business - new software releases, price changes, new techniques. Please check back via our main site for current courses, prices, versions, etc - any mention of a price in "The Horse's Mouth" cannot be taken as an offer to supply at that price.

Link to Ezine home page (for reading).
Link to Blogging home page (to add comments).