SyntaxHighlighter

Wednesday, December 3, 2008

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.

2 comments:

windsword said...

Connecting to a server on a remote machine (mac): I'm able to do this via phpamdin (web based), but when I try to call it from the command line on my local machine (linux) I can't:
mysql --user=myid --password=my.passwd --host=remote.server.ip.addr dbname
I get the following error:
ERROR 2003 (HY000): Can't connect to MySQL server on 'remote.server.ip.addr' (110), any ideas?

Unknown said...

Erm... I might be wrong here, but connecting to the remote server via web (port 80 and apache/tomcat) is not the same as connecting directly to mysql (port 3306). So the fact that phpadmin is remotely accessible doesn't imply mysql would be.

A quick search on google reveals that this is usually a firewall issue from the local machine.
http://www.bluehostforum.com/archive/index.php/t-5292.html

Here is a FAQ on the topic:
http://forge.mysql.com/wiki/Error2003-CantConnectToMySQLServer