InnoDB Storage Engine

Introduction

InnoDB Storage engine default configuration creates a file called ibdata1 which holds the innodb data. By default this file has an initial size of 10mb and it automatically extends.This may eat up your server space and there is a need of knowhow to fix on this kind of problem.

This file is located under ‘C:\Mysql data’

Reasons for Innodb File growing too large

Inno db data file cannot be shrinked,if settings not set during installation the file continues getting large.

Prevention

This can be prevented by forcing the server create an *.ibd for each newly created table using the innodb_file_per_table

How to Solve

a)      Create Back Up and ensure backup is running properly.

b)      MySQLDump all databases into a SQL text file

c)       Drop all databases (except mysql schema)

d)      Shutdown mysql by  running command : mysqladmin –u root –p shutdown.

e)      Add the following lines to /etc/my.cnf

[mysqld]

innodb_file_per_table

innodb_flush_method=O_DIRECT

innodb_log_file_size=1G

innodb_buffer_pool_size=4G

Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.

f)       Delete ibdata1, ib_logfile0 and ib_logfile1

g)      Restart mysql

h)      Reload SQLData.sql into mysql