HomeSupport

Error - "Host not allowed to connect to server" / Connecting to a MySQL database

The error is generated when connecting to MySQL Server, which is remote.

When MySQL is installed on Window, the command prompt is opened by selecting Start>Programs>Command Prompt.

Windows Command : Start > Programs > Command Prompt
> CD\
> CD mysql\bin

During the MySQL installation, MySQL creates an account called root with no password, which can be used to log into the database. It is highly recommended that a password is assigned to this account since root has full control over the MySQL databases.

To create a separate user account for your web application, connect to MySQL and log in with the superuser account using one of the following methods. In the example below, the account with superuser privileges is the root account. MySQL should prompt you to enter a password when you press the Enter key:

> mysql -u root -p password

The message below will pop up if connected.



You can view the data on the User table by connecting to the Database
User is located inside of mysql database.
Execute the command below to connect to User Database

mysql>Use mysql
Database changed

mysql> SELECT Host, User, Select_priv, Insert_priv,Update_priv, Delete_priv FROM user;

The picture above is how the db table will look if all of the GRANT statements are run:
If these privileges are granted, the dbuser will have access to the system database.
Here is how test user can connect to the server.
Host : localhost
User : test

mysql> GRANT ALL PRIVILEGES ON *.* to test@"%" IDENTIFIED BY 'test'
WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

If the command above is worked properly, please try the command below.
Mysql>flush privileges;

Check out User table.
mysql> SELECT Host, User, Select_priv, Insert_priv,Update_priv, Delete_priv FROM user;

The new user is set up at the bottom besides the existing test user.
Host : %
User : test

the user account called 'test' enables remote connect

Here is how to have test account connect only to emp Database

1. In this example, dbuser can only access the database from localhost
   

   mysql>GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO
   test@localhost IDENTIFIED BY "TestPassword";
   
   mysql>flush privileges;

2. In this example, dbuser can only access the database from www.mysite.com
   

   mysql>GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO
   test@www.mysite.com IDENTIFIED BY "TestPassword";
   
   mysql>flush privileges;

   
   
3. In this example, dbuser can access the database from any host.
   

   GRANT SELECT, INSERT, UPDATE, DELETE ON emp.* TO
   test@"%" IDENTIFIED BY "TestPassword";
   
   mysql>flush privileges;