Following
steps would solve the issue:
Step-1: MySQLDump all
databases into a SQL text file (e.g. SQLDataBackup.sql)
Step-2: Drop all databases
(except mysql, information_schema and performance_schema schemas)
Step-3: Shutdown MySQL
Step-4: Add the following
lines to (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini
if xampp in windows)
[mysqld]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G
innodb_data_file_path=ibdata1:10M:autoextend
Note: Whatever you set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
Step-5: Delete ibdata1,
ib_logfile0 and ib_logfile1
There should only be the mysql schema in data location.
Step-6: Restart MySQL
This will recreate ibdata1 at 10MB (do not configure the option)
, ib_logfile0 and ib_logfile1 at 1G each
Step-7: Reload
SQLDataBackup.sql into MySQL
ibdata1 will grow but only contain table metadata and
intermittent MVCC data. Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go
into path/mysql/data, you will see two files representing the table
mytable.frm (Storage Engine Header)
mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in (/etc/my.cnf if linux) or (C:\xampp\mysql\data\my.ini if xampp in windows), you can run OPTIMIZE TABLE mydb.mytable OR ALTER TABLE
mydb.mytable ENGINE=InnoDB; and the file will actually shrink.
