DGE Database Maintenance on UNIX

The DGE databases are located in the directory <TRAVERSE_HOME>/database/mysql/<DB_NAME> with each table for that database represented by a file with the extension .MYD. Traverse comes with a utility to create a fast snapshot by locking all the databases and then directly saving the raw databases.

Note: Remember to replace <TRAVERSE_HOME> in the commands below. Also use the correct name of your backup file where backup-mm-dd-yy,hh-mm.tar.gz appears in the gunzip command below.

Backing up the DGE Database

To back up the DGE database on a UNIX platform, create a backup snapshot with the following commands:

cd <TRAVERSE_HOME>

utils/db_backup.sh

This creates a new snapshot as a tar/gzip archive in <TRAVERSE_HOME>/database/backup.

Restoring the DGE Database

To restore the DGE database from a snapshot created by db_backup.sh, you will must restore the .MYD and .FRM files from the snapshot archive in <TRAVERSE_HOME>/database/backup, and then rebuild the database indexes with the following steps:

1. Shut down all components.
2. At a command prompt, execute the following commands:

   cd <TRAVERSE_HOME>

   etc/traverse.init stop

   mv database/mysql/aggregateddatadb database/mysql/aggregateddatadb.OLD

   gunzip -c database/backup/backup-mm-dd-yy,hh-mm.tar.gz | tar xvf - database/mysql/backup_dge

   etc/dgedb.init start restore

   apps/mysql/bin/mysql --defaults-file=etc/mysql.conf --skip-column-names -u root --password= --batch -e 'show tables;' backup_dge > /tmp/names.txt

   apps/mysql/bin/mysql --defaults-file=etc/mysql.conf -u root --password= --execute "CREATE DATABASE aggregateddatadb;"

   for i in `cat /tmp/names.txt`; do apps/mysql/bin/mysql --defaults-file=etc/mysql.conf -u root --password= --execute "RESTORE TABLE $i FROM '<TRAVERSE_HOME>/database/mysql/backup_dge'" aggregateddatadb; done

   etc/dgedb.init stop

   rm -rf database/mysql/backup_dge

   etc/traverse.init start

Repairing the MySQL DGE Database

Occasionally, because of an unexpected shutdown or a process such as an antivirus program scanning the database directories, database tables might become corrupt. To repair the DGE database tables, shut down Traverse and execute the following commands to rebuild the indexes:

cd <TRAVERSE_HOME>

etc/traverse.init stop

utils/db_repair.sh

If the db_repair.sh script cannot be executed or fails to repair the database (because the extent of damage is too severe), you can manually repair the database as follows:

cd <TRAVERSE_HOME>

rm -f database/mysql/aggregateddatadb/*.TMD

apps/mysql/bin/myisamchk --defaults-file=etc/mysql.conf -r database/mysql/aggregateddatadb/*.MYI

If the -r option fails to repair the database tables, try using the -o option to perform a slower but more effective repair method on the affected tables.

Optimizing the MySQL DGE Database Indexes

In some cases, the database indexes for MySQL can become inefficient and can benefit from some optimization. Generally, this is not needed. However, if the database performance suffers and it is not caused by slow disk I/O, lack of memory, or other typical causes, performing an index optimization can improve performance. To optimize the indexes, use the following commands:

cd <TRAVERSE_HOME>

utils/db_optimize.sh