# 1. 创建用户
mysql> CREATE USER ‘app_user’@’localhost’ IDENTIFIED BY ‘strong_password’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘app_user’@’192.168.1.%’ IDENTIFIED BY ‘strong_password’;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘read_user’@’%’ IDENTIFIED BY ‘read_only_password’;
Query OK, 0 rows affected (0.00 sec)

# 2. 查看用户
mysql> SELECT user, host FROM mysql.user;
+——————+————-+
| user | host |
+——————+————-+
| app_user | 192.168.1.% |
| app_user | localhost |
| read_user | % |
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+——————+————-+

# 3. 修改用户密码
mysql> ALTER USER ‘app_user’@’localhost’ IDENTIFIED BY ‘new_strong_password’;
Query OK, 0 rows affected (0.00 sec)

# 4. 删除用户
mysql> DROP USER ‘read_user’@’%’;
Query OK, 0 rows affected (0.00 sec)

# 5. 锁定和解锁用户
mysql> ALTER USER ‘app_user’@’localhost’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER ‘app_user’@’localhost’ ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

# 6. 查看用户状态
mysql> SELECT user, host, account_locked FROM mysql.user;
+——————+————-+—————-+———-+
| user | host | account_locked | … |
+——————+————-+—————-+———-+
| app_user | 192.168.1.% | N | … |
| app_user | localhost | N | … |
| mysql.infoschema | localhost | Y | … |
| mysql.session | localhost | Y | … |
| mysql.sys | localhost | Y | … |
| root | localhost | N | … |
+——————+————-+—————-+———-+

# 1. 授予权限
# 授予用户对特定数据库的所有权限
mysql> GRANT ALL PRIVILEGES ON fgedudb.* TO ‘app_user’@’localhost’;
Query OK, 0 rows affected (0.00 sec)

# 授予用户对特定表的SELECT和INSERT权限
mysql> GRANT SELECT, INSERT ON fgedudb.table1 TO ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.00 sec)

# 授予用户只读权限
mysql> GRANT SELECT ON *.* TO ‘read_user’@’%’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

# 2. 查看权限
mysql> SHOW GRANTS FOR ‘app_user’@’localhost’;
+———————————————————-+
| Grants for app_user@localhost |
+———————————————————-+
| GRANT USAGE ON *.* TO `app_user`@`localhost` |
| GRANT ALL PRIVILEGES ON `fgedudb`.* TO `app_user`@`localhost` |
+———————————————————-+

mysql> SHOW GRANTS FOR ‘app_user’@’192.168.1.%’;
+—————————————————————+
| Grants for app_user@192.168.1.% |
+—————————————————————+
| GRANT USAGE ON *.* TO `app_user`@`192.168.1.%` |
| GRANT SELECT, INSERT ON `fgedudb`.`table1` TO `app_user`@`192.168.1.%` |
+—————————————————————+

# 3. 撤销权限
mysql> REVOKE INSERT ON fgedudb.table1 FROM ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.00 sec)

# 4. 刷新权限
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

# 5. 查看当前用户权限
mysql> SHOW GRANTS;
+———————————————————-+
| Grants for app_user@localhost |
+———————————————————-+
| GRANT USAGE ON *.* TO `app_user`@`localhost` |
| GRANT ALL PRIVILEGES ON `fgedudb`.* TO `app_user`@`localhost` |
+———————————————————-+

# 1. 查看密码策略配置
mysql> SHOW GLOBAL 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 |
+————————————–+——–+

# 2. 修改密码策略
# 设置密码长度为12
mysql> SET GLOBAL validate_password.length = 12;
Query OK, 0 rows affected (0.00 sec)

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

# 设置至少需要2个数字
mysql> SET GLOBAL validate_password.number_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 设置至少需要2个特殊字符
mysql> SET GLOBAL validate_password.special_char_count = 2;
Query OK, 0 rows affected (0.00 sec)

# 3. 验证密码策略
# 尝试创建弱密码用户（应该失败）
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 ‘MyS3cr3tP@ssw0rd’;
Query OK, 0 rows affected (0.00 sec)

# 4. 设置密码过期策略
# 设置密码30天后过期
mysql> ALTER USER ‘app_user’@’localhost’ PASSWORD EXPIRE INTERVAL 30 DAY;
Query OK, 0 rows affected (0.00 sec)

# 设置密码永不过期
mysql> ALTER USER ‘read_user’@’%’ PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

# 5. 强制用户下次登录时修改密码
mysql> ALTER USER ‘new_user’@’localhost’ PASSWORD EXPIRE DEFAULT;
Query OK, 0 rows affected (0.00 sec)

# 6. 查看用户密码过期信息
mysql> SELECT user, host, password_expired, password_lifetime FROM mysql.user;
+——————+————-+——————+——————-+
| user | host | password_expired | password_lifetime |
+——————+————-+——————+——————-+
| app_user | 192.168.1.% | N | NULL |
| app_user | localhost | N | 30 |
| read_user | % | N | NULL |
| new_user | localhost | Y | NULL |
| mysql.infoschema | localhost | N | NULL |
| mysql.session | localhost | N | NULL |
| mysql.sys | localhost | N | NULL |
| root | localhost | N | NULL |
+——————+————-+——————+——————-+

# 1. 网络访问控制
# 配置MySQL只监听特定IP地址
# vi /etc/my.cnf
bind-address = 127.0.0.1,192.168.1.100

# 重启MySQL服务
# systemctl restart mysqld

# 验证监听地址
# netstat -tlnp | grep mysql
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 1234/mysqld
tcp 0 0 192.168.1.100:3306 0.0.0.0:* LISTEN 1234/mysqld

# 2. 用户主机限制
# 创建只能从特定IP访问的用户
mysql> CREATE USER ‘app_user’@’192.168.1.100’ IDENTIFIED BY ‘strong_password’;
Query OK, 0 rows affected (0.00 sec)

# 创建只能从本地访问的用户
mysql> CREATE USER ‘local_user’@’localhost’ IDENTIFIED BY ‘local_password’;
Query OK, 0 rows affected (0.00 sec)

# 3. 资源访问控制
# 设置用户最大连接数
mysql> CREATE USER ‘limited_user’@’localhost’ IDENTIFIED BY ‘limited_password’ WITH MAX_USER_CONNECTIONS 10;
Query OK, 0 rows affected (0.00 sec)

# 设置用户每小时最大查询数
mysql> CREATE USER ‘query_user’@’localhost’ IDENTIFIED BY ‘query_password’ WITH MAX_QUERIES_PER_HOUR 1000;
Query OK, 0 rows affected (0.00 sec)

# 设置用户每小时最大更新数
mysql> CREATE USER ‘update_user’@’localhost’ IDENTIFIED BY ‘update_password’ WITH MAX_UPDATES_PER_HOUR 500;
Query OK, 0 rows affected (0.00 sec)

# 4. 查看用户资源限制
mysql> SELECT user, host, max_connections, max_queries_per_hour, max_updates_per_hour FROM mysql.user;
+——————+————-+—————–+———————–+———————–+
| user | host | max_connections | max_queries_per_hour | max_updates_per_hour |
+——————+————-+—————–+———————–+———————–+
| app_user | 192.168.1.% | 0 | 0 | 0 |
| limited_user | localhost | 10 | 0 | 0 |
| query_user | localhost | 0 | 1000 | 0 |
| update_user | localhost | 0 | 0 | 500 |
| mysql.infoschema | localhost | 0 | 0 | 0 |
| mysql.session | localhost | 0 | 0 | 0 |
| mysql.sys | localhost | 0 | 0 | 0 |
| root | localhost | 0 | 0 | 0 |
+——————+————-+—————–+———————–+———————–+

# 1. 生成SSL证书
# 创建证书目录
$ mkdir -p /mysql/ssl
$ cd /mysql/ssl

# 生成CA证书
$ openssl genrsa 2048 > ca-key.pem
$ openssl req -new -x509 -nodes -days 3650 -key ca-key.pem > ca.pem

# 生成服务器证书
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout server-key.pem > server-req.pem
$ openssl x509 -req -in server-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 01 >
server-cert.pem

# 生成客户端证书
$ openssl req -newkey rsa:2048 -days 3650 -nodes -keyout client-key.pem > client-req.pem
$ openssl x509 -req -in client-req.pem -days 3650 -CA ca.pem -CAkey ca-key.pem -set_serial 02 >
client-cert.pem

# 修改证书权限
$ chown -R mysql:mysql /mysql/ssl
$ chmod 600 /mysql/ssl/*-key.pem
$ chmod 644 /mysql/ssl/*.pem

# 2. 配置MySQL启用SSL
# vi /etc/my.cnf
[mysqld]
ssl-ca=/mysql/ssl/ca.pem
ssl-cert=/mysql/ssl/server-cert.pem
ssl-key=/mysql/ssl/server-key.pem

# 重启MySQL服务
# systemctl restart mysqld

# 3. 验证SSL配置
mysql> SHOW GLOBAL VARIABLES LIKE ‘%ssl%’;
+———————+——————————–+——–+
| Variable_name | Value | … |
+———————+——————————–+——–+
| have_openssl | YES | … |
| have_ssl | YES | … |
| ssl_ca | /mysql/ssl/ca.pem | … |
| ssl_capath | | … |
| ssl_cert | /mysql/ssl/server-cert.pem | … |
| ssl_cipher | | … |
| ssl_crl | | … |
| ssl_crlpath | | … |
| ssl_key | /mysql/ssl/server-key.pem | … |
| version_ssl_library | OpenSSL 1.1.1k FIPS 25 Mar 2021 | … |
+———————+——————————–+——–+

# 4. 创建需要SSL的用户
mysql> CREATE USER ‘ssl_user’@’localhost’ IDENTIFIED BY ‘ssl_password’ REQUIRE SSL;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE USER ‘x509_user’@’localhost’ IDENTIFIED BY ‘x509_password’ REQUIRE X509;
Query OK, 0 rows affected (0.00 sec)

# 5. 使用SSL连接MySQL
$ mysql -u ssl_user -p –ssl-ca=/mysql/ssl/ca.pem –ssl-cert=/mysql/ssl/client-cert.pem
–ssl-key=/mysql/ssl/client-key.pem
Enter password:

mysql> STATUS;
————–
mysql Ver 8.0.32 for Linux on x86_64 (MySQL Community Server – GPL)

Connection id: 123
Current database:
Current user: ssl_user@localhost
SSL: Cipher in use is TLS_AES_256_GCM_SHA384
Current pager: stdout
Using outfile: ”
Using delimiter: ;
Server version: 8.0.32 MySQL Community Server – GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 1 hour 30 min 45 sec

Threads: 10 Questions: 1000 Slow queries: 0 Opens: 200 Flush tables: 1 Open tables: 150 Queries per second
avg: 0.184
————–

# 1. 查看InnoDB加密配置
mysql> SHOW GLOBAL VARIABLES LIKE ‘innodb_encrypt%’;
+———————————-+——-+
| Variable_name | Value |
+———————————-+——-+
| innodb_encrypt_log | OFF |
| innodb_encrypt_tables | OFF |
| innodb_encryption_rotate_key_age | 1 |
| innodb_encryption_rotation_iops | 100 |
| innodb_encryption_threads | 4 |
| innodb_encrypt_temporary_tables | OFF |
+———————————-+——-+

# 2. 启用表空间加密
# 设置加密密钥管理插件
# vi /etc/my.cnf
[mysqld]
early-plugin-load=file_key_management.so
file_key_management_filename=/mysql/keys/keyfile.enc
file_key_management_filekey=”FILE:/mysql/keys/keyfile.passwd”
file_key_management_encryption_algorithm=aes_ctr
innodb_encrypt_tables=ON
innodb_encrypt_log=ON

# 创建密钥文件
$ mkdir -p /mysql/keys
$ openssl rand -hex 32 > /mysql/keys/keyfile.enc
$ chown -R mysql:mysql /mysql/keys
$ chmod 600 /mysql/keys/*

# 创建密码文件
$ echo “mysecretpassword” > /mysql/keys/keyfile.passwd
$ chown mysql:mysql /mysql/keys/keyfile.passwd
$ chmod 400 /mysql/keys/keyfile.passwd

# 重启MySQL服务
# systemctl restart mysqld

# 3. 创建加密表
mysql> CREATE TABLE encrypted_table (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
sensitive_data VARCHAR(255)
) ENGINE=InnoDB ENCRYPTION=’Y’;
Query OK, 0 rows affected (0.00 sec)

# 4. 查看表加密状态
mysql> SELECT NAME, SPACE, ENCRYPTION FROM information_schema.INNODB_TABLESPACES WHERE NAME LIKE
‘%encrypted_table%’;
+—————————+——-+————+
| NAME | SPACE | ENCRYPTION |
+—————————+——-+————+
| fgedudb/encrypted_table | 12 | YES |
+—————————+——-+————+

# 5. 列级加密
mysql> CREATE TABLE credit_cards (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
card_number VARBINARY(255),
expiration_date VARBINARY(255)
);
Query OK, 0 rows affected (0.00 sec)

# 插入加密数据
mysql> INSERT INTO credit_cards (customer_id, card_number, expiration_date)
VALUES (1, AES_ENCRYPT(‘1234-5678-9012-3456’, ‘encryption_key’),
AES_ENCRYPT(’12/28′, ‘encryption_key’));
Query OK, 1 row affected (0.00 sec)

# 查询解密数据
mysql> SELECT customer_id,
CAST(AES_DECRYPT(card_number, ‘encryption_key’) AS CHAR) AS card_number,
CAST(AES_DECRYPT(expiration_date, ‘encryption_key’) AS CHAR) AS expiration_date
FROM credit_cards;
+————-+———————-+——————+
| customer_id | card_number | expiration_date |
+————-+———————-+——————+
| 1 | 1234-5678-9012-3456 | 12/28 |
+————-+———————-+——————+

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

# 2. 配置审计日志
# vi /etc/my.cnf
[mysqld]
plugin-load=audit_log.so
audit_log_policy=ALL
# audit_log_policy=LOGINS # 只记录登录事件
# audit_log_policy=QUERIES # 只记录查询事件
# audit_log_policy=NONE # 不记录任何事件

audit_log_file=/mysql/logs/audit.log
audit_log_format=JSON
audit_log_rotate_on_size=104857600 # 100MB

# 3. 查看审计配置
mysql> SHOW GLOBAL VARIABLES LIKE ‘audit_log%’;
+—————————–+——————————-+
| Variable_name | Value |
+—————————–+——————————-+
| audit_log_buffer_size | 1048576 |
| audit_log_connection_policy | ALL |
| audit_log_current_session | ON |
| audit_log_database_engine | OFF |
| audit_log_exclude_accounts | |
| audit_log_file | /mysql/logs/audit.log |
| audit_log_flush | OFF |
| audit_log_format | JSON |
| audit_log_include_accounts | |
| audit_log_policy | ALL |
| audit_log_rotate_on_size | 104857600 |
| audit_log_rotations | 0 |
| audit_log_strategy | ASYNCHRONOUS |
+—————————–+——————————-+

# 4. 查看审计日志
# tail -n 10 /mysql/logs/audit.log
{“audit_record”:{“name”:”Connect”,”recorded_time”:”2026-04-07T22:00:00″,”server_id”:1,”status”:0,”connection_id”:123,”user”:”root”,”priv_user”:”root”,”os_login”:””,”proxy_user”:””,”host”:”localhost”,”ip”:””,”db”:””}}
{“audit_record”:{“name”:”Query”,”recorded_time”:”2026-04-07T22:00:10″,”server_id”:1,”status”:0,”connection_id”:123,”user”:”root”,”priv_user”:”root”,”os_login”:””,”proxy_user”:””,”host”:”localhost”,”ip”:””,”db”:”fgedudb”,”sql_text”:”SELECT
* FROM table1 WHERE id = 1″}}
{“audit_record”:{“name”:”Query”,”recorded_time”:”2026-04-07T22:00:20″,”server_id”:1,”status”:0,”connection_id”:123,”user”:”root”,”priv_user”:”root”,”os_login”:””,”proxy_user”:””,”host”:”localhost”,”ip”:””,”db”:”fgedudb”,”sql_text”:”UPDATE
table1 SET name = ‘updated’ WHERE id = 1″}}
{“audit_record”:{“name”:”Disconnect”,”recorded_time”:”2026-04-07T22:00:30″,”server_id”:1,”status”:0,”connection_id”:123,”user”:”root”,”priv_user”:”root”,”os_login”:””,”proxy_user”:””,”host”:”localhost”,”ip”:””,”db”:”fgedudb”}}

# 5. 使用Performance Schema进行审计
# 启用Performance Schema
mysql> SET GLOBAL performance_schema = ON;
Query OK, 0 rows affected (0.00 sec)

# 查看连接审计
mysql> SELECT * FROM performance_schema.host_cache;
+———–+————+—————-+———————+——————-+
| IP | HOST | HOST_VALIDATED | SUM_CONNECT_ERRORS | COUNT_HOST_BLOCKED |
+———–+————+—————-+———————+——————-+
| 127.0.0.1 | localhost | YES | 0 | 0 |
| 192.168.1.100 | host100 | YES | 0 | 0 |
| 192.168.1.200 | host200 | YES | 10 | 1 |
+———–+————+—————-+———————+——————-+

# 查看查询审计
mysql> SELECT * FROM performance_schema.events_statements_history WHERE sql_text LIKE ‘%DELETE%’ LIMIT 10;
+———————+———————+————+———–+———–+
| THREAD_ID | EVENT_ID | SQL_TEXT | TIMER_WAIT| RETURNED_SQLSTATE |
+———————+———————+————+———–+———–+
| 123 | 456 | DELETE FROM table1 WHERE id = 1 | 1000000000 | 00000 |
+———————+———————+————+———–+———–+

# 1. 禁用不必要的插件
mysql> SHOW PLUGINS;
+—————————-+———-+——————–+———————-+——–+
| Name | Status | Type | Library | License |
+—————————-+———-+——————–+———————-+——–+
| binlog | ACTIVE | STORAGE ENGINE | NULL | GPL |
| mysql_native_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha256_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| caching_sha2_password | ACTIVE | AUTHENTICATION | NULL | GPL |
| sha2_cache_cleaner | ACTIVE | AUDIT | NULL | GPL |
| innodb | ACTIVE | STORAGE ENGINE | NULL | GPL |
| INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL |
| … | … | … | … | … |
+—————————-+———-+——————–+———————-+——–+

# 禁用不必要的插件
mysql> UNINSTALL PLUGIN example_plugin;
Query OK, 0 rows affected (0.00 sec)

# 2. 限制系统变量修改权限
# 防止普通用户修改系统变量
# vi /etc/my.cnf
[mysqld]
# 只允许SUPER权限用户修改的变量
secure_file_priv=/mysql/tmp

# 3. 启用严格SQL模式
# vi /etc/my.cnf
[mysqld]
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

# 4. 禁用本地文件访问
# vi /etc/my.cnf
[mysqld]
secure_file_priv=”” # 禁用LOAD_FILE()函数

# 5. 限制连接超时
# vi /etc/my.cnf
[mysqld]
wait_timeout=7200
interactive_timeout=7200
connect_timeout=10

# 6. 启用密码验证插件
# vi /etc/my.cnf
[mysqld]
plugin-load-add=validate_password.so
validate_password_policy=STRONG
validate_password_length=12

# 7. 禁用符号链接
# vi /etc/my.cnf
[mysqld]
skip-symbolic-links

# 8. 重启MySQL服务使配置生效
# systemctl restart mysqld

# 9. 运行安全加固脚本
# 使用mysql_secure_installation脚本加固MySQL
$ mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: y

There are three levels of password validation policy:

LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2

Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have

a user account created for them. This is intended only for

testing, and to make the installation go a bit smoother.

You should remove them before moving into a production

environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) : y

Success.

Normally, root should only be allowed to connect from

‘localhost’. This ensures that someone cannot guess at

the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y

Success.

By default, MySQL comes with a database named ‘test’ that

anyone can access. This is also intended only for testing,

and should be removed before moving into a production

environment.

Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y

– Dropping test database…

Success.

– Removing privileges on test database…

Success.

Reloading the privilege tables will ensure that all changes

made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

Success.

All done!