Fresh MySQL backup

Today i complete script that apply whole dump SQL files from backup server (fresh files, that creates every day):

#!/bin/bash
##wr by init v0.4
USER="root"
PASSWORD=""
HOSTNAME="10.1.254.4"
DBS="$(mysql -u$USER -Bse 'show databases' | egrep -v -i "lost\+found|performance_schema|information_schema|test|mysql")"
DATE=`date +%F`
DBBCK=`find /mnt/ -name ""$HOSTNAME"_*_data_"$DATE".sql" -printf "%f\n"|sed "s/_data_$DATE.sql//;s/"$HOSTNAME"_//"`

##Doing JOB for creating perms
scp -q syncdb@$HOSTNAME:/var/db/mysql/mysql/user.* /var/lib/mysql/mysql/
scp -q syncdb@$HOSTNAME:/var/db/mysql/mysql/tables_priv.* /var/lib/mysql/mysql/
scp -q syncdb@$HOSTNAME:/var/db/mysql/mysql/columns_priv.* /var/lib/mysql/mysql/
chown mysql:mysql /var/lib/mysql/mysql/*
systemctl restart mysqld

##Doing JOB for creating and filling backup of DBs

for g in $DBBCK
do
#echo Creating NEW DB $g
mysql -u$USER -se "create database IF NOT EXISTS $g"
done
for db in $DBS
do
t="$(mysql -u$USER $db -Bse 'show tables')"
for t in $t
do
#echo Drop Table \"$t\" from Database \"$db\"
mysql -u $USER $db -se "drop table $t"
done
done
for db in $DBS
do
#echo Filling up NEWER SQL dump $db
mysql -u$USER $db < /mnt/"$HOSTNAME"_"$db"_struct_`date +%F`.sql mysql -u$USER $db < /mnt/"$HOSTNAME"_"$db"_data_`date +%F`.sql done

Leave a Reply