Allowing hosts to connect remotely to MySQL?

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

From serverfault Chris_45

• 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'@'%'
  

  Chris_45 : Ok but in Win32?

  rahul286 : 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)

  Chris_45 : But there is no my.cnf in Win-environment?

  lg : In win environment there is mysql.ini or my.ini
  From andol

• 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

  Chris_45 : Ok , great I took a user and I missed the semicolon btw.
  From rahul286