标签 mysql 下的文章

前言

作为世界上最流行的开源数据库,MySQL各方面的功能都在不断完善,比如密码管理这一块,从一开始最简单的用户名密码、到 5.7 版本的 validate_password 插件、再到 8.0 版本丰富多彩的密码策略,已经完全可以媲美 DB2、Oracle、SQL Server 等大型商业数据库;今天就给大家带来 MySQL 8.0 新特性 — 密码管理。

密码管理

在MySQL 8.0版本中,针对密码管理这一块,做出了非常大的改进与完善,支持以下功能:

  1. 密码认证插件
  2. 密码过期策略
  3. 密码复用策略
  4. 密码修改验证策略
  5. 双重密码支持
  6. 密码强度策略
  7. 随机密码生成
  8. 登录失败追踪

密码认证插件

在MySQL 8.0版本中,支持以下3种密码认证插件:

(1)mysql_native_password:8.0之前默认

(2)caching_sha2_password:8.0默认

(3)sha256_password:可选

caching_sha2_password 作为 8.0 默认的密码认证插件,其安全性强于 mysql_native_password,性能优于sha256_password。但由于客户端和驱动的兼容性问题,建议还是采用 mysql_native_password 作为默认的密码认证插件。

如果采用 caching_sha2_password 作为默认的密码认证插件,那么在建立连接时,一定要指定RSA公钥,否则会报错 "ERROR 2061 (HY000): Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection."

密码过期策略

密码过期策略,可以通过系统参数进行设置,对所有用户生效

vim /etc/mysql/my.cnf

default_password_lifetime=180

也可以通过SQL语句,对指定用户进行设置

alter user [email protected]'%' password expire interval 180 day; -- 密码有效期180天
alter user [email protected]'%' password expire never;            -- 密码永不过期
alter user [email protected]'%' password expire default;          -- 采用默认的密码有效期

还可以手动指定某用户过期

mysql > alter user [email protected]'%' password expire;

密码复用策略

密码复用策略,可以通过系统参数进行设置,对所有用户生效

vim /etc/mysql/my.cnf

password_history = 6            -- 密码多少次不重复
password_reuse_interval = 365   -- 密码多少天不复用

也可以通过SQL语句,对指定用户进行设置

mysql > alter user [email protected]'%' password history 6;              -- 密码6次不复用
mysql > alter user [email protected]'%' password reuse interval 365 day; -- 密码365天不复用
mysql > alter user [email protected]'%' password history default reuse interval default; -- 采用默认复用策略

密码修改验证策略

密码修改前,需要指定旧密码,才能进行修改;这也是大大提高了安全性,大大降低了 客户端记住密码/临时离开工位 导致密码被别人篡改的风险。

可以通过系统参数进行设置,对所有用户生效

vim /etc/mysql/my.cnf

password_require_current = on --启用密码修改验证策略

也可以通过SQL语句,对指定用户进行设置

mysql > alter user [email protected]'%' password require current;          -- 启用密码修改验证策略
mysql > alter user [email protected]'%' password require optional;         -- 密码验证可选
mysql > alter user [email protected]'%' password require current default;  -- 采用默认密码验证策略

启用密码修改验证策略后,修改密码需要提供旧密码和新密码

mysql > alter user [email protected]'%' identified by 'new_password' replace 'old_password';

双重密码支持

相信很多的开发、管理员和 DBA 都遇见过这样一个场景:修改完数据库用户密码,业务系统某个/某些模块立刻不可用,报密码错误;其实这种情况很常见,业务系统模块那么多,有时候会有遗漏不奇怪。双重密码,支持旧密码和新密码同时登录,就可以很好地规避这个问题,平滑地实现密码修改。

保留旧密码

mysql > alter user [email protected]'%' identified by 'new_password' retain current password;

废弃旧密码

mysql > alter user [email protected]'%' discard old password;

密码强度策略

在 MySQL 5.7 版本中,提供了 validate_password 的插件,实现密码强度策略;在 MySQL 8.0 版本中,官方是将validate_password 改造成组件,并提供一系列系统参数,以实现密码强度策略。

首先,我们需要安装 validate_password 组件,通过以下SQL语句

mysql > INSTALL COMPONENT 'file://component_validate_password';
mysql > UNINSTALL COMPONENT 'file://component_validate_password';

然后,我们可以通过以下系统参数,进行密码强度策略设置:

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+--------+
| Variable_name                        | Value  |
+--------------------------------------+--------+
| validate_password.check_user_name    | ON     |
| validate_password.dictionary_file    |        |
| validate_password.length             | 8      |
| validate_password.mixed_case_count   | 1      |
| validate_password.number_count       | 1      |
| validate_password.policy             | MEDIUM |
| validate_password.special_char_count | 1      |
+--------------------------------------+--------+
7 rows in set (0.06 sec)

关于 mysql 密码策略相关参数:

validate_password.length  固定密码的总长度;
validate_password.dictionary_file 指定密码验证的文件路径;
validate_password.mixed.case_count  整个密码中至少要包含大/小写字母的总个数;
validate_password.number_count  整个密码中至少要包含阿拉伯数字的个数;
validate_password_special_char_count 整个密码中至少要包含特殊字符的个数;
validate_password.policy 指定密码的强度验证等级,默认为 MEDIUM;
    关于 validate_password.policy 的取值:
        0/LOW:只验证长度;
        1/MEDIUM:验证长度、数字、大小写、特殊字符;
        2/STRONG:验证长度、数字、大小写、特殊字符、字典文件;
mysql> set global validate_password.length=4;
mysql> set global validate_password.policy='LOW';

修改密码验证策略后我们就可以使用简单的密码进行登录了。

密码随机生成

在 MySQL 8.0 中,还支持随机密码生成

mysql > alter user [email protected]'%' identified by random password;

可以通过以下系统参数,对随机密码生成的长度进行控制

generated_random_password_length = 20  -- 随机密码生成长度

登录失败追踪

最后,再来看一下登录失败的问题;我们可以设置连续登录失败多少次,账号会被锁定多少天,具体如下:

mysql > alter user [email protected]'%' failed_login_attempts 3 password_lock_time 3; -- 失败3次锁定3天

总结

在 MySQL 8.0 版本中,密码管理这一块功能日趋完善,大大提高了MySQL数据库的安全性,尤其对政务行业、金融行业等监管要求高的企业来说,更具有吸引力。

explain sql语句 可以用来分析索引的一些情况:

idselect_typetabletypepossiable_keyskeykey_lenref

TYPE

显示子查询使用何种类型的查询

从最好到最差依次是:system > const > eq_ref > ref > rang > index > ALL

一般的优化需要达到 `rang` 级别,最好是 `ref` 级别

system

单个表中的一行记录,const 类型的特例,查询速度最快

const

通过索引一次就能找到,通常是查询主键查询或者唯一查询 (primay_key , unique)

eq_ref

唯一索引扫描,用索引查询出了一条记录 select * from users where phone = ''

ref

非唯一索引表,用到了索引查出了多条记录 select * from users where age = 18

- 阅读剩余部分 -

与关系型数据库相比,MongoDB的优点:

1. 弱一致性(最终一致),保证用户的访问速度:

举例来说,在传统的关系型数据库中,一个 COUNT 类型的操作会锁定数据集,这样可以保证得到“当前”情况下的精确值。

这在某些情况下,例如:通过 ATM 查看账户信息的时候很重要,但对于 Wordnik 来说,数据是不断更新和增长的,这种精确的保证几乎没有任何意义,反而会产生很大的延迟。他们需要的是一个大约的数字以及更快的处理速度。

但某些情况下 MongoDB 会锁住数据库。如果此时正有数百个请求,则它们会堆积起来,造成许多问题。我们使用了下面的优化方式来避免锁定:

2. 每次更新前,我们会先查询记录。

查询操作会将对象放入内存,于是更新则会尽可能的迅速。在主/从部署方案中,从节点可以使用“-pretouch”参数运行,这也可以得到相同的效果。

使用多个 mongod 进程。我们根据访问模式将数据库拆分成多个进程。

文档结构的存储方式,能够更便捷的获取数据。

对于一个层级式的数据结构来说,如果要将这样的数据使用扁平式的,表状的结构来保存数据,这无论是在查询还是获取数据时都十分困难。

举例1:

就拿一个“字典项”来说,虽然并不十分复杂,但还是会关系到“定义”、“词性”、“发音”或是“引用”等内容。大部分工程师会将这种模型使用关系型数据库
中的主键和外键表现出来,但把它看作一个“文档”而不是“一系列有关系的表”岂不更好?使用
“dictionary.definition.partOfSpeech='noun'”来查询也比表之间一系列复杂(往往代价也很高)的连接查询方便
且快速。

举例2:

在一个关系型数据库中,一篇博客(包含文章内容、评论、评论的投票)会被打散在多张数据表中。在 MongoDB 中,能用一个文档来表示一篇博客,
评论与投票作为文档数组,放在正文主文档中。这样数据更易于管理,消除了传统关系型数据库中影响性能和水平扩展性的“JOIN”操作。

db.blogposts.save({
  title : "My First Post", author: {name : "Jane", id :1},
  comments : [
    { by: "Abe", text: "First" },
    { by : "Ada", text : "Good post" }
  ]
})
db.blogposts.find({ "author.name" : "Jane" })
db.blogposts.findOne({
  title : "My First Post",
  "author.name": "Jane",
  comments : [
    { by: "Abe", text: "First" },
    { by : "Ada", text : "Good post" }
  ]
})
db.blogposts.find({ "comments.by" : "Ada" })
db.blogposts.ensureIndex({ "comments.by" : 1 });

举例3:

MongoDB 是一个面向文档的数据库,目前由 10gen 开发并维护,它的功能丰富,齐全,完全可以替代 MySQL。在使用 MongoDB 做产品原型的过程中,我们总结了 MonogDB 的一些亮点:

1、使用 JSON 风格语法,易于掌握和理解

MongoDB 使用 JSON 的变种 BSON 作为内部存储的格式和语法。针对 MongoDB 的操作都使用 JSON 风格语法,客户端提交或接收的数据都使用 JSON 形式来展现。相对于 SQL 来说,更加直观,容易理解和掌握。

2、Schema-less,支持嵌入子文档:MongoDB 是一个 Schema-free 的文档数据库。

一个数据库可以有多个 Collection,每个 Collection 是 Documents 的集合。Collection 和 Document 和传统数据库的 Table 和 Row 并不对等。无需事先定义 Collection,随时可以创建。

3、Collection 中可以包含具有不同 schema 的文档记录。

这意味着,你上一条记录中的文档有3个属性,而下一条记录的文档可以有10个属性,属性的类型既可以是基本的数据类型(如数字、字符串、日期等),也可以是数组或者散列,甚至还可以是一个子文档(embed document)。这样,可以实现逆规范化(denormalizing)的数据模型,提高查询的速度。

4、内置 GridFS,支持大容量的存储。

GridFS 是一个出色的分布式文件系统,可以支持海量的数据存储。内置了 GridFS 的 MongoDB,能够满足对大数据集的快速范围查询。

5、内置Sharding。

提供基于Range的Auto Sharding机制:一个collection可按照记录的范围,分成若干个段,切分到不同的Shard上。Shards可以和复制结合,配合Replica sets能够实现Sharding+fail-over,不同的Shard之间可以负载均衡。

查询是对客户端是透明的。客户端执行查询,统计,MapReduce等操作,这些会被MongoDB自动路由到后端的数据节点。这让我们关注于自己的业务,适当的时候可以无痛的升级。

MongoDB 的 Sharding 设计能力最大可支持约 20 petabytes,足以支撑一般应用。这可以保证 MongoDB 运行在便宜的 PC 服务器集群上。PC 集群扩充起来非常方便并且成本很低,避免了“sharding”操作的复杂性和成本。

6、第三方支持丰富。(这是与其他的 NoSQL 相比,MongoDB 也具有的优势)

现在网络上的很多 NoSQL 开源数据库完全属于社区型的,没有官方支持,给使用者带来了很大的风险。而开源文档数据库 MongoDB 背后有商业公司 10gen 为其提供供商业培训和支持。而且MongoDB社区非常活跃,很多开发框架都迅速提供了对 MongDB 的支持。不少知名大公司和网站也在生产环境中使用MongoDB,越来越多的创新型企业转而使用 MongoDB 作为和 Django,RoR 来搭配的技术方案。

7、性能优越:
在使用场合下,千万级别的文档对象,近10G的数据,对有索引的 ID 的查询不会比 mysql 慢,而对非索引字段的查询,则是全面胜出。 mysql 实际无法胜任大数据量下任意字段的查询,而 mongodb 的查询性能实在让我惊讶。写入性能同样很令人满意,同样写入百万级别的数 据,mongodb 比我以前试用过的 couchdb 要快得多,基本10分钟以下可以解决。补上一句,观察过程中 mongodb 都远算不上是 CPU 杀手。

与关系型数据库相比,MongoDB 的缺点:

1、 mongodb 不支持事务操作。
所以事务要求严格的系统(如果银行系统)肯定不能用它。(这点和优点①是对应的)
2、 mongodb 占用空间过大。
关于其原因,在官方的FAQ中,提到有如下几个方面:

  • 空间的预分配:为避免形成过多的硬盘碎片,mongodb 每次空间不足时都会申请生成一大块的硬盘空间,而且申请的量从64M、128M、256M那 样的指数递增,直到 2G 为单个文件的最大体积。随着数据量的增加,你可以在其数据目录里看到这些整块生成容量不断递增的文件。
  • 字段名所占用的空间:为了保持每个记录内的结构信息用于查询,mongodb 需要把每个字段的key-value都以BSON的形式存储,如果 value 域相对于 key 域并不大,比如存放数值型的数据,则数据的 overhead 是最大的。一种减少空间占用的方法是把字段名尽量取短一些,这样占用空间就小了,但这就要求在易读性与空间占用上作为权衡了。我曾建议作者把字段名作个 index,每个字段名用一个字节表示,这样就不用担心字段名取多长 了。但作者的担忧也不无道理,这种索引方式需要每次查询得到结果后把索引值跟原值作一个替换,再发送到客户端,这个替换也是挺耗费时间的。现在的实现算是 拿空间来换取时间吧。
  • 删除记录不释放空间:这很容易理解,为避免记录删除后的数据的大规模挪动,原记录空间不删除,只标记“已删除”即可,以后还可以重复利用。
  • 可以定期运行 db.repairDatabase() 来整理记录,但这个过程会比较缓慢
  • MongoDB 没有如 MySQL 那样成熟的维护工具,这对于开发和IT运营都是个值得注意的地方。

作者:李天火
链接:https://www.jianshu.com/p/7a41907a1549
來源:简书
简书著作权归作者所有,任何形式的转载都请联系作者获得授权并注明出处。