| 1 | #!/bin/bash |
| 2 | |
| 3 | # |
| 4 | # This script backs up every database to its own file, gzipped. |
| 5 | # docelic@hcoop.net, Tue Aug 28 13:38:26 EDT 2007 |
| 6 | # |
| 7 | |
| 8 | BACKUP_ROOT=/srv/backup/database |
| 9 | BACKUP_DAYS=7 |
| 10 | |
| 11 | if ! test -d "$BACKUP_ROOT"; then |
| 12 | echo "Backup root directory '$BACKUP_ROOT' inaccessible." |
| 13 | exit 1 |
| 14 | fi |
| 15 | |
| 16 | # change cwd; default will be /root which postgres user can't chdir into |
| 17 | cd /tmp |
| 18 | |
| 19 | MYSQL_DBS=`sudo -H mysql -Bse 'show databases' | xargs` |
| 20 | echo "Mysql Databases are: $MYSQL_DBS" |
| 21 | PGSQL_DBS=`sudo -u postgres psql template1 -tAc 'SELECT datname FROM pg_database WHERE datistemplate = false;' | xargs` |
| 22 | echo "Postgres Databases are: $PGSQL_DBS" |
| 23 | |
| 24 | # Delete oldest |
| 25 | rm -rf "$BACKUP_ROOT/mysql.$BACKUP_DAYS" |
| 26 | rm -rf "$BACKUP_ROOT/postgres-9.$BACKUP_DAYS" |
| 27 | |
| 28 | # Rotate backup dirs |
| 29 | for day in `seq $BACKUP_DAYS -1 1`; do |
| 30 | to=".$day" |
| 31 | let day_before=" $day - 1" |
| 32 | if test "$day" = "1"; then |
| 33 | from='' |
| 34 | else |
| 35 | from=".$day_before" |
| 36 | fi |
| 37 | |
| 38 | mv -f "$BACKUP_ROOT/mysql$from" "$BACKUP_ROOT/mysql$to" 2>/dev/null |
| 39 | mv -f "$BACKUP_ROOT/postgres-9$from" "$BACKUP_ROOT/postgres-9$to" 2>/dev/null |
| 40 | done |
| 41 | |
| 42 | # backups should not be world-readable, mask all o permissions |
| 43 | umask 0027 |
| 44 | |
| 45 | # Create current dump dirs |
| 46 | mkdir --mode=770 "$BACKUP_ROOT/mysql" "$BACKUP_ROOT/postgres-9" |
| 47 | |
| 48 | # Perform MYSQL backup |
| 49 | for db in $MYSQL_DBS; do |
| 50 | sudo -H mysqldump --single-transaction "$db" | pigz - > "$BACKUP_ROOT/mysql/$db.gz" |
| 51 | done |
| 52 | |
| 53 | # Perform PGSQL backup |
| 54 | for db in $PGSQL_DBS; do |
| 55 | sudo -H -u postgres pg_dump -Fc "$db" > "$BACKUP_ROOT/postgres-9/$db.pg" |
| 56 | done |