全量备份

首先先创建一个专门备份的用户并且授权。

  1. create user backup identified by 'Back_132';
  3. grant all on sjwl.* to backup@'localhost' identified by "Back_132";

创建全量备份的脚本

#!/bin/bash
#auto backup mysql db
#before you back ,please grant to the user backup
#grant all on sjwl.* to backup@'localhost' identified by "Back_132"
#flush privileges

BAK_DIR=/data1/mysql_back
BAK_FILE=/data1/mysql_back/full_`date +'%Y%m%d'`
MYSQLDB=sjwl
MYSQLUSR=backup
MYSQLPWD=Back_132
MYSQLCMD=/usr/bin/mysqldump

if [ $UID -ne 0 ];then
    echo "You must to be use root"
    exit 1
fi

if [ ! -d $BAK_DIR ];then
    mkdir -p $BAK_DIR && echo -e "\033[32mcreate the backdir success\033[0m"
else
    echo "THIS $BAK_DIR IS EXSITS..."
fi

mysqldump -u$MYSQLUSR -p$MYSQLPWD --skip-add-drop-table $MYSQLDB --ignore-table=sjwl.ddb_task_schedule_info --skip-opt | gzip > $BAK_FILE.$MYSQLDB.sql.gz

if [ $? -eq 0 ];then
    echo -e "\033[32mTHE mysql backup $MYSQLDB successful!\033[0m"
else
    echo -e "\033[32mTHE mysql backup $MYSQLDB faild!\033[0m"
fi

# 删除超过日期的备份
find $BAK_DIR -mtime +15 |xargs rm -rf

执行脚本

执行脚本
[root@maomao home]# bash mysql.sh 
create the backdir success
mysqldump: [Warning] Using a password on the command line interface can be insecure.
THE mysql backup school successful!

备份成功
[root@maomao back]# ls
full_20210413.school.sql

定时启动：编辑

crontab -e

0 0 * * * /root/mysql_backup.sh > /data1/mysql_back/run.log 2>&1