MySQL如何控制用户输错密码尝试次数

jiezi

5 年前

生产环境 MySQL 死锁如何监控及如何减少死锁发生的概率
MongoDB 有哪些优秀特性及适合的场景是什么
GO 语言对比其他的编程语言有何优势？实际生产环境如何取舍？
一个大事务，有很多更新，现在被回滚了，但是又着急关机重启，怎么办才好？
如何降低 UPDATE/DELETE 时 WHERE 条件写错，或者压根没写 WHERE 条件带来的影响
MySQL 如何控制用户输错密码尝试次数？

一、生产环境 MySQL 死锁如何监控及如何减少死锁发生的概率

首先，死锁并不是 ” 锁死 ”，死锁是由于两个或两个以上会话锁等待产生回路造成。

（一）死锁监控及处理方法

对于死锁的监控，各个版本都提供了 innodb_print_all_deadlocks 选项，打开该选项即会将死锁的日志输出到 MySQL 的错误日志当中, 因此可以通过监控错误日志来达到监控死锁的目的。而对于 MariaDB 就更加简单了，MariaDB 提供了 Innodb_deadlocks 的计数器，可以通过监控该计数器的增长来监控是否存在发生死锁。
假如线上出现死锁并且频率较高的话，务必要引起重视。由于死锁日志仅记录了最后引起死锁的两条 SQL，因此并不能通过死锁日志立即定位
出死锁的原因，应当及时协同开发模拟出死锁过程，分析死锁产生原因，修改程序逻辑。

（二）如何降低死锁发生的概率

1、尽量使用短小事务，避免大事务
2、加FOR UPDATE/LOCK IN SHARE MODE 锁时，最好降低事务隔离级别，例如用 RC 级别，降低死锁发生概率，也可以降低锁定粒度
3、事务中涉及多个表，或者涉及多行记录时，每个事务的操作顺序都要保持一致
4、通过索引优化 SQL 效率，降低死锁概率，避免全表扫描导致锁定所有数据
5、程序中应有事务失败检测及自动重复提交机制
6、高并发（秒杀）场景中，关闭innodb_deadlock_detect 选项，降低死锁检测开销，提高并发效率

二、MongoDB 有哪些优秀特性及适合的场景是什么

（一）优秀特性

1、实用性：面向类 json 富文档数据模型，对开发人员天然的友好
2、可用性：基于 raft 协议的自动高可用，轻松提供 99.999% 的可用性
3、扩展性：对分片集群的支持，为业务提供了友好的水平扩展
4、高性能：嵌套模型设计支持，减少了离散写，充分的物理内存利用率，避免了磁盘读
5、强压缩：WiredTiger 引擎提供多种数据压缩策略，2~7 倍的压缩比，大大节省了磁盘资源

（二）适合的场景

1、无多文档事务及多表关联查询需求
2、业务快速迭代，需求频繁变动行业
3、单集群并发过大无法支撑业务增长
4、数据量增长预期 TB 及以上存储需求
5、期望要求 99.999% 数据库高可用场景

三、GO 语言对比其他的编程语言有何优势？实际生产环境如何取舍？

1、天生支持高并发，强一致语言，开发效率高兼具线上运行稳定安全
2、垃圾回收，不用关心内存分配与回收
3、强大的 GMP 模型，异步处理，支持高并发，小白也能轻松写出高并发代码

在实际生产环境中建议从如下几个方面考虑：

1、看业务场景，电商，大数据处理有现成的解决方案，不适合用。另外数学运算，cpu 密集型的也不用。
2、GO 擅长快速出业务原型，迭代开发效率高，初创公司强推
3、看公司开发的技术栈，如果差异较大，那么选用 GO 的话上手更快，编程风格也能统一起来

四、一个大事务，有很多更新，现在被回滚了，但是又着急关机重启，怎么办才好？

1、首先，尽量避免在 MySQL 中执行大事务，因为大事务将会带来主从复制延迟等问题
2、大事务被 kill，MySQL 会自动进行回滚操作，通过 show engine innodb status 的 TRANSACTIONS 可以看到 ROLLING BACK 的事务，并且在回滚操作的时候仍然会持有相应的行锁
3、此时如果强行关闭 MySQL，等到 MySQL 再次启动后，仍然会进行回滚动作
4、因此，为确保数据安全，建议还是耐心等待回滚完成以后再进行关机重启。关机重启前，可以调低innodb_max_dirty_pages_pct 让脏页尽量刷新完毕，并且关闭innodb_fast_shutdown
5、假如实在没有办法需要关机的情况下，可以 kill - 9 先关闭 MySQL，前提是需要设置双一保证事务安全，否则可能丢更多事务数据。然后重启实例后 innodb 会自行 crash recovery 回滚之前的事务

PS，kill - 9 是高危操作，可能导致 MySQL 无法启动等不可预知的问题，请谨慎使用

五、如何降低 UPDATE/DELETE 时 WHERE 条件写错，或者压根没写 WHERE 条件带来的影响

1、尽量不要在线手工执行任何 SQL 命令，很容易出差错。线上直接执行 SQL 命令最好有第二检查人帮助确认
2、最好在测试环境执行 SQL 确认无误后，再到生产环境执行，或者提前在本地文本环境编辑好确认后再执行
3、建议打开sql_safe_updates 选项，禁止没有 WHERE 条件或者不加 LIMIT 或者没有使用索引条件的 UPDATE/DELETE 命令被执行。也可以在用 mysql 客户端连接到服务器端时增加 --safe-updates 选项，例如：mysql --safe-updates -h xx -u xx
4、线上手动执行 DML 操作时，先开启事务模式，万一误操作可以回滚。例如：mysql> begin; update xxx; rollback;
5、通过 DB 管理平台执行 DML 操作，且在平台上增加对此类危险 SQL 的判断，直接拒绝危险 SQL 的执行
6、配置延迟从库，发现误删除数据后，从延迟从库快速恢复数据

六、MySQL 如何控制用户输错密码尝试次数？

（一）插件辅助

从官方 MySQL5.7.17 开始，提供了 CONNECTION_CONTROL 和CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS插件，该插件又提供了 connection_control_failed_connections_threshold、connection_control_min_connection_delay、connection_control_max_connection_delay 三个参数

1、connection_control_failed_connections_threshold
该参数的含义是控制登陆失败多少次数后开启延迟登陆

2、connectioncontrolminconnectiondelay
该参数分别表示超过失败次数后每次重新连接最小的延迟时间，延迟计算公式为 (当前失败总次数 - 失败阈
值)connectioncontrolminconnection_delay，因此错误尝试次数越多那么延迟时间也是越大

3、connection_control_max_connection_delay
最大延迟时间，超过该值后客户端可重新连接

4、安装插件后，可通过监控 Connection_control_delay_generated 状态值和 INFORMATION_SCHEMA 下的表 CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS 来监控错误登录尝试次数

（二）错误日志监控

通过定时扫描 MySQL 错误日志来捕获账号密码错误次数，达到某个阈值以后可在系统防火墙屏蔽对应的主机 ip 达到屏蔽账号的目的 (具体操作视情况而定)
如：错误日志会显示 2019-05-10T13:04:41.232259Z 5 [Note] Access denied for user ‘xucl’@’127.0.0.1’ (using password: YES)

（三）其他说明

1、有些同学会误以为 max_connection_errors 能够控制错误密码的尝试次数，其实该参数只能防止如 telnet 类的端口探测，即记录协议握手错误的次数
2、最后，在生产环境一定要关注 aborted_clients 和 aborted_connects 的状态，发生异常必须及时关注

公众号：知数堂，更多 MySQL 干货知识，关注公众号获取。

原文链接：https://zhishutang.com/CID

推荐阅读：https://zhishutang.com/xdI

目录