What is the best way to reduce the size of ibdata in mysql?
"Ibdata" file normally houses many classes of information (when innodb_file_per_table is 0)
Table Data
Table Indexes
MVCC (Multiversioning Concurrency Control) Data
Rollbacks Segments
Undo Tablespace
Table Metadata
Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:
* Makes the table's data and indexes contiguous inside ibdata1
** It makes ibdata1 grow because the contiguous data is appended to ibdata1
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently using innodb_file_per_table. To shrink ibdata1 once and for all you must do the following
01) MySQLDump all databases into a SQL text file (call it SQLData.sql)
02) Drop all databases (except mysql schema)
03) Shutdown mysql
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.
05) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
06) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
07) Reload SQLData.sql into mysql
ibdata1 will never contain InnoDB data and Indexes anymore.
