Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

How do you allow hosts X to connect remotely to MySQL and have full access?

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

I often need to access to mysql from remote locations and this is what I do [warning: its not smartest or most secure way but gets job done for me... ;-)].

I log into mysql prompt on database server and execute command like:

grant all privileges on *.* to 'username'@'%' identified by 'userpassword';

This grants all privileges to mysql user 'username' from any location (indicated by '%') using password 'userpassword'

In above line *.* indicates access to all databases and all tables.

You can specify like dbname.* in case you want to limit access to dbname.

You can also replace *.* with dbname.tablename in case you want to limit access at table level.

For more info: Check MySQL ref for GRANT Command

share|improve this answer
    
Ok , great I took a user and I missed the semicolon btw. –  Chris_45 Nov 26 '09 at 8:58
add comment

In your MySQL configuration, /etc/mysql/my.cnf or similar, you most likely have one or two of these settings: skip-networking and bind-address. In case you have skip-networking you obviously want to remove it.

Regarding bind-address in might be set to only bind/listen to 127.0.0.1 (aka: localhost). One solution is to also remove that option completely, falling back on the default of listening on every interface. Another solution is to explicitly set the ip address of the network interface you want mysqld to listen on.

Also, remember that when you create MySQL users you have to be explicit on where from they are allowed to connect. Here are a few examples.

'andreas'@'localhost'
'olsson'@'192.168.1.42'
'andol'@'%'
share|improve this answer
    
Ok but in Win32? –  Chris_45 Nov 26 '09 at 8:02
    
On Win32 also u get mysql shell where u can use GRANT command as I mentioned in my answer below. Sorry if I'm overestimating windows (haven't used it in long time) –  rahul286 Nov 26 '09 at 8:08
    
But there is no my.cnf in Win-environment? –  Chris_45 Nov 26 '09 at 8:18
    
In win environment there is mysql.ini or my.ini –  lg. Nov 26 '09 at 8:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.