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@wesleyan.edu – jesse@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
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.
Oh, yeah - that’s generally a very good tip anyway. Ironically, I’m
living together with some friends - one of them is a system administrator
at a web hosting firm which use mysql extensively to everything. We
started talking about backups, and I told him how nice the logging
possibilities in mysql are, and particularly the update log, and told him
that it was very important to have this one turned on for backup purposes.
One week later he sat 24 hours in a row at his work fighting with a broken
database. Of course he hadn’t yet started that logging I suggested 
Tobias Brox
aka TobiX
+47 22 925 871
Tobias Brox wrote:
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.
Oh, yeah - that’s generally a very good tip anyway. Ironically, I’m
living together with some friends - one of them is a system administrator
at a web hosting firm which use mysql extensively to everything. We
started talking about backups, and I told him how nice the logging
possibilities in mysql are, and particularly the update log, and told him
that it was very important to have this one turned on for backup purposes.
At our work the CVS repositories are backed up rigorously, but other
stuff like MySQL is sometimes given a lower priority (if they are
on development servers for example).
A neat trick is to put a mysqldump into CVS, and then CVS commit the
updated MySQL dump bi-daily … it diff’s very well indeed since its
all largely text, and you can pull out old copies of the database
quite easily from the cvs at any time … saves keeping old full
dumps in backups as you can just backup the repository and have full
history available at any time for recreation.
Anil
At our work the CVS repositories are backed up rigorously, but other
stuff like MySQL is sometimes given a lower priority (if they are
on development servers for example).
IMO a good backup possibilities and a good roll-forward system (like the
update-log) is some of the more significal advantages of using a DBMS.
When discarding that, you could just as well use flat files.
Tobias Brox
aka TobiX
+47 22 925 871
Tobias Brox wrote:
At our work the CVS repositories are backed up rigorously, but other
stuff like MySQL is sometimes given a lower priority (if they are
on development servers for example).
IMO a good backup possibilities and a good roll-forward system (like the
update-log) is some of the more significal advantages of using a DBMS.
When discarding that, you could just as well use flat files.
Very true …
In MySQL’s case though, I would never use it to store truely important
data, as it doesn’t provide functions like atomic transactions or
rollback … a sudden power outage in the middle of an operation is
occasionally sufficent to totally corrupt your MySQL database, which
is something rarely seen in high-end transaction based databases that
simply rollback to the previous state after a failure.
Still, for a system like RT, which has relatively few records, and
also a low rate of entries (unless your entire company
is sitting there feverishly entering tasks 24 hours a day , MySQL
is pretty perfect - small, fast, and free.
There’s an interesting thread on Slashdot at the moment about the
advantages/disadvantages of MySQL and other RDBMS here:
Not of relevance for RT1, but possibly for RT2 when considering
the platform to use if reliability is of great important
(Postgres 7 is meant to support rollback I believe, which makes
it quite an attractive choice for reliability, if not speed)
Anil