MySql for Windows, MySql Administrator: Scheduled backup not working

Nehbur picture Nehbur · Nov 7, 2011 · Viewed 11.5k times · Source

My question is a bit specific, but I hope someone has experience with the issue.

I'm running a Windows 2008 server with MySql for Windows installed it. It's MySql 5.2 and that package contains "MySql Administrator". There you can make backups of a specific database. When I configure the backups and also say that it needs to be done daily around 3 am in the morning, it doesn't work automatically. It's only when I press "Execute backup now" that I get to save the .SQL file and then the backup itselve works fine. When I ask it to fire automatically nothing is created and not even a log file is being made. I've checked the rights on the selected folder and i've added the user "Everyone" with all rights to rule out any permission issues. No effect.

I've been Googling for day's and I can't find a solution for this. Does anyone know why my automated backup via the MySQL administrator is not working on MS Windows 2008 and why it isn't even creating a log file?

Answer

Serjik picture Serjik · Aug 31, 2013

CREATE A USER WITH MINIMUM PERMISSION

mysql> grant select,lock tables on foo.* to bar@localhost identified by'backup'; 
mysql> flush privileges;

CREATE A BATCH FILE LIKE FOLLOWING AND SCHEDULE IT *This will backup file in specified path with db name + date and time*

@echo off
set mySqlPath=C:\[path to my sql]\mysql5.1.36
set dbUser=bar
set dbPassword=backup
set dbName=MyDB1

set hr=%time:~0,2%
if "%hr:~0,1%" equ " " set hr=0%hr:~1,1%

set file=%dbName%.%date:~-4,4%%date:~-10,2%%date:~-7,2%_%hr%%time:~3,2%%time:~6,2%.sql
set path=C:\[path to folder]\mysql_bak

echo Running dump for database %dbName% ^> ^%path%\%file%
"%mySqlPath%\bin\mysqldump.exe" -u %dbUser% -p%dbPassword% --result-file="%path%\%file%" %dbName%
echo Done!