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
su
//log in to mysql service
mysql -p
//grant access to user-remote host combination
GRANT USAGE ON *.* to root@'yourremotehost' IDENTIFIED BY 'newpassword';
//force update of authorization changes
FLUSH PRIVILEGES;
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.
Comments: 14
Reviews: 5
Average rating:
Highest rating: 5
Lowest rating: 3
COMMENT: NOOB
JUN 30, 02:35:37 AM
COMMENT: MYSQL TO ACCESS
AUG 29, 03:48:22
COMMENT: HARSH MISHRA
DEC 27, 09:02:54 AM
I tried your solution and it works for me. But now after that i am facing another probllem that i can able to log in to my MySQL server now it is giving me the error
" ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) "
Please give your comments. «
COMMENT: DARREN
JAN 21, 10:56:28 AM
COMMENT: JAY HOWELL
MAR 3, 05:04:11 PM
If you really want to open it up, just use the above syntax using
//switch to root user
su
//log in to mysql service
mysql -p
//grant access to user-remote host combination
GRANT ALL ON *.* to root@'yourremotehost' IDENTIFIED BY 'newpassword';
//force update of authorization changes
FLUSH PRIVILEGES;
This will grant all privileges. «
COMMENT: YOGESHK@REDIM.NET
OCT 11, 06:00:43 AM
COMMENT: JUAN
DEC 6, 01:31:42 AM
I am still waiting for advice on how to enable remote to mysql from remote machines. my company uses a custom configuration so I am waiting to hear to make sure my changes comply, but I really need access granted to my user to access the DB from remote machines.
Good luck! «
COMMENT: PETER K.
JUN 10, 2008 - 21:17:06
mysql> GRANT USAGE ON *.* to root@'10.0.0.12' IDENTIFIED BY '123';
ERROR 1045 (28000): Access denied for user ''@'localhost' (using password: NO)
mysql> «
COMMENT: PETER K.
JUN 10, 2008 - 21:21:55
1) sudo su
2) mysql -p
3) GRANT ... «
COMMENT: DAMODAR MUKHOPADHYAY
JUL 18, 2008 - 09:36:48 AM
COMMENT: JOE SMYTH
OCT 6, 2009 - 02:30:50 PM
Opening the port on the fire wall and GRANT ALL ON *.* to root@'...' IDENTIFIED BY 'password'; worked perfect.
thanks,
Joe «
COMMENT: ANONYMOUS
DEC 18, 2010 - 00:25:03
thanks for the article :-) «
COMMENT: MIHAI
FEB 3, 2012 - 08:37:20 PM
use:
GRANT USAGE ON *.* to root@"%" IDENTIFIED BY "newpassword";
to access the database from any host.
Thanks! «
COMMENT: ELENA
MAR 29, 2013 - 02:59:16 PM