Support & Service

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 Windows, the command prompt is opened by selecting Start > Programs > Command Prompt.

Windows : 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 the mysql database.

Execute the command below to connect to the 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 a test user can connect to the server:

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

Check out the 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 the test account connect only to the 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:

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

돌아가기