# 1. 安装密码验证插件
# 1.1 查看可用的密码验证插件
mysql> SHOW PLUGINS LIKE ‘validate_password%’;
Empty set (0.00 sec)

# 1.2 安装validate_password插件
mysql> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
Query OK, 0 rows affected (0.00 sec)

# 1.3 验证插件安装
mysql> SHOW PLUGINS LIKE ‘validate_password%’;
+——————-+———-+——————–+———————-+———+
| Name | Status | Type | Library | License |
+——————-+———-+——————–+———————-+———+
| validate_password | ACTIVE | VALIDATE PASSWORD | validate_password.so | GPL |
+——————-+———-+——————–+———————-+———+

# 2. 查看密码验证插件配置
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 |
+————————————–+——–+

# 3. 密码验证插件参数说明
# validate_password.check_user_name：是否检查密码是否包含用户名
# validate_password.dictionary_file：字典文件路径，用于检查密码是否在字典中
# validate_password.length：密码最小长度
# validate_password.mixed_case_count：密码中必须包含的大小写字母数量
# validate_password.number_count：密码中必须包含的数字数量
# validate_password.policy：密码强度策略（LOW, MEDIUM, STRONG）
# validate_password.special_char_count：密码中必须包含的特殊字符数量

# 4. 测试密码验证插件
# 尝试创建弱密码用户（应该失败）
mysql> CREATE USER ‘weak_user’@’localhost’ IDENTIFIED BY ‘password’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 尝试创建符合要求的密码用户（应该成功）
mysql> CREATE USER ‘strong_user’@’localhost’ IDENTIFIED BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 1. 配置密码强度策略
# 1.1 设置密码强度为STRONG
mysql> SET GLOBAL validate_password.policy = ‘STRONG’;
Query OK, 0 rows affected (0.00 sec)

# 1.2 设置密码最小长度为12
mysql> SET GLOBAL validate_password.length = 12;
Query OK, 0 rows affected (0.00 sec)

# 1.3 设置密码中必须包含的大小写字母数量为2
mysql> SET GLOBAL validate_password.mixed_case_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 1.4 设置密码中必须包含的数字数量为2
mysql> SET GLOBAL validate_password.number_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 1.5 设置密码中必须包含的特殊字符数量为2
mysql> SET GLOBAL validate_password.special_char_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 2. 验证密码策略配置
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+————————————–+——–+
| Variable_name | Value |
+————————————–+——–+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 12 |
| validate_password.mixed_case_count | 2 |
| validate_password.number_count | 2 |
| validate_password.policy | STRONG |
| validate_password.special_char_count | 2 |
+————————————–+——–+

# 3. 在配置文件中永久配置密码策略
$ vi /etc/my.cnf
[mysqld]
validate_password.policy = STRONG
validate_password.length = 12
validate_password.mixed_case_count = 2
validate_password.number_count = 2
validate_password.special_char_count = 2

# 4. 重启MySQL服务
$ systemctl restart mysqld

# 5. 测试密码策略
# 尝试创建符合STRONG策略的密码（应该成功）
mysql> CREATE USER ‘strongest_user’@’localhost’ IDENTIFIED BY ‘SecurePassw0rd!@’;
Query OK, 0 rows affected (0.01 sec)

# 尝试创建不符合STRONG策略的密码（应该失败）
mysql> CREATE USER ‘weakest_user’@’localhost’ IDENTIFIED BY ‘SecurePassword123’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 1. 设置全局密码过期时间
# 1.1 设置密码过期时间为90天
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

# 1.2 验证密码过期时间配置
mysql> SHOW VARIABLES LIKE ‘default_password_lifetime’;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| default_password_lifetime | 90 |
+—————————+——-+

# 2. 为特定用户设置密码过期时间
# 2.1 创建用户并设置密码过期时间为60天
mysql> CREATE USER ‘user_with_expiry’@’localhost’ IDENTIFIED BY ‘SecurePassword123!’ PASSWORD EXPIRE
INTERVAL 60 DAY;
Query OK, 0 rows affected (0.01 sec)

# 2.2 修改现有用户的密码过期时间为30天
mysql> ALTER USER ‘existing_user’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.01 sec)

# 3. 手动设置用户密码过期
# 3.1 设置用户密码过期
mysql> ALTER USER ‘user_to_expire’@’localhost’ PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)

# 3.2 验证用户密码过期状态
mysql> SELECT User, Host, password_expired FROM mysql.user WHERE User = ‘user_to_expire’;
+—————–+———–+—————–+
| User | Host | password_expired |
+—————–+———–+—————–+
| user_to_expire | localhost | Y |
+—————–+———–+—————–+

# 4. 密码过期后处理
# 4.1 尝试使用过期密码登录（应该失败）
$ mysql -u user_to_expire -p
Enter password:
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

# 4.2 重置过期密码
mysql> ALTER USER ‘user_to_expire’@’localhost’ IDENTIFIED BY ‘NewSecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 4.3 验证密码重置成功
$ mysql -u user_to_expire -pNewSecurePassword123!
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 8.4.0 MySQL Community Server – GPL

Copyright (c) 2000, 2026, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

# 5. 在配置文件中永久配置密码过期时间
$ vi /etc/my.cnf
[mysqld]
default_password_lifetime = 90

# 6. 重启MySQL服务
$ systemctl restart mysqld

# 1. 设置密码历史记录数量
# 1.1 设置密码历史记录为10个
mysql> SET GLOBAL password_history = 10;
Query OK, 0 rows affected (0.00 sec)

# 1.2 验证密码历史记录配置
mysql> SHOW VARIABLES LIKE ‘password_history’;
+—————–+——-+
| Variable_name | Value |
+—————–+——-+
| password_history | 10 |
+—————–+——-+

# 2. 为特定用户设置密码历史记录
# 2.1 创建用户并设置密码历史记录为5个
mysql> CREATE USER ‘user_with_history’@’localhost’ IDENTIFIED BY ‘SecurePassword123!’ PASSWORD HISTORY 5;
Query OK, 0 rows affected (0.01 sec)

# 2.2 修改现有用户的密码历史记录为8个
mysql> ALTER USER ‘existing_user’@’localhost’ PASSWORD HISTORY 8;
Query OK, 0 rows affected (0.01 sec)

# 3. 测试密码历史限制
# 3.1 创建测试用户
mysql> CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘Password1!’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 修改密码历史限制为2个
mysql> ALTER USER ‘test_user’@’localhost’ PASSWORD HISTORY 2;
Query OK, 0 rows affected (0.01 sec)

# 3.3 第一次修改密码
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘Password2!’;
Query OK, 0 rows affected (0.01 sec)

# 3.4 第二次修改密码
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘Password3!’;
Query OK, 0 rows affected (0.01 sec)

# 3.5 尝试使用最近使用过的密码（应该失败）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘Password2!’;
ERROR 3638 (HY000): Cannot use these credentials for ‘test_user’@’localhost’ because they contradict the
password history policy

# 3.6 尝试使用更旧的密码（应该成功，因为只限制最近2个）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘Password1!’;
Query OK, 0 rows affected (0.01 sec)

# 4. 在配置文件中永久配置密码历史记录
$ vi /etc/my.cnf
[mysqld]
password_history = 10

# 5. 重启MySQL服务
$ systemctl restart mysqld

# 6. 设置密码重用间隔
# 6.1 设置密码重用间隔为365天
mysql> SET GLOBAL password_reuse_interval = 365;
Query OK, 0 rows affected (0.00 sec)

# 6.2 验证密码重用间隔配置
mysql> SHOW VARIABLES LIKE ‘password_reuse_interval’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| password_reuse_interval | 365 |
+————————+——-+

# 6.3 为特定用户设置密码重用间隔
mysql> ALTER USER ‘test_user’@’localhost’ PASSWORD REUSE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.01 sec)

# 1. 密码复杂度配置示例
# 1.1 设置密码强度为STRONG
mysql> SET GLOBAL validate_password.policy = ‘STRONG’;
Query OK, 0 rows affected (0.00 sec)

# 1.2 设置密码最小长度为14
mysql> SET GLOBAL validate_password.length = 14;
Query OK, 0 rows affected (0.00 sec)

# 1.3 设置密码中必须包含的大小写字母数量为3
mysql> SET GLOBAL validate_password.mixed_case_count = 3;
Query OK, 0 rows affected (0.00 sec)

# 1.4 设置密码中必须包含的数字数量为3
mysql> SET GLOBAL validate_password.number_count = 3;
Query OK, 0 rows affected (0.00 sec)

# 1.5 设置密码中必须包含的特殊字符数量为2
mysql> SET GLOBAL validate_password.special_char_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 1.6 验证密码复杂度配置
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+————————————–+——–+
| Variable_name | Value |
+————————————–+——–+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 14 |
| validate_password.mixed_case_count | 3 |
| validate_password.number_count | 3 |
| validate_password.policy | STRONG |
| validate_password.special_char_count | 2 |
+————————————–+——–+

# 2. 密码强度评估
# 2.1 评估密码强度
mysql> SELECT validate_password_strength(‘Password123!’);
+——————————————–+
| validate_password_strength(‘Password123!’) |
+——————————————–+
| 50 |
+——————————————–+

# 2.2 评估符合STRONG策略的密码
mysql> SELECT validate_password_strength(‘SecurePassw0rd!@#’);
+————————————————+
| validate_password_strength(‘SecurePassw0rd!@#’) |
+————————————————+
| 100 |
+————————————————+

# 2.3 评估弱密码
mysql> SELECT validate_password_strength(‘password’);
+—————————————-+
| validate_password_strength(‘password’) |
+—————————————-+
| 0 |
+—————————————-+

# 3. 密码复杂度测试
# 3.1 测试符合STRONG策略的密码（应该成功）
mysql> CREATE USER ‘test_user’@’localhost’ IDENTIFIED BY ‘SecurePassw0rd!@#’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 测试不符合长度要求的密码（应该失败）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘SecureP@ss123’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 3.3 测试不符合大小写要求的密码（应该失败）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘securepassword012!@#’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 3.4 测试不符合数字要求的密码（应该失败）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘SecurePassword!@#’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 3.5 测试不符合特殊字符要求的密码（应该失败）
mysql> ALTER USER ‘test_user’@’localhost’ IDENTIFIED BY ‘SecurePassw0rd123’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 4. 密码复杂度最佳实践
# 4.1 使用密码生成器生成强密码
$ openssl rand -base64 16
WXc3Z9vR5tY7uI2oP4sA6dF8gH1jK3lM5nO7qR9sT

# 4.2 密码复杂度要求示例
# – 长度：至少14个字符
# – 包含：至少3个大写字母，3个小写字母，3个数字，2个特殊字符
# – 不包含：用户名、生日、电话号码等个人信息
# – 不使用：常见密码、键盘模式、字典词汇

# 1. 双因素认证概述
# MySQL 8.0.27及以上版本支持双因素认证
# 双因素认证需要使用PAM认证插件
# 双因素认证需要结合TOTP（基于时间的一次性密码）

# 2. 安装和配置双因素认证
# 2.1 安装PAM认证插件
mysql> INSTALL PLUGIN authentication_pam SONAME ‘authentication_pam.so’;
Query OK, 0 rows affected (0.01 sec)

# 2.2 验证PAM认证插件安装
mysql> SHOW PLUGINS LIKE ‘authentication_pam%’;
+———————-+———-+——————–+———————-+———+
| Name | Status | Type | Library | License |
+———————-+———-+——————–+———————-+———+
| authentication_pam | ACTIVE | AUTHENTICATION | authentication_pam.so | GPL |
+———————-+———-+——————–+———————-+———+

# 2.3 安装TOTP相关工具
$ yum install -y google-authenticator

# 2.4 创建PAM配置文件
$ vi /etc/pam.d/mysql_tfa
#%PAM-1.0
auth required pam_google_authenticator.so
account include password-auth

# 3. 启用双因素认证
# 3.1 创建使用双因素认证的用户
mysql> CREATE USER ‘tfa_user’@’localhost’ IDENTIFIED WITH authentication_pam AS ‘mysql_tfa’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 为用户生成TOTP密钥
$ google-authenticator

Do you want authentication tokens to be time-based (y/n) y
https://www.google.com/chart?chs=200×200&chld=M|0&cht=qr&chl=otpauth://totp/root@server?secret=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Your new secret key is: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Your verification code is 123456
Your emergency scratch codes are:
11111111
22222222
33333333
44444444
55555555

Do you want me to update your “~/.google_authenticator” file? (y/n) y

Do you want to disallow multiple uses of the same authentication token? This restricts you to one login
about every 30s, but it increases your chances to notice or even prevent man-in-the-middle attacks (y/n) y

By default, a new token is generated every 30 seconds by the mobile app. In order to compensate for possible
time-skew between the client and the server, we allow an extra token before and after the current time. This
allows for a time skew of up to 30 seconds between authentication server and client. If you experience
problems with poor time synchronization, you can increase the window from its default size of 3 permitted
codes (one previous code, the current code, the next code) to 17 permitted codes (the 8 previous codes, the
current code, and the 8 next codes). This will permit for a time skew of up to 4 minutes between client and
server.
Do you want to do so? (y/n) n

If the computer that you are logging into isn’t hardened against brute-force login attempts, you can enable
rate-limiting for the authentication module. By default, this limits attackers to no more than 3 login
attempts every 30s.
Do you want to enable rate-limiting? (y/n) y

# 4. 测试双因素认证
# 4.1 尝试使用双因素认证登录
$ mysql -u tfa_user -p
Enter password: [输入PAM密码]
Enter TOTP code: [输入Google Authenticator生成的6位验证码]
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 8.4.0 MySQL Community Server – GPL

Copyright (c) 2000, 2026, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the current input statement.

mysql>

# 4.2 测试错误的TOTP码（应该失败）
$ mysql -u tfa_user -p
Enter password: [输入PAM密码]
Enter TOTP code: 654321
ERROR 1045 (28000): Access denied for user ‘tfa_user’@’localhost’ (using password: YES)

# 5. 双因素认证最佳实践
# 5.1 为所有管理员用户启用双因素认证
# 5.2 使用可靠的TOTP应用程序（如Google Authenticator、Microsoft Authenticator）
# 5.3 安全存储紧急恢复代码
# 5.4 定期更新TOTP密钥
# 5.5 结合密码策略和其他安全措施使用

# 1. 环境准备
# 1.1 MySQL版本检查
$ mysql -V
mysql Ver 8.4.0 for Linux on x86_64 (MySQL Community Server – GPL)

# 1.2 安装依赖
$ yum install -y openssl-devel

# 2. 安装密码验证插件
mysql> INSTALL PLUGIN validate_password SONAME ‘validate_password.so’;
Query OK, 0 rows affected (0.00 sec)

# 3. 配置密码策略
# 3.1 设置密码强度为STRONG
mysql> SET GLOBAL validate_password.policy = ‘STRONG’;
Query OK, 0 rows affected (0.00 sec)

# 3.2 设置密码最小长度为14
mysql> SET GLOBAL validate_password.length = 14;
Query OK, 0 rows affected (0.00 sec)

# 3.3 设置密码中必须包含的大小写字母数量为3
mysql> SET GLOBAL validate_password.mixed_case_count = 3;
Query OK, 0 rows affected (0.00 sec)

# 3.4 设置密码中必须包含的数字数量为3
mysql> SET GLOBAL validate_password.number_count = 3;
Query OK, 0 rows affected (0.00 sec)

# 3.5 设置密码中必须包含的特殊字符数量为2
mysql> SET GLOBAL validate_password.special_char_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 4. 配置密码过期管理
# 4.1 设置全局密码过期时间为90天
mysql> SET GLOBAL default_password_lifetime = 90;
Query OK, 0 rows affected (0.00 sec)

# 5. 配置密码历史管理
# 5.1 设置密码历史记录为10个
mysql> SET GLOBAL password_history = 10;
Query OK, 0 rows affected (0.00 sec)

# 5.2 设置密码重用间隔为365天
mysql> SET GLOBAL password_reuse_interval = 365;
Query OK, 0 rows affected (0.00 sec)

# 6. 配置登录失败策略
# 6.1 安装connection_control插件
mysql> INSTALL PLUGIN connection_control SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

mysql> INSTALL PLUGIN connection_control_failed_login_attempts SONAME ‘connection_control.so’;
Query OK, 0 rows affected (0.01 sec)

# 6.2 设置登录失败次数限制为3次
mysql> SET GLOBAL connection_control_failed_connections_threshold = 3;
Query OK, 0 rows affected (0.00 sec)

# 6.3 设置锁定时间为180秒
mysql> SET GLOBAL connection_control_min_connection_delay = 180000;
Query OK, 0 rows affected (0.00 sec)

# 7. 永久配置密码策略
# 7.1 修改配置文件
$ vi /etc/my.cnf
[mysqld]
# 密码验证插件
plugin-load-add = validate_password.so
validate_password.policy = STRONG
validate_password.length = 14
validate_password.mixed_case_count = 3
validate_password.number_count = 3
validate_password.special_char_count = 2

# 密码过期管理
default_password_lifetime = 90

# 密码历史管理
password_history = 10
password_reuse_interval = 365

# 登录失败策略
plugin-load-add = connection_control.so
connection_control_failed_connections_threshold = 3
connection_control_min_connection_delay = 180000

# 8. 重启MySQL服务
$ systemctl restart mysqld

# 9. 验证配置
# 9.1 验证密码验证插件配置
mysql> SHOW VARIABLES LIKE ‘validate_password%’;
+————————————–+——–+
| Variable_name | Value |
+————————————–+——–+
| validate_password.check_user_name | ON |
| validate_password.dictionary_file | |
| validate_password.length | 14 |
| validate_password.mixed_case_count | 3 |
| validate_password.number_count | 3 |
| validate_password.policy | STRONG |
| validate_password.special_char_count | 2 |
+————————————–+——–+

# 9.2 验证密码过期配置
mysql> SHOW VARIABLES LIKE ‘default_password_lifetime’;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| default_password_lifetime | 90 |
+—————————+——-+

# 9.3 验证密码历史配置
mysql> SHOW VARIABLES LIKE ‘password_history’;
mysql> SHOW VARIABLES LIKE ‘password_reuse_interval’;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| password_reuse_interval | 365 |
+————————+——-+

# 9.4 验证登录失败配置
mysql> SHOW VARIABLES LIKE ‘connection_control%’;
+——————————————+——–+
| Variable_name | Value |
+——————————————+——–+
| connection_control_failed_connections_threshold | 3 |
| connection_control_max_connection_delay | 2147483647 |
| connection_control_min_connection_delay | 180000 |
+——————————————+——–+

# 10. 测试密码策略
# 10.1 创建符合策略的用户
mysql> CREATE USER ‘admin_user’@’localhost’ IDENTIFIED BY ‘SecureAdmin123!@#’;
Query OK, 0 rows affected (0.01 sec)

# 10.2 尝试创建不符合策略的用户（应该失败）
mysql> CREATE USER ‘weak_user’@’localhost’ IDENTIFIED BY ‘password123’;
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

# 10.3 测试密码过期
mysql> ALTER USER ‘admin_user’@’localhost’ PASSWORD EXPIRE;
Query OK, 0 rows affected (0.01 sec)

# 10.4 测试密码历史
mysql> ALTER USER ‘admin_user’@’localhost’ IDENTIFIED BY ‘SecureAdmin456!@#’;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER ‘admin_user’@’localhost’ IDENTIFIED BY ‘SecureAdmin123!@#’;
ERROR 3638 (HY000): Cannot use these credentials for ‘admin_user’@’localhost’ because they contradict the
password history policy

# 1. 密码生成脚本
# 1.1 创建密码生成脚本
$ vi /usr/local/bin/generate_mysql_password.sh
#!/bin/bash
# 生成符合MySQL密码策略的强密码
# 参数：密码长度（默认14）

length=${1:-14}

# 生成包含大小写字母、数字和特殊字符的密码
password=$(openssl rand -base64 $length | tr -dc ‘A-Za-z0-9!@#$%^&*()_+-=[]{}|;:,.<>?’)

# 确保密码长度符合要求
while [ ${#password} -lt $length ]; do
password=$(openssl rand -base64 $length | tr -dc ‘A-Za-z0-9!@#$%^&*()_+-=[]{}|;:,.<>?’)
done

# 截取指定长度的密码
password=$(echo $password | cut -c 1-$length)

echo “Generated MySQL password: $password”

# 1.2 给脚本添加执行权限
$ chmod +x /usr/local/bin/generate_mysql_password.sh

# 1.3 测试密码生成脚本
$ generate_mysql_password.sh
Generated MySQL password: SecurePassw0rd!@#

$ generate_mysql_password.sh 20
Generated MySQL password: SuperSecureP@ssw0rd123!@#$%

# 2. 密码定期更改脚本
# 2.1 创建密码定期更改脚本
$ vi /usr/local/bin/rotate_mysql_passwords.sh
#!/bin/bash
# 定期更改MySQL用户密码

# 配置
MYSQL_USER=”root”
MYSQL_PASS=”root_password”
LOG_FILE=”/var/log/mysql_password_rotation.log”
PASS_FILE=”/etc/mysql/user_passwords.txt”

# 日志函数
log() {
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) – $1” >> $LOG_FILE
}

# 检查参数
if [ $# -ne 1 ]; then
echo “Usage: $0 ”
exit 1
fi

USER_LIST_FILE=$1

# 检查用户列表文件是否存在
if [ ! -f $USER_LIST_FILE ]; then
log “Error: User list file $USER_LIST_FILE not found”
exit 1
fi

# 生成新密码并更新用户
while read user; do
if [ -z “$user” ]; then
continue
fi

# 生成新密码
new_password=$(openssl rand -base64 14 | tr -dc ‘A-Za-z0-9!@#$%^&*()_+-=[]{}|;:,.<>?’ | cut -c
1-14)

# 更新MySQL用户密码
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “ALTER USER ‘$user’@’%’ IDENTIFIED BY ‘$new_password’;
FLUSH PRIVILEGES;”

if [ $? -eq 0 ]; then
log “Successfully changed password for user: $user”
echo “$user:$new_password” >> $PASS_FILE
else
log “Failed to change password for user: $user”
fi
done < $USER_LIST_FILE log "Password rotation completed" # 2.2 给脚本添加执行权限 $ chmod +x /usr/local/bin/rotate_mysql_passwords.sh # 2.3 创建用户列表文件 $ vi /etc/mysql/user_list.txt app_user readonly_user dba_user # 2.4 运行密码定期更改脚本 $ rotate_mysql_passwords.sh /etc/mysql/user_list.txt # 2.5 查看日志 $ cat /var/log/mysql_password_rotation.log 2026-04-01 12:00:00 - Successfully changed password for user: app_user 2026-04-01 12:00:01 - Successfully changed password for user: readonly_user 2026-04-01 12:00:02 - Successfully changed password for user: dba_user 2026-04-01 12:00:02 - Password rotation completed # 3. 密码过期提醒脚本 # 3.1 创建密码过期提醒脚本 $ vi /usr/local/bin/mysql_password_expiry_alert.sh #!/bin/bash # 提醒即将过期的MySQL用户密码 # 配置 MYSQL_USER="root" MYSQL_PASS="root_password" LOG_FILE="/var/log/mysql_password_expiry_alert.log" ALERT_DAYS=15 # 提前15天提醒 # 日志函数 log() { echo "$(date '+%Y-%m-%d %H:%M:%S') - $1">> $LOG_FILE
}

# 查询即将过期的用户
EXPIRING_USERS=$(mysql -u $MYSQL_USER -p$MYSQL_PASS -N -e ”
SELECT
User,
Host,
IFNULL(EXTRACT(DAY FROM (password_lifetime – INTERVAL DATEDIFF(CURDATE(),
password_last_changed) DAY)), ‘Never’) AS days_until_expiry
FROM
mysql.user
WHERE
password_expired = ‘N’ AND
password_lifetime IS NOT NULL AND
DATEDIFF(CURDATE(), password_last_changed) >= (password_lifetime – $ALERT_DAYS)
ORDER BY
days_until_expiry ASC;”)

# 检查是否有即将过期的用户
if [ -n “$EXPIRING_USERS” ]; then
log “The following MySQL users have passwords expiring soon:”
echo “$EXPIRING_USERS” | while read line; do
log “$line”
done

# 发送邮件提醒（需要安装sendmail或postfix）
echo “$EXPIRING_USERS” | mail -s “MySQL Password Expiry Alert” admin@example.com
else
log “No MySQL users have passwords expiring soon”
fi

# 3.2 给脚本添加执行权限
$ chmod +x /usr/local/bin/mysql_password_expiry_alert.sh

# 3.3 运行密码过期提醒脚本
$ mysql_password_expiry_alert.sh

# 3.4 查看日志
$ cat /var/log/mysql_password_expiry_alert.log
2026-04-01 12:00:00 – No MySQL users have passwords expiring soon

# 4. 定时任务配置
# 4.1 配置密码定期更改任务（每90天执行一次）
$ crontab -e
0 0 1 */3 * /usr/local/bin/rotate_mysql_passwords.sh /etc/mysql/user_list.txt

# 4.2 配置密码过期提醒任务（每天执行一次）
$ crontab -e
0 8 * * * /usr/local/bin/mysql_password_expiry_alert.sh

# 1. 环境准备
# 1.1 MySQL版本检查
$ mysql -V
mysql Ver 8.4.0 for Linux on x86_64 (MySQL Community Server – GPL)

# 1.2 安装依赖
$ yum install -y pam-devel google-authenticator

# 2. 配置MySQL PAM认证
# 2.1 创建PAM配置文件
$ vi /etc/pam.d/mysql_tfa
#%PAM-1.0
auth required pam_google_authenticator.so
account include password-auth

# 3. 配置MySQL
# 3.1 安装PAM认证插件
mysql> INSTALL PLUGIN authentication_pam SONAME ‘authentication_pam.so’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 验证PAM认证插件安装
mysql> SHOW PLUGINS LIKE ‘authentication_pam%’;
+———————-+———-+——————–+———————-+———+
| Name | Status | Type | Library | License |
+———————-+———-+——————–+———————-+———+
| authentication_pam | ACTIVE | AUTHENTICATION | authentication_pam.so | GPL |
+———————-+———-+——————–+———————-+———+

# 4. 创建使用双因素认证的用户
# 4.1 创建DBA用户
mysql> CREATE USER ‘dba_admin’@’localhost’ IDENTIFIED WITH authentication_pam AS ‘mysql_tfa’;
Query OK, 0 rows affected (0.01 sec)

# 4.2 授予管理员权限
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘dba_admin’@’localhost’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 5. 配置TOTP
# 5.1 为用户生成TOTP密钥
$ su – mysql
$ google-authenticator

Do you want authentication tokens to be time-based (y/n) y
https://www.google.com/chart?chs=200×200&chld=M|0&cht=qr&chl=otpauth://totp/mysql@server?secret=XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Your new secret key is: XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Your verification code is 123456
Your emergency scratch codes are:
11111111
22222222
33333333
44444444
55555555

Do you want me to update your “~/.google_authenticator” file? (y/n) y

Do you want to disallow multiple uses of the same authentication token? (y/n) y

By default, a new token is generated every 30 seconds… Do you want to do so? (y/n) n

Do you want to enable rate-limiting? (y/n) y

# 6. 测试双因素认证
# 6.1 使用双因素认证登录
$ mysql -u dba_admin -p
Enter password: [输入MySQL用户密码]
Enter TOTP code: 123456
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12345
Server version: 8.4.0 MySQL Community Server – GPL

mysql>

# 6.2 验证登录成功
mysql> SELECT CURRENT_USER();
+——————+
| CURRENT_USER() |
+——————+
| dba_admin@localhost |
+——————+

# 7. 双因素认证管理
# 7.1 重置TOTP密钥
$ su – mysql
$ google-authenticator

# 7.2 禁用双因素认证（临时）
mysql> ALTER USER ‘dba_admin’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘NewPassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 7.3 重新启用双因素认证
mysql> ALTER USER ‘dba_admin’@’localhost’ IDENTIFIED WITH authentication_pam AS ‘mysql_tfa’;
Query OK, 0 rows affected (0.01 sec)

# 8. 双因素认证最佳实践
# 8.1 为所有管理员用户启用双因素认证
# 8.2 安全存储TOTP密钥和紧急恢复代码
# 8.3 定期更换TOTP密钥
# 8.4 结合密码策略使用
# 8.5 培训用户正确使用双因素认证