CentOS 7 MySQL 5.6 RPM 安裝配置與遠程訪問完整實戰

CentOS 7 MySQL 5.6 RPM 安裝配置與遠程訪問完整實戰

生產環境常因系統預設倉庫版本過舊 (CentOS 7 默認 MySQL 5.1/5.5/MariaDB)，需手動安裝官方 RPM 包獲取更新版本。本文記錄 MySQL 5.6.31 在 CentOS 7 上的完整 RPM 安裝、配置、啟動、遠程授權與防火牆穿透流程，包含踩坑避雷指南。

環境準備與舊版本清理

1. 檢查並移除現有 MySQL/MariaDB

# 查看已安裝包
rpm -qa | grep -i mysql
rpm -qa | grep -i mariadb

# 普通移除 (有依賴會報錯)
rpm -e mysql-community-server mysql-community-client ...

# 強制移除 (忽略依賴，慎用)
rpm -e --nodeps mysql-community-server mysql-community-client ...

# 驗證清理
rpm -qa | grep -i mysql
# 應無輸出

2. 關閉 SELinux (關鍵！否則 XtraBackup SST 失敗)

# 官方下載頁
https://dev.mysql.com/downloads/mysql/5.6.html#downloads

# 選擇：Red Hat Enterprise Linux 7 / Oracle Linux 7 (x86, 64-bit), RPM Bundle
# 文件：mysql-5.6.31-1.el7.x86_64.rpm-bundle.tar

2. 解壓並按依賴順序安裝

mkdir -p /opt/mysql-rpm && cd /opt/mysql-rpm
tar -xvf mysql-5.6.31-1.el7.x86_64.rpm-bundle.tar

# 核心四包安裝順序 (依賴關係)
rpm -ivh mysql-community-common-5.6.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-5.6.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-5.6.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-server-5.6.31-1.el7.x86_64.rpm

# 可選包
rpm -ivh mysql-community-devel-5.6.31-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-5.6.31-1.el7.x86_64.rpm

3. 驗證安裝

# 確保數據目錄權限
chown -R mysql:mysql /var/lib/mysql

# 初始化 (生成隨機 root 密碼存放在 ~/.mysql_secret)
mysql_install_db --user=mysql --datadir=/var/lib/mysql

# 或指定基礎目錄
mysql_install_db --user=mysql --basedir=/usr --datadir=/var/lib/mysql

2. 啟動服務並設置開機自啟

cat ~/.mysql_secret
# 輸出：The random password for root@localhost is: xXxXxXxX

mysql -u root -p'xXxXxXxX' --connect-expired-password
# 5.6 需加 --connect-expired-password 參數

核心配置優化 (/etc/my.cnf)

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
server-id=1
log-bin=mysql-bin
binlog_format=ROW
expire_logs_days=7

# 字符集 (關鍵)
character-set-server=utf8mb4
collation-server=utf8mb4_general_ci
skip-character-set-client-handshake

# InnoDB 核心
innodb_buffer_pool_size=2G        # 物理內存 60-70%
innodb_log_file_size=256M
innodb_log_buffer_size=64M
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT
innodb_file_per_table=ON

# 連接優化
max_connections=1000
thread_cache_size=100
table_open_cache=2000
skip-name-resolve

# 日志
slow_query_log=1
slow_query_log_file=/var/log/mysql/slow.log
long_query_time=1
log-error=/var/log/mysql/error.log

# 確保日志目錄存在
mkdir -p /var/log/mysql && chown mysql:mysql /var/log/mysql

重啟生效

mysql -u root -p

# 修改 root 密碼
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewStrongPass123!');

# 創建遠程訪問用戶 (推薦：具體 IP 段)
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY 'AppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE ON app_db.* TO 'app_user'@'192.168.1.%';

# 或通配符 (僅內網/測試環境)
CREATE USER 'dev'@'%' IDENTIFIED BY 'DevPass123!';
GRANT ALL PRIVILEGES ON *.* TO 'dev'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

2. 防火牆開放 3306 端口 (CentOS 7 firewalld)

# 客戶端機器測試
mysql -h 192.168.1.100 -u app_user -p'AppPass123!' -e "SELECT VERSION();"

# Navicat / SQLyog / DataGrip 測試連接
# Host: 192.168.1.100, Port: 3306, User: app_user, Password: AppPass123!

常見坑點與解決方案

安全加固清單