MySQL远程访问权限的设置

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 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访问权限。、
  1. grant all privileges … identified by ‘password’,此处的password可以不是这用户的密码,远程访问以这个密码为准。

  2. create user设置密码,需要用引号括起来,否则会提示语法错误。

  3. create user用户不加@信息,则默认创建的用户host是%。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

MySQL远程访问权限的设置

      MySQL远程访问权限的设置  MySQL远程访问权限的设置      

MySQL远程访问权限的设置
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> MySQL远程访问权限的设置


 上一篇
一个触发器需求的案例 一个触发器需求的案例
有一位兄弟,问了一问题, 用触发器实现一个功能,如果插入的字段AGE为空,则将此字段AGE的值置为0。 以下是一版实现, SQL create table t (id number, age number); Table creat
下一篇 
一个关于Definer和Invoker的权限问题 一个关于Definer和Invoker的权限问题
前两天有位朋友,微信公众号提了一个问题,原文描述如下, 我的需求是在tag库中执行一个处理,使得tag中所有用户seq的nextval与src库中一致。 我在tag库的user1中创建了一个存储过程,代码逻辑为通过dblink(指向s