在mysql备份中始终出现有时备份成功,有时失败。今天来说一说通过dump脚本进行备份失败的案例吧。
环境:
OS :windows server 2012
DB:Mysql5.7.17
问题:
Mysql 通过mysqldump 命令每天晚上22 点自动备份可以成功;
上午9 点多,单独执行了下自动备份的bat 文件,备份报错,错误信息如下:
mysqldump: [Warning] Using a password on the command line interface can be insec ure.
mysqldump: Couldn't find table: "9-29-04.sql"
问题分析:
备份脚本里,使用%date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2% 获取当前时间,因为一天可能有多个备份,备份格式为” 年- 月- 日- 时- 分- 秒” ,
通过报错” mysqldump: Couldn't find table: "9-29-04.sql" 可知,在使用%time:~0,2% 获取小时出现了问题;
问题重现:
Cmd 窗口执行echo %date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2% 时,发现小时9 前面多了个空格,导致备份失败;
C:\Users\Administrator>echo %date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
2020-12-29- 9-28-17
但是从10 点开始获取小时正常,没有多余的空格:
C:\Users\Administrator>echo %date:~0,4%-%date:~5,2%-%date:~8,2%-%time:~0,2%-%time:~3,2%-%time:~6,2%
2020-12-29- 12-29-54
问题原因:
通过%time:~0,2% 获取小时,如果当前小时为个位数,即0-9 点时,小时前会出现 空格:
C:\Users\Administrator>echo %time:~0,2%
9
C:\Users\Administrator>echo %time:~0,2%
12
解决方案:
将小时前面的空格用0 代替;
C:\Users\Administrator>set h=%time:~0,2%
C:\Users\Administrator>set h=%h: =0%
C:\Users\Administrator>echo %date:~0,4%-%date:~5,2%-%date:~8,2%-%h%-%time:~3,2%-%time:~6,2%
2020-12-29-09-28-55
最后贴上备份文件命令:
@echo off ::删除一周前的备份数据 forfiles /p "x:\mysql_backup" /m backup_*.sql -d -7 /c "cmd /c del /f @path"::设置时间变量 set h=%time:~0,2% set h=%h: =0% set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%h%%time:~3,2%%time:~6,2%"::进入mysql安装目录的bin目录下 cd x:\mysql-5.7.24\bin\ ::执行备份操作 mysqldump --opt --single-transaction=TRUE --user=xxx --password=yyy --host=127.0.0.1 --protocol=tcp --port=9906 --default-character-set=utf8 --single-transaction=TRUE --routines --events "db" >x:\mysql_backup\backup_%Ymd%.sql@echo on