背景
从前，有一个客户，数据库高权账号的密码，要求每天自动地修改为随机密码，以增强数据库的安全性。
我们开发了一个用程序模块来实现的，当然您也可以用脚本 + cron 来实现。
现在，实现这个需求更为简单了。
8.0.18版本新增了一个小功能，CREATE USER，ALTER USER和 SET PASSWORD语句可以生成随机密码。
生成随机密码
mysql> create user 'fanderchan'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------------+------+----------------------+
| user | host | generated password |
+------------+------+----------------------+
| fanderchan | % | YMeIblT.jRciKLWkB0RL |
+------------+------+----------------------+
1 row in set 0.01 sec)
mysql> ALTER USER 'fanderchan'@'%' IDENTIFIED BY RANDOM PASSWORD;
+------------+------+----------------------+
| user | host | generated password |
+------------+------+----------------------+
| fanderchan | % | !bnMk/Lu4C87VMOdXQh. |
+------------+------+----------------------+
1 row in set 0.01 sec)
mysql> SET PASSWORD FOR 'fanderchan'@'%' TO RANDOM;
+------------+------+----------------------+
| user | host | generated password |
+------------+------+----------------------+
| fanderchan | % | dCdJT5h9[Gm/dsC.aVHm |
+------------+------+----------------------+
1 row in set 0.01 sec)
可以看见默认情况下，随机密码长度为20个字符，并且具有很好的复杂度大写字母、小写字母、特殊符号、数字)
相关参数
控制随机密码的长度
mysql> show variables like 'generated_random_password_length';
+----------------------------------+-------+
| Variable_name | Value |
+----------------------------------+-------+
| generated_random_password_length | 20 |
+----------------------------------+-------+
1 row in set 0.00 sec)
补充说明
validate_password是一个可选安装的MySQL密码增强插件，但随机密码不受validate_password插件的影响和控制
MySQL8.0 默认的密码认证插件是caching_sha2_password,MySQL5.7 默认的密码认证插件是mysql_native_password。
mysql> show global variables like 'default_authentication_plugin';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
1 row in set 0.00 sec)
所以，我在这里补充两个MySQL 8.0的授权语法和现象:
mysql> create user 'fander1'@'%' IDENTIFIED WITH 'caching_sha2_password' BY RANDOM PASSWORD;
+---------+------+----------------------+
| user | host | generated password |
+---------+------+----------------------+
| fander1 | % | Mk3UO%gi8HB6Qe>KFKxE |
+---------+------+----------------------+
1 row in set 0.01 sec)
mysql> create user 'fander2'@'%' IDENTIFIED WITH 'mysql_native_password' BY RANDOM PASSWORD;
+---------+------+----------------------+
| user | host | generated password |
+---------+------+----------------------+
| fander2 | % | Kp+t0g-3ALKeisQ>yBU/ |
+---------+------+----------------------+
1 row in set 0.00 sec)
mysql> select user,host,plugin,authentication_string from mysql.user where user like 'fander%';
+------------+------+-----------------------+------------------------------------------------------------------------+
| user | host | plugin | authentication_string |
+------------+------+-----------------------+------------------------------------------------------------------------+
| fander1 | % | caching_sha2_password | $A$005$WG[R/ c]0Z8wdaalCKRoKJFNkh1owsuzQ0lsP9JSGLDHlmdGhM8DvSM1 |
| fander2 | % | mysql_native_password | *8DADAA12E42653774E3CB670F92E0A58171FE2E8 |
+------------+------+-----------------------+------------------------------------------------------------------------+
5 rows in set 0.00 sec)
我想表达的是，在binlog里，授权语句的密码显示为加密后的的密文密码 authentication_string字段)。
参考:
https://dev.mysql.com/doc/refman/8.0/en/password-management.html