Remote Access to MySQL databases

One way in which MySQL differs from other databases such as Oracle, is that by default, only accounts on the same machine as the database are allowed to access the database. This means that, although it’s not heavily documented, all user names consist of two parts: the user and the machine name (or IP address); hence on the local machine, a user called ‘fred’ is really, by default, called ‘fred@localhost’ - although the ‘localhost’ part is usually hidden.

This means that remote users must be granted access to a MySQL database before they’re allowed to access it. You can either grant access to individual machines or all machines.

Grant and Revoke Remote Access to MySQL to Individually Named Hosts

Once the database seems to be working, you’ll need to grant access to remote machines.
To do that you’ll need to log in to mysql:

su mysql
mysql –u=root –p=password

Assuming you haven’t already created a database then:

create database myNewDatabase

Now grant access to a user on an individual IP address:

GRANT ALL ON myNewDatabase.* TO [email protected] IDENTIFIED BY 'password';

or machine name:

GRANT ALL ON myNewDatabase.* TO root@MyMachineName IDENTIFIED BY 'password';

For Toad for MySQL (and possibly other IDEs) to work try then you’ll need to grant remote access to everything:

GRANT ALL ON *.* TO root@MyMachineName IDENTIFIED BY 'password';

This is great for secure databases, where access needs to be restricted then you need to:

Grant and Revoke Remote root Access to MySQL to all hosts

To grant root access from all hosts (except for localhost) use the following SQL:

GRANT ALL PRIVILEGES ON *.* TO root@"%" IDENTIFIED BY 'password';

The percent symbol ("%") in the notation root@"%" means “any host”, but it doesn’t imply localhost, you’ll need to repeat the commands above with root@localhost in order to grant/revoke permissions for localhost.

To enable MySQL service to accept connections from all hosts change the following line in file mysql.conf:

bind-address=127.0.0.1

to

bind-address=0.0.0.0

or better just comment out:

#bind-address=127.0.0.1

and restart the MySQL service.

Revoking Permissions

To revoke root access from all hosts except for localhost, use the following SQL:

DELETE FROM mysql.user WHERE User = 'root' AND Host = '%';
FLUSH PRIVILEGES;