iRefIndex Maintenance

When building iRefIndex there can be a need to manage the database system and to assess whether enough disk space is available. The use of MySQL's single tablespace can lead to a very large single file in the filesystem that can appear to use most of the available space (as reported by df -h):

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1             996M  575M  370M  61% /
/dev/sda3              24G  885M   22G   4% /biotek/cn1/programs
/dev/sda8             475G  443G  8.0G  99% /biotek/cn1/storage
tmpfs                 7.9G     0  7.9G   0% /dev/shm
/dev/sda5             9.5G  151M  8.9G   2% /tmp
/dev/sda6             7.6G  3.2G  4.1G  44% /usr
/dev/sda7             1.5G  437M  941M  32% /var

The file itself will look like this (as reported by ls -lh):

-rwxr-xr-x 1 nobody nobody 440G Feb 17 13:39 /biotek/cn1/storage/mysql/var/ibdata1

The following resources describe the situation and potential solutions:

• MySQL Forums :: Database Administration :: how to shrink a MySQL database
• MySQL: Reducing ibdata1
• MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.2 InnoDB Configuration
• MySQL 5.0 Reference Manual :: 13 Storage Engines :: 13.2 The InnoDB Storage Engine :: 13.2.2 InnoDB Configuration :: 13.2.2.1 Using Per-Table Tablespaces

Unfortunately, reducing the database footprint on the disk requires substantial administrative work in single tablespace mode.

Useful Common Options

When dealing with multiple instances of MySQL it can be useful to remember the --defaults-file option to the MySQL tools. For example:

mysql --defaults-file=/home/mysql/etc/my.cnf -h localhost -u root -p -A

This option is omitted from the examples given below.

Dumping and Restoring Databases

MySQL supports SQL and delimited/tabular dumps. Although the latter is arguably more elegant, it does not offer much help with the task of restoring the tables in the correct order so that foreign key constraints are always satisfied. Thus, only the SQL-based dump format is discussed here.

To dump a database:

mysqldump -h <host> -u <username> -p --databases <database>... > <dump file>

To do so in the background:

nohup mysqldump -h <host> -u <username> --password=<password> --databases <database>... > <dump file> 2> <log file> &

To restore a database:

mysql -h <host> -u <username> -p < <dump file>

To do so in the background:

nohup mysql -h <host> -u <username> --password=<password> < <dump file> > <log file> 2>&1 &

Moving Tables into Separate Table Files

To move existing tables into separate files, the server must be stopped:

mysqladmin -u root --password=<password> shutdown

Then, the my.cnf file needs to be changed to enable the single file per table mode. In the [mysqld] section, the following line can be added:

innodb_file_per_table

The server can then be started:

mysqld_safe &

At this point, existing tables residing in the single, common table storage file will continue to be accessible, and only new tables will be created in separate files, but it is possible to move existing tables into separate files by either using an alter table command on each table or by running mysqlcheck as follows:

mysqlcheck --optimize --databases <database>...

This is discussed in the user comments of the Using Per-Table Tablespaces section of the MySQL documentation.

After this process has completed, a directory for the database will appear inside the directory specified by datadir in the defaults (my.cnf) file. However, the shared tablespace (ibdata1) will not be reduced or removed, and InnoDB will retain state information about the tables, regardless of their location, such that removing the shared tablespace will render the tables inaccessible.

Unfortunately, the InnoDB "data dictionary" maintained in the ibdata1 file cannot be easily replaced, and although various hacks exist (such as Recovering an InnoDB table from only an .ibd file.) to attempt to register separate tables with a new, clean "data dictionary", the process seems somewhat speculative.

Promising but Ultimately Unusable Solutions

XtraBackup and innodb_expand_import should together be able to handle the migration of .ibd files. Unfortunately, the software does not even build according to a variety of ways of interpreting the build instructions.