Dumps MySQL

Script de dump des bases SQL

Prérequis : un utilisateur dédié au backup doit exister (il n'as que des droits de lecture et de lock) :

CREATE USER 'backup'@'localhost' IDENTIFIED BY '*****';
GRANT SELECT ,SHOW DATABASES ,SUPER ,LOCK TABLES ON * . * TO 'backup'@'localhost';

le script :

#!/bin/bash
DATE=$(date +%Y%m%d%H%M)

BIN_MYSQL="/usr/bin/mysql"
BIN_MYDUMP="/usr/bin/mysqldump"

DUMP_PATH="/home/backup/databases/"
DUMP_RETENTION="30"

DB_HOST="localhost"
DB_USER="backup"
DB_PASSWORD="*****"

##begin##
# Delete first oldest backups
find ${DUMP_PATH}/OLD/ -mtime +${DUMP_RETENTION} -delete
# Then move the latest backup to the old directory
mv ${DUMP_PATH}/CURRENT/* ${DUMP_PATH}/OLD/

DB_LIST=$($BIN_MYSQL -h${DB_HOST} -u${DB_USER} -p${DB_PASSWORD} --batch -e "SHOW DATABASES;" | grep -v "Database" | grep -v "information_schema" | grep -v "performance_schema")
for DB in $DB_LIST
do
        ${BIN_MYDUMP} --lock-tables -v -h ${DB_HOST} -u ${DB_USER} --password="${DB_PASSWORD}" ${DB} | /bin/gzip > ${DUMP_PATH}/CURRENT/${DB}_${DATE}.sql.gz
done