InnoDB tips

We are handling lots of database issues related to innodb mysql server. While troubleshooting database issues with the innodb mysql server, you should be more careful otherwise simple mistake might cause database corruption and data loss. I’ll share some important InnoDB tips in this article.

The versions MySQL 5.5 have MyISAM as their default database storage engine. In MySQL 5.5, the default settings have been changed as InnoDB becomes the default database storage engine. InnoDB maintains a buffer pool to store table and index data. When some information needs to be processed, it is directly fetched from this buffer speeding up data access and retrieval.

You can check innodb support enabled or not by MySQL system variable “have_inndb”. The following command can use for this :
mysqladmin variables | grep have_innodb

Two important disk-based resources managed by the InnoDB storage engine are its tablespace data files and its log files. If you do not specify the InnoDB configuration options, MySQL creates ibdata1 and two log files named ib_logfile0 and ib_logfile1 in the MySQL data directory. Their size is given by the size of the innodb_log_file_size system variable.
The ibdata file contains the table data of the InnoDB tables. Please note when we deleted the contends from InnoDB tables, the ibdata file will contain pages marked as “free” which could be used for future data, but the file itself is unshrinkable. That means the file size will not be reduced. The ibdata1 file not shrunk unless you delete all databases, remove the files and reload a dump. If you have deleted larger amounts of data and need to shrink ibdata, the only supported way goes through backing up the tables using mysql dump and re-initializing your database.

The steps in this process are given below :-
Do a mysqldump of all databases, procedures, triggers etc
Drop all databases except the mysql database
Stop mysql
Delete ibdata1 and ib_log files
Start mysql
Restore from dump
When we start MySQL, the ibdata1 and ib_log files will be recreated.

As we discussed, various environmental factors may cause corruption in InnoDB database.
The possible reason for innodb crash are followed:

1) A disk failure instance, may lead to database corruption or loss of files
2) An issue in the disk subsystem could fake flush of files to persistent storage. If you face a power outage or system crash at this stage, it may result in serious corruption.
3) Hardware failures
4) It would happen on a forceful reboot Or if you killed the MySQL Service.
5) Bugs in the MySQL or InnoDB code
6) Using small InnoDB buffer size; innodb_buffer_pool_size in my.cnf
7) Storage of mysql dumb file in database location.
8) Moving the database to another location while the mysql server is active.

InnoDB can perform crash recovery, ‘undo logs’ are there to store all incomplete database transactions. During crash recovery, these transactions are rolled back to make the database logically consistent.

Recovering steps from corrupted InnoDB tables
Step 1 – Bring up your database in recovery mode
You should bring down your database,kill the process and start it in recovery mode, with innodb_force_recovery. The recovery mode makes your databases read only. To prevent your MySQL getting hammered the second it comes back, change port of MySQL server from 3306 to something random. Add innodb_force_recovery=1 to your my.cnf In case your server doesn’t want to come back, you may further increase this number from 1 to 6, check MySQL manual to see what the differences are.

Check the MySQL logs and if you see like:
InnoDB: Waiting for the background threads to start
You should also add innodb_purge_threads=0 to your my.cnf.
So all together to bring back database, I had to add these 3 parameters in my.cnf:
port = 8881
innodb_force_recovery=3
innodb_purge_threads=0

Step 2 – Check which tables are corrupted and make a list
The database back up and running in recovery mode. Now we need to find out which tables got corrupted.
mysqlcheck –all-databases
Check for lines where it says table is Corrupted. Write down all tables / databases that got you an error.

Step 3 – Backup and drop your corrupted tables
Once you got the list of corrupted tables, you should mysqldump them to their own .sql files, that way you will have backup for reimport.
dump one table in database:
mysqldump my_database table > database.table.sql
After the backup process completed, drop the corrupted tables by executing: drop table database.table; from MySQL shell.
MySQL database clean process completed and now we can boot it up back without recovery mode.

Step 4 – Restart MySQL in normal mode
When we don’t have any corrupted tables left in our database, we should remove the my.cnf settings that we added in Step 1. Don’t remove the port setting yet, because your database is still missing tables you backed up and need to be reimported. Restart MySQL.

Step 5 – Import backup .sql
Import each dumped .sql table to their respected database.
mysql database < database.table.sql

Step 6 – Change port
Reboot MySQL and check. It should come back and start working just as before the crash.
You can follow the given steps if you need to create an alternate database dumb file  from R1soft.

1. Restore the raw DB files from the recovery point:
ib_logfile0
ib_logfile1
ibdata1
/var/lib/mysql/mysql
/var/lib/mysql/<username>_dbname
The log files, ibdata and mysql tables must always be restored in addition to the actual user database(s).
Important: Restore to an ALTERNATE location (do NOT overwrite existing location). Use e.g. /var/lib/mysql_tmp (ensure there is enough space). Folder needs to have ownership by mysql:mysql.

2. Start a temporary MySQL instance
Replace the path with where the data was restored.
mysqld_safe –socket=/var/lib/mysql_tmp/mysql.sock –port=3307 –datadir=/var/lib/mysql_tmp
You now have a separate MySQL server running with the data from the restored backup. It is now possible to connect to the instance using port 3307 or the socket path.

3. Generate DB dump
To create a database dump from the temp instance, use this command (replace path to socket with actual path):
mysqldump –socket=/var/lib/mysql_tmp/mysql.sock dbname > dbname.sql

4. Cleanup
Stop the temp instance by killing the process and delete the restored files.