ibdata1 once and for all you must do the following:
- Dump (e.g., with
mysqldump) all databases into a .
sqltext file (
SQLData.sqlis used below)
- Drop all databases (except for
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
- Shutdown MySQL
- Add the following lines to
[mysqld] innodb_file_per_table innodb_flush_method=O_DIRECT innodb_log_file_size=1G innodb_buffer_pool_size=4G
innodb_buffer_pool_size, make sure
innodb_log_file_sizeis 25% of
innodb_flush_method=O_DIRECTis not available on Windows)
ib_logfile*, Optionally, you can remove all folders in
- Start MySQL (This will recreate
ibdata1[10MB by default] and
ib_logfile1at 1G each).
ibdata1 will still grow but only contain table metadata because each InnoDB table will exist outside of
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:
mytable.frm(Storage Engine Header)
mytable.ibd(Table Data and Indexes)
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.
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