Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

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>"
share|improve this answer
    
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

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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