Unix/Linux 環境安裝 MySQL 最佳實踐：專用用戶、權限分離與安全啟動

核心原則：最小權限、專用身分、關鍵路徑隔離

安裝前準備

1. 建立專用用戶與群組：

   groupadd -r mysql
   useradd -r -g mysql -s /sbin/nologin -M -d /var/lib/mysql mysql
   # -r: 系統帳號、-s nologin: 禁止登入、-M: 不建立家目錄、-d: 指定資料目錄

2. 規劃目錄結構：

   /usr/local/mysql/           # 安裝根目錄 (root:root, 755)
   /usr/local/mysql/bin/       # 執行檔
   /usr/local/mysql/data/      # 資料目錄 (mysql:mysql, 700) — 掛載獨立磁碟最佳
   /var/log/mysql/             # 日誌目錄 (mysql:mysql, 750)
   /etc/my.cnf                 # 配置檔 (root:root, 644)
   /var/run/mysqld/            # Socket/PID 目錄 (mysql:mysql, 755)

3. 預建目錄並設定權限：

   mkdir -p /usr/local/mysql/data /var/log/mysql /var/run/mysqld
   chown -R mysql:mysql /usr/local/mysql/data /var/log/mysql /var/run/mysqld
   chmod 700 /usr/local/mysql/data
   chmod 750 /var/log/mysql
   chmod 755 /var/run/mysqld

安裝流程 (以原始碼編譯為例)

# 1. 下載並驗證簽名
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.44.tar.gz
gpg --verify mysql-5.7.44.tar.gz.asc

# 2. 解壓、配置、編譯、安裝
tar xzf mysql-5.7.44.tar.gz
cd mysql-5.7.44
cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
      -DMYSQL_DATADIR=/usr/local/mysql/data \
      -DSYSCONFDIR=/etc \
      -DWITH_SSL=system \
      -DWITH_ZLIB=system \
      -DDEFAULT_CHARSET=utf8mb4 \
      -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
      -DWITH_INNODB_MEMCACHED=ON
make -j$(nproc)
make install

# 3. 初始化資料目錄 (5.7+)
/usr/local/mysql/bin/mysqld --initialize-insecure --user=mysql \
    --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

# 4. 配置 my.cnf (關鍵安全參數)
cat > /etc/my.cnf <<'EOF'
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/var/run/mysqld/mysqld.sock
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysql/error.log
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=2
bind-address=127.0.0.1
require_secure_transport=ON
sql_mode=STRICT_ALL_TABLES,NO_AUTO_TYPE_CONVERSION,ERROR_FOR_DIVISION_BY_ZERO
EOF

# 5. 建立 systemd 服務
cat > /etc/systemd/system/mysql.service <<'EOF'
[Unit]
Description=MySQL Server
After=network.target
[Service]
Type=notify
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE=65535
Restart=on-failure
RestartSec=5
[Install]
WantedBy=multi-user.target
EOF

# 6. 啟動並設定開機自啟
systemctl daemon-reload
systemctl enable mysql
systemctl start mysql

# 7. 設定 Root 密碼、移除匿名帳號、限制遠端
/usr/local/mysql/bin/mysqladmin -u root password 'StrongPass123!'
/usr/local/mysql/bin/mysql -u root -p'StrongPass123!' -e "
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE Host='%';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');
FLUSH PRIVILEGES;"

安全啟動檢查清單

結語

Unix/Linux 安裝 MySQL 最佳實踐核心：**專用 mysql 用戶、資料目錄 700 權限、systemd 服務化、bind-address=127.0.0.1、require_secure_transport=ON、嚴格 SQL 模式、安裝後立即清理預設帳號**。將此流程標準化為 Ansible Playbook 或 Shell 腳本，確保每次部署、擴容、重建環境皆符合安全基準。