MySQL简单备份脚本

mkdir /backup
mkdir /backup/backupset
mkdir /backup/script
# 创建备份目录，存放脚本和备份集

# 这是一个使用mysqldump的简单备份脚本
# 使用该命令mysqldump会锁表
# 如果数据库有较大容量的表时不建议使用该命令备份库
# 建议使用Xtrabackup命令进行备份
vi /backup/script/backup.sh
#!/bin/sh
. ~/.bash_profile
MYUSER=root
MYPASS=vincent
DATABASES="mysql test"
LogFile=/backup/script/log_backup.log
SOCKET=/usr/local/mariadb/run/mysql.sock
MYCMD="/usr/local/mariadb/bin/mysql -u$MYUSER -p$MYPASS -S$SOCKET"
MYDUMP="/usr/local/mariadb/bin/mysqldump -u$MYUSER -p$MYPASS -B --flush-privileges --single-transaction --master-data=2 --triggers --routines --events --hex-blob -S$SOCKET" # --flush-logs
TIME=$(date +%F_%H-%M-%S)
echo -e ''$_{1..75}'\b#'>>$LogFile
echo -e ''$_{1..75}'\b#'>>$LogFile
echo -e "\n$(date +%F' '%T) [Info] START BACKUP DATABASE\n">>$LogFile
for database in ${DATABASES}
do
  echo -e "$(date +%F' '%T) [Info] START BACKUP DATABASE $database">>$LogFile
  $MYDUMP $database|gzip >/backup/backupset/${database}_${TIME}.sql.gz
  zcat /backup/backupset/${database}_${TIME}.sql.gz|head -50|grep 'CHANGE MASTER TO'>>$LogFile
  echo -e "$(date +%F' '%T) [Info] END   BACKUP DATABASE $database\n">>$LogFile
  find /backup/backupset -name "$database*" -mtime +3 -exec rm -f {} \;
done
# 脚本如上

chmod +x /backup/script/backup.sh
# 添加可执行权限

crontab -l
#MariDB backup
0 0 * * * /backup/script/backup.sh
# 添加自动任务