本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer，已开源在github ，欢迎star！
今儿有位同事提出，一套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
总结：
MySQL中可以设置某个IP访问权限，也可以设置%所有IP访问权限。、
grant all privileges … identified by ‘password’，此处的password可以不是这用户的密码，远程访问以这个密码为准。
create user设置密码，需要用引号括起来，否则会提示语法错误。
create user用户不加@信息，则默认创建的用户host是%。
如果您觉得此篇文章对您有帮助，欢迎关注微信公众号：bisal的个人杂货铺，您的支持是对我最大的鼓励！共同学习，共同进步:)
