Can't find what you need? Do it yourself

It so happened that I needed to set up a backup of MySQL and PostgreSQL databases. I wanted a very definite one, namely:

• Simplicity of solution
• Each database in a separate file
• Name in the format "bdname.YYYY-MM-DD-HH: mm: ss.sql.gz"
• For each database a certain number of archives is stored
• A set of database archives is in rotation
• Solving specific password issues for pg_dump
• etc.

I did not find any ready-made scripts on the network, but I picked up and combined several solutions, getting what I wanted.

The general script algorithm is such that a list of databases is selected, and archives are saved from it. Obsolete are deleted. In scripts, there is the possibility and need for customization: specifying the number of archive copies for rotation, login / password for the DBMS, and a folder for storing work results.

MySQL:

#!/bin/bash

# user & password
USER=root
PASS=pass

# number of backups to be saved
KEEP=14

# dir to backup
DIR=/var/backups/mysql

NOW=$(date +"%Y-%m-%d")
DBS="$(mysql -u $USER -p$PASS -Bse 'show databases')"

for db in $DBS
do

  BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`
  while [ $BACKUPS -ge $KEEP ]
  do
    ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f
    BACKUPS=`expr $BACKUPS - 1`
  done
  
  FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz
  mysqldump -u $USER -p$PASS $db | gzip -9 > $FILE

done

exit 0

* This source code was highlighted with Source Code Highlighter.

I fiddled with PostgreSQL for a long time because I couldn’t pass the password in any way, but there was a solution.

PostgreSQL:

#!/bin/bash

# user & password
USER=postgres
PASS=pass

# number of backups to be saved
KEEP=14

# dir to backup
DIR=/var/backups/pgsql

PGPASSWORD=$PASS
export PGPASSWORD

NOW=$(date +"%Y-%m-%d")
DBS="$(psql -U $USER -lt |awk '{ print $1}' |grep -vE '^-|^List|^Name|template[0|1]')"

for db in $DBS
do

  BACKUPS=`find $DIR -name "$db.*.gz" | wc -l | sed 's/\ //g'`
  while [ $BACKUPS -ge $KEEP ]
  do
    ls -tr1 $DIR/$db.*.gz | head -n 1 | xargs rm -f
    BACKUPS=`expr $BACKUPS - 1`
  done
  
  FILE=$DIR/$db.$NOW-$(date +"%T").sql.gz

  pg_dump -U $USER $db | gzip -c > $FILE

done

PGPASSWORD=
export PGPASSWORD

exit 0

* This source code was highlighted with Source Code Highlighter.

These wonderful scripts have long and successfully worked on a machine running Debian. Just add water to the crowns.