If, like me, you have a database full of small tables, but all amounting to a large amount of data then dumping the data from it into a single file is handy, but not very useful when it comes to retrieving a backup of one single table.
Instead, wouldn’t it be easier to dump each individual table into it’s own file? What about keeping a week’s worth of backups? Here’s one way I backup my WordPress db with a little Bash script:
export d=`date +%u`
mkdir -p backup/$d
for i in `echo "show tables" | mysql -u username -ppassword database|grep -v Tables_in_`;
do
echo $i; mysqldump --add-drop-table --allow-keywords -q -a -c -u username -ppassword database $i > backup/$d/$i.sql
rm -f backup/$d/$i.sql.gz
gzip backup/$d/$i.sql
done
