mysql full backup and incremental backup script

vim MYSQL_FULLBAK.sh //Full backup, once a week

#!/bin/bash
#set -x
################################################ ##########
if [ $# -ne 2 ]; then
echo "Usage: Enter the parameters in sequence: <ROOTPASSWD> <BAKDIR> ";
exit 1
fi

if ! ps -ef | grep -v "grep"|grep -q "mysqld ";then
echo "mysql process not detected"
exit 1
fi
################################################ ##########
# Enter parameters
PASSWD=$1
BAKDIR=$2
################################################ ##########
# PORT & amp; & amp; HOST & amp; & amp; MYSQLHOME & amp; & amp; BAKDIR
PORT=$(netstat -lanp | grep LISTEN | grep "mysqld"|awk -F ":" 'NR==1{print $4}')
MYSQL_HOME=$(ps -ef | grep -v "grep"|grep "mysqld "|tr -s ' ' '\\
'|grep "\-\-basedir\ "|awk -F "=" '{print $NF}')
PATH=$MYSQL_HOME/bin:$PATH

localnetcard=$(route -n|grep UG|head -n 1|awk '{print $NF}')
if [ ! "$localnetcard" == "" ];then
    HOST=$(ip -4 addr show $localnetcard|awk -F '[/ ]' '{for(i=1;i<=NF;i + + ){if($i~"[0 -9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1 ,3}"){print $i;break}}}'|awk 'NR==1{print $0}')
fi

if [ ! -d $BAKDIR ];then
mkdir -p $BAKDIR
chown -Rf mysql:mysql $BAKDIR
fi
################################################ ##########
# Master-slave
LANG=C
MYSQL_CONN="mysql -uroot -p$PASSWD -h$HOST -P$PORT -s -e"
MHOST=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Host:")
if [ "X$MHOST" != "X" ];then
    echo "current is Slave, got Master Info..."
    MPORT=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Port:")
    if [ "X$MPORT" != "X" ]; then
        HOST=$(echo $MHOST | awk '{ print $2 }')
        PORT=$(echo $MPORT | awk '{ print $2 }')
        MYSQL_CONN="mysql -uroot -p$PWD -h$HOST -P$PORT -s -e"
    fi
fi
################################################ ##########
# Backup time
TIME=$(date + "%Y%m%d%H%M%S")
# Backup file name
DUMPFILE=FULL_$TIME.sql
GZDUMPFILE=FULL_$TIME.sql.tgz
################################################ ##########
# Start backup
cd $BAKDIR
LAST=$(date + "%Y year %m month %d day %H:%M:%S")
mysqldump -uroot -p$PASSWD -h$HOST -P$PORT --quick --events --all-databases >$DUMPFILE 2> & amp;1
if [ $? -eq 0 ];then
tar -czf $GZDUMPFILE $DUMPFILE 2> & amp;1
rm -f $DUMPFILE
echo "*************$GZDUMPFILE******************"
echo "******************${LAST} full backup successful******************"
else
rm -f $DUMPFILE
echo "******************${LAST} full backup failed******************"
exit 1
fi
################################################ ##########
# Clean up the backup and keep it for 1 month
COUNT=$(ls -lrt *.tgz | wc -l)
if [ "$COUNT" -gt 4 ];then
FILE=$(ls -lrt *.tgz |awk 'NR==1{print $NF}')
rm -f $FILE
fi

# Delete increment
#if [ -d "/data/backup/increment" ];then
#rm -f /data/backup/increment/*
#fi

vim MYSQL_INCREMENT_BAK.sh //Incremental backup, once a day

#!/bin/bash
#set -x
################################################ ##########
if [ $# -ne 2 ]; then
    echo "Usage: Enter the parameters in sequence: <ROOTPASSWD> <BAKDIR>";
    exit 1
fi

if [ "$UID" != 0 ];then
    echo "must be root"
    exit 1
fi

if ! ps -ef | grep -v "grep"|grep -q "mysqld ";then
    echo "mysql process not detected"
    exit 1
fi
################################################ ##########
# Enter parameters
PASSWD=$1
BAKDIR=$2
################################################ ##########
# PORT & amp; & amp; HOST & amp; & amp; MYSQLHOME & amp; & amp; BAKDIR
PORT=$(netstat -lanp | grep LISTEN | grep "mysqld"|awk -F ":" 'NR==1{print $4}')
MYSQL_HOME=$(ps -ef | grep -v "grep"|grep "mysqld "|tr -s ' ' '\\
'|grep "\-\-basedir\ "|awk -F "=" '{print $NF}')
PATH=$MYSQL_HOME/bin:$PATH

localnetcard=$(route -n|grep UG|head -n 1|awk '{print $NF}')
if [ ! "$localnetcard" == "" ];then
    HOST=$(ip -4 addr show $localnetcard|awk -F '[/ ]' '{for(i=1;i<=NF;i + + ){if($i~"[0 -9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1 ,3}"){print $i;break}}}'|awk 'NR==1{print $0}')
fi

if [ ! -d $BAKDIR ];then
    mkdir -p $BAKDIR
    chown -Rf mysql:mysql $BAKDIR
fi
################################################ ##########
# Master-slave
LANG=C
MYSQL_CONN="mysql -uroot -p$PASSWD -h$HOST -P$PORT -s -e"
MHOST=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Host:")
if [ "X$MHOST" != "X" ];then
    echo "current is Slave, got Master Info..."
    MPORT=$($MYSQL_CONN "show slave status\G" | grep -i "Master_Port:")
    if [ "X$MPORT" != "X" ]; then
        HOST=$(echo $MHOST | awk '{ print $2 }')
        PORT=$(echo $MPORT | awk '{ print $2 }')
        MYSQL_CONN="mysql -uroot -p$PASSWD -h$HOST -P$PORT -s -e"
    fi
fi
################################################ ##########
if $MYSQL_CONN "show variables like 'log_%';" |egrep -i -q "log_bin[[:space:]]ON";then
    #binlog generate directory
    BINDIR=$($MYSQL_CONN "show variables like 'log_%';" |egrep -i "log_bin_basename"|awk '{print $NF}'|awk -F "mysql-bin| " '{print $1}')
    # binlog index
    BINFILE=$($MYSQL_CONN "show variables like 'log_%';" |egrep -i "log_bin_index"|awk '{print $NF}')
    TIME=$(date + "%Y year %m month %d day %H:%M:%S")
    # Generate new mysql-bin.00000* file
    mysqladmin -uroot -p$PASSWD -h$HOST -P$PORT flush-logs
    if [ $? -ne 0 ]; then
        echo "******************${TIME} incremental backup failed******************"
        exit 1
    fi
else
    echo "mysql binlog is not enabled"
    exit 1
fi
################################################ ##########
COUNT=$(wc -l $BINFILE |awk '{print $1}')
i=0
cat $BINFILE|while read line;do
    BINNAME=$(basename $line)
    i=$(expr $i + 1)
    if [ $i -ne $COUNT ];then
        dest=$BAKDIR/$BINNAME
        if [ ! -e $dest ];then
            cp $BINDIR/$BINNAME $BAKDIR
            if [ $? -eq 0 ]; then
                echo "******************${TIME} $BINNAME incremental backup successful******************"
            else
                echo "******************${TIME} $BINNAME incremental backup failed******************"
                exit 1
            fi
        fi

    fi
done

//empowerment

chmod +x /data/backup/*.sh

Scheduled tasks

crontab -e
#Execute full backup script every Sunday at 23:00
0 23 * * 0 sh /data/backup/MYSQL_FULLBAK.sh /back1 123456 >/dev/null 2> & amp;1
#Do incremental backups from Monday to Saturday at 23:00
0 23 * * 1-6 sh /data/backup/MYSQL_INCREMENT_BAK.sh /back2 123456>/dev/null 2> & amp;1

The knowledge points of the article match the official knowledge files, and you can further learn related knowledge. MySQL entry skill treeSQL advanced skillsCTE and recursive query 74095 people are learning the system