Enable remote access to MySQL

Date: 2007-01-19

It seems that everytime I need to set this up I have a terrible time trying to remember how it is done (esp. what the correct syuntax is) so I figure posting it here will help me remember (and possibly be of use to somebody else).

Enabling remote (non localhost) access to MySQL is often required to use tools such as NaviCat to manage a MySQL database. Simply follow the steps below and all should be good :)

Firstly, login to MySQL via ssh (or similar access) with root/dba permissions.

Type:

SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db;

to view a table showing all the current permissions for the various databases.

Type the following to grant permissions to allow remote access to a database:

GRANT ALL ON dbname.* TO dbuser@'ipaddress' IDENTIFIED BY 'password';

note the variables in this statement:
dbname: name of the database you wish to grant access to
dbuser: username of the user you want to grant access to
ipaddress: ip address which access is to be granted to (if you want it to be from any ip address simply replace this with a % sign)
password: the users password to access the database