MySQL and the joys of security

I've been running my local MySQL server on Gentoo without hassles for a while and recently tried to connect to it from a remote machine. Obviously there was bound to be some issues and here's how I solved them.

When you first try to connect remotely to the machine, you'll be greeted by this lovely message:

# mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.1' (111)

To get this sucker out of the way, edit your /etc/mysql/my.cnf file and comment the following line:

# bind-address = 127.0.0.1

Note to all the security girls: Your server will now be accessible from any other networked machine. Oooohh nooooo!!

Anyhow, now we try to connect again, and whooopa!! Another error:

mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 1130 (00000): Host 'remote_pc_hostname' is not allowed to connect to this MySQL server

Ah bite me. Ok, so you have to grant his Lordship permissions to connect to thy server. In order to do that logon to sql on the sql server itself:

# mysql --user=root --password=some_password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.70-log Gentoo Linux mysql-5.0.70-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'root'@'remote_pc_hostname';
Query OK, 0 rows affected (0.00 sec)

Now all should be well in fairyland, but alas. The code securo freaks over at Sun have presented ye with another trial.

# mysql --user=root --password=some_password --host=192.168.0.1 MYDB
ERROR 1045 (28000): Access denied for user 'root'@'remote_pc_hostname' (using password: YES)

Ok I'm taking the fairytale rubbish too far. Here's what you should've done on the sql server in the first place:

# mysql --user=root --password=some_password
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.0.70-log Gentoo Linux mysql-5.0.70-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> GRANT ALL ON *.* TO 'root'@'remote_pc_hostname' IDENTIFIED BY 'some_password';
Query OK, 0 rows affected (0.00 sec)

Sheesh! You little girls are wasting my time. Take a que from MS SQL Server.