Home / server / Questions / 90819
Accepted
sal
Asked: 2009-12-04 12:38:51 +0800 CST

how to back up a lot of small tables from mysql?

  • 772

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?

mysql

2 Answers

  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.

    • 1
  2. Best Answer

    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>"
    • 1