The ibdata1 file usually grows up to a very large size. The bad new is that the
ibdata1file can´t actually be shrunk unless you delete all databases, remove the files and reload a dump.
But you can configure MySQL so that each table, including its indexes, is stored as a separate file. In that way
ibdata1will not grow as large.
It was a while ago I did this. However, to setup your server to use separate files for each table you need to change
my.cnfin order to enable this:
In this regard I found the following excerpt from the Mysql documents very enlightening:
"You can store each
InnoDBtable and its indexes in its own file. This feature is called “multiple tablespaces” because in effect each table has its own tablespace.
Using multiple tablespaces can be beneficial to users who want to move specific tables to separate physical disks or who wish to restore backups of single tables quickly without interrupting the use of other
To enable multiple tablespaces, start the server with the
--innodb_file_per_tableoption. For example, add a line to the
With multiple tablespaces enabled,
InnoDBstores each newly created table into its own
file in the database directory where the table belongs. This is similar to what the
MyISAMstorage engine does, but
MyISAMdivides the table into a
data file and an
index file. For
InnoDB, the data and the indexes are stored together in the
file is still created as usual.
You cannot freely move
.ibdfiles between database directories as you can with
MyISAMtable files. This is because the table definition that is stored in the
InnoDBshared tablespace includes the database name, and because
InnoDBmust preserve the consistency of transaction IDs and log sequence numbers.
If you remove the
my.cnfand restart the server,
InnoDBcreates tables inside the shared tablespace files again.
--innodb_file_per_tableoption affects only table creation, not access to existing tables. If you start the server with this option, new tables are created using
.ibdfiles, but you can still access tables that exist in the shared tablespace. If you start the server without this option, new tables are created in the shared tablespace, but you can still access any tables that were created using multiple tablespaces.
InnoDBalways needs the shared tablespace because it puts its internal data dictionary and undo logs there. The
.ibdfiles are not sufficient for
.ibdfile and the associated table from one database to another, use a
If you have a “clean” backup of an
.ibdfile, you can restore it to the MySQL installation from which it originated as follows:
.ibdfile backup is one for which the following requirements are satisfied:
There are no uncommitted modifications by transactions in
There are no unmerged insert buffer entries in the
Purge has removed all delete-marked index records from the
mysqld has flushed all modified pages of
.ibdfile from the buffer pool to the file.
.ibdfile using the following method:
.ibdfile is to use the commercial InnoDB Hot Backup tool:
Use InnoDB Hot Backup to back up the
Start a second mysqld server on the
backup and let it clean up the
.ibdfiles in the backup.