MySQL 共享表空間 ibdata1 大小修改驗證與 mysqld_multi 多實例管理實戰

ibdata1 大小修改的陷阱與修復

ibdata1 是 InnoDB 共享表空間，初始化時生成。若在運行中修改 innodb_data_file_path 指定大小不匹配實際文件，MySQL 將拒絕啟動：

[ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 
768 pages (12MB) than specified in .cnf file: initial 1280 pages (20MB)!

修復原則

• 配置值必須 等於或小於 實際文件大小
• 得益於 autoextend，實際 > 配置值合法；反之非法
• 獲取實際大小：ls -l ibdata1 或按錯誤日誌頁數換算（頁大小 16KB）

擴容方案：新增表空間文件

innodb_data_file_path=ibdata1:12M;ibdata2:20M:autoextend

注意：共享表空間不回收空間，刪除數據後體積不縮減，唯一緩解是遷移數據重新初始化。

mysqld_multi 多實例管理完整配置

核心配置文件 multi.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = multiadmin
password = 123456
log = /usr/local/mysql/multi.log

[mysqld3306]
port = 3306
socket = /sales3306/mysql/run/mysql.sock
datadir = /sales3306/mysql/data
server-id = 1003306
log-bin = /sales3306/mysql/log/mysql-bin
tmpdir = /sales3306/mysql/tmp/
innodb_log_group_home_dir = /sales3306/mysql/data
innodb_buffer_pool_size = 200M
log-error = /sales3306/mysql/log/error.log
pid-file = /sales3306/mysql/run/mysqld.pid

[mysqld3307]
port = 3307
socket = /sales3307/mysql/run/mysql.sock
datadir = /sales3307/mysql/data
server-id = 1003307
log-bin = /sales3307/mysql/log/mysql-bin
tmpdir = /sales3307/mysql/tmp/
innodb_log_group_home_dir = /sales3307/mysql/data
innodb_buffer_pool_size = 100M
log-error = /sales3307/mysql/log/error.log
pid-file = /sales3307/mysql/run/mysqld.pid

關鍵排錯經驗

1. PATH 必須包含 my_print_defaults：export PATH=$PATH:/usr/local/mysql/bin/
2. 每實例必須獨立指定：socket、log-error、pid-file、datadir、tmpdir，否則衝突
3. 關閉實例需密碼：配置文件密碼有時不生效，命令行 --password=xxx stop 可靠
4. 創建專用管理賬戶：GRANT SHUTDOWN ON *.* TO 'multiadmin'@'localhost' IDENTIFIED BY '123456'; 避免 root 明文密碼

常用操作命令

# 啟動所有
mysqld_multi --defaults-file=/root/multi.cnf start

# 啟動指定實例
mysqld_multi --defaults-file=/root/multi.cnf start 3306

# 查看狀態
mysqld_multi --defaults-file=/root/multi.cnf report

# 停止所有（需密碼）
mysqld_multi --defaults-file=/root/multi.cnf --password=123456 stop

補充：MySQL 用戶宿主 '%' 不包含 'localhost'

-- 僅授權 % 無法本地登錄
GRANT ALL ON *.* TO 'multiadmin'@'%' IDENTIFIED BY '123456';
-- 必須額外授權 localhost
GRANT ALL ON *.* TO 'multiadmin'@'localhost' IDENTIFIED BY '123456';

安全加固建議：刪除 mysql.user 中 User='' 的匿名賬戶。