Ubuntu 12.04 通用二進制包安裝 MySQL 5.6 完整實戰記錄

Ubuntu 12.04 官方 Generic Binaries 安裝 MySQL 5.6 全過程

官方預編譯二進制包 (Generic Binaries) 經過深度優化，無需編譯即可獲得最佳效能。本文記錄 Ubuntu 12.04 64 位環境下完整安裝、配置、安全加固、自動啟動全流程。

1. 版本選擇與下載

• 官方文檔建議：生產環境使用最新 GA (General Availability) 版 → MySQL 5.6
• 下載：mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz (MD5: 79f9a54bdc3731fd4bdf22db77f27ca7)
• ⚠️ 先移除系統包管理器安裝的 MySQL：apt-get autoremove --purge mysql-server-5.5 mysql-server mysql-common

2. 解壓與基礎部署

# 建立用戶組
groupadd mysql
useradd -r -g mysql mysql

# 解壓至 /usr/local 並建立軟連結
cd /usr/local
tar zxvf /path/to/mysql-5.6.21-linux-glibc2.5-x86_64.tar.gz
ln -s mysql-5.6.21-linux-glibc2.5-x86_64 mysql

# 目錄權限
cd mysql
chown -R mysql .
chgrp -R mysql .

# 安裝依賴 (libaio 為 InnoDB 非同步 IO 必須)
apt-get install libaio1 libaio-dev

# 初始化系統表 (data 目錄自動建立)
./scripts/mysql_install_db --user=mysql

# 調整權限：程式目錄屬 root，資料目錄屬 mysql
chown -R root .
chown -R mysql data

3. 啟動腳本與開機自啟 (Ubuntu 使用 sysv-rc-conf)

# 複製啟動腳本
cp support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql

# Ubuntu 12.04 無 chkconfig，改用 sysv-rc-conf
apt-get install sysv-rc-conf
sysv-rc-conf mysql on  # 設定 runlevel 2,3,4,5 自啟

# 驗證
sysv-rc-conf --list mysql

4. 配置檔優化 (採用 Percona Configuration Wizard 生成)

配置檔位置優先順序：/etc/my.cnf → /etc/mysql/my.cnf → $MYSQL_HOME/my.cnf。建議放 /etc/mysql/my.cnf。

[mysql]
port = 3306
socket = /usr/local/mysql/data/mysql.sock

[mysqld]
# GENERAL
user = mysql
default-storage-engine = InnoDB
socket = /usr/local/mysql/data/mysql.sock
pid-file = /usr/local/mysql/data/mysql.pid

# MyISAM
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY
max-allowed-packet = 16M
max-connect-errors = 1000000

# DATA STORAGE
datadir = /usr/local/mysql/data/

# BINARY LOGGING
log-bin = /usr/local/mysql/data/mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 4096

# INNODB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 64M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 128M  # 依實體記憶體調整，建議 50-70%

# LOGGING
log-error = /usr/local/mysql/data/mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = /usr/local/mysql/data/mysql-slow.log

# mysql.server 專用
[mysql.server]
basedir = /usr/local/mysql

# 客戶端統一 socket
[client]
socket = /usr/local/mysql/data/mysql.sock

5. 關鍵問題排查：Socket 檔案路徑不一致

預設 Socket 在 /tmp/mysql.sock，但 /tmp 可能被清理導致連線失敗。解決：在 [mysqld]、[client]、[mysql.server] 統一指定 socket = /usr/local/mysql/data/mysql.sock。

6. 安全加固 (Securing Initial Accounts)

# 啟動服務
bin/mysqld_safe --user=mysql &

# 驗證
bin/mysqladmin version

# 進入客戶端設定密碼
bin/mysql -u root -p
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('StrongPass123');
mysql> SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('StrongPass123');
mysql> SET PASSWORD FOR 'root'@'::1' = PASSWORD('StrongPass123');
mysql> UPDATE mysql.user SET Password = PASSWORD('StrongPass123') WHERE User = 'root';
mysql> FLUSH PRIVILEGES;

# 匿名用戶設密或刪除
mysql> UPDATE mysql.user SET Password = PASSWORD('AnonPass') WHERE User = '';
mysql> FLUSH PRIVILEGES;

# 移除 test 資料庫存取權限
mysql> DELETE FROM mysql.db WHERE Db LIKE 'test%';
mysql> FLUSH PRIVILEGES;

# 保護歷史檔
chmod 600 ~/.mysql_history

7. 環境變數與時區 (可選)

# ~/.bashrc
export PATH=${PATH}:/usr/local/mysql/bin

# 時區表填充 (需時再執行)
mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql

8. 驗收清單

• mysqladmin version 顯示正確版本、Socket、Uptime
• mysqlshow 列出資料庫
• mysql -u root -p 密碼登入成功
• SHOW VARIABLES LIKE 'datadir'; 指向正確目錄
• 錯誤日誌 /usr/local/mysql/data/mysql-error.log 無異常
• 慢查詢日誌、Binlog 正常輪轉