Linux:
#!/bin/bash
#保存路径
mysql_bak_dir="/data/backup"
#日期
current_date=`date +%Y%m%d-%H%M%S`
#保存时长
days=15
db_host="127.0.0.1"
db_port="3306"
db_name="dbname"
db_user="root"
db_pass="admin"
bin_dir="/data/mysql/bin/"
mkdir -p ${mysql_bak_dir}
${bin_dir}/mysqldump -h${db_host} -u${db_user} -p${db_pass} --triggers ${db_name} > ${mysql_bak_dir}/${db_name}-$current_date.sql
#自动删除15天前的数据
#find ${mysql_bak_dir} -name ${db_name}* -type f -mmin +1 -exec rm {} \; > /dev/null 2>&1
find ${mysql_bak_dir} -name ${db_name}* -type f -mtime +${days} -exec rm {} \; > /dev/null 2>&1
#设置定时任务,每天凌晨3点
#crontab -e
#0 3 * * * /data/dump_mysql.sh
#service crond restart
Windows:
@echo off
echo.
echo MySQL数据库备份脚本
echo *****************************
echo.
echo 备份日期:%date%
echo 备份时间:%time%
echo.
echo *****************************
set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
md "C:\xj\mysql\%Ymd%"
"C:\xj\mysql-8.0.11-winx64\bin\mysqldump.exe" -uroot -p123456 mysql --default-character-set=utf8 --opt -Q -R --skip-lock-tables > C:\xj\mysql\%Ymd%\mysql_bak.sql
echo.
forfiles /p C:\xj\mysql\ /s /m *.* /d -7 /c "cmd /c del @file"
echo MySQL数据库备份完成
echo.
echo.