有时候需要使用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