MySQL connection from an external host

MySQL permits the connection from localhost by default. For permitting the connection from an external host, it needs to set up in the following procedure. Add account to the user table of Database mysql.

[root@4a-o01-b3 ~]# mysql -u root -p ※Loging to MySQL by root.
Enter password: ※response password

mysql> select user,host from mysql.user;
+------+--------------------------+
| user | host                     |
+------+--------------------------+
| root | 127.0.0.1                |
| root | example.com              |
| root | localhost                |
+------+--------------------------+



■The items of the above-mentioned setup are as follows.

root@127.0.0.1 - IP Address "127.0.0.1(Namely, a local host)"can be accessed by user root.

root@example.com - HOST "example.com(Here, this is also a local host. )"can be accessed by user root.

root@localhost - localhost "localhost(Namely, a local host)"can be accessed by user root.

Only connection from a localhost can be performed in this stage.
Run following command to permit the connection from an external host. (※Fo example, username "bogati"、The external host who connects"nepal.com"、DB to be used "test" is set up)。


mysql> show databases; ※ Check a list of the existing database.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| test               |
+--------------------+

mysql> grant all privileges on test.* to bogati@"nepal.com" identified by 'password' with grant option;
Query OK, 0 rows affected (1.00 sec)

mysql> select * from user where user="bogati"\G ※ check of executed command.
*************************** 1. row ***************************
Host: nepal.com
User: bogati
Password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

...................................................

■Moreover, to connect from all the hosts, a wild card is specified as follows.

mysql> grant all privileges on test.* to bogati@"%" identified by 'password' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where user="bogati" && host="%"\G ※ check of executed command.
*************************** 1. row ***************************
Host: %
User: bogati
Password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29

...................................................

■To connect from all the hosts of LAN set up as follows.

mysql> grant all privileges on test.* to bogati@"192.168.122.%" identified by 'password' with grant option;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user where user="bogati" && host="192.168.122.%"\G ※ check of executed command.
*************************** 1. row ***************************
Host: 192.168.122.%
User: bogati
Password: *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29


■To connect from an external host.

After setting up the permission from an external host.
check that whether login is actually possible from an external host or not.
Since it becomes conditions that MySQL is installed in an external host also , when there is no environment, it is necessary to install.

Now connect from nepal.com

[root@nepal.com]# mysql -h example.com -u bogati -p

After the execution of the above-mentioned command, if login is successful, it will be the completion of a setting.


Thanks