原地升級 MySQL 5.5 → 5.6(直接替換二進制、復用 datadir)後,服務正常啟動,但執行 GRANT 語句報錯:
mysql> GRANT ALL ON new_db.* TO 'user'@'%';
ERROR 2013 (HY000): Lost connection to MySQL server during query
伴隨錯誤日志:
Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock'
MySQL 版本升級時,系統表結構會變更(新增權限列、修改列類型、調整索引)。直接啟動新版本對舊系統表操作,導致:
mysql.user、mysql.db 等表時觸發崩潰/斷連# 1. 確保 MySQL 服務正在運行
systemctl status mysqld
# 2. 執行升級腳本
mysql_upgrade -u root -p --force
# --force 強制檢查所有表,即使版本號一致
# 3. 輸出示例
Phase 1/7: Checking and upgrading mysql database
Processing databases
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
...
Phase 2/7: Installing used storage engines... Skipped
Phase 3/7: Fixing views
Phase 4/7: Running 'mysql_fix_privilege_tables'
Phase 5/7: Fixing table and database names
Phase 6/7: Checking and upgrading tables
Phase 7/7: Flushing privileges
OK
CHECK TABLE ... FOR UPGRADE,必要時 REPAIR TABLEmysql.user、mysql.db、mysql.tables_priv 等結構以匹配新版本FLUSH PRIVILEGES 使新結構生效--gtid-mode=ON 時 禁止 使用 mysql_upgrade!# 1. 備份(核心原則)
mysqldump --all-databases --single-transaction --master-data=2 --routines --triggers --events > /backup/pre_upgrade_$(date +%F).sql
# 2. 停止服務
systemctl stop mysqld
# 3. 替換二進制 / 安裝新版本
# ... 安裝步驟 ...
# 4. 啟動新版本(暫不加載業務流量)
systemctl start mysqld
# 5. 執行升級腳本
mysql_upgrade -u root -p --force
# 6. 重啟確保所有緩存刷新
systemctl restart mysqld
# 7. 驗證
mysql -u root -p -e "SELECT VERSION(); SHOW GRANTS FOR 'root'@'localhost';"
mysql -u root -p -e "GRANT SELECT ON test.* TO 'test_user'@'%';" # 原報錯操作驗證
# 8. 恢復業務流量
mysql_upgrade,升級邏輯整合到服務啟動過程(--upgrade=FORCE)util.checkForServerUpgrade()tail -100 /var/log/mysqld.logdf -h /data/mysqlls -la /data/mysql/ 確保 mysql 用戶可讀寫mysqlcheck --auto-repair -u root -p mysql