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

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 &