windows和linux下MySql定时备份

Windows下MySQL定时备份脚本

一、写定备份脚本,保存为.bat文件

@echo off
set hour=%time:~0,2%
if "%time:~0,1%"==" " set hour=0%time:~1,1%
set now=%Date:~0,4%%Date:~5,2%%Date:~8,2%%hour%%Time:~3,2%%Time:~6,2%
echo %now%
set host=192.168.41.11
set port=3310
set user=root
set pass=123456
set dbname=test
set backupfile=D:\db_autobackup\%dbname%-%now%.sql
"D:\mysql-8.0.11\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile%

%注意：路径中有空格的要加上双引号%
二、在Windows下添加定时执行任务
1.任务计划程序中创建基本任务
2.输入基本任务名称
3.选择触发规则
4.选择触发时间及间隔
5.选择执行的操作
6.选择执行的备份脚本
参考文档：https://blog.csdn.net/m0_38025207/article/details/83302882

linux系统下mysql备份

#!/bin/bash
# 设置mysql的登录用户名和密码(根据实际情况填写)
mysql_user="***"
mysql_password="****"
mysql_host="localhost"
mysql_port="3306"
mysql_charset="utf8mb4"

# 备份文件存放地址(根据实际情况填写)
backup_location=/home/app/***/sql_backup

# 是否删除过期数据
expire_backup_delete="ON"
expire_days=7
backup_time=`date +%Y%m%d%H%M`
backup_dir=$backup_location
welcome_msg="Welcome to use MySQL backup tools!"

# 判断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_backup_test)
mysqldump -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -B mysql_backup_test> $backup_dir/mysql_backup_test-$backup_time.sql
flag=`echo $?`
if [ $flag == "0" ];then
        echo "database mysql_backup_test success backup to $backup_dir/mysql_backup_test-$backup_time.sql.gz"
else
        echo "database mysql_backup_test backup fail!"
fi

# 删除过期数据
if [ "$expire_backup_delete" == "ON" -a  "$backup_location" != "" ];then
        `find $backup_location/ -type f -mtime +$expire_days | xargs rm -rf`
        echo "Expired backup data delete complete!"
fi

编辑定时任务:

crontab -e
# 添加定时任务(每天12:50以及23:50执行备份操作)
50 12,23 * * * cd /home/app/jinxd/;sh mysql_backup_test_backup.sh >> log.txt 2>>log.txt

参考地址