MySQL 安全安裝腳本 mysql_secure_installation 逐步詳解與最佳實踐

腳本功能概覽

`mysql_secure_installation` 是 MySQL 官方提供的安全強化腳本，執行以下五項關鍵操作：

1. 為 root 用戶設定強密碼
2. 刪除匿名用戶（`User=''`）
3. 禁止 root 遠程登入（僅允許 `localhost`/`127.0.0.1`/`::1`）
4. 刪除測試資料庫 `test` 及其訪問權限
5. 刷新授權表使變更即時生效

互動式執行完整流程（含預設值與建議選項）

[root@server ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!

Enter current password for root (enter for none): 
# 初次安裝 root 無密碼，直接按 Enter

OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
Set root password? [Y/n] Y
New password: 強密碼(≥12位,大小寫數字特殊字元)
Re-enter new password: 再次輸入
Password updated successfully!
Reloading privilege tables.. Success!

By default, a MySQL installation has an anonymous user...
Remove anonymous users? [Y/n] Y
... Success!

Normally, root should only be allowed to connect from 'localhost'...
Disallow root login remotely? [Y/n] Y  # 如需遠程管理請選 N 並配置防火牆/VPN
... Success!

By default, MySQL comes with a database named 'test'...
Remove test database and access to it? [Y/n] 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? [Y/n] Y
... Success!

Cleaning up...
All done! If you've completed all of the above steps, your MySQL
installation should now be secure.
Thanks for using MySQL!

各步驟底層 SQL 等價操作（便於自動化腳本）

-- 1. 設定 root 密碼 (5.7+)
ALTER USER 'root'@'localhost' IDENTIFIED BY '強密碼';
-- 5.6 及以下
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('強密碼');

-- 2. 刪除匿名用戶
DELETE FROM mysql.user WHERE User = '';

-- 3. 禁止 root 遠程登入（保留 localhost/127.0.0.1/::1）
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost','127.0.0.1','::1');

-- 4. 刪除 test 資料庫及權限
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\\_%';

-- 5. 刷新權限
FLUSH PRIVILEGES;

非互動式自動化部署（Shell 腳本範例）

#!/bin/bash
# 適用於 CI/CD 流水線、Ansible、Docker 初始化
ROOT_PWD="$(openssl rand -base64 24)"  # 生成隨機強密碼

mysql -u root -p"${OLD_PWD:-}" <