Back up all MySQL and PostgreSQL databases

From LinuxServerTech

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
  #done