linux备份mysql的脚本 (mysql备份数据库命令脚本)

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

#!/bin/bash

read -p "存放备份的目录(默认路径/opt/mysql_backup) :" back_dir
test -z "$back_dir" && back_dir=/opt/mysql_backup
echo "备份目录为：: $back_dir"

read -p "MySQL的bin目录(默认路径/usr/local/mysql/bin) :" mysql_bin
test -z "$mysql_bin" && mysql_bin=/usr/local/mysql/bin
echo "MySQL的bin目录为: $mysql_bin"

read -p "要备份的数据库IP地址(默认地址 127.0.0.1):" database_host
test -z "$database_host" && database_host=127.0.0.1
echo "数据库地址为: $database_host"

read -p "数据库用户名: " username
echo "数据库用户名: $username"

read -p "数据库密码: " password
echo "数据库密码为: $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.sh

cat << EOF > $back_dir/mysql_back.sh
#!/bin/bash
###backup info
db_host=${database_host}
user=${username}
passwd=${password}
keepdate=\`date +%Y%m%d --date="-7 day"\`
expirdate=\`date +%Y%m%d --date="-15 day"\`

####MySQL info
backdate=\`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 database
test ! -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 info

echo 'backup date: '\`date "+%Y-%m-%d %H:%m:%S"\` >> \${backlog}

if [ ! -f "\${keepdate}.tar.gz" ];then
echo "old backup not found !" >> \${backlog}

else
if [ \`du -k \${keepdate}.tar.gz |awk '{print \$1}'\` -gt 102400 ]
then
rm -rf \${expirdate}.tar.gz
else
echo 'backup file size is less than 100M, please check if the backup is successful!' >> \${backlog}
fi
fi

####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} ];then
echo 'back not compalete or error!'
else
echo 'backup successd!' >> \${backlog}

echo "###############################################"
echo ""
echo 'backup successd !!! '
echo ""
echo "###############################################"

fi

cat \${errlog} >> \${errlog}.old
rm -rf \${errlog}

EOF

${back_dir}/mysql_back.sh

cat << EOF >> /var/spool/cron/root
00 02 * * * ${back_dir}/mysql_back.sh
EOF

systemctl restart crond
exit 0

二、Windows系统备份脚本

rem *****************************Code start****************************
@echo off
set DaysAgo=30
set "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 on
rem *****************************Code End*****************************