一、Linux系统根据下面配置脚本生成数据库备份脚本

#!/bin/bashread -p "存放备份的目录(默认路径/opt/mysql_backup) :" back_dirtest -z "$back_dir" && back_dir=/opt/mysql_backupecho "备份目录为：: $back_dir"read -p "MySQL的bin目录(默认路径/usr/local/mysql/bin) :" mysql_bintest -z "$mysql_bin" && mysql_bin=/usr/local/mysql/binecho "MySQL的bin目录为: $mysql_bin"read -p "要备份的数据库IP地址(默认地址 127.0.0.1):" database_hosttest -z "$database_host" && database_host=127.0.0.1echo "数据库地址为: $database_host"read -p "数据库用户名: " usernameecho "数据库用户名: $username"read -p "数据库密码: " passwordecho "数据库密码为: $password"test ! -d "${back_dir}" && mkdir -p "${back_dir}"test ! -d "${back_dir}/mysql_back.sh" && touch "${back_dir}/mysql_back.sh"chmod +x ${back_dir}/mysql_back.shcat << EOF > $back_dir/mysql_back.sh#!/bin/bash###backup infodb_host=${database_host}user=${username}passwd=${password}keepdate=\`date +%Y%m%d --date="-7 day"\`expirdate=\`date +%Y%m%d --date="-15 day"\`####MySQL infobackdate=\`date +%Y%m%d\`datadir="${back_dir}"backdir="\${datadir}/\${backdate}"mysqldir="${mysql_bin}/mysql"dumpdir="$mysql_bin/mysqldump"errlog="\${datadir}/mysqldump.log"backlog="\${datadir}/back_info.log"####check backup directory && backup all databasetest ! -d "\${backdir}" && mkdir  "\${backdir}"for db_list in \`\${mysqldir} -u\${user} -p\${passwd} -h\${db_host} -e 'show databases'|egrep -v "Database|information_schema|performance_schema|mysql|sys"\`     do     \${dumpdir} -h\${db_host} -u\${user} -p\${passwd} --default-character-set=utf8 --single-transaction --master-data=2 --set-gtid-purged=OFF -f -R \${db_list} --log-error=\${errlog} > \${backdir}/\${db_list}.sql done####handle backupfile cd \${datadir}tar zcvf \${backdate}.tar.gz \${backdate}rm -rf \${backdir}####backup infoecho 'backup date: '\`date "+%Y-%m-%d %H:%m:%S"\` >> \${backlog}if [ ! -f "\${keepdate}.tar.gz" ];thenecho "old backup not found !" >> \${backlog}elseif [ \`du -k \${keepdate}.tar.gz |awk '{print \$1}'\` -gt 102400 ]thenrm -rf \${expirdate}.tar.gzelseecho 'backup file size is less than 100M, please check if the backup is successful!' >> \${backlog}fifi####send mail#[ -s \${errlog} ] && /bin/mail -s "DB Backup Error on MySQL Server" -a \${errlog} test1@mail.com,test2@mail.com < \${backlog}if [ -s \${errlog} ];thenecho 'back not compalete or error!'elseecho 'backup successd!' >> \${backlog}echo "###############################################"echo ""echo 'backup successd !!! 'echo ""echo "###############################################"ficat \${errlog} >> \${errlog}.oldrm -rf \${errlog}EOF${back_dir}/mysql_back.shcat << EOF >> /var/spool/cron/root00 02 * * * ${back_dir}/mysql_back.shEOFsystemctl restart crondexit 0

二、Windows系统备份脚本

rem *****************************Code start****************************@echo offset DaysAgo=30set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%""D:\Program Files\MySQL\MySQL Server 5.6\bin"\mysqldump -uroot -p"数据库密码" -E -R --single-transaction数据库 > "D:\mysql_backup\backup_%Ymd%.sql"删除过期备份文件C:\Windows\System32\forfiles /p "D:\mysql_backup" /s /m *.sql /d -5 /c "cmd /c del @path" @echo onrem *****************************Code End*****************************