Linux 下部署 MySQL 5.7 通用二進制包（Generic Linux GLIBC 2.12+）是生產環境標準姿勢。本文提供經驗證的全流程 SOP：用戶隔離、目錄規範、配置調優、安全初始化、systemd 管理、防火牆放行、遠程授權、驗證測試。
| 項目 | 規格 |
|---|---|
| OS | CentOS 7.9 x86_64 (最小安裝) |
| MySQL | mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz (官網下載) |
| 依賴 | libaio, numactl-libs (yum install -y libaio numactl-libs) |
| 目標目錄 | /usr/local/mysql (軟鏈接 /usr/local/mysql → mysql-5.7.44...) |
| 數據目錄 | /data/mysql/data (獨立掛載點，SSD，RAID10) |
| 日誌目錄 | /data/mysql/logs |
| 運行用戶 | mysql:mysql (UID/GID 固化，如 27:27) |
# 1. 創建固定 UID/GID 用戶組
groupadd -g 27 mysql
useradd -u 27 -g mysql -s /sbin/nologin -M -d /usr/local/mysql mysql
# 2. 創建目錄結構並授權
mkdir -p /usr/local/mysql /data/mysql/{data,logs,tmp}
chown -R mysql:mysql /usr/local/mysql /data/mysql
chmod 750 /data/mysql/data /data/mysql/logs
chmod 1777 /data/mysql/tmp # 粘滯位，防止誤刪cd /usr/local
tar -zxvf /path/to/mysql-5.7.44-linux-glibc2.12-x86_64.tar.gz
ln -sf mysql-5.7.44-linux-glibc2.12-x86_64 mysql
chown -R mysql:mysql mysql-5.7.44-linux-glibc2.12-x86_64/etc/my.cnf[client]
default-character-set = utf8mb4
socket = /data/mysql/tmp/mysql.sock
[mysql]
no-auto-rehash
default-character-set = utf8mb4
[mysqld]
# === 基礎路徑 ===
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket = /data/mysql/tmp/mysql.sock
pid-file = /data/mysql/tmp/mysqld.pid
tmpdir = /data/mysql/tmp
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
# === 連接與安全 ===
port = 3306
bind-address = 0.0.0.0 # 生產建議綁定內網 IP
max_connections = 3000
max_connect_errors = 100000
skip-name-resolve # 禁用 DNS 反解，加速連接
skip-external-locking
sql_mode = STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO
# === InnoDB 核心調優 (假設 32GB 內存，獨佔 DB 服) ===
default-storage-engine = InnoDB
innodb_buffer_pool_size = 22G # 物理內存 60-70%
innodb_buffer_pool_instances = 16
innodb_log_file_size = 2G # 總大小 4G (2 files)
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = ON
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_io_capacity = 4000 # SSD IOPS 基準
innodb_io_capacity_max = 8000
innodb_lru_scan_depth = 2000
innodb_thread_concurrency = 0 # 0=自動
innodb_online_alter_log_max_size = 1G
# === 日誌與備份 ===
log-error = /data/mysql/logs/error.log
slow_query_log = 1
slow_query_log_file = /data/mysql/logs/slow.log
long_query_time = 0.5
log_queries_not_using_indexes = 0
log_slow_admin_statements = 1
log_slow_slave_statements = 1
binlog_format = ROW
server-id = 101
gtid_mode = ON
enforce_gtid_consistency = ON
log-bin = /data/mysql/logs/binlog
binlog_expire_logs_seconds = 604800 # 7 天
max_binlog_size = 1G
sync_binlog = 1
master_info_repository = TABLE
relay_log_info_repository = TABLE
relay_log_recovery = ON
# === 其他優化 ===
open_files_limit = 65535
table_open_cache = 4000
table_definition_cache = 2000
thread_cache_size = 100
query_cache_type = 0
query_cache_size = 0
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
join_buffer_size = 4M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
key_buffer_size = 32M # 僅用於系統表
explicit_defaults_for_timestamp = ONcd /usr/local/mysql
# 生產環境：生成隨機 root 密碼，記錄在 error log 中
bin/mysqld --initialize --user=mysql \
--basedir=/usr/local/mysql \
--datadir=/data/mysql/data \
--secure-file-priv=/data/mysql/tmp \
2>&1 | tee /data/mysql/logs/init.log
# 查看臨時密碼
grep 'temporary password' /data/mysql/logs/init.log
# 2023-10-15T08:30:45.123456Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: xKp#9qLv!2mR⚠️ 必須記錄臨時密碼，首次登入強制修改。開發環境可用
--initialize-insecure生成無密碼 root。
# 1. 複製服務文件
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
# 2. 或創建 systemd unit (推薦)
cat > /etc/systemd/system/mysqld.service <<'EOF'
[Unit]
Description=MySQL 5.7 Database Server
After=network.target
After=syslog.target
[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/data/mysql/tmp/mysqld.pid
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
ExecStop=/usr/local/mysql/bin/mysqladmin --defaults-file=/etc/my.cnf shutdown
TimeoutSec=600
Restart=on-failure
RestartSec=5
LimitNOFILE=65535
LimitNPROC=65535
[Install]
WantedBy=multi-user.target
EOF
# 3. 重載並啟動
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld# 1. 登入並修改密碼
mysql -u root -p'xKp#9qLv!2mR' --connect-expired-password
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewStrongPass123!@#';
mysql> FLUSH PRIVILEGES;
# 2. 執行安全腳本 (交互式)
/usr/local/mysql/bin/mysql_secure_installation
# 依次：修改 root 密碼(Y) → 移除匿名用戶(Y) → 禁止 root 遠程登入(Y) → 移除 test 庫(Y) → 重載權限(Y)
# 3. 創建遠程應用帳號 (替代 root 遠程)
mysql -u root -p'NewStrongPass123!@#'
mysql> CREATE USER 'app_user'@'10.0.%.%' IDENTIFIED BY 'AppPass456$%^';
mysql> GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON myapp.* TO 'app_user'@'10.0.%.%';
mysql> FLUSH PRIVILEGES;# firewalld
firewall-cmd --permanent --add-port=3306/tcp
firewall-cmd --permanent --add-rich-rule='rule family="ipv4" source address="10.0.0.0/16" port protocol="tcp" port="3306" accept'
firewall-cmd --reload
# SELinux (若啟用)
setsebool -P mysql_connect_any 1
semanage port -a -t mysqld_port_t -p tcp 3306 # 非標準端口時# 本地驗證
mysql -u app_user -p'AppPass456$%^' -h 127.0.0.1 -e "SELECT VERSION(); SHOW DATABASES;"
# 遠程驗證 (應用服務器)
mysql -u app_user -p'AppPass456$%^' -h 10.0.1.101 -e "SELECT VERSION();"
# 性能基線
sysbench /usr/share/sysbench/oltp_read_write.lua \
--mysql-host=127.0.0.1 --mysql-port=3306 \
--mysql-user=app_user --mysql-password='AppPass456$%^' \
--mysql-db=myapp --tables=10 --table-size=100000 \
--threads=64 --time=300 --report-interval=10 run| 現象 | 原因 | 解決 |
|---|---|---|
| Can't connect to local MySQL server through socket | socket 路徑不一致/服務未啟動 | 檢查 my.cnf socket 路徑，確認服務 running | ERROR 1820 (HY000): You must reset your password | 首次登入未改密碼 | 使用 --connect-expired-password 登入改密 |
| ERROR 1130 (HY000): Host not allowed to connect | 用戶宿主不匹配 | CREATE USER 'u'@'%' 或具體 IP 段，FLUSH PRIVILEGES |
| InnoDB: Operating system error number 13 | 目錄權限問題 | chown -R mysql:mysql datadir; chmod 750 datadir |
| Table 'performance_schema.xxx' doesn't exist | 升級後系統表缺失 | mysql_upgrade -u root -p |
CentOS 7 部署 MySQL 5.7 核心在於：**目錄隔離、用戶最小權限、配置匹配硬件、安全初始化、systemd 托管、網絡最小暴露、賬號最小授權**。遵循本 SOP，可構建穩定、安全、可維運的生產級 MySQL 服務。
