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