Can't find what you need? Do it yourself

It so happened that I needed to set up a backup of the MySQL and PostgreSQL databases. I wanted quite certain, namely:

• Ease of solution
• Each database in a separate file
• The 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 problems with the password for pg_dump
• etc.

I did not find ready-made scripts in the network, but I picked up and combined several solutions, having received the required one.

The general algorithm of scripts is such that a list of databases is selected, and archives are saved by it. Outdated are removed. The scripts have the possibility and necessity of setting: specifying the number of copies of the archive for rotation, the login / password for the DBMS and the folder for storing the results of the work.

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 spent a lot of time with PostgreSQL due to the fact that I could not transfer the password, but the solution was found.

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 great scripts have long and successfully been working on a machine running Debian. Just add water to the crowns.