# 1. 系统环境准备
# 1.1 检查系统版本
$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)

# 1.2 安装必要的依赖
$ yum install -y wget curl gcc gcc-c++ make cmake openssl openssl-devel

# 1.3 关闭不必要的服务
$ systemctl disable firewalld NetworkManager
$ systemctl stop firewalld NetworkManager

# 1.4 关闭SELinux
$ sed -i ‘s/SELINUX=enforcing/SELINUX=disabled/’ /etc/selinux/config
$ setenforce 0

# 1.5 创建MySQL用户和组
$ groupadd mysql
$ useradd -r -g mysql -s /bin/false mysql

# 2. MySQL安装
# 2.1 下载MySQL安装包
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-el7-x86_64.tar.gz

# 2.2 验证安装包完整性
$ wget https://dev.mysql.com/get/Downloads/MySQL-8.4/mysql-8.4.0-el7-x86_64.tar.gz.sha256
$ sha256sum -c mysql-8.4.0-el7-x86_64.tar.gz.sha256
mysql-8.4.0-el7-x86_64.tar.gz: OK

# 2.3 解压安装包
$ tar -zxvf mysql-8.4.0-el7-x86_64.tar.gz -C /usr/local/
$ ln -s /usr/local/mysql-8.4.0-el7-x86_64 /usr/local/mysql

# 2.4 初始化MySQL
$ cd /usr/local/mysql
$ bin/mysqld –initialize –user=mysql –basedir=/usr/local/mysql –datadir=/mysql/data

# 输出示例
2026-04-10T00:00:00.000000Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.4.0)
initializing of server in progress as process 12345
2026-04-10T00:00:00.000000Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2026-04-10T00:00:00.000000Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2026-04-10T00:00:00.000000Z 6 [Note] [MY-010454] [Server] A temporary password is generated for
root@localhost: abcdef123456

# 2.5 配置MySQL环境变量
$ echo ‘export PATH=/usr/local/mysql/bin:$PATH’ >> /etc/profile
$ source /etc/profile

# 2.6 创建MySQL配置文件
$ cat > /etc/my.cnf << EOF [mysqld] basedir=/usr/local/mysql datadir=/mysql/data port=3306 socket=/tmp/mysql.sock # 安全配置 skip_name_resolve=ON skip_networking=OFF secure_file_priv=/mysql/secure_file # 字符集配置 character_set_server=utf8mb4 collation_server=utf8mb4_unicode_ci # 日志配置 error_log=/mysql/logs/error.log log_bin=/mysql/logs/mysql-bin slow_query_log=ON slow_query_log_file=/mysql/logs/slow.log long_query_time=2 # 连接配置 max_connections=1000 wait_timeout=300 interactive_timeout=300 # 密码配置 default_authentication_plugin=caching_sha2_password EOF # 2.7 创建日志和数据目录 $ mkdir -p /mysql/logs /mysql/data /mysql/secure_file $ chown -R mysql:mysql /mysql # 2.8 启动MySQL服务 $ cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld $ chmod +x /etc/init.d/mysqld $ systemctl enable mysqld $ systemctl start mysqld # 2.9 验证MySQL服务 $ systemctl status mysqld ● mysqld.service - MySQL Server Loaded: loaded (/etc/rc.d/init.d/mysqld; bad; vendor preset: disabled) Active: active (running) since Wed 2026-04-10 00:00:00 CST; 1min ago Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 12345 ExecStart=/etc/rc.d/init.d/mysqld start (code=exited, status=0/SUCCESS) Main PID: 12346 (mysqld) CGroup: /system.slice/mysqld.service └─12346 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --log-error=/mysql/logs/error.log --pid-file=/mysql/data/mysqld.pid --socket=/tmp/mysql.sock --port=3306 # 3. 安全初始化 # 3.1 登录MySQL并修改root密码 $ mysql -u root -p Enter password: # 输入初始化时生成的临时密码 mysql> ALTER USER ‘root’@’localhost’ IDENTIFIED BY
‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 运行mysql_secure_installation脚本
$ mysql_secure_installation

# 输出示例
Securing the MySQL server deployment.

Enter password for user root: # 输入root密码

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: 1 # 选择密码策略

Using existing password for root.
Estimated strength of the password: 100
Change the password for root ? ((Press y|Y for Yes, any other key for No) : n # 不修改root密码

… skipping.
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 # 禁止root远程登录
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 # 删除test数据库
– 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!

# 1. 基础安全配置
# 1.1 禁用远程root登录
mysql> DELETE FROM mysql.user WHERE User=’root’ AND Host!=’localhost’;
mysql> FLUSH PRIVILEGES;

# 1.2 删除匿名用户
mysql> DELETE FROM mysql.user WHERE User=”;
mysql> FLUSH PRIVILEGES;

# 1.3 删除test数据库
mysql> DROP DATABASE IF EXISTS test;
mysql> DELETE FROM mysql.db WHERE Db=’test’ OR Db=’test\_%’;
mysql> FLUSH PRIVILEGES;

# 1.4 限制root用户权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM ‘root’@’localhost’;
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘root’@’localhost’ WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;

# 2. 连接安全配置
# 2.1 禁用DNS解析
$ vi /etc/my.cnf
[mysqld]
skip_name_resolve = ON

# 2.2 限制网络访问
$ vi /etc/my.cnf
[mysqld]
bind_address = 127.0.0.1,192.168.1.10

# 2.3 设置连接超时时间
$ vi /etc/my.cnf
[mysqld]
wait_timeout = 300
interactive_timeout = 300

# 2.4 限制最大连接数
$ vi /etc/my.cnf
[mysqld]
max_connections = 1000
max_user_connections = 100

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

# 3.2 配置密码策略
$ vi /etc/my.cnf
[mysqld]
validate_password.policy = MEDIUM
validate_password.length = 12
validate_password.mixed_case_count = 2
validate_password.number_count = 2
validate_password.special_char_count = 2

# 3.3 设置密码过期时间
$ vi /etc/my.cnf
[mysqld]
default_password_lifetime = 90
password_history = 5

# 4. 日志安全配置
# 4.1 启用错误日志
$ vi /etc/my.cnf
[mysqld]
error_log = /mysql/logs/error.log
log_error_verbosity = 3

# 4.2 启用二进制日志
$ vi /etc/my.cnf
[mysqld]
log_bin = /mysql/logs/mysql-bin
binlog_format = ROW
expire_logs_days = 7

# 4.3 启用慢查询日志
$ vi /etc/my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /mysql/logs/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

# 4.4 启用通用查询日志（生产环境谨慎使用）
$ vi /etc/my.cnf
[mysqld]
general_log = ON
general_log_file = /mysql/logs/general.log

# 5. 数据安全配置
# 5.1 限制文件访问
$ vi /etc/my.cnf
[mysqld]
secure_file_priv = /mysql/secure_file

# 5.2 禁用符号链接
$ vi /etc/my.cnf
[mysqld]
symbolic-links = 0

# 5.3 启用数据加密
$ 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
require_secure_transport = ON

innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables = ON

# 6. 性能安全配置
# 6.1 限制查询缓存大小
$ vi /etc/my.cnf
[mysqld]
query_cache_type = 0
query_cache_size = 0

# 6.2 配置临时表
$ vi /etc/my.cnf
[mysqld]
tmp_table_size = 64M
max_heap_table_size = 64M

# 6.3 配置连接缓冲区
$ vi /etc/my.cnf
[mysqld]
join_buffer_size = 128K
sort_buffer_size = 256K
read_buffer_size = 128K
read_rnd_buffer_size = 256K

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

# 8. 验证配置
# 8.1 查看MySQL配置
mysql> SHOW VARIABLES LIKE ‘skip_name_resolve’;
+——————-+——-+——————-+
| Variable_name | Value | Variable_source |
+——————-+——-+——————-+
| skip_name_resolve | ON | MYSQL_CONFIG |
+——————-+——-+——————-+

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 | MEDIUM |
| validate_password.special_char_count | 2 |
+————————————–+——–+

mysql> SHOW VARIABLES LIKE ‘default_password_lifetime’;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| default_password_lifetime | 90 |
+—————————+——-+

mysql> SHOW VARIABLES LIKE ‘secure_file_priv’;
+——————+——————-+
| Variable_name | Value |
+——————+——————-+
| secure_file_priv | /mysql/secure_file |
+——————+——————-+

# 1. 用户创建规范
# 1.1 创建应用用户
mysql> CREATE USER ‘app_user’@’192.168.1.%’ IDENTIFIED BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 1.2 创建报表用户
mysql> CREATE USER ‘report_user’@’192.168.1.%’ IDENTIFIED BY ‘SecurePassword456!’;
Query OK, 0 rows affected (0.01 sec)

# 1.3 创建开发用户
mysql> CREATE USER ‘dev_user’@’192.168.1.%’ IDENTIFIED BY ‘SecurePassword789!’;
Query OK, 0 rows affected (0.01 sec)

# 1.4 创建管理员用户
mysql> CREATE USER ‘admin_user’@’localhost’ IDENTIFIED BY ‘SecurePasswordAdmin!’;
Query OK, 0 rows affected (0.01 sec)

# 2. 用户属性配置
# 2.1 设置密码过期时间
mysql> ALTER USER ‘app_user’@’192.168.1.%’ PASSWORD EXPIRE INTERVAL 90 DAY;
Query OK, 0 rows affected (0.01 sec)

# 2.2 设置密码历史
mysql> ALTER USER ‘app_user’@’192.168.1.%’ PASSWORD HISTORY 5;
Query OK, 0 rows affected (0.01 sec)

# 2.3 锁定/解锁用户
mysql> ALTER USER ‘app_user’@’192.168.1.%’ ACCOUNT LOCK;
Query OK, 0 rows affected (0.01 sec)

mysql> ALTER USER ‘app_user’@’192.168.1.%’ ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.01 sec)

# 2.4 设置用户资源限制
mysql> CREATE USER ‘limited_user’@’192.168.1.%’ IDENTIFIED BY ‘SecurePassword123!’
WITH MAX_USER_CONNECTIONS 10
MAX_QUERIES_PER_HOUR 1000
MAX_UPDATES_PER_HOUR 500
MAX_CONNECTIONS_PER_HOUR 100;
Query OK, 0 rows affected (0.01 sec)

# 3. 用户验证
# 3.1 使用密码验证插件
mysql> CREATE USER ‘sha256_user’@’localhost’ IDENTIFIED WITH caching_sha2_password BY ‘SecurePassword123!’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 使用SSL证书验证
mysql> CREATE USER ‘ssl_user’@’%’ IDENTIFIED BY ‘SecurePassword123!’ REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

# 3.3 使用双因素认证（MySQL 8.0.27+）
# 参考：https://dev.mysql.com/doc/refman/8.4/en/password-authentication-plugins.html

# 4. 用户管理最佳实践
# 4.1 定期清理无效用户
mysql> SELECT User, Host, account_locked, password_expired FROM mysql.user WHERE account_locked = ‘Y’ OR
password_expired = ‘Y’;
+———-+—————+—————-+—————–+
| User | Host | account_locked | password_expired |
+———-+—————+—————-+—————–+
| old_user | 192.168.1.% | Y | N |
+———-+—————+—————-+—————–+

# 删除无效用户
mysql> DROP USER ‘old_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 4.2 定期修改密码
# 创建密码修改脚本
#!/bin/bash
# change_password.sh

MYSQL_USER=”root”
MYSQL_PASS=”SecurePasswordAdmin!”
USER_LIST=(‘app_user’ ‘report_user’ ‘dev_user’)

for USER in “${USER_LIST[@]}”
do
NEW_PASS=$(openssl rand -base64 12)
echo “Changing password for $USER…”
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “ALTER USER ‘$USER’@’192.168.1.%’ IDENTIFIED BY ‘$NEW_PASS’;”
echo “New password for $USER: $NEW_PASS” >> /mysql/docs/new_passwords.txt
done

# 设置crontab定期执行
# crontab -e
# 每90天修改一次密码
0 0 */90 * * /mysql/scripts/change_password.sh

# 4.3 监控用户活动
# 使用performance_schema监控用户活动
mysql> SELECT
USER,
HOST,
COUNT(*) AS connection_count
FROM performance_schema.threads
WHERE USER IS NOT NULL
GROUP BY USER, HOST
ORDER BY connection_count DESC;
+———-+—————+——————+
| USER | HOST | connection_count |
+———-+—————+——————+
| app_user | 192.168.1.100 | 50 |
| report_user | 192.168.1.101 | 10 |
| dev_user | 192.168.1.102 | 5 |
+———-+—————+——————+

# 1. 权限授予规范
# 1.1 遵循最小权限原则
# 错误示例：授予用户过多权限
mysql> GRANT ALL PRIVILEGES ON *.* TO ‘app_user’@’%’;

# 正确示例：只授予用户需要的权限
mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON fgedudb.* TO ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 1.2 授予表级权限
mysql> GRANT SELECT, INSERT ON fgedudb.fgedu_users TO ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 1.3 授予列级权限
mysql> GRANT SELECT (id, name, email) ON fgedudb.fgedu_users TO ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

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

# 2. 角色管理
# 2.1 创建角色
mysql> CREATE ROLE ‘read_only’, ‘read_write’, ‘ddl_admin’, ‘db_admin’;
Query OK, 0 rows affected (0.01 sec)

# 2.2 授予角色权限
mysql> GRANT SELECT ON *.* TO ‘read_only’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO ‘read_write’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT CREATE, ALTER, DROP, CREATE VIEW, CREATE INDEX ON *.* TO ‘ddl_admin’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ALL PRIVILEGES ON *.* TO ‘db_admin’ WITH GRANT OPTION;
Query OK, 0 rows affected (0.01 sec)

# 2.3 分配角色给用户
mysql> GRANT ‘read_write’ TO ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ‘read_only’ TO ‘report_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ‘read_write’, ‘ddl_admin’ TO ‘dev_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> GRANT ‘db_admin’ TO ‘admin_user’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

# 2.4 设置默认角色
mysql> SET DEFAULT ROLE ‘read_write’ FOR ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> SET DEFAULT ROLE ‘read_only’ FOR ‘report_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> SET DEFAULT ROLE ‘read_write’, ‘ddl_admin’ FOR ‘dev_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

mysql> SET DEFAULT ROLE ‘db_admin’ FOR ‘admin_user’@’localhost’;
Query OK, 0 rows affected (0.01 sec)

# 3. 权限撤销
# 3.1 撤销数据库级权限
mysql> REVOKE DELETE ON fgedudb.* FROM ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 3.2 撤销表级权限
mysql> REVOKE INSERT ON fgedudb.fgedu_users FROM ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 3.3 撤销角色权限
mysql> REVOKE ‘ddl_admin’ FROM ‘dev_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 3.4 撤销所有权限
mysql> REVOKE ALL PRIVILEGES ON fgedudb.* FROM ‘app_user’@’192.168.1.%’;
Query OK, 0 rows affected (0.01 sec)

# 4. 权限审计
# 4.1 查看用户权限
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, UPDATE ON `fgedudb`.* TO `app_user`@`192.168.1.%` |
| GRANT `read_write`@`%` TO `app_user`@`192.168.1.%` |
| SET DEFAULT ROLE `read_write` FOR `app_user`@`192.168.1.%` |
+———————————————————————-+

# 4.2 查看角色权限
mysql> SHOW GRANTS FOR ‘read_write’;
+————————————————+——————-+
| Grants for read_write@% | Variable_source |
+————————————————+——————-+
| GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO `read_write`@`%` | NULL |
+————————————————+——————-+

# 4.3 查看角色分配
mysql> SELECT * FROM mysql.role_edges;
+———–+———–+———+———–+——————-+——————-+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION | GRANTEE_PRIVILEGES |
+———–+———–+———+———–+——————-+——————-+
| % | app_user | % | read_write| N | NULL |
| % | report_user | % | read_only | N | NULL |
| % | dev_user | % | read_write| N | NULL |
| % | dev_user | % | ddl_admin | N | NULL |
| localhost | admin_user | localhost | db_admin | N | NULL |
+———–+———–+———+———–+——————-+——————-+

# 4.4 定期审计权限
# 创建权限审计脚本
#!/bin/bash
# audit_permissions.sh

MYSQL_USER=”root”
MYSQL_PASS=”SecurePasswordAdmin!”
AUDIT_DIR=”/mysql/audit/$(date ‘+%Y-%m-%d’)”

mkdir -p $AUDIT_DIR

# 导出用户列表
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “SELECT User, Host, plugin, account_locked, password_expired FROM
mysql.user;” > $AUDIT_DIR/user_list.txt

# 导出角色列表
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “SELECT * FROM mysql.role_edges;” > $AUDIT_DIR/role_list.txt

# 导出权限列表
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “SELECT * FROM mysql.db;” > $AUDIT_DIR/db_privileges.txt
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “SELECT * FROM mysql.tables_priv;” > $AUDIT_DIR/table_privileges.txt
mysql -u $MYSQL_USER -p$MYSQL_PASS -e “SELECT * FROM mysql.columns_priv;” > $AUDIT_DIR/column_privileges.txt

# 生成审计报告
cat > $AUDIT_DIR/permission_audit_report.txt << EOF MySQL权限审计报告 审计时间：$(date '+%Y-%m-%d %H:%M:%S' ) 服务器IP：192.168.1.10 MySQL版本：8.4.0 用户列表： $(cat $AUDIT_DIR/user_list.txt) 角色列表： $(cat $AUDIT_DIR/role_list.txt) 数据库权限： $(cat $AUDIT_DIR/db_privileges.txt) 表级权限： $(cat $AUDIT_DIR/table_privileges.txt) 列级权限： $(cat $AUDIT_DIR/column_privileges.txt) 审计建议： - 定期审查用户权限，确保符合最小权限原则 - 及时撤销不必要的权限 - 监控权限变更操作，确保可追溯性 - 使用角色管理权限，提高权限管理效率 EOF # 设置crontab定期执行 # crontab -e # 每周一凌晨3点执行权限审计 0 3 * * 1 /mysql/scripts/audit_permissions.sh

# 1. 数据加密
# 1.1 SSL/TLS加密传输
# 生成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

# 验证证书
$ openssl verify -CAfile ca.pem server-cert.pem client-cert.pem
server-cert.pem: OK
client-cert.pem: OK

# 配置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

# 要求加密连接
require_secure_transport = ON

# 重启MySQL服务
$ systemctl restart mysqld

# 验证SSL配置
mysql> SHOW VARIABLES LIKE ‘%ssl%’;
+————————————-+——————————–+——————-+
| Variable_name | Value | Variable_source |
+————————————-+——————————–+——————-+
| have_openssl | YES | COMPILED |
| have_ssl | YES | COMPILED |
| ssl_ca | /mysql/ssl/ca.pem | MYSQL_CONFIG |
| ssl_capath | | MYSQL_CONFIG |
| ssl_cert | /mysql/ssl/server-cert.pem | MYSQL_CONFIG |
| ssl_cipher | | MYSQL_CONFIG |
| ssl_crl | | MYSQL_CONFIG |
| ssl_crlpath | | MYSQL_CONFIG |
| ssl_key | /mysql/ssl/server-key.pem | MYSQL_CONFIG |
| require_secure_transport | ON | MYSQL_CONFIG |
+————————————-+——————————–+——————-+

# 测试SSL连接
$ mysql -u root -p –ssl-ca=/mysql/ssl/ca.pem –ssl-cert=/mysql/ssl/client-cert.pem
–ssl-key=/mysql/ssl/client-key.pem

mysql> SHOW STATUS LIKE ‘Ssl_cipher’;
+—————+—————————+
| Variable_name | Value |
+—————+—————————+
| Ssl_cipher | TLS_AES_256_GCM_SHA384 |
+—————+—————————+

# 1.2 数据存储加密
# 启用InnoDB表空间加密
$ vi /etc/my.cnf
[mysqld]
innodb_encrypt_tables = ON
innodb_encrypt_log = ON
innodb_encrypt_temporary_tables = ON
innodb_encryption_threads = 4

# 重启MySQL服务
$ systemctl restart mysqld

# 创建加密表
mysql> CREATE TABLE fgedudb.encrypted_table (
id INT PRIMARY KEY AUTO_INCREMENT,
sensitive_data VARCHAR(255) NOT NULL
) ENCRYPTION=’Y’;
Query OK, 0 rows affected (0.01 sec)

# 查看表加密状态
mysql> SELECT TABLE_SCHEMA, TABLE_NAME, CREATE_OPTIONS FROM information_schema.TABLES WHERE
TABLE_NAME = ‘encrypted_table’;
+————–+——————+—————-+——————-+
| TABLE_SCHEMA | TABLE_NAME | CREATE_OPTIONS | Variable_source |
+————–+——————+—————-+——————-+
| fgedudb | encrypted_table | ENCRYPTION=”Y” | NULL |
+————–+——————+—————-+——————-+

# 1.3 列级加密
# 使用AES加密函数
mysql> INSERT INTO fgedudb.users (id, name, email, phone, id_card)
VALUES (1, ‘张三’, ‘zhangsan@example.com’,
AES_ENCRYPT(‘13800138000’, ‘encryption_key’),
AES_ENCRYPT(‘110101199001011234’, ‘encryption_key’));
Query OK, 1 row affected (0.00 sec)

# 使用AES解密函数
mysql> SELECT id, name, email,
AES_DECRYPT(phone, ‘encryption_key’) AS phone,
AES_DECRYPT(id_card, ‘encryption_key’) AS id_card
FROM fgedudb.users WHERE id = 1;
+—-+——–+———————+————-+——————+——————-+
| id | name | email | phone | id_card | Variable_source |
+—-+——–+———————+————-+——————+——————-+
| 1 | 张三 | zhangsan@example.com | 13800138000 | 110101199001011234 | NULL |
+—-+——–+———————+————-+——————+——————-+

# 2. 数据备份与恢复
# 2.1 逻辑备份（使用mysqldump）
# 备份单个数据库
$ mysqldump -u root -p fgedudb > /mysql/backup/fgedudb_$(date ‘+%Y%m%d’).sql

# 备份所有数据库
$ mysqldump -u root -p –all-databases > /mysql/backup/all_databases_$(date ‘+%Y%m%d’).sql

# 备份特定表
$ mysqldump -u root -p fgedudb fgedu_users fgedu_orders > /mysql/backup/fgedudb_tables_$(date
‘+%Y%m%d’).sql

# 2.2 物理备份（使用xtrabackup）
# 安装xtrabackup
$ yum install -y percona-xtrabackup-80

# 全量备份
$ xtrabackup –backup –user=root –password=SecurePasswordAdmin!
–target-dir=/mysql/backup/full_$(date ‘+%Y%m%d’)

# 增量备份
$ xtrabackup –backup –user=root –password=SecurePasswordAdmin!
–target-dir=/mysql/backup/incr_$(date ‘+%Y%m%d_%H%M%S’)
–incremental-basedir=/mysql/backup/full_$(date ‘+%Y%m%d’)

# 2.3 备份验证
# 验证逻辑备份
$ mysqlcheck -c -u root -p fgedudb < /mysql/backup/fgedudb_$(date '+%Y%m%d' ).sql # 验证物理备份 $ xtrabackup --prepare --target-dir=/mysql/backup/full_$(date '+%Y%m%d' ) # 2.4 数据恢复 # 逻辑恢复 $ mysql -u root -p fgedudb < /mysql/backup/fgedudb_$(date '+%Y%m%d' ).sql # 物理恢复 # 停止MySQL服务 $ systemctl stop mysqld # 清理数据目录 $ rm -rf /mysql/data/* # 恢复备份 $ xtrabackup --copy-back --target-dir=/mysql/backup/full_$(date '+%Y%m%d' ) # 设置权限 $ chown -R mysql:mysql /mysql/data # 启动MySQL服务 $ systemctl start mysqld # 3. 数据脱敏 # 3.1 敏感数据识别 # 识别敏感字段 mysql> SELECT TABLE_SCHEMA,
TABLE_NAME, COLUMN_NAME, DATA_TYPE
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = ‘fgedudb’
AND COLUMN_NAME IN (‘phone’, ‘id_card’, ’email’, ‘password’, ‘credit_card’);
+————–+—————+————-+———–+——————-+
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | DATA_TYPE | Variable_source |
+————–+—————+————-+———–+——————-+
| fgedudb | users | phone | varbinary | NULL |
| fgedudb | users | id_card | varbinary | NULL |
| fgedudb | users | email | varchar | NULL |
| fgedudb | users | password | varchar | NULL |
+————–+—————+————-+———–+——————-+

# 3.2 数据脱敏处理
# 创建脱敏函数
DELIMITER //
CREATE FUNCTION fgedudb.mask_phone(phone VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
RETURN CONCAT(SUBSTRING(phone, 1, 3), ‘****’, SUBSTRING(phone, 8));
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION fgedudb.mask_id_card(id_card VARCHAR(18)) RETURNS VARCHAR(18)
BEGIN
RETURN CONCAT(SUBSTRING(id_card, 1, 6), ‘********’, SUBSTRING(id_card, 15));
END //
DELIMITER ;

DELIMITER //
CREATE FUNCTION fgedudb.mask_email(email VARCHAR(100)) RETURNS VARCHAR(100)
BEGIN
RETURN CONCAT(SUBSTRING(email, 1, 1), ‘****’, SUBSTRING(email, LOCATE(‘@’, email)));
END //
DELIMITER ;

# 使用脱敏函数
mysql> SELECT id, name,
fgedudb.mask_phone(AES_DECRYPT(phone, ‘encryption_key’)) AS masked_phone,
fgedudb.mask_id_card(AES_DECRYPT(id_card, ‘encryption_key’)) AS masked_id_card,
fgedudb.mask_email(email) AS masked_email
FROM fgedudb.users;
+—-+——–+————–+—————–+———————-+——————-+
| id | name | masked_phone | masked_id_card | masked_email | Variable_source |
+—-+——–+————–+—————–+———————-+——————-+
| 1 | 张三 | 138****8000 | 110101********34 | z****@example.com | NULL |
+—-+——–+————–+—————–+———————-+——————-+

# 4. 数据访问控制
# 4.1 限制直接访问敏感表
mysql> REVOKE ALL PRIVILEGES ON fgedudb.users FROM ‘app_user’@’192.168.1.%’;
mysql> GRANT SELECT ON fgedudb.view_users TO ‘app_user’@’192.168.1.%’;

# 创建视图，只包含非敏感字段或脱敏后的字段
mysql> CREATE VIEW fgedudb.view_users AS
SELECT id, name, fgedudb.mask_email(email) AS masked_email
FROM fgedudb.users;

# 4.2 审计敏感数据访问
# 配置审计日志，记录敏感表的访问
$ vi /etc/my.cnf
[mysqld]
audit_log_include_databases = ‘fgedudb’
audit_log_include_events = ‘query’
audit_log_include_tables = ‘users’

# 重启MySQL服务
$ systemctl restart mysqld

# 查看敏感表访问记录
$ grep “‘sqltext’:’SELECT.*users'” /mysql/logs/audit.log

# 1. 日志类型与配置
# 1.1 错误日志
# 配置错误日志
$ vi /etc/my.cnf
[mysqld]
error_log = /mysql/logs/error.log
log_error_verbosity = 3

# 1.2 二进制日志
# 配置二进制日志
$ vi /etc/my.cnf
[mysqld]
log_bin = /mysql/logs/mysql-bin
binlog_format = ROW
expire_logs_days = 7
max_binlog_size = 1G

# 1.3 慢查询日志
# 配置慢查询日志
$ vi /etc/my.cnf
[mysqld]
slow_query_log = ON
slow_query_log_file = /mysql/logs/slow.log
long_query_time = 2
log_queries_not_using_indexes = ON

# 1.4 通用查询日志
# 配置通用查询日志（生产环境谨慎使用）
$ vi /etc/my.cnf
[mysqld]
general_log = ON
general_log_file = /mysql/logs/general.log

# 1.5 审计日志
# 安装审计日志插件
mysql> INSTALL PLUGIN audit_log SONAME ‘audit_log.so’;

# 配置审计日志
$ vi /etc/my.cnf
[mysqld]
audit_log_format = JSON
audit_log_file = /mysql/logs/audit.log
audit_log_policy = ALL
audit_log_rotate_on_size = 104857600 # 100MB
audit_log_rotations = 10

# 2. 日志安全管理
# 2.1 设置日志文件权限
$ chown -R mysql:mysql /mysql/logs
$ chmod -R 600 /mysql/logs

# 2.2 日志备份
# 创建日志备份脚本
#!/bin/bash
# backup_logs.sh

LOG_DIR=”/mysql/logs”
BACKUP_DIR=”/mysql/backup/logs/$(date ‘+%Y-%m-%d’)”

mkdir -p $BACKUP_DIR

# 备份错误日志
cp $LOG_DIR/error.log $BACKUP_DIR/

# 备份慢查询日志
cp $LOG_DIR/slow.log $BACKUP_DIR/

# 备份通用查询日志
cp $LOG_DIR/general.log $BACKUP_DIR/ 2>/dev/null

# 备份审计日志
cp $LOG_DIR/audit.log $BACKUP_DIR/ 2>/dev/null

# 记录备份日志
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) Backup logs completed” >> /mysql/logs/backup_log.log

# 设置crontab定期执行
# crontab -e
# 每天凌晨2点备份日志
0 2 * * * /mysql/scripts/backup_logs.sh

# 2.3 日志清理
# 创建日志清理脚本
#!/bin/bash
# clean_logs.sh

LOG_DIR=”/mysql/logs”
BACKUP_DIR=”/mysql/backup/logs”
RETENTION_DAYS=30

# 清理过期的日志备份
find $BACKUP_DIR -name “*” -mtime +$RETENTION_DAYS -type f -exec rm -f {} \;

# 清理过期的二进制日志
mysql -u root -p -e “PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 7 DAY);”

# 记录清理日志
echo “$(date ‘+%Y-%m-%d %H:%M:%S’) Cleaned old logs” >> /mysql/logs/clean_log.log

# 设置crontab定期执行
# crontab -e
# 每天凌晨1点清理日志
0 1 * * * /mysql/scripts/clean_logs.sh

# 3. 日志分析
# 3.1 错误日志分析
# 使用mysqldumpslow分析慢查询日志
$ mysqldumpslow -s t -t 10 /mysql/logs/slow.log

# 输出示例
Reading mysql slow query log from /mysql/logs/slow.log
Count: 10 Time=5.00s (50s) Lock=0.00s (0s) Rows=1000.0 (10000 rows), root[root]@localhost
SELECT * FROM fgedudb.users WHERE name LIKE ‘%?%’

# 3.2 二进制日志分析
# 查看二进制日志内容
$ mysqlbinlog /mysql/logs/mysql-bin.000001

# 3.3 审计日志分析
# 使用脚本分析审计日志
#!/bin/bash
# analyze_audit_log.sh