-- 創(chuàng)建最小權(quán)限的應(yīng)用賬號(hào)（僅允許特定庫的增刪改查）
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'StrongP@ss2024!';
GRANT SELECT, INSERT, UPDATE, DELETE
  ON your_database.*
  TO 'app_user'@'192.168.1.%';
FLUSH PRIVILEGES;

-- 查看當(dāng)前所有賬號(hào)權(quán)限
SELECT user, host, authentication_string FROM mysql.user;

-- 撤銷危險(xiǎn)權(quán)限（SUPER、FILE、PROCESS 等）
REVOKE SUPER ON *.* FROM 'app_user'@'192.168.1.%';

-- 查找并刪除匿名賬戶
SELECT user, host FROM mysql.user WHERE user = '';
DELETE FROM mysql.user WHERE user = '';

-- 刪除 test 數(shù)據(jù)庫及其授權(quán)記錄
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE db = 'test' OR db = 'test\_%';
FLUSH PRIVILEGES;

# 推薦：使用官方安全初始化腳本（MySQL 8.0+）
mysql_secure_installation

[mysqld]
# 修改監(jiān)聽端口（避免使用 3306）
port = 33066

# 僅綁定內(nèi)網(wǎng)IP，禁止 0.0.0.0 監(jiān)聽
bind-address = 127.0.0.1
# 若需多網(wǎng)卡綁定（MySQL 8.0.13+）
# bind-address = 192.168.1.100,127.0.0.1

# 禁用 MySQL X Protocol（若不使用）
mysqlx = 0

# 僅允許應(yīng)用服務(wù)器網(wǎng)段訪問數(shù)據(jù)庫端口
firewall-cmd --permanent --add-rich-rule='
  rule family="ipv4"
  source address="192.168.1.0/24"
  port protocol="tcp" port="33066"
  accept'
firewall-cmd --reload

-- 安裝密碼驗(yàn)證組件（MySQL 8.0+）
INSTALL COMPONENT 'file://component_validate_password';

-- 配置密碼強(qiáng)度策略
SET GLOBAL validate_password.policy = STRONG;
SET GLOBAL validate_password.length = 12;
SET GLOBAL validate_password.mixed_case_count = 1;
SET GLOBAL validate_password.special_char_count = 1;
SET GLOBAL validate_password.number_count = 1;

-- 失敗登錄追蹤：連續(xù)失敗3次鎖定賬號(hào)1天
ALTER USER 'app_user'@'%'
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LOCK_TIME 1;

-- 設(shè)置密碼90天強(qiáng)制過期
ALTER USER 'app_user'@'%'
  PASSWORD EXPIRE INTERVAL 90 DAY;

# MySQL 自帶工具一鍵生成 CA + 服務(wù)端 + 客戶端證書
mysql_ssl_rsa_setup --datadir=/var/lib/mysql

# 驗(yàn)證生成的證書文件
ls /var/lib/mysql/*.pem
# ca.pem  ca-key.pem  server-cert.pem  server-key.pem
# client-cert.pem  client-key.pem

[mysqld]
ssl-ca   = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/server-cert.pem
ssl-key  = /var/lib/mysql/server-key.pem
# 強(qiáng)制所有連接使用 TLS 1.2+
tls_version = TLSv1.2,TLSv1.3
[client]
ssl-ca   = /var/lib/mysql/ca.pem
ssl-cert = /var/lib/mysql/client-cert.pem
ssl-key  = /var/lib/mysql/client-key.pem

-- 對(duì)特定賬號(hào)強(qiáng)制 SSL（推薦）
ALTER USER 'app_user'@'%' REQUIRE SSL;

-- 更嚴(yán)格：要求雙向證書認(rèn)證（適合 DBA 賬號(hào)）
ALTER USER 'dba_user'@'%' REQUIRE X509;

-- 驗(yàn)證 SSL 是否生效
SHOW VARIABLES LIKE '%ssl%';
SHOW STATUS LIKE 'Ssl_cipher';

[mysqld]
# 慢查詢?nèi)罩荆ㄍ扑]常駐開啟）
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 2

# Binary Log（復(fù)制與時(shí)間點(diǎn)恢復(fù)的基礎(chǔ)）
log_bin         = /var/log/mysql/mysql-bin
binlog_format   = ROW
expire_logs_days = 7

# 通用查詢?nèi)罩荆ㄉa(chǎn)環(huán)境慎用，性能影響大，可臨時(shí)開啟排查）
# general_log      = 1
# general_log_file = /var/log/mysql/general.log

-- MySQL Enterprise Audit Plugin
INSTALL PLUGIN audit_log SONAME 'audit_log.so';

SET GLOBAL audit_log_policy = 'ALL';
SET GLOBAL audit_log_format = 'JSON';
SET GLOBAL audit_log_file   = '/var/log/mysql/audit.log';

[mysqld]
# 禁止 LOAD DATA INFILE 訪問服務(wù)端文件
local_infile = 0

# 限制 SELECT INTO OUTFILE 只能寫入指定目錄
secure_file_priv = /var/lib/mysql-files

# ? 以下配置極度危險(xiǎn)，絕對(duì)禁止！
# secure_file_priv =   （空值 = 允許寫入任意路徑）

-- 確認(rèn)沒有普通賬號(hào)擁有 FILE 權(quán)限
SELECT user, host, File_priv
  FROM mysql.user
  WHERE File_priv = 'Y' AND user != 'root';
-- 撤銷 FILE 權(quán)限
REVOKE FILE ON *.* FROM 'some_user'@'%';

[mysqld]
# 禁止符號(hào)鏈接（防止繞過權(quán)限訪問文件系統(tǒng)）
symbolic-links = 0

# 禁用 FEDERATED 存儲(chǔ)引擎（若不使用）
skip-federated

# 禁止 DNS 反向解析（提升連接性能，防止 DNS 欺騙）
skip_name_resolve = 1

# 禁止 SHOW DATABASES 暴露所有庫名給普通用戶
skip-show-database

# 限制最大連接數(shù)，防止連接耗盡攻擊（DoS）
max_connections    = 500
max_connect_errors = 10

#!/bin/bash
# 建議配合 crontab 每日凌晨執(zhí)行
DATE=$(date +%Y%m%d_%H%M%S)
BACKUP_DIR=/backup/mysql

# 使用 mysqldump 全量備份
mysqldump \
  --single-transaction \
  --flush-logs \
  --master-data=2 \
  --all-databases \
  -u backup_user -p'BackupP@ss!' \
  | gzip > $BACKUP_DIR/full_$DATE.sql.gz

# 驗(yàn)證備份文件大?。ㄐ∮?1KB 視為異常）
FILESIZE=$(stat -c%s "$BACKUP_DIR/full_$DATE.sql.gz")
[ "$FILESIZE" -lt 1024 ] && echo "[ERROR] 備份文件異常！請(qǐng)檢查！"

# 清理7天前的舊備份
find $BACKUP_DIR -name "*.sql.gz" -mtime +7 -delete

-- 定期執(zhí)行：找出所有擁有全局高危權(quán)限的賬號(hào)
SELECT user, host,
       Super_priv, Grant_priv, File_priv,
       Shutdown_priv, Process_priv
FROM   mysql.user
WHERE  Super_priv = 'Y'
    OR Grant_priv = 'Y'
    OR File_priv  = 'Y';

[mysqld]
# ===== 網(wǎng)絡(luò)安全 =====
bind-address        = 127.0.0.1
port                = 33066
skip_name_resolve   = 1
mysqlx              = 0
max_connections     = 500
max_connect_errors  = 10
# ===== 認(rèn)證與加密 =====
ssl-ca              = /var/lib/mysql/ca.pem
ssl-cert            = /var/lib/mysql/server-cert.pem
ssl-key             = /var/lib/mysql/server-key.pem
tls_version         = TLSv1.2,TLSv1.3
# ===== 文件系統(tǒng)隔離 =====
local_infile        = 0
secure_file_priv    = /var/lib/mysql-files
symbolic-links      = 0
# ===== 功能最小化 =====
skip-federated
skip-show-database
# ===== 日志 =====
slow_query_log      = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time     = 2
log_bin             = /var/log/mysql/mysql-bin
binlog_format       = ROW
expire_logs_days    = 7

# 重啟 MySQL 服務(wù)
systemctl restart mysqld

# 驗(yàn)證關(guān)鍵參數(shù)是否生效
mysqladmin -u root -p variables | grep -E \
  'ssl|local_infile|secure_file|skip_name|max_conn|port'

# 檢查數(shù)據(jù)庫完整性
mysqlcheck --all-databases -u root -p