MySql for Windows, MySql Administrator: Scheduled backup not working

Question

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 1

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!

Answer 2

I guess it might be associated with windows "cron" jobs. Anyway, this do the trick for me:

http://realm3.com/articles/how_to_schedule_regular_mysql_backups_in_windows

Of course you have to do everything by hand but is also better. I got this same issue in PostgreSQL because o windows "at" is different from linux "cron". Since than, I started to do my own "cron" jobs.

Also check this: http://kedar.nitty-witty.com/blog/scheduled-backup-mysql-administrator-windows-scheduler-odd

Hope it helps! :)

Answer 3

I'll post what are my findings for other people to enjoy.

I didn't get the MySQL Administrator backup function to work automatically, instead I used the suggestion of @mOrSa

My findings are these: I've made a batch file using this code:

@echo off
set zy=2011
set zm=1
set zd=17
set zz = ((zy*100)+zm)*100+zd
Set hr=%TIME:~0,2%
Set mn=%TIME:~3,2%
echo Running dump...
C:\Progra~1\MySQL\MYSQLS~1.5\bin\mysqldump -uuser -password --port=3307 --result-file="c:\MySqlBackups\backup_%zz%_%hr%%mn%.sql" meibergen_dbo
echo Done!
echo Deleting old files
forfiles /P "C:\MySqlBackups" /S /M *.sql /D -7 /C "cmd /c del @PATH"
echo Done!

This makes an .SQL file within the chosen folder with the name format being "backup_20110825_14:54.sql". It also deletes all files older than 7 day's in that folder. I have Windows Server 2008, so the file "forfiles.exe" is standard installed within the system32 folder. If you don't have that program, you can download it somewhere for free.

After that I've set a scheduled task running this batch file in the Windows Task Scheduler. Make sure you have given the appropriate rights in the schedular. It's executed daily.

Answer 4

The simple trick is to create a stored connection (tested on both Win7 and Win2008 with MySQL Administrator 1.2.17 can download it from here, it's among MySQL GUI tools).

1. Open MySQL Administrator, then go in menu Tools > Options

2. In the pop up windows select "Connection" on the left and hit the button "New Connection".

3. Fill in your connection values: username, password, host... and Save it.

4. Close MySQL Administartor and re-open it, but this time connect by selecting the new stored connection you just created on step (3) from the select list (the select list is the 1st field in the login window)

5. Now you can create a backup and schedule it, and the schedule will work like a charm.

Answer 5

I got Solution , When you create schedule backup that time MySQL Administrator take information your system username and password. That time give proper system username and password

Answer 6

I tried also using version 1.2.17 with win 7. I faced the same problem.MySQL ADMIN is scheduling the backup via the windows scheduler. I realy don't know why but when i scheduled the backup(when i SAVE the new created project) under a user with an ADMIN account it didn't operate. When i scheduled the backup under a standard ACCOUNT it operated fine. to solve this problem i defined a new standard account in WIN 7 . I am logging to win 7 under my primary account and scheduling the backup under the new account . Now it works fine .

Answer 7

I know it's a old problem but I had the same problem in recent days and searching solutions I couldn't find how to fix my problem but I found some information in my event logs and I remembered I had another connection (obviously, event logs show me that) so I made new connection with the same name which event logs show me and voila, problem fixed.