Our project (SETI_BOINC) had a corrupt repilca server and we needed to restore it with a minimal outage.

We could not copy the data directly accross because the replica server(Sparc) was not binary compatible with the master server(x86-64bit).

The command line below dumps and compresses SETI_BOINC db from around 38GB to 1.7GB gzip file
in 1':42".

bin/mysqldump --opt -F -pxxxx -u mysql SETI_BOINC | gzip -c - > /mnt/gowron_share/seti_boinc_mysql_backup_4_28_2005/db_dump.gz

mysqldump options
--opt  -   this combines a number of optimizations for the dump process
-F     -   this forces a flush to disk before starting the dump
-pxxxxx =  password is xxxxxx
-u      -  usercode is mysql

gzip -     external compression utility > output to file


Some explanation :

1.  There were lots of partially used pages in result and workunit tables because of a recent purge/delete operation.

2.  The mysqldump takes advantage of concatenated inserts with mainly ascii data for multiple row inserts creating insert statements of up to 2MB in length for lots of rows.

3.  MySQLDump avoids dumping indices which account for approx 25% of the db size


Surprising compactness and swiftness. This is less interruption than any backup process other than snapshot and  copy. The backedup db can also be restored to any kind of server regardless of hardware type.

The dump file can then be read my bin/mysql client to restore the db. It creates the tables and indices and then inserts the rows for each table in the database.