MySQL 無(wú)法啟動(dòng)例一

場(chǎng)景

某個(gè)打算用于slave新搭建的實(shí)例啟動(dòng)報(bào)錯(cuò)，啟動(dòng)過(guò)程中報(bào)告InnoDB數(shù)據(jù)頁(yè)發(fā)生損壞。錯(cuò)誤日志像下面這樣：

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 332768 to 216384 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.

分析

正常情況下，新部署的實(shí)例是不太可能出現(xiàn)InnoDB數(shù)據(jù)頁(yè)損壞的。經(jīng)了解，這個(gè)實(shí)例是采用xtrabackup工具從master備份過(guò)來(lái)做恢復(fù)的。

細(xì)心的同學(xué)，通過(guò)觀察上面的日志，應(yīng)該能從中發(fā)現(xiàn)一些蛛絲馬跡。有幾個(gè)地方需要引起注意：

1、版本是Percona Server 5.6.21；
2、剛啟動(dòng)就把InnoDB的redo log給resize了；
3、剛啟動(dòng)就發(fā)現(xiàn)InnoDB的page LSN和redo log中的不匹配；

之所以提醒大家注意上面的三點(diǎn)，并且把版本信息放在了第一條，是因?yàn)閺?.6版本開(kāi)始，InnoDB如如果發(fā)現(xiàn)當(dāng)前的redo log文件大小和預(yù)設(shè)配置的redo log大小不一致的話(huà)，就會(huì)自動(dòng)將其刪除重建。

寫(xiě)到這里，相信聰明的你應(yīng)該已經(jīng)想到什么了吧，沒(méi)錯(cuò)，導(dǎo)致這個(gè)啟動(dòng)報(bào)錯(cuò)的原因是：從master上x(chóng)trabackup備份出來(lái)的innodb redo log大小和本地配置參數(shù)不一致，被刪除重建，結(jié)果事務(wù)恢復(fù)失敗，提示數(shù)據(jù)也損壞錯(cuò)誤信息。

解決

修改slave本地配置文件，把下面幾個(gè)InnoDB配置選項(xiàng)都修改成和在master上的一樣，再次執(zhí)行恢復(fù)啟動(dòng)即可。

innodb_data_file_path
innodb_log_file_size
innodb_log_files_in_group
innodb_file_per_table