Linux 平台 MySQL 安裝配置與常用運維操作全攻略

Linux 與 MySQL 是經典黃金搭檔。本文提供從原始碼編譯安裝到生產級配置、啟停管理、遠程維護、常用 SQL 操作的完整指令手冊，適合初學者建立標準化部署流程，也供資深運維快速查閱。

一、原始碼編譯安裝 (以 MySQL 5.7/8.0 為例)

1. 依賴套件

# CentOS/RHEL
yum install -y gcc gcc-c++ cmake ncurses-devel bison openssl-devel libaio-devel

# Ubuntu/Debian
apt-get install -y build-essential cmake libncurses5-dev bison libssl-dev libaio-dev

2. 編譯參數詳解

cmake \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DMYSQL_DATADIR=/data/mysql \
  -DSYSCONFDIR=/etc \
  -DWITH_INNODB_STORAGE_ENGINE=1 \
  -DWITH_PARTITION_STORAGE_ENGINE=1 \
  -DWITH_FEDERATED_STORAGE_ENGINE=1 \
  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
  -DWITH_MYISAM_STORAGE_ENGINE=1 \
  -DENABLED_LOCAL_INFILE=1 \
  -DDEFAULT_CHARSET=utf8mb4 \
  -DDEFAULT_COLLATION=utf8mb4_general_ci \
  -DWITH_SSL=system \
  -DWITH_ZLIB=system \
  -DWITH_BOOST=boost_1_69_0   # MySQL 5.7+ 需指定 Boost 路徑

3. 編譯安裝

make -j$(nproc)
make install

二、目錄結構與權限初始化

# 建立 mysql 用戶與組
groupadd mysql
useradd -r -g mysql -s /bin/false mysql

# 目錄佈局
mkdir -p /data/mysql/{data,log,tmp,run}
chown -R mysql:mysql /data/mysql /usr/local/mysql

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

# SSL 憑證自動生成
/usr/local/mysql/bin/mysql_ssl_rsa_setup --datadir=/data/mysql/data

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

[mysqld]
# 基礎路徑
basedir = /usr/local/mysql
datadir = /data/mysql/data
socket  = /data/mysql/run/mysql.sock
pid-file = /data/mysql/run/mysqld.pid
log-error = /data/mysql/log/error.log

# 網路與連線
bind-address = 0.0.0.0
port = 3306
max_connections = 2000
max_connect_errors = 100000
skip-name-resolve
wait_timeout = 28800
interactive_timeout = 28800

# 字元集
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init_connect = 'SET NAMES utf8mb4'

# InnoDB 核心參數
innodb_buffer_pool_size = 4G          # 實體記憶體 60-70%
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
innodb_file_per_table = 1
innodb_io_capacity = 2000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# 二進制日誌 (主從/備份必備)
log-bin = /data/mysql/log/binlog
binlog_format = ROW
expire_logs_days = 7
sync_binlog = 1
gtid_mode = ON
enforce_gtid_consistency = ON
server-id = 1

# 慢查詢與審計
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

# 其他
sql_mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
explicit_defaults_for_timestamp = 1
lower_case_table_names = 1

[client]
default-character-set = utf8mb4
socket = /data/mysql/run/mysql.sock

[mysql]
no-auto-rehash

四、systemd 服務管理

# /usr/lib/systemd/system/mysqld.service
[Unit]
Description=MySQL Server
After=network.target

[Service]
Type=forking
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --daemonize
ExecStop=/usr/local/mysql/bin/mysqladmin -u root -p shutdown
PIDFile=/data/mysql/run/mysqld.pid
LimitNOFILE=65535
LimitNPROC=65535

[Install]
WantedBy=multi-user.target

systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld
systemctl status mysqld

五、Root 密碼設定與安全初始化

# 登入 (initialize-insecure 無密碼)
/usr/local/mysql/bin/mysql -u root -S /data/mysql/run/mysql.sock

-- 設定密碼
ALTER USER 'root'@'localhost' IDENTIFIED BY 'StrongP@ssw0rd!';
-- 或 MySQL 5.7
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('StrongP@ssw0rd!');

-- 刪除匿名用戶、測試庫、遠程 root
DELETE FROM mysql.user WHERE User='';
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost','127.0.0.1','::1');
FLUSH PRIVILEGES;

-- 建立應用帳號
CREATE USER 'app_user'@'%' IDENTIFIED BY 'AppP@ssw0rd!';
GRANT SELECT,INSERT,UPDATE,DELETE,EXECUTE ON mydb.* TO 'app_user'@'%';
FLUSH PRIVILEGES;

六、常用運維指令速查

七、日誌分析與故障排查

• 錯誤日誌：tail -f /data/mysql/log/error.log — 啟動失敗、崩潰堆疊、主從報錯首選
• 慢查詢日誌：pt-query-digest /data/mysql/log/slow.log — 找出 Top N 慢查詢、執行計劃、索引建議
• 二進制日誌：mysqlbinlog -v /data/mysql/log/binlog.000001 | grep -A 20 'table_name' — 追蹤 DML、PITR 定位

八、避坑錦囊

1. 記憶體不足導致 OOM Killer 殺掉 mysqld：innodb_buffer_pool_size 勿超過物理記憶體 75%，預留 OS、連線緩衝、排序緩衝空間。
2. lower_case_table_names=1 必須在初始化前設定，事後修改無效且會導致大小寫不一致錯誤。
3. GTID 模式下從庫跳過錯誤：SET GTID_NEXT='aaa-bbb-ccc:10'; BEGIN; COMMIT; SET GTID_NEXT=AUTOMATIC; (慎用)。
4. 備份檔案權限：chmod 600 /backup/*.sql，避免洩露。
5. 定期演練恢復：每季從備份還原至測試環境，驗證 RTO/RPO。

標準化的安裝配置、完善的監控日誌、熟練的操作肌肉記憶，是 Linux 上穩定運行 MySQL 的三大基石。下一篇我們將深入剖析 MySQL Binlog 內部處理機制與主從複制原理，敬請期待。