After the ib_logfile and ibdata1 parameters in MySQL are modified

After the ib_logfile and ibdata1 parameters in MySQL are modified

1. Environment Description

RHEL 6.4 x86_64 + MySQL 5.5.37 and MySQL 5.6.19

Blog: http://blog.csdn.net/hw_libo/article/details/39215723

2. Case: The redo log file (ib_logfile) size parameter is modified: innodb_log_file_size

Innodb_log_file_size = 256 M -- change to 200 M

If the version is 5.5 and startup fails, the following error occurs:

InnoDB: Error: log file./ib_logfile0 is of different size 0 268435456 bytes # InnoDB: than specified in the original redo log size in the. cnf file 0 209715200 bytes! # Redo log size modified 140912 0:01:12 [ERROR] Plugin 'innodb' init function returned error.140912 0:01:12 [ERROR] Plugin 'innodb' registry as a storage engine failed.140912 0:01:12 [ERROR] Unknown /unsupported storage engine: innoDB140912 0:01:12 [ERROR] Aborting

The version is 5.6.16 or later. If it is started normally, the original redo log will be automatically deleted and a new redo log will be generated again (this is important ):

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. # Delete the original 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. /ib_logfile02014-09-11 21:57:07 1093 [Warning] InnoDB: New log files created, LSN = 71249171 # reconstruction of the New redo log

Original redo log size: 2*16384*8 (page size: 8 k)/1024 = 256 MB

New redo log size: 2*12800*8/1024 = 200 MB

3. Case: The ibdata1 file size parameter is modified: innodb_data_file_path

(1) ibdata1 changes from large to small

Innodb_data_file_path = ibdata1: 1G: autoextend -- change to: ibdata1: 300 M: autoextend

In versions 5.5 and 5.6, the startup is normal and there is no exception in the log. However, if autoextend is removed, an error is reported, indicating that the startup fails, for example, [version: 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: cocould 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 shocould 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: 5 1: 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' registry as a storage engine failed.140911 23:51:53 [ERROR] Unknown/unsupported storage engine: innoDB140911 23:51:53 [ERROR] Aborting original ibdata1 size: 65536*8*2/1024 = 1024 M = 1g current ibdata1 is changed to: 19200*8*2/1024 = 300 M

(2) increase the value of ibdata1

For example:

Innodb_data_file_path = ibdata1: 1G: autoextend -- change to: ibdata1: 1200 M: autoextend

The following error occurs during startup:

* *** In version 5.5: [version: 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

* *** In version 5.6: [version: 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

The original ibdata1 size is 65536*8*2/1024 = 1024 M = 1G

The current ibdata1 is modified to 76800*8*2/1024 = 1200 M.

Blog: http://blog.csdn.net/hw_libo/article/details/39215723

-- Bosco QQ: 375612082

---- END ----
Bytes -------------------------------------------------------------------------------------------------------
All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Can I delete ib logfile0 under mysql \ data, ib logfile1 and ibdata1?

Not clear

Relationship between mysql Data deletion and ibdata1

According to my current experience, only mysqldump can be performed each time, and then the whole process is restored.

Haha, no good method is found. If it is too big, reinstall mysql and delete all relevant data, but pay attention to the backup of the configuration file.