MySQL無法啟動例一 · MySQL FAQ系列整理

## 場景某個打算用于slave新搭建的實例啟動報錯，啟動過程中報告InnoDB數據頁發生損壞。錯誤日志像下面這樣： > 150330 15:37:44 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306 > 2015-03-30 15:37:45 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use –explicit_defaults_for_timestamp server option (see documentation for more details). > 2015-03-30 15:37:45 5884 [Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead. > 2015-03-30 15:37:45 5884 [Note] Plugin ‘FEDERATED’ is disabled. > 2015-03-30 15:37:45 5884 [Note] InnoDB: Using atomics to ref count buffer pool pages > 2015-03-30 15:37:45 5884 [Note] InnoDB: The InnoDB memory heap is disabled > 2015-03-30 15:37:45 5884 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins > 2015-03-30 15:37:45 5884 [Note] InnoDB: Memory barrier is not used > 2015-03-30 15:37:45 5884 [Note] InnoDB: Compressed tables use zlib 1.2.3 > 2015-03-30 15:37:45 5884 [Note] InnoDB: Using Linux native AIO > 2015-03-30 15:37:45 5884 [Note] InnoDB: Using CPU crc32 instructions > 2015-03-30 15:37:45 5884 [Note] InnoDB: Initializing buffer pool, size = 2.0G > 2015-03-30 15:37:46 5884 [Note] InnoDB: Completed initialization of buffer pool > 2015-03-30 15:37:47 5884 [Note] InnoDB: Highest supported file format is Barr. > 2015-03-30 15:37:48 5884 [Warning] InnoDB: Resizing redo log from 3*32768 to 2*16384 pages, LSN=2740249189 > 2015-03-30 15:37:48 5884 [Warning] InnoDB: Starting to delete and rewrite log files. > 2015-03-30 15:37:48 5884 [Note] InnoDB: Setting log file ./ib_logfile101 size to 256 MB > InnoDB: Progress in MB: 100 200 > 2015-03-30 15:37:49 5884 [Note] InnoDB: Setting log file ./ib_logfile1 size to 256 MB > InnoDB: Progress in MB: 100 200 > 2015-03-30 15:37:50 5884 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 > 2015-03-30 15:37:50 5884 [Warning] InnoDB: New log files created, LSN=2740249612 > 2015-03-30 15:37:50 5884 [Note] InnoDB: 128 rollback segment(s) are active. > 2015-03-30 15:37:50 5884 [Note] InnoDB: Waiting for purge to start > 2015-03-30 15:37:50 5884 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.21-rel69.0 started; log sequence number 2740249189 > 2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6. > 150330 15:36:33 mysqld_safe Starting mysqld daemon with databases from /data/mysql/mytest_3306 > 2015-03-30 15:37:50 5884 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: ab27d3e4-d6af-11e4-8020-c81f66eeffa6. > 2015-03-30 15:37:50 5884 [Note] RSA private key file not found: /data/mysql/mytest_3306//private_key.pem. Some authentication plugins will not work. > 2015-03-30 15:37:50 5884 [Note] RSA public key file not found: /data/mysql/mytest_3306//public_key.pem. Some authentication plugins will not work. > 2015-03-30 15:37:50 5884 [Note] Server hostname (bind-address): ’10.x.x.x'; port: 3306 > 2015-03-30 15:37:50 5884 [Note] – ’10.x.x.x’ resolves to ’10.x.x.x'; > 2015-03-30 15:37:50 5884 [Note] Server socket created on IP: ’10.x.x.x’. > 2015-03-30 15:37:50 7f4ce4d68700 InnoDB: Error: page 32769 log sequence number 2740254202 > InnoDB: is in the future! Current system log sequence number 2740249622. > InnoDB: Your database may be corrupt or you may have copied the InnoDB > InnoDB: tablespace but not the InnoDB log files. See > InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html > InnoDB: for more information. > 2015-03-30 15:37:50 5884 [Note] Event Scheduler: Loaded 0 events > 2015-03-30 15:37:50 5884 [Note] /opt/Percona-Server-5.6.21-rel69.0-675.Linux.x86_64/bin/mysqld: ready for connections. > Version: ‘5.6.21-69.0-log’ socket: ‘/data/mysql/mytest_3306/mysql.sock’ port: 3306 Percona Server (GPL), Release 69.0, Revision 675 > 2015-03-30 15:37:58 7f4ce4d68700 InnoDB: Error: page 6327 log sequence number 2740254445 > InnoDB: is in the future! Current system log sequence number 2740251356. > InnoDB: Your database may be corrupt or you may have copied the InnoDB > InnoDB: tablespace but not the InnoDB log files. See > InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html > InnoDB: for more information. ## 分析正常情況下，新部署的實例是不太可能出現InnoDB數據頁損壞的。經了解，這個實例是采用xtrabackup工具從master備份過來做恢復的。細心的同學，通過觀察上面的日志，應該能從中發現一些蛛絲馬跡。有幾個地方需要引起注意： ~~~ 1、版本是Percona Server 5.6.21； 2、剛啟動就把InnoDB的redo log給resize了； 3、剛啟動就發現InnoDB的page LSN和redo log中的不匹配； ~~~ 之所以提醒大家注意上面的三點，并且把版本信息放在了第一條，是因為從5.6版本開始，InnoDB如如果發現當前的redo log文件大小和預設配置的redo log大小不一致的話，就會自動將其刪除重建。寫到這里，相信聰明的你應該已經想到什么了吧，沒錯，導致這個啟動報錯的原因是：從master上xtrabackup備份出來的innodb redo log大小和本地配置參數不一致，被刪除重建，結果事務恢復失敗，提示數據也損壞錯誤信息。 ## 解決修改slave本地配置文件，把下面幾個InnoDB配置選項都修改成和在master上的一樣，再次執行恢復啟動即可。 ~~~ innodb_data_file_path innodb_log_file_size innodb_log_files_in_group innodb_file_per_table ~~~