MySQL Auto backup All Databases [SOLVED]

 In IT_Consultant_Singapore

MySQL Auto backup All Databases [SOLVED]

  1. Create a folder called /backup/
  2. Create 2 blank files (bkp_dbs.sh, log_bkpdbs.log)
  3. Just copy the below snippet into ‘bkp_dbs.sh’
  4. Try run with SSH > CLI interface  bash backup/bkp_dbs.sh
  5. See the output if it works well.
  6. Once confirmed , then run this as cron job (maybe every 8 hrs)
  7. The ROOT’s password has to be inside file >> /root/.my.cnf
  8. The ‘.my.cnf’ must be readable by ROOT ONLY (chmod 600)
  9. The server should be installed with TAR

#!/bin/bash
## backup each mysql db into a different file, rather than one big file
## as with –all-databases. This will make restores easier.
## To backup a single database simply add the db name as a parameter (or multiple dbs)

# mkdir -p /var/backups/mysql/databases
# useradd –home-dir /var/backups/mysql –gid backup –no-create-home mysql-backup
## Remember to make the script executable, and unreadable by others
# chown -R mysql-backup:backup /var/backups/mysql
# chmod u=rwx,g=rx,o= /var/backups/mysql/dump.sh

## crontab entry – backup every 8 hours
# sudo crontab -e
# * 8 * * * /backup/bkp_dbs.sh

###########################################################
## Create ‘backup’ mysql user (CLI >>>> mysql -u root -p)
## CREATE USER ‘backup’@’localhost’ IDENTIFIED BY ‘s3cr3t’;
## GRANT EVENT, LOCK TABLES, PROCESS, REFERENCES, SELECT, SHOW DATABASES, SHOW VIEW, TRIGGER ON *.* TO ‘backup’@’localhost’ ;
## Or optionally, run this script as root with password stored on ‘/root/.my.cnf’ file
###########################################################

##Getting mysql cridentials from .my.cnf
#source /root/.my.cnf
USER=$user
PASS=$password

OUTPUTDIR=$(dirname $0)”/databases”
MYSQLDUMP=”/usr/bin/mysqldump”
MYSQL=”/usr/bin/mysql”
log=$(dirname $0)/log_bkpdbs.log
timestamp=$(date +%Y%m%d_%H%M%S)
pfix=”AllDbs”
sfix=”DBs.tar.gz”
bkp_file=$pfix-$timestamp-$sfix #File name to be as backup done
days=+10 #Days old files will be deleted (system modified date)
rm -r $OUTPUTDIR #remove the previous database source folder
mkdir -p $OUTPUTDIR #make the new database source folder
path=$(dirname $0) #set the working directory as current path
log=log_bkpdbs.log #log file name
log=$(dirname $0)/$log #will make new log file if not there
echo ***** – finding older then $days bkps to be delete***>>$log #will delete the compressed bkp files older than $days
find $(dirname $0) -name “*$sfix” -type f -mtime $days -print -delete >>$log #will write the logs into $log file
echo This is All Databases Bkp Script, will run as of your cron job.
echo *** “$timestamp” — Starting Backup Process ***>>$log
##########################################################################################

if [ -z “$1” ]; then
#databases=`$MYSQL –user=$USER –password=$PASS –batch –skip-column-names -e “SHOW DATABASES;” | grep -v ‘mysql\|information_schema’`
databases=`$MYSQL –user=$USER –batch –skip-column-names -e “SHOW DATABASES;” | grep -v ‘mysql\|information_schema’`
for database in $databases; do
$MYSQLDUMP \
–user=$USER \
–force \
–quote-names –dump-date \
–opt –single-transaction \
–events –routines –triggers \
–databases $database \
–result-file=”$OUTPUTDIR/$database.sql”
done
else
for database in ${@}; do
$MYSQLDUMP \
–user=$USER \
–force \
–quote-names –dump-date \
–opt –single-transaction \
–events –routines –triggers \
–databases $database \
–result-file=”$OUTPUTDIR/$database.sql”
done
fi
tar -zcvf $(dirname $0)/$bkp_file $OUTPUTDIR |tee -a $log
#/usr/bin/mail -s “This is email subject” MyEmail@mygmail.com < $(dirname $0)/log_bkpdbs.log

Recent Posts