Category Archives: MySQL

Securing MySQL for development

When developing in Rails, the canonical thing to do is to have a root MySQL account with no password. While this makes the Rails configuration automagic, it leaves things somewhat open because MySQL accepts TCP/IP connections by default. Note that this is separate from the webserver hosting the Rails application on localhost. It turns out that MySQL has a nice command line option --skip-networking, which will turn off all TCP/IP networking. Database connections from the local webserver will go through a Unix socket, so they will continue to work and that’s all you need for development.

I installed MySQL using the MySQL AB official binary distribution. However, I have been starting and stopping MySQL using the conveniently supplied panel for Mac OS X System Preferences, thus preventing my from adding any command line arguments. After some fiddling, it turns out that the preference panel is just calling the shell script /usr/local/mysql/support-files/mysql.server. This file (or one of the scripts that it calls) will read ~/.my.cnf or /etc/my.cnf for MySQL configuration options. To make it work with the pref pane, I had to put the following options in /etc/my.cnf:

# MySQL options file
[mysqld]
# turn off all networking, for safety during development
skip-networking

After that, MySQL is no longer listening via TCP, as confirmed with CocoaMySQL’s Show Variables, and via netstat -a. I feel safer already. 🙂

Advertisements

MySQL installation and configuration

Most Rails tutorials use MySQL as their canonical database. Locomotive includes SQLite, but not MySQL.

The consensus seems to be that installing MySQL via the official Mac OS X MySQL installer is the best way. It installs in /usr/local/ and includes a System Preferences pane for easy startup and shutdown.

MySQL installs with 4 accounts (including 2 root accounts) that have no password. While the default install only allows connections from localhost, it’s still a good idea to set the passwords to something. MySQL AB has a help page on Securing the Initial MySQL Accounts.

For manipulating MySQL databases directly, the open source CocoaMySQL seems nice. I used it to secure the initial MySQL accounts: Tools->Edit User.