CentOS 升級 MySQL 至最新版：官方 Yum 源配置、字符集遷移 utf8mb4、參數調優全流程

一、配置官方 MySQL Yum 源

# 1. 確認系統版本
cat /etc/redhat-release
# CentOS 6.x / 7.x / 8.x

# 2. 下載對應 RPM 源包 (以 CentOS 7 為例)
# 官網：https://repo.mysql.com/
rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7-3.noarch.rpm
# 或 CentOS 6:
# rpm -Uvh https://repo.mysql.com/mysql80-community-release-el6-3.noarch.rpm

二、升級執行

# 1. 升級所有 MySQL 包
yum -y upgrade mysql-community-server mysql-community-client mysql-community-common mysql-community-libs

# 2. 升級後必須運行系統表升級腳本
mysql_upgrade -u root -p
# 輸入 root 密碼，自動檢查/修復系統表、重建索引統計

三、字符集全鏈路遷移至 utf8mb4 (支援 Emoji、完整 Unicode)

1. 修改數據庫/表/列級字符集

-- 庫級
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- 表級 (會同步轉換所有字符列)
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 列級 (精確控制，需指定完整類型)
ALTER TABLE table_name CHANGE col_name col_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 注意：utf8mb4 索引長度限制 767 字節 (InnoDB 默認)，VARCHAR(191) 為安全上限
-- 若開啟 innodb_large_prefix=ON + Barracuda 格式，可用 VARCHAR(255)

2. 修改 my.cnf 核心配置

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
# 強制服務端字符集，忽略客戶端請求
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'

# 8.0 新增：默認校對規則
# default-collation = utf8mb4_0900_ai_ci  # 推薦：不區分大小寫/重音，索引友好

3. 重啟驗證

systemctl restart mysqld

mysql -uroot -p -e "SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';"

預期輸出：

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

四、批量轉換所有表字符集 (生成 SQL 再執行)

SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db_name'
  AND TABLE_TYPE = 'BASE TABLE';

將輸出保存為 alter_charset.sql，審核後執行：mysql -uroot -p your_db_name < alter_charset.sql

五、關鍵參數調優建議 (my.cnf [mysqld] 區)

# InnoDB 核心
innodb_buffer_pool_size = 物理內存 60-70%  # 核心緩衝池，數據+索引熱點
innodb_log_file_size = 1G                  # Redo Log 大小，減少 checkpoint 衝刷
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1         # ACID 保證，極致性能可設 2 (風險 1 秒數據)
innodb_file_per_table = ON                 # 獨立表空間，便於管理/回收空間
innodb_large_prefix = ON                   # 允許索引前綴 > 767 字節 (5.7+ 默認 ON)
innodb_default_row_format = DYNAMIC        # 高效行格式，支援壓縮/長字段

# 連接/線程
max_connections = 2000
thread_cache_size = 100
open_files_limit = 65535

# 查詢緩存 (8.0 移除，5.7 可關閉)
query_cache_type = 0
query_cache_size = 0

# 慢查詢
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1

六、升級後驗證清單

1. mysql --version 確認版本號
2. SHOW ENGINES; 確認 InnoDB DEFAULT
3. 核心業務 SQL 回歸測試 (關注 GROUP BY 隱式排序移除、ONLY_FULL_GROUP_BY 模式、保留字變更)
4. 監控 error.log 無異常報錯
5. 備份策略適配新版本 (mysqldump 參數、XtraBackup 版本匹配)