Fix large INNODB

Taken from dba.stackexchange.com/questions/11043/mysql-changing-innodb-file-per-table-for-a-live-db

Here are the steps listed vertically:

Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)

Step 02) Drop all databases (except mysql schema)

Step 03) Shutdown mysql

CAVEAT: To totally clean out uncommitted transactions from the InnoDB Files, run this ...

mysql -uroot -p... -Ae"SET GLOBAL innodb_fast_shutdown = 0;"
service mysql stop

Step 04) 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

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

Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1
      At this point, there should only be the mysql schema in /var/lib/mysql

Step 06) Restart mysql
      This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each

Step 07) Reload SQLData.sql into mysql
      ibdata1 will grow but only contain table metadata
      Each InnoDB table will exist outside of ibdata1
      Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, 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, you can run OPTIMIZE TABLE mydb.mytable and the file /var/lib/mysql/mydb/mytable.ibd will actually shrink.

I have done this many times in my career as a MySQL DBA

In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.

Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!

There is an alternative that will extract the InnoDB table without shrinking ibdata1.

Step 01) 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

Step 02) service mysql restart

Step 03) To extract a single InnoDB table called mydb.mytable, do this:

ALTER TABLE mydb.mytable ENGINE=InnoDB;

This will create one file pluss keep the original structure file

• /var/lib/mysql/mydb/mytable.frm
• /var/lib/mysql/mydb/mytable.ibd

You can do this for every InnoDB table. Unfortunately, ibdata1 will stay 150GB.