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 ' > $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 > $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´ > $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