[rt-users] Headsup! Mysql 3.23.x Instability (AKA: Keep Backups!)

[Jesse]

FWIW, fsck.com's Mysql instance suffered a crash last week. I lost about
10% of my each_req table.  It's currently running an out of date version
of mysql 3.23 (.3, if i remember correctly)  

As a hedge against a recurrence, I've turned on mysql's update logging
by adding the "--log-update" flag to my mysql start script. This will capture
all database changes in the event that I ever have to replay transactions.

I've got a really rudimentary hack of rt-backup running to back up all my RT
tables and transactions and config every night at 3 am.


If you depend on RT for anything business critical, you should be doing mysql
update logging and making regular backups.


	Jesse

This script will do a passable job backing up a reasonably sized RT instance.
It needs lots of work

#!/bin/sh

#originally by _____
# hacked up by jesse after a big mysql crash
umask 0077
LOCKFILE=/opt/rt/save_rt.pid
LOGFILE=/opt/rt/save_rt.log
OUTPUT=/var/rt/rt_dump-`date +%y%m%d`.tgz
TMPPATH=/tmp/
TERM=linux; export TERM

if [ -r ${LOCKFILE} ]; then
           echo /opt/rt/save_rt.sh already running \(PID=`cat ${LOCKFILE}`\).
           kill -9 `cat ${LOCKFILE}` && \
           rm ${LOCKFILE} || exit 1
fi

echo $$>$LOCKFILE
echo STARTING at `date` ----------->>$LOGFILE


export PATH=/opt/mysql/bin:$PATH
mysqldump -c -t rt>${TMPPATH}rt.$$.sqldump && \
mysqldump -d rt>${TMPPATH}rt.$$.sqlschema && \
mysqldump -c -t mysql>${TMPPATH}rt.$$.mysqldump && \
mysqldump -d mysql>${TMPPATH}rt.$$.mysqlschema && \
cd /opt && tar czf ${TMPPATH}rt.$$.tgz rt && \
cd ${TMPPATH} && \
tar czf $OUTPUT rt.$$.* && \
        echo BACKUP DATA COLLECTED at `date` ----------->>$LOGFILE
rm ${TMPPATH}rt.$$*

rm $LOCKFILE
~                     


-- 
jesse reed vincent -- jrvincent at wesleyan.edu -- jesse at fsck.com 
pgp keyprint:  50 41 9C 03 D0 BC BC C8 2C B9 77 26 6F E1 EB 91
--------------------------------------------------------------
...realized that the entire structure of the net could be changed to be made 
more efficient, elegant, and spontaneously make more money for everyone 
involved. It's a marvelously simple diagram, but this form doesn't have a way 
for me to draw it.  It'll wait. 				-Adam Hirsch