今兒有位同事提出，一套MySQL 5.6的環境，從資料庫伺服器本地登錄，一切正常，可是若從遠程伺服器訪問，就會報錯， ERROR 1045 (28000): Access denied for user 'bisal'@'x.x.x.x' (using password: YES) 我才開始接觸My ...
今兒有位同事提出，一套MySQL 5.6的環境，從資料庫伺服器本地登錄，一切正常，可是若從遠程伺服器訪問，就會報錯，
ERROR 1045 (28000): Access denied for user 'bisal'@'x.x.x.x' (using password: YES)
我才開始接觸MySQL，因此每一個錯誤場景，都是增長經驗的機會，這種錯誤要麼是密碼錯誤，要麼是未設置遠程IP訪問許可權。
我們模擬下這個過程，首先，創建用戶bisal，如果密碼不加引號會報錯，
mysql> create user bisal identified by bisal;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bisal' at line 1
創建完成，可以看出，用戶bisal的host是%，不是具體某個IP，
mysql> create user bisal identified by 'bisal';
Query OK, 0 rows affected (0.00 sec)
mysql> select user, password, host from user;
+-------+-------------------------------------------+-----------------+
| user | password | host |
+-------+-------------------------------------------+-----------------+
...
| bisal | *9AA096167EB7110830776F0438CEADA9A7987E31 | % |+-------+-------------------------------------------+-----------------+
實驗一：讓指定IP訪問資料庫
假設資料庫伺服器IP是x.x.x.1，授權讓x.x.x.3用戶可以訪問，
mysql> grant all privileges on *.* to 'bisal'@'x.x.x.3';
Query OK, 0 rows affected (0.00 sec)
此時從x.x.x.2上訪問資料庫，就會提示錯誤，因為僅允許x.x.x.3伺服器，可以訪問資料庫，
mysql -h x.x.x.1 -ubisal
ERROR 1045 (28000): Access denied for user 'bisal'@'app' (using password: YES)
授權讓x.x.x.2用戶可以訪問，
mysql> grant all privileges on *.* to 'bisal'@'x.x.x.2' identified by 'bisal';
Query OK, 0 rows affected (0.00 sec)
此時從x.x.x.2上，就可以訪問資料庫了，
mysql -h x.x.x.1 -ubisal -pbisal
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1008
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
實驗二：讓所有IP訪問資料庫
首先，收回剛纔的授權，
mysql> revoke all privileges on *.* from bisal@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for bisal;
+--------------------------------------------------------------------------------------------+
| Grants for bisal@% |
+--------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'bisal'@'%' IDENTIFIED BY PASSWORD '*9AA096167EB7110830776F0438CEADA9A7987E31' |
+--------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
此時從x.x.x.2訪問資料庫，會提示錯誤，
mysql -h x.x.x.x -ubisal -pbisal
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 997
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> use mysql
ERROR 1044 (42000): Access denied for user 'bisal'@'%' to database 'mysql'
此時授予%所有機器訪問許可權，
mysql> grant all privileges on *.* to 'bisal'@'%' identified by 'bisal';
Query OK, 0 rows affected (0.00 sec)
從x.x.x.2訪問資料庫，此處的報錯，是因為未輸入密碼，
mysql -ubisal
ERROR 1045 (28000): Access denied for user 'bisal'@'localhost' (using password: YES)
但如果之前設置的密碼，和輸入的密碼不同，還是會提示錯誤，
mysql> grant all privileges on *.* to 'bisal'@'%' identified by '123';
Query OK, 0 rows affected (0.00 sec)
[root@vm-kvm11853-app ~]# mysql -h x.x.x.129 -ubisal -pbisal
Warning: Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'bisal'@'vm-kvm11853-app' (using password: YES)
使用正確的密碼登錄，一切正常了，
mysql -ubisal -p123
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 987
Server version: 5.6.31-log MySQL Community Server (GPL)
Copyright (c) 2000, 2016, 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> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
總結：
1. MySQL中可以設置某個IP訪問許可權，也可以設置%所有IP訪問許可權。、
2. grant all privileges ... identified by 'password'，此處的password可以不是這用戶的密碼，遠程訪問以這個密碼為準。
3. create user設置密碼，需要用引號括起來，否則會提示語法錯誤。
4. create user用戶不加@信息，則預設創建的用戶host是%。
