专栏首页数据之美详解 MySQL 5.7 新的权限与安全问题

详解 MySQL 5.7 新的权限与安全问题

1、新版 MySQL 权限问题: 

问题:SQL Error (1130): Host '192.168.1.100' is not allowed to connect to this MySQL server     说明所连接的用户帐号没有远程连接的权限,只能在本机(localhost)登录。     需更改 mysql 数据库里的 user表里的 host项:把localhost改称%     mysql>use mysql;     mysql>update user set host = '%'  where user ='root';     mysql>flush privileges;     mysql>select 'host','user' from user where user='root';     旧版本 MySQL 可以 IP 授权与修改密码同时进行: GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123456' WITH GRANT OPTION;  注意0:授权可以用上述语句,但是修改密码新版 MySQL 不能再用 WITH GRANT OPTION 了:Using GRANT statement to modify existing user properties other than privileges is deprecated and will be removed in future release. Use ALTER USER statement for this operation. ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';

注意1:MySQL 用户权限标示是 user 和 host 组成的二元组,上述语句需要确保该二元组存在,否则会报错:

    mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'pwd';     ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'%'     mysql> create user 'root'@'%' identified by 'pwd';     Query OK, 0 rows affected (0.00 sec)

    mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;     Query OK, 0 rows affected (0.00 sec)

    mysql>  show grants for current_user();     +---------------------------------------------------------------------+     | Grants for root@localhost                                           |     +---------------------------------------------------------------------+     | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |     | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |     +---------------------------------------------------------------------+     2 rows in set (0.00 sec)     如果使用 ALTER USER 'root'@'%' IDENTIFIED WITH sha256_password BY 'pwd'; 指定加密方式则可能在客户端连接时有问题: 注意2:ERROR 2059 (HY000): Authentication plugin 'sha256_password' cannot be loaded: No such file or directory update user set plugin='mysql_native_password' where user = 'root' and host = '%';     update mysql.user set password=PASSWORD("pwd") where User='root'; 注意3:ERROR 1054 (42S22): Unknown column 'password' in 'field list' update mysql.user set authentication_string=password("pwd") where user='root'; 注意4:'PASSWORD' is deprecated and will be removed in a future release.      password 即将被废弃,官方不建议用继续使用了,建议使用第1点中的 ALTER USER 语法去管理用户属性。

Access denied for user 'root'@'IP地址' ,是因为相应的主机没有对应的访问权限

--开放权限如下
use mysql;
update user u set u.host = '%' where u.user = 'root' limit 1;
flush privileges;

--查看用户权限
show grants for current_user();

--mysql不推荐通过修改表的方式修改用户密码
INSERT or UPDATE statements for the mysql.user table that refer to literal passwords are logged as is,so you should avoid such statements
--通过客户端sql修改
MariaDB [mysql]>  UPDATE user SET Password = password('123456') WHERE User = 'root' ;
--此时可在binglog中可以看到明文的密码
[root@rudy_01 3306]# mysqlbinlog binlog.000006 --start-position=4224 >/tmp/test.sql
[root@rudy_01 3306]# cat /tmp/test.sql 
SET @@session.collation_database=DEFAULT/*!*/;
UPDATE user SET Password = password('123456') WHERE User = 'root'

--在 mysql 5.7 中 password 字段已经不存在了
mysql> UPDATE user SET Password = password('123456') WHERE User = 'root' ;
ERROR 1054 (42S22): Unknown column 'Password' in 'field list'

mysql> desc user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                  | Type                              | Null | Key | Default               | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                   | char(60)                          | NO   | PRI |                       |       |
| User                   | char(32)                          | NO   | PRI |                       |       |
| Select_priv            | enum('N','Y')                     | NO   |     | N                     |       |

--注意出于安全考虑,alter user 时提示更新的是 0 条数据,但实际 password 已更新
mysql> select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string                     | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| %    | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | 2016-01-08 15:38:13   |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.04 sec)

--提示更新0条,使用此方法不需要再 flush privileges
If you modify the grant tables indirectly using account-management statements such as GRANT, REVOKE,SET PASSWORD, or RENAME USER, 
the server notices these changes and loads the grant tables into memory again immediately.
mysql>  alter user 'root'@'%' identified by '12345678';
Query OK, 0 rows affected (0.00 sec)
--实际已更新
mysql>  select host,user,authentication_string,password_last_changed from user where user='root' and host='%';
+------+------+-------------------------------------------+-----------------------+
| host | user | authentication_string                     | password_last_changed |
+------+------+-------------------------------------------+-----------------------+
| %    | root | *84AAC12F54AB666ECFC2A83C676908C8BBC381B1 | 2016-01-08 15:53:09   |
+------+------+-------------------------------------------+-----------------------+
1 row in set (0.00 sec)

--在binlog中查出的sql如下
[root@rudy mysql]# cat /tmp/test.sql
SET @@session.collation_database=DEFAULT/*!*/;
ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9'


--mysql对于密码有3种检验策略,默认validate_password_policy为MEDIUM
? LOW policy tests password length only. Passwords must be at least 8 characters long.
? MEDIUM policy adds the conditions that passwords must contain at least 1 numeric character, 1 lowercase and uppercase character, and 1 special (nonalphanumeric) character.
? STRONG policy adds the condition that password substrings of length 4 or longer must not match words

--注意validate_password默认是没有安装的
If the validate_password plugin is not installed, the validate_password_xxx system variables are not available, 
passwords in statements are not checked, and VALIDATE_PASSWORD_STRENGTH() always returns 0.
 
 
--检验密码复杂度 
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.00 sec)
--查找安装的插件,发现找不到validate_password
mysql> show plugins;
--手动安装
mysql> INSTALL PLUGIN validate_password SONAME 'validate_password.so';
mysql> show plugins;
+----------------------------+----------+--------------------+----------------------+---------+
| Name                       | Status   | Type               | Library              | License |
+----------------------------+----------+--------------------+----------------------+---------+
| validate_password          | ACTIVE   | VALIDATE PASSWORD  | validate_password.so | GPL     |
+----------------------------+----------+--------------------+----------------------+---------+
45 rows in set (0.04 sec)
--再次检验密码复杂度 
mysql> select VALIDATE_PASSWORD_STRENGTH('abc1235jeme');
+-------------------------------------------+
| VALIDATE_PASSWORD_STRENGTH('abc1235jeme') |
+-------------------------------------------+
|                                        50 |
+-------------------------------------------+

--安装validate_password插件后,就必需符合validate_password_policy的要求,否则语句执行出错
mysql> alter user 'root'@'%' identified by '123456';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

2、主从复制遇到的权限及异常问题

1、主从均需重启mysql服务
/etc/init.d/mysqld restart
或者:
sudo service mysqld restart 

2、主配置:增加从机复制账户并授权,以便从机远程登录过来复制 binlog
create user 'replicationUsername'@'%' identified by 'Passwd';
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO 'name'@'%'  WITH GRANT OPTION;
show master status;

3、从配置:
stop slave;
CHANGE MASTER TO
  MASTER_HOST='110.126.103.126',
  MASTER_USER='replicationUsername',
  MASTER_PASSWORD='Passwd',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=154,
  MASTER_CONNECT_RETRY=10;
start slave;
show slave status\G

4、Slave_SQL_Running: No
    1.程序可能在slave上进行了写操作
    2.也可能是slave机器重起后,事务回滚造成的.
    3.也可能遇到各种SQL错误导致 SQL 线程中断退出。
    解决方法:
stop slave;
set global sql_slave_skip_counter = 1 ;
start slave;
    之后Slave会和Master去同步 主要看:
从机:show slave status\G
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master是否为0,0就是已经同步了
主机:show processlist\G
如果出现Command: Binlog Dump,则说明配置成功.

5、测试:
create database db_test_slave;  
use db_test_slave;  
create table tb_test(id int(3), name varchar(50));  
insert into tb_test values(1,'hello slave');  
show databases;  

6、slave 从零开始同步 master 所有数据库:
(1)master操作:
RESET MASTER;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

candidates=$(echo "show databases" | mysql -uroot -pPASSWD| grep -Ev "^(Database|sys|mysql|performance_schema|information_schema)$")
mysqldump -uroot -pPASSWD --databases ${candidates} --single-transaction > mysqldump.sql
UNLOCK TABLES;

(2)slave操作:
nc -l 12345 < <(cat mysqldump.sql) ##主
nc -n 10.48.186.32 12345 > mysqldump.sql 

STOP SLAVE;
mysql -uroot -pPASSWD < mysqldump.sql

show master status; --主

CHANGE MASTER TO
  MASTER_HOST='10.48.186.32',
  MASTER_USER='birepl',
  MASTER_PASSWORD='PASSWD',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mysql-bin.000001',
  MASTER_LOG_POS=398062,
  MASTER_CONNECT_RETRY=10;
RESET SLAVE;
start slave;
SHOW SLAVE STATUS\G

Refer:

[1] mysql 权限与安全

https://yq.aliyun.com/articles/2719

[2] mysql-5.7主从同步安装配置

http://www.apelearn.com/bbs/thread-13435-1-1.html

[3] CentOS 7 下MySQL 5.7.12主从复制架构配置记录(亲自验证可行)

http://www.voidcn.com/blog/juan0728juan/article/p-6050119.html

[4] MySQL 5.7的多源复制

http://www.cnblogs.com/xuanzhi201111/p/5151666.html

[5] Slave_SQL_Running: No mysql同步故障解决方法

http://kerry.blog.51cto.com/172631/277414

[6] Slave_SQL_Running: No mysql同步故障解决方法

http://blog.csdn.net/xiaoxinla/article/details/7679578

[7] 有没有办法让从msyql主动从零开始在主mysql那里同步数据

https://www.v2ex.com/t/78848

[8] How to re-sync the Mysql DB if Master and slave have different database incase of Mysql replication?

http://stackoverflow.com/questions/2366018/how-to-re-sync-the-mysql-db-if-master-and-slave-have-different-database-incase-o

[9] Any option for mysqldump to ignore databases for backup?

http://dba.stackexchange.com/questions/35081/any-option-for-mysqldump-to-ignore-databases-for-backup

[10] mysql的binlog详解

http://blog.csdn.net/wyzxg/article/details/7412777

[11] 在什么时候可以调用reset master?

http://bbs.chinaunix.net/thread-1199047-1-1.html

[12] mysql只读模式的设置方法与实验

http://blog.csdn.net/yumushui/article/details/41645469

[13] MySql 创建只读账号

http://blog.csdn.net/norsd/article/details/9081833

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 基于Docker的持续集成方案(安装和配置MySql) - Part.3

    这篇文章介绍了使用Docker安装MySql数据库的操作步骤,以及安装完成后如何对MySql进行配置。

    张子阳
  • MySQL企业版线上专场 | 三合一精华版

    2020年4月7~9日,MySQL团队与3306π社区联合举办大型线上活动,为广大MySQL的爱好者和使用者带来一场MySQL技术盛宴。活动期间,叶金荣老师每晚...

    MySQLSE
  • 手把手教你在centos上安装MySQL(避坑必备)

    在上篇文章中手把手教你在centos上配置Django项目(超详细步骤) 已经非常详细的讲解了centos部署Django的过程。

    Python进击者
  • 通过yum在centos安装mysql并配置远程登录

    前天按照Oracle上的文档装了一遍mysql,选了最新8.0的版本,后来出现一些问题,网上搜答案,出来的基本还是5.x版本的解决方案,并不适用8.0版本。然后...

    _淡定_
  • centos通过yum安装mysql

    前天按照Oracle上的文档装了一遍mysql,选了最新8.0的版本,后来出现一些问题,网上搜答案,出来的基本还是5.x版本的解决方案,并不适用8.0版本。然后...

    _淡定_
  • 如何在Ubuntu 14.04上安装MySQL

    MySQL是一个开源数据库管理系统,通常作为流行的LAMP(Linux,Apache,MySQL,PHP / Python / Perl)堆栈的一部分来进行安装...

    陈树丶
  • docker安装mysql及navicat远程连接

    华创信息技术
  • 从零搭建java后台管理系统(二)mysql和redis安装

    老梁
  • 腾讯黑科技:删大表不抖动,自动补充虚拟隐藏主键

    ☆  点击▲关注 腾讯云数据库 ☆ ---- ? 2019年9月,腾讯云数据库正式按地域发布TXSQL 5.7-201908版本,该版本主要实现写性能提升,...

    腾讯云数据库 TencentDB
  • MySQL安全性解决方案

    随着数据爆发式的增长,安全性已经是用户头等重要的工作,数据已经成为其最重要的资产。

    MySQLSE
  • MySQL数据库1初识MySQL

    MySQL软件官方下载地址(https://dev.mysql.com/downloads/mysql/),个人感觉下载压缩包版比下载安装包办的要好,因为安装包...

    GH
  • MySQL 5.7.30 的安装/升级(所有可能的坑都在这里) 楔子卸载老版本安装5.7.30版本MySQL老数据升级到5.7

    由于之前电脑上安装的MySQL版本是比较老的了,大概是5.1的版本,不支持JSON字段功能。而最新开发部门开发的的编辑器产品,使用到了JSON字段的功能。 因此...

    用户3158888
  • mysql ERROR 2002 (HY000): '/tmp/mysql.sock' 问题解决

    一直用的好好的,突然就不能用了, 然后在网上搜了一圈,什么改配置my.cnf, 什么改软连接啊,换用5.7版本,都用了一次,结论是都不对.

    solate
  • CentOS7mysql5.6升级5.7

    yum -y remove mysql-libs.x86_64 yum -y remove mysql-*

    崔笑颜
  • 【 腾讯云的1001种玩法 】腾讯云数据库优化最佳实战:以 TXSQL 为例

    TXSQL-Tencent MySQL 自从2016年5月份正式立项,在近一年的时间里对 MySQL 的读写性能、强同步、大并发量访问和稳定性等方面做了大量工作...

    musazhang
  • 技术分享 | 如何优雅地在 Windows 上从 MySQL 5.6 升级到 5.7

    爱可生 MySQL DBA 团队成员,Oracle 10g OCM,MySQL 5.7 OCP,擅长数据库性能问题诊断、事务与锁问题的分析等,负责处理客户 My...

    爱可生开源社区
  • MySQL 5.7 新特性之二

    本系列基于5.7.20 版本来讲述MySQL的新特性,从安装,文件结构,SQL ,优化 ,运维层面 复制,等几个方面展开介绍5.7 的新特性和功能,同时也建议大...

    用户1278550
  • Linux安装mysql5.7.26 --(傻瓜版3分钟搞定)

    在这之前的一天时间里,我全网搜mysql 的各种安装方式,还有版本不同带来的问题,会发现在 Mac 或者 在linux上安装5.7 一下版本时,出现的问题会少很...

    胖虎
  • MySQL的疑难问题解决

    1.https://www.cnblogs.com/xujishou/p/6306765.html(MySQL5.7 添加用户、删除用户与授权) 2.htt...

    星尘的一个朋友

扫码关注云+社区

领取腾讯云代金券