#!/bin/bash
#daily database-backup with 1 week retention, basics taken from https://ma.ttias.be/mysql-back-up-take-a-mysqldump-with-each-database-in-its-own-sql-file/
mkdir -p /backup/$(date '+%F'); mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /backup/$(date '+%F')/"$dbname".sql; done && sync && sleep 1 && find /backup/ -mindepth 1 -mtime +8 -delete && sync && sleep 1 && find /backup/ -type d -empty -delete

# cronjob-version (escaped "%" for date):
#0 3 * * * root mkdir -p /backup/$(date '+\%F'); mysql -N -e 'show databases' | while read dbname; do mysqldump --complete-insert --routines --triggers --single-transaction "$dbname" > /backup/$(date '+\%F')/"$dbname".sql; done && sync && sleep 1 && find /backup/ -mindepth 1 -mtime +8 -delete && sync && sleep 1 && find /backup/ -type d -empty -delete