mysql backups w/ per table files

[molesquirrel]

I've been using the cp's mysql backup, which has done fine backing up thus far. However, there doesn't seem to be a way to have it in this fashion:

mysql_backup_20100101.sql.bz2
----contents
table_1.sql
table_2.sql

where-in each of the tables is isolated to a single file, with the resultant compressed file holding all those table files.

If this is not possible with the cp, what would you suggest for backing it up otherwise? would it be possible for you to show me what the mysql backup script is actually producing in terms of the command-line arguments so that I could possible take that, alter it, then put it in as a cronjob?

[Matt]

There isn't a way directly via mysqldump without enumerating over each table definition. Something like this will work. DB refers to the target database to dump.

DB=mysql
mkdir $DB
TABLES=`echo "show tables" | mysql -N $DB`
for i in $TABLES
    do mysqldump $DB $i > $DB/$i.sql
done
tar -cvzf "$DB-`date '+%Y%m%d'`.tar.gz" $DB/
rm -f $DB/*.sql
rmdir $DB