Allow Mysql remote access (ERROR 2003 (HY000): Can't connect to MySQL server on 'x.y.z.200' (111))

Để cho phép remote machine remote access (từ lệnh mysql ở command line) tới MYSQL (version 5) server, cần:
- Chỉnh binding address ở my.cnf
- Grant quyền truy cập cho user cụ thể (root) tứ một máy cụ thể (x.y.z.200/100)

Nếu không làm vậy, sẽ gặp hiện tượng lỗi:
ERROR 2003 (HY000): Can't connect to MySQL server on 'x.y.z.200' (111)


vuhung@webserver:~$ mysql -h x.y.z.200 -u root -p
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'x.y.z.200' (111)

vuhung@dbserver:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.* to 'root'@'x.y.z.100';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'x.y.z.200';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'root'@'127.0.0.1';
Query OK, 0 rows affected (0.00 sec)

mysql> Bye

root@dbserver: /var/log # mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 54
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> grant all privileges on *.8 to 'root'@'x.y.z.100' identified by '123456';
ERROR 1046 (3D000): No database selected
mysql> grant all privileges on *.* to 'root'@'x.y.z.100' identified by '123456';
Query OK, 0 rows affected (0.00 sec)

root@dbserver: /etc/mysql # mysql -h x.y.z.200  -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 36
Server version: 5.5.24-0ubuntu0.12.04.1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> Bye
root@dbserver: /etc/mysql # diff my.cnf*
48d47
< bind-address          = x.y.z.200

--   Best Regards,  Nguyen Hung Vu [aka: NVH] ( in Vietnamese: Nguyễn Vũ Hưng )  vuhung16plus{remove}@gmail.dot.com , YIM: vuhung16 , Skype:  vuhung16plus, twitter: vuhung, MSN: vuhung16.  http://www.facebook.com/nguyenvuhung  Nguyễn Vũ Hưng's blog on Free and Open Source:  http://nguyenvuhungvietnam.wordpress.com/  Học tiếng Nhật: http://hoc-tiengnhat.blogspot.com/  Vietnamese LibreOffice: http://libo-vi.blogspot.com/  Mozilla & Firefox tiếng Việt: http://mozilla-vi.blogspot.com/    Disclaimer: When posted to social networking groups include, but not limited Linux Users' Groups,  Free and Open Sources forums, mailing lists, the above is my personal opinion and is *not*  the opinion of my employer(s), associations and/or groups I join.