Thursday, October 25, 2007

Connecting to a MySQL Server remotely

I've been working on a PHP code that uses MySQL DBs, and I've been doing it manually from Quanta and PHPMyAdmin. And everyone knows that coding in a browser is a very bad habit. I sure wish I have an IDE with MySQL Admin integrated in it. But before I go searching for that particular IDE, I wanted to make sure that I can access it from both my desktop and my laptop.

I'll be using MySQL Administrator for this tutorial and my MySQL server is hosted on Ubuntu Server Gutsy, subtle differences are expected (if any).

First, the error that'll show up would be this:

To determine if there's a firewall preventing access, try to ping it:

If you can't ping it, open port 3306

sudo iptables -A INPUT -i eth0 -p tcp -m tcp --dport 3306 -j ACCEPT

As I've said earlier, I have PHPMyAdmin installed, I used it to create 4 users for my laptop and my desktop. I made sure I have both the IP Address and Hostname in it. And since I wanted this to be an admin account, I granted it all privileges. Consult the help docs if you don't have PHPMyAdmin installed or if you want to do this manually.

Next up would be to edit /etc/mysql/my.cnf
Comment out bind =

Restart MySQL

And lastly, test it out:

I can now create, edit, and drop DBs/Tables from MySQL Administrator. I don't have any need for PHPMyAdmin now that I can access MySQL remotely so I removed it. I'll be searching for a PHP/MySQL IDE next.

Blogged with Flock