0

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

2 Answers 2

4

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

1
  • Ok , great I took a user and I missed the semicolon btw.
    – Chris_45
    Nov 26, 2009 at 8:58
1

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'@'%'
4
  • Ok but in Win32?
    – Chris_45
    Nov 26, 2009 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, 2009 at 8:08
  • But there is no my.cnf in Win-environment?
    – Chris_45
    Nov 26, 2009 at 8:18
  • In win environment there is mysql.ini or my.ini
    – lg.
    Nov 26, 2009 at 8:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .