CentOS 7/8 上 MySQL 5.7/8.0 生產級安裝配置全流程：Yum/RPM、字符集、安全加固、驗證測試

CentOS (現 Rocky/AlmaLinux) 仍是主流 Linux 服務器選擇。本文基於官方 Yum 倉庫，給出 MySQL 5.7/8.0 生產環境標準化安裝、配置優化、安全加固、連接驗證全流程，含 systemd 管理、防火牆、SELinux 協同。

一、 環境準備

# 1. 關閉 SELinux (或設為 Permissive)
sed -i 's/^SELINUX=enforcing/SELINUX=permissive/' /etc/selinux/config
setenforce 0

# 2. 配置防火牆開放 3306 (僅內網/應用服 IP)
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.0/8" port protocol="tcp" port="3306" accept'
firewall-cmd --reload

# 3. 關閉透明大頁 (THP) - 關鍵效能優化
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo 'echo never > /sys/kernel/mm/transparent_hugepage/enabled' >> /etc/rc.local
chmod +x /etc/rc.local

二、 安裝 MySQL 官方 Yum 倉庫

# CentOS 7
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

# CentOS 8 / Rocky 8 / Alma 8
rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el8-5.noarch.rpm

# 選擇版本 (預設 8.0，如需 5.7)
yum-config-manager --disable mysql80-community
yum-config-manager --enable mysql57-community

三、 安裝 Server 與客戶端

yum install -y mysql-community-server mysql-community-client mysql-community-libs

# 驗證版本
mysqld --version

四、 核心配置 /etc/my.cnf (生產級模板)

[mysqld]
# 基礎路徑
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log

# 字符集 (全鏈路 utf8mb4)
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

# 連接層
max_connections = 2000
max_connect_errors = 100000
skip-name-resolve                    # 禁用 DNS 反解，加速遠程連接
wait_timeout = 600
interactive_timeout = 600

# InnoDB 核心 (假設 16GB 內存，預留 4GB 給 OS)
innodb_buffer_pool_size = 10G
innodb_buffer_pool_instances = 8
innodb_log_file_size = 1G
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1   # ACID 嚴格模式，極致性能可改 2
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Binlog (主從/PITR 必備)
server-id = 1
log-bin = mysql-bin
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1                      # 配合 flush_log_at_trx_commit=1 保證不丟數據
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查詢
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 安全
local_infile = 0
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION

[client]
default-character-set = utf8mb4
socket = /var/lib/mysql/mysql.sock

[mysql]
default-character-set = utf8mb4
no-auto-rehash

五、 啟動、開機自啟、初始化

systemctl enable mysqld
systemctl start mysqld

# 首次啟動會初始化數據目錄、生成臨時 root 密碼 (5.7+)
grep 'temporary password' /var/log/mysqld.log

六、 安全加固 (mysql_secure_installation)

mysql_secure_installation
# 交互式完成：
# 1. 修改 root 密碼 (強密碼策略)
# 2. 移除匿名用戶
# 3. 禁止 root 遠程登錄
# 4. 移除 test 庫
# 5. 刷新權限

七、 手動創建業務用戶與授權 (最小權限原則)

mysql -uroot -p
CREATE USER 'app_user'@'10.0.%' IDENTIFIED BY 'StrongPass#2024';
CREATE DATABASE app_db CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app_db.* TO 'app_user'@'10.0.%';
FLUSH PRIVILEGES;

八、 連接驗證與基準測試

# 本地 Socket 連接
mysql -uapp_user -p -S /var/lib/mysql/mysql.sock -e "SELECT VERSION();"

# 遠程 TCP 連接 (應用服驗證)
mysql -uapp_user -p -h 10.0.1.50 -P 3306 -e "SHOW DATABASES;"

# 簡單壓測
sysbench oltp_read_write --mysql-host=10.0.1.50 --mysql-user=app_user --mysql-password=StrongPass#2024 --mysql-db=app_db --threads=64 --time=60 run

九、 監控與日誌輪轉

# logrotate 配置 /etc/logrotate.d/mysql
/var/log/mysqld.log /var/log/mysql-slow.log {
    daily
    rotate 14
    compress
    delaycompress
    missingok
    notifempty
    create 640 mysql mysql
    sharedscripts
    postrotate
        mysqladmin flush-logs 2>/dev/null || true
    endscript
}

十、 總結

標準化安裝 = 官方源 + 核心參數模板 + 安全加固 + 自動化驗證。將上述流程封裝為 Ansible Playbook / Shell 腳本 / Dockerfile，實現「一鍵交付、配置不漂移、審計可追溯」。核心參數 (Buffer Pool、Binlog、字符集、skip-name-resolve) 直接決定上線後的穩定性與效能上限。