美文网首页
允许远程连接Mysql/远程连接mysql报错1130

允许远程连接Mysql/远程连接mysql报错1130

作者: 闫松林 | 来源:发表于2017-12-29 09:46 被阅读0次

MySQL5.7

CHARACTER SET utf8 COLLATE utf8_general_ci

use mysql;
CREATE USER 'reader'@'%' IDENTIFIED BY '123456';
grant all privileges on mall.* to 'reader'@'%';
flush privileges;

进入mysql
切换到use表
更新host为%
执行修改

mysql -uroot -p   
psaaword:
use mysql;
 update user set host = '%' where user = 'root';
flush privileges;

[client]

default-character-set=utf8

[mysqld]
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
lower_case_table_names=1

grant all privileges on guoya_vitual_mall.* to 'test'@'%' identified by '123456..';

grant all privileges on guoya_vitual_mall.* to 'test1'@'%' identified by '123456';

安装MySQL

sudo mysql -u root #使用root用户权限登录mysql

SELECT User,Host FROM mysql.user; #查看用户与host信息
+------------------+-----------+
| User | Host |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)

DROP USER 'root'@'localhost'; #删除root账号
Query OK, 0 rows affected (0.00 sec)

CREATE USER 'root'@'%' IDENTIFIED BY '****'; #添加root账户并设置登录密码,'****'符号内为密码
Query OK, 0 rows affected (0.00 sec)

4.为root用户授权,使其具有可以创建修改数据库等的权限
GRANT ALL PRIVILEGES ON . TO 'root'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)
CREATE USER 'test'@'%' IDENTIFIED BY '123456..';
create database guoya_teach_test;
FLUSH PRIVILEGES; #重新加载权限
Query OK, 0 rows affected (0.00 sec)

为用户授权

授权格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.1 登录MYSQL(有ROOT权限),这里以ROOT身份登录:

@>mysql -u root -p

@>密码

2.2 首先为用户创建一个数据库(testDB):

mysql>create database testDB;

2.3 授权test用户拥有testDB数据库的所有权限(某个数据库的所有权限):

mysql>grant all privileges on testDB.* to test@localhost identified by '1234';
  mysql>flush privileges;//刷新系统权限表

格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by "密码";

2.4 如果想指定部分权限给一用户,可以这样来写:

mysql>grant select,update on testDB.* to test@localhost identified by '1234';

mysql>flush privileges; //刷新系统权限表

2.5 授权test用户拥有所有数据库的某些权限:

mysql>grant select,delete,update,create,drop on . to test@"%" identified by "1234";

 //test用户对所有数据库都有select,delete,update,create,drop 权限。

//@"%" 表示对所有非本地主机授权,不包括localhost。(localhost地址设为127.0.0.1,如果设为真实的本地地址,不知道是否可以,没有验证。)

//对localhost授权:加上一句grant all privileges on testDB.* to test@localhost identified by '1234';即可。

相关文章

网友评论

      本文标题:允许远程连接Mysql/远程连接mysql报错1130

      本文链接:https://www.haomeiwen.com/subject/matigxtx.html