阮胜昌的技术记录站-LinuxMySQL数据库运维

vim mysql_full_back_schema.sh

#!/bin/bash
DBPATH="/root/backup_file"
MYUSER="root"

MYPASS="123456"
SOCKET=/var/lib/mysql/mysql.sock
#MYCMD="/usr/bin/mysql -u$MYUSER -p$MYPASS -S $SOCKET"
MYCMD="/usr/bin/mysql"
#MYDUMP="/usr/bin/mysqldump -u$MYUSER -p$MYPASS -S $SOCKET"
MYDUMP="/usr/bin/mysqldump"
DATADIR="/var/lib/mysql"
DB_CONFIG="/etc/my.cnf"
DB_NAME="test"
LOG_FILE="$DB_NAME-full-backup.log"
[ ! -d "$DBPATH"  ]&& mkdir $DBPATH
[ -f "$DBPATH/$LOG_FILE" ] || touch $DBPATH/$LOG_FILE


echo $($MYCMD -e "flush logs")
#bin_log_name=$(cat $DB_CONFIG | grep "^log_bin"|awk -F "=" '{print $2}')
bin_log=`tail -n 1 $DATADIR/mysql-bin.index | awk -F "/" '{print $2}'`
echo "flush logs after the bin_log file is $DATADIR/$bin_log">>$DBPATH/$LOG_FILE



SUB_DIR="${DB_NAME}-FULL_BACK_$(date +%F-%H)"
mkdir $DBPATH/$SUB_DIR -p
        echo "$(date +%F_%H-%M-%S)-Start to Backup $DB_NAME ....">>$DBPATH/$LOG_FILE
                $MYDUMP --add-drop-table --set-gtid-purged=off --flush-privileges  --triggers --routines --events --master-data=2 --single-transaction --databases $DB_NAME|gzip>$DBPATH/$SUB_DIR/$DB_NAME-$(date +%F-%H).sql.gz
        echo "$(date +%F_%H-%M-%S)-Backup $DB_NAME success">>$DBPATH/$LOG_FILE
find $DBPATH/* -type d -mtime +90 -exec rm -rf  {} \; &>/dev/null

exit 0

添加计划任务：

crontab -e

00 00 * * 7  /server/shell_scripts/mysql_back_recover/mysql_full_back_schema.sh