Nombre: script_backup_mysql.sh
Autor: Desconocido
Visto y información de configuración en Ciber Terminal
Leer más...
#!/bin/bash
BACKUPUSER="backup_user"
BACKUPPASS="PASSWORD"
BACKUPPATH="/home/backup/mysql"
MYDATE="$(date +%Y%m%d)"
MYSQLDUMP="/usr/bin/mysqldump"
MYSQL="/usr/bin/mysql"
MYCNF="/etc/mysql/my.cnf"
ZIP="/bin/bzip2"
ZIPOPS="-9f"
IGNOREDDBB="Database|mysql|information_schema"
DBLIST=""
CHMOD="440"
CHOWN="root:admins"
BINLOGINDEX="/home/mysql/log/mysql-bin.index"
list_databases()
{
DBLIST="`echo "show databases ;" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS" | egrep -v "$IGNOREDDBB"`"
return 1
}
dump_databases()
{
for i in $DBLIST
do
$MYSQLDUMP --master-data=2 -u "$BACKUPUSER" --password="$BACKUPPASS" $i > $BACKUPPATH/$i-$MYDATE.sql
$ZIP $ZIPOPS $BACKUPPATH/$i-$MYDATE.sql
done
return 1
}
dump_grants()
{
mysql -p$BACKUPPASS --batch --skip-column-names --execute="SELECT DISTINCT CONCAT('SHOW GRANTS FOR ',user,'@\'',host,'\';') AS query FROM user" mysql | mysql -p$BACKUPPASS --batch --skip-column-names mysql | perl -p -e '$_ =~ s/$/;/; END { print "FLUSH PRIVILEGES;n" }' > $BACKUPPATH/grants-$MYDATE.sql
$ZIP $ZIPOPS $BACKUPPATH/grants-$MYDATE.sql
}
binlog_backup()
{
local let LINES=$(cat $BINLOGINDEX | wc -l)
let LINES--
tar cjfv $BACKUPPATH/MYSQL_BINLOGS-$MYDATE.tar.bz2 $(head -$LINES $BINLOGINDEX | xargs)
}
purge_binlogs()
{
local LOGBIN="$(cat $MYCNF | grep -v ^# | grep log_bin | awk -F= '{print $2}')"
local BINLOGNAME="$(basename $LOGBIN | awk -F. '{print $1}')"
local BINLOGPATH="$(dirname $LOGBIN)"
local let MINAGE="$(cat $MYCNF | grep -v ^# | grep expire | awk -F\= '{print $2}')"
let MINAGE=$((${MINAGE}+2))
local LASTBINLOG="$(find $BINLOGPATH -mtime +$MINAGE -name "*$BINLOGNAME*" | tail -1)"
if [[ "$LASTBINLOG" ]]
then
local LASTBINLOG="$(basename $LASTBINLOG)"
echo "PURGE BINARY LOGS TO "$LASTBINLOG";" | $MYSQL -u "$BACKUPUSER" --password="$BACKUPPASS"
fi
}
list_databases
dump_databases
dump_grants
purge_binlogs
find $BACKUPPATH -type f -exec chmod $CHMOD {} ;
find $BACKUPPATH -type f -exec chown $CHOWN {} ;
exit 0
