InnoDB, given its (relatively) recent change as the default engine of MySQL, can quickly take an unfamiliar administrator off-guard if they\u2019re not aware of the way that InnoDB works behind the scenes. With that in mind, there\u2019s a few key concepts you should probably be aware of if you\u2019re working with any InnoDB tables \u2013 which, if you’re running MySQL 5.6+, you will at the very least have a few of MySQL’s system tables using InnoDB, even with the default set to MyISAM in the configuration file.<\/p>\n
In MyISAM, the table file structure was fairly flexible in that the engine would adapt when it detected new table files or if database folders were present. At a glance, it sounds like this is a great feature, and it is great in the context of MyISAM, but the reason that this is not feasible in InnoDB is not because they\u2019re trying to tie you down or make things more difficult for you \u2013 it\u2019s because InnoDB adheres to data consistency standards, or specifically, <\/span>ACID compliance<\/span><\/a>. This stands for Atomicity, Consistency, Isolation, and Durability.<\/span><\/p>\n In short, this means: Operations only succeed or fail (no in-between), data integrity is ensured, operations are kept from interfering with one another, and the data is stored in a way that minimizes impact from any environmental failures. <\/span><\/p>\n To actually meet these standards, though, InnoDB must maintain a method of verifying the data. To accomplish this, it stores information about your InnoDB tables and their respective databases in places other than the table files themselves. However, if something is moved out of place or changed by hand, InnoDB will notice and raise the alarm \u2013 because as far as InnoDB is concerned, the data integrity has been compromised.<\/span><\/p>\n While 5.6\u2019s transportable tablespace functionality is very useful, it\u2019s still not as simple as throwing the table file into the database directory and firing up MySQL unfortunately – though it\u2019s certainly further in the right direction than we were before. <\/span><\/p>\n If you\u2019re interested in taking advantage of this feature, I\u2019d certainly suggest looking through <\/span>the documentation<\/span><\/a>, as it can come in handy.<\/span><\/p>\n <\/p>\n 2. Re-initializing the ibdata file is almost certainly not the answer.<\/strong><\/p>\n There are very few situations when the InnoDB data file must be re-created to resolve an issue. <\/span><\/p>\n I\u2019ve come across a number of guides and articles online recommending this, and I highly suggest not following their advice unless you are absolutely sure that it is the right move for your situation. It\u2019s presented often as a \u201csolution to everything InnoDB-related\u201d, which \u2013 in a way \u2013 it is. <\/span><\/p>\n If MySQL is crashing because of InnoDB, removing or renaming (renaming always preferred) ibdata1 and the ib_logfiles will certainly bring MySQL back online. However, it will do so without the inclusion of any of your data, even though it will recognize that the table files are present and ask questions about them. <\/span><\/p>\n So if the goal is simply to get MySQL online, and you\u2019re not concerned about any of the data involved, this will do the trick. However, it\u2019d be a good idea to at least move the old database folders out of your data directory first.<\/span><\/p>\n On the other hand, re-creating the log files (ib_logfile*) alone can actually be helpful in a few different situations, and is not quite as risky (of course backups should always be made prior \u2013 be ready to replace with the original files in case you run into trouble). <\/span><\/p>\n For example, in some versions of MySQL, if your log files become corrupted, there\u2019s a chance that it will cause MySQL to segfault, producing a \u201cSignal 11\u201d error in the error logs (signal 11 is the <\/span>POSIX signal code<\/span><\/a> for a generic segmentation fault, so this is not the only reason that you might run into this). In situations like this, it makes complete sense to re-initialize the ib_logfiles. <\/span><\/p>\n Afterwards, you\u2019ll see log sequence number errors flood the error logs \u2013 don\u2019t panic. That\u2019s not a bad thing \u2013 the logs are fresh, and don\u2019t contain any data. It will take a minute for InnoDB to run through the data and get everything up to speed. Keep in mind that the log files are exclusively for the purpose of crash recovery. There is no other time that its records are utilized.<\/span><\/p>\n <\/p>\n 3. The innodb_force_recovery option should not be used as a way of keeping your server online<\/strong><\/p>\n I\u2019ve come across too many servers where, upon investigating, it turns out that the my.cnf file has unnecessarily defined \u201cinnodb_force_recovery=6\u201d, with the administrator not even aware of or recollecting this changes, meaning that it may have been enabled in this way for a significant period of time. <\/span><\/p>\n Granted, it\u2019s difficult to go for any length of time in this mode of recovery without noticing something, given the restrictions that it involves, but yet it does happen often enough to be concerning. <\/span><\/p>\n On the surface, it may seem like a good idea to leave it this way, or in any of the other recovery modes \u2013 the important part is that your server stays online, right? The problem here is due to the way that innodb_force_recovery works, and the reason that it allows your MySQL server to start at all. <\/span><\/p>\n Each mode, with the lowest severity being 1, highest being 6, disables core components of InnoDB. These components are what helps to maintain the integrity and the structure of your data. The higher you go, the more aspects of the InnoDB engine you\u2019re invalidating or disabling. This feature is included solely for the purpose of allowing you to perform dumps or to retrieve other specific MySQL data for recovery so that you can stop MySQL again, and continue with your primary recovery method.<\/span><\/p>\n Operating with innodb_force_recovery enabled, even at its lowest value, is just putting your data at additional risk, and potentially introducing new problems into the mix. <\/span><\/p>\n The general rule of thumb is that modes 1-4 can be enabled without much significant risk to your data (though stability and functionality will be affected) and that 5-6 should only be used in extreme situations. I posted a <\/span>guide<\/span><\/a> on the forums some time ago that includes a bit more detail on the innodb_force_recovery setting, as well as some of the circumstances that you should use it in. That said, there\u2019s no substitute for going through the <\/span>official documentation on this topic<\/span><\/a>.<\/span><\/p>\n <\/p>\n 4. Seeing an InnoDB crash recovery in the MySQL error logs, on its own, is a good<\/em> thing.<\/strong><\/p>\n Believe it or not, InnoDB is designed to crash. That\u2019s a bit of an exaggeration, but its components function the way that they do because they know that there are fail-safes in place to save them if things go awry. One of the reasons that InnoDB\u2019s performance is highly regarded is due to its ability to perform sequential I\/O, which in the context of InnoDB requires that writes are delayed so that they can be grouped and re-ordered before flushing to the disk. <\/span><\/p>\n The problem here is that, when you include delays like this, in which data changes that have not been written to disk yet are still hanging around in the memory, you introduce the risk that a system crash will occur, and anything that was in memory will be wiped clean.<\/span><\/p>\n To solve this, InnoDB has the redo log files. By default, InnoDB stores two of these in your data directory, going by the names ib_logfile0<\/strong> and ib_logfile1<\/strong> (the number of log files stored, as well as their file sizes, can be adjusted via startup parameters defined in my.cnf as documented <\/span>here<\/span><\/a>). <\/span><\/p>\n
\n1. Save for the transportable tablespace<\/a> functionality introduced in MySQL 5.6, InnoDB table files should not<\/em> be considered portable.<\/strong><\/p>\n