Remote MySQL Access »
FERDY CHRISTANT - OCT 1, 2005 (01:32:15 PM)
I have MySQL running on my Linux box, and would like to remotely access it from my Windows machine. I'm not talking about PHPMyAdmin, because that uses PHP to talk to the MySQL server. I mean directly accessing the actual database server. I need this for a program I use, Zend Studio, which is a cool IDE in which you can develop PHP code and manage MySQL databases. I managed to connect to my Linux FTP server from Zend studio, so I can work with remote files directly from the IDE. Setting up the remote access to the MySQL server was a bit harder. Here's what I did:
- MySQL usually runs on port 3306. In most distributions this port is not enabled in the firewall settings. For Suse, I used Yast to add this port number
- MySQL by default does not allow connections from anything other than localhost. To change this, issue the following commands:
- This will grant the user 'root' access to the MySQL server from 'yourremotehost'. At least that's what I expected. I fired up PHPMyAdmin, opened the 'MySQL' database, 'user' table and saw that the new user record was indeed created. Unfortunately, all the privileges of this new entry were set to 'N' (no, not allowed). I still don't know what went wrong, but to fix the problem, I simply manually edited the record using PHPMyAdmin to set them to 'Y'. That made it work in the end.
//switch to root user
//log in to mysql service
//grant access to user-remote host combination
GRANT USAGE ON *.* to root@'yourremotehost' IDENTIFIED BY 'newpassword';
//force update of authorization changes
Not a very intuitive approach I admit, but it worked for me. Note that this all has nothing to do with Zend studio, it's just a way to enable remote access on the MySQL server. Which client you use to connect, Zend, MySQL administrator, or anything else, is up to you.