How to Grant Remote Access to a MySQL Database

So you’ve just created a user in MySQL, but the user cannot log in, even when using the correct password. What’s the problem?
Generally MySQL users can access the server from the server itself, or from the same network subnet. Remote access to MySQL (through the Internet, or from another private network) must be granted as through a special process.


Granting Remote Access to MySQL:
The database administrator grants permission for the user to access the server from all of the remote locations where the user is authorized.
A detailed discussion of remote access is found in the MySQL 5.1 Reference Manual, however, some simplified steps are here:


1. Log in to MySQL as an administrator. This must be done by command line. Enter the following command from a shell prompt:

mysql -u admin –p

Enter the admin password when prompted.

2. From the MySQL prompt, grant the user access to a specific database schema. The general format of the command to use is:

GRANT ALL PRIVILEGES ON database.* to ‘user’@'yourremotehost' IDENTIFIED BY 'newpassword';

Where:

‘user’ is the user name of an existing MySQL account. The username is enclosed in single quotes, as shown.

database is the name of the database schema where access will be granted. Either the name of the schema can be used, or an asterisk (*) can be used to specify all databases.

‘yourremotehost’ contains either an IP address where the user will access from, or a domain name. The IP address or host name must appear in single quotes, as shown.

‘newpassword’ contains the password the user must use to access the server. The password must appear in single quotes as well.

3. To force the changes to take effect immediately, enter the following command:

FLUSH PRIVILEGES;

Specific Examples:

Example A: Granting access for the user jsmith from an IP address:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example B: Granting access from a domain:

GRANT ALL PRIVILEGES ON mydatabase.* to jsmith@'%.mycompany.com' IDENTIFIED BY 'jimspassword';

Example C: Granting access to all schemas:

GRANT ALL PRIVILEGES ON *.* to jsmith@'69.234.27.102' IDENTIFIED BY 'jimspassword';

Example D: Granting access from a specific host name on a domain:

GRANT ALL PRIVILEGES ON *.* to jsmith@'jimspc.mycompany.com' IDENTIFIED BY 'jimspassword';