Wednesday, February 29, 2012

MySQl workbench, database backup using Windows scheduler

>br>
MySQL workbench does not support  scheduled backups.

Below I used Windows scheduler to backup my MySQL database schs_lms and schs_research_projects.

A) Create a directory for MYSQL backup dumping:  d:\mysql_dump\. 
I install MySQL in:
c:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.37

B) Create a bat file c:\bat_dir\mysql_backup.bat as below,  note: carat character ("^" ; shift-6) as a line extension character (continue line) in DOS batch files.
set DATET=%date:~-4%_%date:~7,2%
"c:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.37\mysqldump" --host="localhost" ^
  --user="root" --password="YOUR_PASSWORD" schs_lms > ^
 "d:\mysql_dump\"backup_%DATET%_schs_lms.sql 
"c:\Program Files (x86)\MySQL\MySQL Workbench CE 5.2.37\mysqldump" --host="localhost" ^
  --user="root" --password="YOUR_PASSWORD" schs_research_projects > ^
    "d:\mysql_dump\"backup_%DATET%_schs_research_projects.sql  
pause

C) Right click mouse and select run as Administrator to test this command.

D) Run in Windows Scheduler, the detail  is as below:
In Windows 2008 Server or other Windows system:
Start->Administrative tools ->Task Scheduler, under action, click a basic task, 
following the instruction step by step. Below is my case: 
1) my task name: scfhs db backup, 
2) Next: backup daily, 
3) next: afternoon 2:29:42 pm, 
4) next: action: start a program
browse the script location: C:\bat_dir\mysql_backup.bat, 
5) select open properties, a window pops up, select Run whether the user is logged on or not.

2 comments: