MySQL&MariaDB数据库备份脚本
MySQL & MariaDB .bat
rem ******MySQL backup start********
@echo off
rem //设置30日之前删除
forfiles /p "D:\MySQL\mysql_backup" /m backup_*.sql -d -30 /c "cmd /c del /f @path"
rem //获取当前时间，包括年月日时分秒
set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"
rem //备份过程
"D:\mysql-5.6.37-win64\bin\mysqldump" --opt --single-transaction=TRUE --user=root --password=888 --host=127.0.0.1 --protocol=tcp --port=3306 --default-character-set=utf8 --single-transaction=TRUE --routines --events "ivms8100v3" > "D:\MySQL\mysql_backup\backup_%Ymd%.sql"
@echo on
rem ******MySQL backup end********
backup.sh
vi /opt/mysqlBackup.sh
#!/bin/bash
以下配置信息请自己修改
mysql_user="USER" #MySQL备份用户
mysql_password="PASSWORD" #MySQL备份用户的密码
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8" #MySQL编码
backup_db_arr=("db1" "db2") #要备份的数据库名称，多个用空格分开隔开 如("db1" "db2" "db3")
backup_location=/opt/mysql #备份数据存放位置，末尾请不要带"/",此项可以保持默认，程序会自动创建文件夹
expire_backup_delete="ON" #是否开启过期备份删除 ON为开启 OFF为关闭
expire_days=3 #过期时间天数 默认为三天，此项只有在expire_backup_delete开启时有效
本行开始以下不需要修改
backup_time=date +%Y%m%d%H%M #定义备份详细时间
backup_Ymd=date +%Y-%m-%d #定义备份目录中的年月日时间
backup_3ago=date -d '3 days ago' +%Y-%m-%d #3天之前的日期
backup_dir=$backup_location/$backup_Ymd #备份文件夹全路径
welcome_msg="Welcome to use MySQL backup tools!" #欢迎语
判断MYSQL是否启动,mysql没有启动则备份退出
mysql_ps=ps -ef |grep mysql |wc -l
mysql_listen=netstat -an |grep LISTEN |grep $mysql_port|wc -l
if [ [$mysql_ps == 0] -o [$mysql_listen == 0] ]; then
echo "ERROR:MySQL is not running! backup stop!"
exit
else
echo $welcome_msg
fi
连接到mysql数据库，无法连接则备份退出
mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password < $backup_dir/$dbname-$backup_time.sql.gz
flag= $?
if [ $flag == "0" ];then
echo "database $dbname success backup to $backup_dir/$dbname-$backup_time.sql.gz"
else
echo "database $dbname backup fail!"
fi
done
else
echo "ERROR:No database to backup! backup stop"
exit
fi
如果开启了删除过期备份，则进行删除操作
if [ "$expire_backup_delete" == "ON" -a "$backup_location" != "" ];then
# $backup_location/ -type d -o -type f -ctime +$expire_days -exec rm -rf {} \;
$backup_location/ -type d -mtime +$expire_days | xargs rm -rf`
echo "Expired backup data delete complete!"
fi
echo "All database backup success! Thank you!"
exit
fi
- 修改shell脚本属性，赋予执行权限
chmod 600 /opt/mysqlBackup.sh
chmod +x /opt/mysqlBackup.sh - 定时执行脚本
vi /etc/crontab - 添加
00 03 * * * /root/mysqlBackup.sh
分 时
vi /var/spool/mail/root可查看脚本执行日志 - MySQL恢复
mysql -u username -p databse < backup.sql
用户名 数据库名 备份sql
Original: https://www.cnblogs.com/iaoexl/p/11963960.html
Author: iaoexl
Title: MySQL&MariaDB数据库备份脚本
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/590861/
转载文章受原作者版权保护。转载请注明原作者出处！