将下面的内容复制到bat文件里,然后将5个变量进行修改:
mysqldumpPath 你本地mysql路径
dbName 需要备份的数据库
dbUser 数据库用户名
dbPwd 数据库密码
outpurDir 备份文件输出路径
echo 取日期、时间变量值
set yy=%date:~0,4%
set mm=%date:~5,2%
set dd=%date:~8,2%
if /i %time:~0,2% lss 10 set hh=0%time:~1,1%
if /i %time:~0,2% geq 10 set hh=%time:~0,2%
set mn=%time:~3,2%
set ss=%time:~6,2%
set date=%yy%%mm%%dd%
set time=%hh%%mn%%ss%%这里不要具体时分数据,要的话可以追加time%
set filename=%date%%赋值等号两边不能有空格%
set mysqldumpPath="C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
set dbName=draw
set dbUser=root
set dbPwd=root
set outpurDir=D:/dbbackup/set outputFileName=%outpurDir%%dbName%_%filename%.sqlecho %outputFileName%%mysqldumpPath% -u%dbUser% -p%dbPwd% --opt --default-character-set=utf8 -e --triggers -R --hex-blob --flush-logs -x %dbName% > %outputFileName%
如果MySQL的端口不是默认的3306,上面的可能会失效,可以用下面的脚本
@echo off
echo 设置MySql数据库的连接信息
set host=127.0.0.1
set port=3307
set user=root
set pass=root@2022
echo 设置要备份MySql数据库名称
set dbname=freight_95306_new
echo 获取当天的日期格式,例如:2022-12-13-14-30-40set backup_date=%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
echo 设置备份文件的路径
set backupfile=E:\mysqlbak\freight_95306\sql\%dbname%-%backup_date%.sql
echo 使用mysqldump对指定的MySql进行备份
echo 注意路径中有空格的要加上双引号
"C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump" -h%host% -P%port% -u%user% -p%pass% -c --add-drop-table %dbname% > %backupfile%
echo 删除过期文件,这里是超过30天就删除
forfiles /p =E:\mysqlbak\freight_95306\sql\ /s /m *.sql /d -30 /c "cmd /c del @file /f"
exist