Linux 下 MySQL 源碼編譯安裝、配置優化與性能調優完整實戰

Linux 下 MySQL 源碼編譯安裝與極致優化實戰

本文記錄 MySQL 5.1/5.5/5.6/5.7/8.0 通用的源碼編譯安裝流程、關鍵 CMake 選項、實例初始化、系統服務註冊、核心配置文件 my.cnf 參數深度優化 (含 MyISAM/InnoDB 專項)，並附帶常用 SQL 操作與導入導出備忘。

一、編譯安裝流程

# 1. 依賴包 (CentOS/RHEL)
yum -y install gcc gcc-c++ make cmake ncurses-devel bison openssl-devel libaio-devel

# 2. 下載源碼
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.33.tar.gz
tar -zxf mysql-8.0.33.tar.gz -C /usr/local/src
cd /usr/local/src/mysql-8.0.33

# 3. CMake 配置 (關鍵選項)
cmake \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
  -DDEFAULT_CHARSET=utf8mb4 \
  -DDEFAULT_COLLATION=utf8mb4_unicode_ci \
  -DWITH_INNODB=ON \
  -DWITH_MYISAM=ON \
  -DWITH_ARCHIVE=ON \
  -DWITH_BLACKHOLE=ON \
  -DWITH_PARTITION_STORAGE_ENGINE=ON \
  -DWITH_SSL=system \
  -DWITH_ZLIB=system \
  -DWITH_BOOST=boost_1_73_0 \
  -DENABLED_LOCAL_INFILE=ON \
  -DWITH_DEBUG=OFF \
  -DCMAKE_BUILD_TYPE=Release

# 4. 編譯安裝 (視 CPU 核心數調整 -j)
make -j$(nproc) && make install

# 5. 創建用戶組
groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

# 6. 權限設置
chown -R mysql:mysql /usr/local/mysql
chmod 750 /usr/local/mysql

# 7. 初始化數據目錄 (8.0)
/usr/local/mysql/bin/mysqld \
  --initialize-insecure \
  --user=mysql \
  --basedir=/usr/local/mysql \
  --datadir=/data/mysql

# 5.7 及以下
/usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

二、關鍵 CMake 選項速查

三、系統服務註冊

# 複製服務腳本
cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld
chkconfig --add mysqld
chkconfig mysqld on

# 或 systemd (8.0 推薦)
cp /usr/local/mysql/support-files/mysql.service /etc/systemd/system/mysqld.service
systemctl daemon-reload
systemctl enable mysqld
systemctl start mysqld

四、環境變量

# /etc/profile.d/mysql.sh
export PATH=/usr/local/mysql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH
export MANPATH=/usr/local/mysql/man:$MANPATH
source /etc/profile.d/mysql.sh

五、核心配置文件 /etc/my.cnf 極致優化模板

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
# 基礎
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysqld.pid
user = mysql
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
skip-name-resolve                    # 禁用 DNS 解析，加速連接
skip-host-cache                      # 不緩存主機名
explicit_defaults_for_timestamp = ON # 5.6+ 兼容

# 連接層
max_connections = 2000
max_connect_errors = 1000000
back_log = 512
wait_timeout = 600
interactive_timeout = 600
connect_timeout = 10

# 緩衝區 (每連接)
sort_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size = 4M
thread_stack = 512K
binlog_cache_size = 4M

# 全局緩衝區
key_buffer_size = 64M                # MyISAM 索引
table_open_cache = 4096
table_definition_cache = 2048
thread_cache_size = 200
query_cache_type = 0                 # 8.0 已移除，5.7 設 0 禁用
query_cache_size = 0

# InnoDB 核心 (假設 16GB 內存，純 InnoDB)
innodb_buffer_pool_size = 10G        # 60-70% 物理內存
innodb_buffer_pool_instances = 10    # 每實例 ≥ 1GB
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_log_file_size = 512M
innodb_log_files_in_group = 2
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 1   # 雙 1 安全
innodb_flush_method = O_DIRECT       # Linux 直寫
innodb_file_per_table = ON
innodb_data_file_path = ibdata1:1G:autoextend
innodb_data_home_dir = /data/mysql
innodb_io_capacity = 2000            # SSD
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8
innodb_purge_threads = 4
innodb_thread_concurrency = 0        # 0=不限制
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 50
transaction_isolation = READ-COMMITTED

# Binlog / 複製
log-bin = /data/mysql/binlog/mysql-bin
binlog_format = ROW
server-id = 1
sync_binlog = 1
expire_logs_days = 7
max_binlog_size = 100M
gtid_mode = ON
enforce_gtid_consistency = ON

# 慢查詢
slow_query_log = 1
slow_query_log_file = /data/mysql/log/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
log_slow_slave_statements = 1

# 臨時表
tmp_table_size = 64M
max_heap_table_size = 64M
tmpdir = /data/mysql/tmp

# MyISAM (若有)
myisam_sort_buffer_size = 64M
myisam_repair_threads = 1

# 安全
local_infile = 0                     # 禁用 LOAD DATA LOCAL (應用層控制)
sql_mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

六、常用 SQL 操作備忘

# 修改 root 密碼
mysqladmin -u root password 'NewPass123!'

# 創建業務用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass123!';
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';
FLUSH PRIVILEGES;

# 備份
mysqldump -u root -p --single-transaction --master-data=2 --routines --triggers --events db_name | gzip > backup.sql.gz

# 還原
gunzip < backup.sql.gz | mysql -u root -p db_name

# 文本導入
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl FIELDS TERMINATED BY '\t';

七、效能驗證清單

1. SHOW ENGINE INNODB STATUS\G 觀察 Buffer Pool 命中率、行鎖等待、死鎖、Purge 進度。
2. SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%'; 計算命中率 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests，目標 > 99%。
3. SHOW GLOBAL STATUS LIKE 'Threads_created'; 應緩慢增長，Threads_cached 足夠。
4. SHOW GLOBAL STATUS LIKE 'Created_tmp_disk_tables'; / Created_tmp_tables < 10%。
5. pt-query-digest 分析慢查詢，優化 Top 10。
6. 壓測 sysbench --db-driver=mysql --mysql-db=test --tables=10 --table-size=1000000 --threads=64 --time=300 run。

源碼編譯安裝賦予了最大定制自由度，但也要求運維團隊具備編譯排錯、版本升級、安全補丁回港能力。生產環境建議優先使用官方 RPM/DEB 或容器鏡像，僅在極致效能、特殊指令集優化、嵌入式場景才考慮源碼編譯。配置優化無終點，唯有持續觀測、基準測試、單一變數調優，方能逼近硬體理論極限。

1. 上一篇文章： mysqldump 導入報錯「LOCK TABLES 權限不足」解決方案
2. 下一篇文章： MySQL MERGE 存儲引擎詳解：分表聚合查詢、限制與最佳實踐

MySQL 資料庫相關知識

MySQL 與 Spring 事務隔離級別對效能的影響分析

背景與核心概念 Spring 預設不配置事務時，數據庫操作走

MySQL 入門完全指南：從關係型概念到 Windows 免安裝配置實戰

一、核心概念：什麼是關係型數據庫？ MySQL 屬於關係型數

MySQL 5.7.14 Windows 免安裝版下載安裝配置圖文全攻略

官網下載正確姿勢 1. 進入 MySQL Downlo

MySQL 命令行導出導入數據庫完整指南：Windows 與 Linux 實操詳解

一、 核心工具鏈 mysqldump —— 邏輯備份導出