Many people create multiple ibdata
files hoping for better disk-space management and performance, however that belief is mistaken.
Can I run ?
Unfortunately, running against an InnoDB table stored in the shared table-space file ibdata1
does two things:
You can however, segregate Table Data and Table Indexes from ibdata1
and manage them independently.
Can I run with ?
Suppose you were to add to /etc/my.cnf (my.ini)
. Can you then just run on all the InnoDB Tables?
Good News : When you run with enabled, this will produce a .ibd
file for that table. For example, if you have table mydb.mytable
witha datadir of /var/lib/mysql
, it will produce the following:
The .ibd
will contain the Data Pages and Index Pages for that table. Great.
Bad News : All you have done is extract the Data Pages and Index Pages of mydb.mytable
from living in ibdata
. The data dictionary entry for every table, including mydb.mytable
, still remains in the data dictionary (See the ). YOU CANNOT JUST SIMPLY DELETE ibdata1
AT THIS POINT !!! Please note that ibdata1
has not shrunk at all.
InnoDB Infrastructure Cleanup
To shrink ibdata1
once and for all you must do the following:
Dump (e.g., with mysqldump
) all databases into a .sql
text file (SQLData.sql
is used below)
Drop all databases (except for mysql
and information_schema
) CAVEAT : As a precaution, please run this script to make absolutely sure you have all user grants in place:
mkdir /var/lib/mysql_grants
cp /var/lib/mysql/mysql/* /var/lib/mysql_grants/.
chown -R mysql:mysql /var/lib/mysql_grants
Login to mysql and run SET GLOBAL innodb_fast_shutdown = 0;
(This will completely flush all remaining transactional changes from ib_logfile0
and ib_logfile1
)
Shutdown MySQL
Add the following lines to /etc/my.cnf
(or my.ini
on Windows)
[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
.
Also: innodb_flush_method=O_DIRECT is not available on Windows)
Delete ibdata*
and ib_logfile*
, Optionally, you can remove all folders in /var/lib/mysql
, except /var/lib/mysql/mysql
.
Start MySQL (This will recreate ibdata1
[10MB by default] and ib_logfile0
and ib_logfile1
at 1G each).
Import SQLData.sql
Now, ibdata1
will still grow but only contain table metadata because each InnoDB table will exist outside of ibdata1
. ibdata1
will no longer contain InnoDB data and indexes for other tables.
For example, suppose you have an InnoDB table named mydb.mytable
. If you look in /var/lib/mysql/mydb
, you will see two files representing the table:
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 shrank a 50GB ibdata1
file down to only 500MB!
Give it a try. If you have further questions on this, just ask. Trust me; this will work in the short term as well as over the long haul.
CAVEAT
At Step 6, if mysql cannot restart because of the mysql
schema begin dropped, look back at Step 2. You made the physical copy of the mysql
schema. You can restore it as follows:
mkdir /var/lib/mysql/mysql
cp /var/lib/mysql_grants/* /var/lib/mysql/mysql
chown -R mysql:mysql /var/lib/mysql/mysql
Go back to Step 6 and continue