administration mode
Pssst...Ferdy is the creator of JungleDragon, an awesome wildlife community. Visit JungleDragon

 

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:

  • //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;

  • 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.


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: 15
Reviews: 5
Average rating: rating
Highest rating: 5
Lowest rating: 3

COMMENT: NOOB email

JUN 30, 02:35:37 AM

comment » Hi Ferdy, I would like to ask that is the coding you provide actually work for MySQL server? I tried to type 'su' and 'mysql -p' but it just give -> sign. "Im new to MySQL so plz dun mind me not knowing any simple things and im using MySQL 5.0 now." I tried the grant command and there is error. After reading your post, I notice that i cant risk My SQL to be not permitted for every single host. Therefore is there any better suggestion I can use or any link that show my how with some SS. Thanks for your help. «

COMMENT: MYSQL TO ACCESS emailhomepage

AUG 29, 03:48:22

comment » 02 interesting blog! «

COMMENT: HARSH MISHRA emailhomepagerating

DEC 27, 09:02:54 AM

comment » Hi

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 » Thanks for this. Worked perfectly on FC6, MySQL 5.0.22 and Zend Studio 5.5. «

COMMENT: JAY HOWELL email

MAR 3, 05:04:11 PM

comment » You need to read the docs for Grant. http://dev.mysql.com/doc/refman/5.0/en/grant.html

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 emailhomepagerating

OCT 11, 06:00:43 AM

comment » good

17 «

COMMENT: JUAN

DEC 6, 01:31:42 AM

comment » I was helped by my DB admin and he told me that I was better off creating a new user and not using root to access the DB from my PHP code. I created a new user and applied the changes recommended here and I was able to access mysql from the php code running on apache.

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

comment » The same error 1045 on

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

comment » NOW OK!! NOT MORE "error 1045"

1) sudo su

2) mysql -p

3) GRANT ... «

COMMENT: DAMODAR MUKHOPADHYAY rating

JUL 18, 2008 - 09:36:48 AM

comment » «

COMMENT: JOE SMYTH rating

OCT 6, 2009 - 02:30:50 PM

comment » Hi

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

comment » lol i read the title of this blog as 'firendly christian com' :P

thanks for the article :-) «

COMMENT: MIHAI rating

FEB 3, 2012 - 08:37:20 PM

comment » worked great!!!

use:

GRANT USAGE ON *.* to root@"%" IDENTIFIED BY "newpassword";

to access the database from any host.

Thanks! «

COMMENT: ELENA email

MAR 29, 2013 - 02:59:16 PM

comment » probably u will need to set the bind address. http://newexception.com/mysql-remote-access «

COMMENT: ALI emailhomepage

JUL 31, 2013 - 03:33:40 PM

comment » Better way is using any gui tool to manage users rather than using mysql console. «

RATE THIS CONTENT (OPTIONAL)
Was this document useful to you?
 
rating Awesome
rating Good
rating Average
rating Poor
rating Useless
CREATE A NEW COMMENT
required field
required field HTML is not allowed. Hyperlinks will automatically be converted.