Linux Server Tech (provided by Daily Data, Inc) - Back up all MySQL and PostgreSQL databases

Back up all MySQL and PostgreSQL databases== Single line command == Following simply takes the output of mysqlshow, parses it, then passes it to mysqldump. Files are created in the current directory

for db in ´mysqlshow | cut -d ' ' -f 2 | grep -v '+'´; do mysqldump $db > $db.dmp ; done

If you want to compress the files as they are created, add the gzip command:

for db in ´mysqlshow | cut -d ' ' -f 2 | grep -v '+'´; do mysqldump $db > $db.dmp ; gzip $db.dmp ; done

Script suitable for a cron job (like cron.daily)

This uses "knowledge" of mysql's location to back up all database

  #! /bin/bash
MYSQLDATAPATH=/var/lib/mysql/
DBSAVEPATH=/home/dbbackup/
POSTGRES_BACKUP_NAME=posgres.dmp
if ! -e $DBSAVEPATH 
then
mkdir $DBSAVEPATH
chown postgres:root $DBSAVEPATH
fi
# get all postgres databases, just do a db dump
su postgres -c 'pg_dumpall -d -c ' &gt; $DBSAVEPATH$POSTGRES_BACKUP_NAME
if -e $DBSAVEPATH$POSTGRES_BACKUP_NAME.gz 
then
rm -f $DBSAVEPATH$POSTGRES_BACKUP_NAME.gz
fi
gzip -9q $DBSAVEPATH$POSTGRES_BACKUP_NAME
# back up all MySQL databases
for file in ´ls $MYSQLDATAPATH´
do
if -d $MYSQLDATAPATH$file 
then
mysqldump -c --add-drop-table --password=sachemic $file &gt; $DBSAVEPATH$file.dmp
if -e $DBSAVEPATH$file.dmp.gz 
then
rm -f $DBSAVEPATH$file.dmp.gz
fi
gzip -9q $DBSAVEPATH$file.dmp
echo Database $file backed up to $DBSAVEPATH$file.dmp.gz
fi
done
# special backup for rmmm
# this will back up all tables that are not zip code or the test table
# which are huge and static
#for file in ´ls $MYSQLDATAPATH | grep -i rmmm´
#do
# if -d $MYSQLDATAPATH$file 
# then
# mysqldump $file -c --add-drop-table --tables ´mysql $file -e 'show tables;' | grep -v zip | grep -v Tables_in | grep -v test´ &gt; $DBSAV
# if -e $DBSAVEPATH$file.dmp.gz 
# then
# rm -f $DBSAVEPATH$file.dmp.gz
# fi
# gzip -9q $DBSAVEPATH$file.dmp
# echo Database $file backed up to $DBSAVEPATH$file.dmp.gz
# fi