MySQL远程访问权限的设置

今儿有位同事提出,一套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是%。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券