MySql允许root远程连接，以及用户相关的几个常用命令

有时候需要使用root用户远程连接数据库，MySql默认是不允许root用户远程连接的，需要设置

1、查看用户和用户的权限

[mysql]> select user, authentication_string, password, host from user;
+---------+-----------------------+-------------------------------------------+---------------+
| user    | authentication_string | password                                  | host          |
+---------+-----------------------+-------------------------------------------+---------------+
| root    |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | localhost     |
| root    |                       |                                           | 127.0.0.1     |
| root    |                       |                                           | ::1           |
|         |                       |                                           | localhost     |
| sst     |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | %             |
| haproxy |                       |                                           | %             |
| root    |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | 10.110.30.170 |
| sst     |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | localhost     |
+---------+-----------------------+-------------------------------------------+---------------+

可以看到上面的root用户的host只有本机，只能本机访问不能远程访问

2、配置远程访问权限，登录数据库后执行

# 配置root允许所有连接（%），密码是XXXXX
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'XXXXX' WITH GRANT OPTION;

# 如果只方形某个IP，可以直接指定IP
GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.110.30.60' IDENTIFIED BY 'XXXXX' WITH GRANT OPTION;

# 刷新使权限生效
flush privileges;

# 再次查看权限，可以看到已经生效了
[mysql]> select user, authentication_string, password, host from user;
+---------+-----------------------+-------------------------------------------+---------------+
| user    | authentication_string | password                                  | host          |
+---------+-----------------------+-------------------------------------------+---------------+
| root    |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | localhost     |
| root    |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | %             |
| root    |                       |                                           | 127.0.0.1     |
| root    |                       |                                           | ::1           |
|         |                       |                                           | localhost     |
| sst     |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | %             |
| haproxy |                       |                                           | %             |
| root    |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | 10.110.30.170 |
| sst     |                       | *B5363C68BA208552A8F8AB110809DC2483BACE05 | localhost     |
| root    |                       | *03A59A80F51CC4F34A7939EABAB38AC65043E2EB | 10.110.30.60  |
+---------+-----------------------+-------------------------------------------+---------------+
10 rows in set (0.00 sec)

 

--------------------------------------------------------------------------------------

后来装MariaDB10.0.21的时候，只执行上面的初始化密码命令不太好使了，又加了一条更新authentication_string的操作，例如下面是将root的密码改成xxxxx，测试是能成功的

如果执行上面的方法不好使，可以再尝试一下这个

mysql -uroot -pxxxxx <<EOF
  USE mysql;
  UPDATE user SET authentication_string=password('xxxxx'),plugin='mysql_native_password' WHERE user='root';
  GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxx' WITH GRANT OPTION;
  FLUSH PRIVILEGES;
EOF

 

用户权限相关的常用命令

## 1、创建db1数据库
CREATE database if NOT EXISTS db1 default character set utf8 collate utf8_general_ci;

## 2、创建用户
# 指定ip：10.110.30.2用testuser用户登录
create user 'testuser'@'10.110.30.2' identified by 'xxxxx';
# 指定ip：192.118.1.开头的用testuser用户登录
create user 'testuser'@'10.110.30.%' identified by 'xxxxx';
# 指定任何ip用testuser用户登录
create user 'testuser'@'%' identified by 'xxxxx';

## 3、删除用户
drop user '用户名'@'IP地址';

## 4、修改密码
set password for '用户名'@'IP地址'=Password('新密码');

## 5、查看用户权限
show grants for '用户'

## 6、授权testuser用户仅对db1.t1有查询、插入和更新的操作
grant select, insert, update on db1.t1 to 'testuser'@'%';

## 7、授权testuser用户对db1数据库中的文件执行任何操作
grant all privileges on db1.* to 'testuser'@'%';

## 8、授权testuser用户对所有数据库中的文件执行任何操作
grant all privileges on *.*  to 'testuser'@'%';
# 授权同时修改密码
GRANT ALL PRIVILEGES ON *.* TO 'testuser'@'%' IDENTIFIED BY 'xxxxxx' WITH GRANT OPTION;

## 9、取消testuser用户对数据库db1所有表的所有权限
revoke all on db1.* from 'testuser'@"%"; 

## 10、取消testuser用户对所有数据库的所有权限 
revoke all privileges on *.* from 'testuser'@'%';

 

 

testuser