MySQL中ib_logfile和ibdata1參數大小被修改後，ib_logfileibdata1

MySQL中ib_logfile和ibdata1參數大小被修改後，ib_logfileibdata1

1. 環境說明

RHEL 6.4 x86_64 + MySQL 5.5.37和MySQL 5.6.19

blog地址：http://blog.csdn.net/hw_libo/article/details/39215723

2. 案例：redo log檔案（ib_logfile）大小參數被修改：innodb_log_file_size

innodb_log_file_size = 256M  -- 修改為200M

版本是5.5的，啟動失敗，會出現如下錯誤：

InnoDB: Error: log file ./ib_logfile0 is of different size 0 268435456 bytes  ## 原來redo log的大小InnoDB: than specified in the .cnf file 0 209715200 bytes!   ## redo log被修改的大小140912  0:01:12 [ERROR] Plugin 'InnoDB' init function returned error.140912  0:01:12 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.140912  0:01:12 [ERROR] Unknown/unsupported storage engine: InnoDB140912  0:01:12 [ERROR] Aborting

版本是5.6.16之後的，正常啟動，會自動將原來的redo log刪除，然後重建新的redo log（這個比較重要）：

2014-09-11 21:57:05 1093 [Warning] InnoDB: Resizing redo log from 2*16384 to 2*12800 pages, LSN=712491712014-09-11 21:57:05 1093 [Warning] InnoDB: Starting to delete and rewrite log files.    ## 刪除原redo log2014-09-11 21:57:05 1093 [Note] InnoDB: Setting log file ./ib_logfile101 size to 200 MBInnoDB: Progress in MB: 100 2002014-09-11 21:57:06 1093 [Note] InnoDB: Setting log file ./ib_logfile1 size to 200 MBInnoDB: Progress in MB: 100 2002014-09-11 21:57:07 1093 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile02014-09-11 21:57:07 1093 [Warning] InnoDB: New log files created, LSN=71249171   ## 新redo log重建完畢

原redo log大小：2*16384*8(page的大小為8k)/1024=256MB

新redo log大小：2*12800*8/1024=200MB

3. 案例：ibdata1檔案大小參數被修改：innodb_data_file_path

（1）ibdata1從大改小

innodb_data_file_path = ibdata1:1G:autoextend  -- 修改為：ibdata1:300M:autoextend

在版本是5.5和5.6中，啟動正常，並且日誌中沒有任何異常。但是如果將autoextend去掉，就會報錯，啟動失敗，如：[版本：5.5.37]

InnoDB: Error: data file ./ibdata1 is of a different sizeInnoDB: 65536 pages (rounded down to MB)InnoDB: than specified in the .cnf file 19200 pages!140911 23:51:53 InnoDB: Could not open or create data files.140911 23:51:53 InnoDB: If you tried to add new data files, and it failed here,140911 23:51:53 InnoDB: you should now edit innodb_data_file_path in my.cnf back140911 23:51:53 InnoDB: to what it was, and remove the new ibdata files InnoDB created140911 23:51:53 InnoDB: in this failed attempt. InnoDB only wrote those files full of140911 23:51:53 InnoDB: zeros, but did not yet use them in any way. But be careful: do not140911 23:51:53 InnoDB: remove old data files which contain your precious data!140911 23:51:53 [ERROR] Plugin 'InnoDB' init function returned error.140911 23:51:53 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.140911 23:51:53 [ERROR] Unknown/unsupported storage engine: InnoDB140911 23:51:53 [ERROR] Aborting原來的ibdata1大小為：65536*8*2/1024=1024M=1G當前ibdata1被修改為：19200*8*2/1024=300M

（2）ibdata1值小改大

比如：

innodb_data_file_path = ibdata1:1G:autoextend  -- 修改為：ibdata1:1200M:autoextend

那麼會在啟動時出現如下錯誤：

*****在5.5版本中：[版本：5.5.37]

InnoDB: Error: auto-extending data file ./ibdata1 is of a different sizeInnoDB: 65536 pages (rounded down to MB) than specified in the .cnf file:InnoDB: initial 76800 pages, max 0 (relevant if non-zero) pages!140911 23:57:43 InnoDB: Could not open or create data files.140911 23:57:43 InnoDB: If you tried to add new data files, and it failed here,140911 23:57:43 InnoDB: you should now edit innodb_data_file_path in my.cnf back140911 23:57:43 InnoDB: to what it was, and remove the new ibdata files InnoDB created140911 23:57:43 InnoDB: in this failed attempt. InnoDB only wrote those files full of140911 23:57:43 InnoDB: zeros, but did not yet use them in any way. But be careful: do not140911 23:57:43 InnoDB: remove old data files which contain your precious data!140911 23:57:43 [ERROR] Plugin 'InnoDB' init function returned error.140911 23:57:43 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.140911 23:57:43 [ERROR] Unknown/unsupported storage engine: InnoDB140911 23:57:43 [ERROR] Aborting

*****在5.6版本中：[版本：5.6.19]

2014-09-11 22:20:01 1222 [ERROR] InnoDB: auto-extending data file ./ibdata1 is of a different size 65536 pages (rounded down to MB) than specified in the .cnf file: initial 76800 pages, max 0 (relevant if non-zero) pages!2014-09-11 22:20:01 1222 [ERROR] InnoDB: Could not open or create the system tablespace. If you tried to add new data files to the system tablespace, and it failed here, you should now edit innodb_data_file_path in my.cnf back to what it was, and remove the new ibdata files InnoDB created in this failed attempt. InnoDB only wrote those files full of zeros, but did not yet use them in any way. But be careful: do not remove old data files which contain your precious data!2014-09-11 22:20:01 1222 [ERROR] Plugin 'InnoDB' init function returned error.2014-09-11 22:20:01 1222 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.2014-09-11 22:20:01 1222 [ERROR] Unknown/unsupported storage engine: InnoDB2014-09-11 22:20:01 1222 [ERROR] Aborting

原來的ibdata1大小為：65536*8*2/1024=1024M=1G

當前ibdata1被修改為：76800*8*2/1024=1200M

blog地址：http://blog.csdn.net/hw_libo/article/details/39215723

-- Bosco  QQ：375612082

---- END ----
-------------------------------------------------------------------------------------------------------
著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

刪掉mysql\data下的ib logfile0,ib logfile1與ibdata1可以?

不太清楚
 
mysql資料刪除與ibdata1關係的問題

按照本人目前的經驗，只能每次mysqldump，然後再整個恢複。

呵呵，沒發現什麼好的方法，要是還嫌大，就重裝下mysql，把所有的相關資料都刪，但是注意設定檔的備份。