SHELL: Simple Shell Script to create database backups

dailyBackupMySQL.sh

# Backup mysql databases into seperate files
USER="mysql-username"
PASSWORD="mysql-password"
OUTPUTDIR="/path/to/db/backup/dir"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
NOW=$(date +"%m-%d-%Y")
MSG="Just to let you know that a full backup of db has been made onto the VPS.Kind Regards"
 
# Remove previous backups
# rm "$OUTPUTDIR/*bak" > /dev/null 2>&1
find /var/backups/mysql/. -mtime +7 -exec rm {} \;
 
# get a list of databases
databases=`$MYSQL --user=$USER --password=$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

# also backup the user permissions
$MYSQL --user=$USER --password=$PASSWORD --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | $MYSQL --user=$USER --password=$PASSWORD --skip-column-names -A | sed 's/$/;/g' > "$OUTPUTDIR/mysql_user_grants.sql"

# dump each database in turn
for db in $databases; do
    echo $db
    $MYSQLDUMP --force --opt --user=$USER --password=$PASSWORD --databases $db > "$OUTPUTDIR/$db.bak"
done
 
# emaili reminder of backup
echo $MSG | mail -s "VPS Server Backup - Backup Created on $NOW" [email protected]

dailyBackupMySQLwithDockerCompose.sh

# Backup mysql databases into seperate files
USER="root"
MYSQL_PWD="YOUR_SECRET"
OUTPUTDIR="/var/backups/mysql"
MYSQLDUMP="mysqldump"
MYSQL="mysql"
NOW=$(date +"%m-%d-%Y")
MSG="Just to let you know that a full backup of db has been made onto the VPS $HOSTNAME. Kind Regards"
DOCKER_APP_PATH="/var/www/laradock"
SERVICE="mysql"

# Create the backup dirs if they don't exist
if [ ! -d $OUTPUTDIR ]
  then
  mkdir -p "$OUTPUTDIR"
fi

# Remove previous backups
find /var/backups/mysql/ -name "*.sql.gz" -type f -mtime +2 -delete

# get a list of databases
databases="$(docker-compose -f $DOCKER_APP_PATH/docker-compose.yml exec $SERVICE $MYSQL -u$USER -p$MYSQL_PWD -e 'SHOW DATABASES WHERE
`database` NOT IN ("information_schema", "performance_schema", "mysql", "sys");' | tr -d "\|\+\- " | grep -v Database )"
databases="$(echo "$databases" | sed '/using/d; /mysql/d; /^[[:space:]]*$/d' | tr -d "\r")"
#echo "$databases"

# dump each database in turn
cd $OUTPUTDIR
for db in $databases; do
    docker-compose -f $DOCKER_APP_PATH/docker-compose.yml exec $SERVICE $MYSQLDUMP -u$USER -p$MYSQL_PWD "$db" | gzip > "$OUTPUTDIR"/"$
db".sql.gz
done

# also backup the user permissions
# $MYSQL --user=$USER --password=$MYSQL_PWD --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM
 mysql.user WHERE user<>''" | $MYSQL --user=$USER --password=$MYSQL_PWD --skip-column-names -A | sed 's/$/;/g' > "$OUTPUTDIR/mysql_use
r_grants.sql"

# emaili reminder of backup
# echo $MSG | mail -s "VPS Server Backup $HOSTNAME - Backup Created on $NOW" [email protected]@Gardrobom-APP