Secure MySQL 8 in 30 Minutes with 6 Production‑Ready Scripts

Ever left MySQL 8 with the default root password and later discovered encrypted ransomware, unknown hacker accounts, and DROP DATABASE statements in the audit log?

Don't wait for a disaster—here are six production‑grade Shell/SQL scripts that harden MySQL 8 security from a bare‑metal setup to military‑grade protection in about 30 minutes.

🛠️ Script 1: Automatically Disable High‑Risk Accounts (including anonymous users and empty passwords)

Risk: MySQL 8 may retain test accounts that allow instant hacker access.

#!/bin/bash
# secure_mysql_users.sh
MYSQL_USER="root"
MYSQL_PASS="YourStrongRootPass!"
MYSQL_CMD="mysql -u$MYSQL_USER -p$MYSQL_PASS -Nse"

echo "🔍 正在清理高危账号..."

# Delete anonymous users
$MYSQL_CMD "DROP USER IF EXISTS ''@'localhost';"
$MYSQL_CMD "DROP USER IF EXISTS ''@'%';"

# Drop test database if it exists
$MYSQL_CMD "DROP DATABASE IF EXISTS test;"

# Remove accounts with empty passwords
$MYSQL_CMD "DELETE FROM mysql.user WHERE authentication_string = '';"

# Flush privileges
$MYSQL_CMD "FLUSH PRIVILEGES;"

echo "✅ 高危账号清理完成！"

Run with

chmod +x secure_mysql_users.sh && ./secure_mysql_users.sh

🛠️ Script 2: Enable Enterprise‑Level Audit Logging Without Plugins

MySQL 8 includes the Enterprise Audit feature (available as audit_log in the community edition).

-- enable_audit.sql
INSTALL COMPONENT 'file://component_audit_log';
SET PERSIST audit_log_format = 'JSON';
SET PERSIST audit_log_policy = 'ALL';  -- Log all operations
SET PERSIST audit_log_rotate_on_size = 1073741824; -- 1 GB rotation
SET PERSIST audit_log_connection_policy = 'ALL';
SET PERSIST audit_log_exclude_accounts = 'monitor@localhost'; -- Exclude monitoring account

Check the log path: SHOW VARIABLES LIKE 'audit_log_file'; Execute with mysql -uroot -p < enable_audit.sql. The log defaults to /var/lib/mysql/audit.log in JSON format, ready for ELK analysis.

🛠️ Script 3: Force All Users to Connect via SSL

Prevent internal traffic sniffing, even for the root account.

-- enforce_ssl.sql
-- Enable SSL for root
ALTER USER 'root'@'localhost' REQUIRE SSL;

-- Example: enable SSL for an application user
CREATE USER 'app_user'@'10.0.0.%' IDENTIFIED BY 'StrongPass!2025' REQUIRE SSL;
GRANT SELECT, INSERT, UPDATE ON myapp.* TO 'app_user'@'10.0.0.%';

-- Global enforcement (MySQL 8.0.28+)
SET PERSIST require_secure_transport = ON;

Verify with:

SHOW STATUS LIKE 'Ssl_cipher'; -- Non‑empty means SSL is active

🛠️ Script 4: Detect and Clean Over‑Privileged Accounts

Did a developer grant ALL PRIVILEGES for testing and forget to remove them?

#!/bin/bash
# check_over_privileged.sh
MYSQL_USER="root"
MYSQL_PASS="YourStrongRootPass!"
OUTPUT="/tmp/over_privileged_users.txt"

mysql -u$MYSQL_USER -p$MYSQL_PASS -sN <<EOF > $OUTPUT
SELECT user, host 
FROM mysql.user 
WHERE (Select_priv = 'Y' AND Insert_priv = 'Y' AND Update_priv = 'Y' AND Delete_priv = 'Y' AND Drop_priv = 'Y')
   OR Super_priv = 'Y'
   OR Grant_priv = 'Y';
EOF

if [ -s "$OUTPUT" ]; then
  echo "⚠️ 发现高权限账号，请立即审查:"
  cat $OUTPUT
else
  echo "✅ 未发现过度授权账号"
fi

🛠️ Script 5: Rotate Root Password Every 90 Days (Compliance Requirement)

Regulatory standards demand periodic password changes for privileged accounts.

#!/bin/bash
# rotate_root_password.sh
NEW_PASS=$(openssl rand -base64 16 | tr -d "=+/" | cut -c1-16)
MYSQL_CMD="mysql -uroot -p$(cat /root/.mysql_root_pass) -se"

# Update root password
$MYSQL_CMD "ALTER USER 'root'@'localhost' IDENTIFIED BY '$NEW_PASS';"

# Store the new password securely
echo "$NEW_PASS" > /root/.mysql_root_pass
chmod 600 /root/.mysql_root_pass

# Optional: push to Vault or send alert
echo "🔑 root 密码已更新: $NEW_PASS" | wechat-alert

Ensure the password file is protected with chmod 600 to avoid leakage.

🛠️ Script 6: Generate a Security Configuration Report for Audits

#!/bin/bash
# mysql_security_report.sh
REPORT="/var/log/mysql/security_report_$(date +%F).txt"

{
  echo "=== MySQL 8 安全配置报告 ==="
  echo "检查时间: $(date)"
  echo ""
  echo "1. 是否启用 SSL 强制传输:"
  mysql -sN -e "SHOW VARIABLES LIKE 'require_secure_transport';"
  echo ""
  echo "2. 审计日志是否启用:"
  mysql -sN -e "SELECT STATUS FROM performance_schema.setup_consumers WHERE NAME='audit_log';"
  echo ""
  echo "3. 高危函数状态 (local_infile):"
  mysql -sN -e "SHOW VARIABLES LIKE 'local_infile';"
  echo ""
  echo "4. 匿名用户数量:"
  mysql -sN -e "SELECT COUNT(*) FROM mysql.user WHERE user='';"
  echo ""
  echo "5. 空密码账号数量:"
  mysql -sN -e "SELECT COUNT(*) FROM mysql.user WHERE authentication_string='';"
} > $REPORT

echo "✅ 报告生成: $REPORT"

🌟 Final Thoughts

Security is not a one‑time setting but an automated process.

Run these scripts daily so the database protects itself continuously.