0

Is there a preferred way to back up a config database in MySQL?

I'm talking about a schema with ~160 tables where most of the tables have less than 100 rows of data. Only one or two tables change in any given month. Restores; when they need to happen, are usually just one table.

The current method is to back up the whole database every night.

If there an easy way to dump out the tables one by one into a directory tree that could then be added to source code control?

1

You could make a script that calls the mysql command line and selects the tables out:

Something like (this probably won't run):

#!/bin/bash

OUTDIR="./tmp"

CONFTABLES=`echo "SHOW TABLES;"|mysql -u<username> -p<passwd> <CONFDB>`

for i in CONFTABLES ; do
  echo "select * from $i;" > $OUTDIR/$i
done;

cd tmp
svn ci -m "<maybe the date as the comment>"
  • The select output will help keep things sane for version control, but will make things very difficult if he ever needs to restore from those backups. – EEAA Dec 3 '09 at 21:09
  • It's not too bad... the select output is compatible with loadfile as I remember it (tab delimited). If not using a "select * from $i into outfile '$OUTDIR/$i'" should work instead. The big problem is this method won't save any DDL/schema information so reconstructing the tables themselves may be challenging. – mfarver Dec 3 '09 at 22:04
  • I ended up writing a script to dump the tables out to sorted csv files. – sal Dec 7 '09 at 2:50
1

I use the automysqlbackup script to maintain versioned backups of my databases. It keeps a directory tree of your daily/weekly/monthly backups. It also is able to email you a log of the backup log, making it easy for you to ensure that your backups are running

As far as putting these in version control, I suppose you could do this. It's going to be rather ineffecient, though, as it's just compressed binary data.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.