Connecting to MySQL DB from various hosts

Question

How to add a host to MySQL's list of hosts it can accept connections from.

Answer 1

The information on the host a user is allowed to connect from is in the user table, see adding users. Also make sure that you are not blocking connections on a firewall/os level.

Answer 2

From http://www.linuxhelp.net/guides/mysql/:

To give localhost permission to access all databases, enter this:

mysql> insert into 
         -> host(host,db,Select_priv, Insert_priv, Update_priv, 
         -> Delete_priv, Create_priv, Drop_priv)
         -> values('localhost','%','Y','Y','Y','Y','Y','Y');

Note, the '%' can be replaced with a database name. The '%' is a wildcard.

Following the previous format, to allow access from another hostname (in this case "windowsbox") add this:

mysql> insert into 
         -> host(host,db,Select_priv, Insert_priv, Update_priv, 
         -> Delete_priv, Create_priv, Drop_priv)
         -> values('windowsbox','%','Y','Y','Y','Y','Y','Y');

Again, '%' is used as a Wild-Card.

To create a user 'djg' who can access the MySQL server from localhost, type this:

mysql> insert into 
         -> user (host, user, password)
         -> values('localhost','djg',password('mypassword'));

To give the user access from another hostname, domain, etc... add other entries accordingly. For example, to give user djg access from windowsbox:

mysql> insert into 
         -> user (host, user, password)
         -> values('windowsbox','djg',password('mypassword'));

Now... to give the user permissions to access a database from localhost, add this entry and change with your appropriate information:

mysql> insert into
      -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv,  Create_priv, Drop_priv)
      -> values ('localhost','mydatabase','djg','Y','Y','Y','Y','Y','Y');

To give the user permissions from windowsbox, add this:

mysql> insert into
      -> db (host,db,user,Select_priv, Insert_priv, Update_priv, Delete_priv, Create_priv, Drop_priv)
      -> values ('windowsbox','mydatabase','djg','Y','Y','Y','Y','Y','Y');

PS: don't forget to flush privileges