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 &