If you want to change MySQL setting innodb_log_file_size to improve performance the top answer on stack overflow says you have to delete the redo log in /var/lib/mysql/ib_logfile* so that MySQL creates it with the new size. Before deleting you obviously shutdown mysql.
But a normal shutdown of MySQL is a fast shutdown which might leave changes stored in logs but not in table pages. This is normally OK as mysql works through the logs and processes all changes.
But by deleting the redo log…you might create an inconsistent database! As table pages might be marked dirty but the changes can’t be loaded anymore from the redo log. I did it many times with fast shutdown not knowing the risk but today mysql got corrupted with the database still working but in inconsistent state.
How to prevent this?
Old answer was: switch off fast shutdown by SET GLOBAL innodb_fast_shutdown = 0
After shutdown you can now safely delete the redo log. But this is only relevant up to MySQL 5.6.8.
So what is the correct way in modern mysql to change innodb_log_file_size?
- shutdown mysql
- change
innodb_log_file_size - start mysql
- documentation says: “If
InnoDBdetects that theinnodb_log_file_sizediffers from the redo log file size, it writes a log checkpoint, closes and removes the old log files, creates new log files at the requested size, and opens the new log files.“
- documentation says: “If
Of course we had backups so nothing happened except for the work.
Lessons learned
- MySQL is really robust: it starts and allows creating a dump even with
- corrupted databases
- corrupted mysql database (missing tables etc)
- read the documentation
- read all answers on stack overflow carefully – not just the top one 😉