Listen Only Where You Want to Hear
Just like memcache, the default mysql install is listening patiently on all your server’s ethernet devices for someone to connect. So, just like memcache, we need to tell mysql to only listen on the ethernet device we care about. For your average single-server web host, you only need to listen to your local host.
Find your my.conf file. On servers I manage, this is either at
/etc/my.conf
or
/etc/mysql/my.conf
Secure Your Root Password
When you first start mysql on a new install, it prints out a message that says something like “Don’t forget to set a root password!”, and gives you a couple of command-line examples. But guess what? A lot of people never get around to setting these (and I have been as guilty of that as anyone). Here are those commands again:
mysqladmin -u root password “newpassword”
mysqladmin -u root -h localhost password “newpassword”
Use an Application-Specific User
If you’re reading this blog, you probably have a Java, PHP, or Ruby application that isconnecting to your mysql server and accessing data in a database. We want to create a user specifically for our application, and give them just the abilities they need for our database.
dbock@my.example ~]$mysql -u root -p
you’ll be prompted to enter your shiny new root password. After you connect, we’ll type:
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> GRANT ALL ON schpoo.* to 'my_app_user'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.11 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.06 sec)
Grant ALL? Seriously?
Well, I’m speaking with my Ruby on Rails hat on – and I’m generally going to use that user to read and write to the database as well as run migrations, so ‘ALL’ makes sense. If you want an extra dose of paranoia, you can grant different sets of privileges to different users – but doing that properly would become advice more tuned to configuring, say Hibernate, than mysql.
A Note About Passwords
We all know its bad to reuse passwords… so how do manage passwords for a dozen clients across a dozen applications, and not reuse passwords?
There is a great little command-line linux tool used for generating universally unique identification strings, called uuidgen. Here’s some sample output:
[dbock@my.example ~]$ uuidgen
b70b8753-bbc0-4881-9b81-5e00baacd39a
[dbock@my.example ~]$ uuidgen
8f79aadd-29a0-41b7-bf66-6ad23ea49f04
[dbock@my.example ~]$ uuidgen
ea56ef7f-556e-44d5-a808-8baebc74c3d1
