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?
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>" 
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. 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.
