Friday, August 19, 2016

MySQL database back up on Windows (every 2 hrs or specify number of minutes).

I put this a file called 06_backup.cmd----------------------------------
@echo off
REM backup data into C:\Temp\backup.sql
"C:\wamp\bin\mysql\mysql5.6.17\bin\mysqldump.exe" -u root -proot -h localhost -P 3310 --default-character-set=utf8 --single-transaction=TRUE --databases taxcollection > C:\Temp\backup.sql
REM rename C:\Temp\backup.sql as a dated and timed file
ren C:\Temp\backup.sql "backup-%date%-%time:~0,2%H%time:~3,2%M%time:~6,2%S.sql"
------------------------------------------------------------------------------
Database Backup (on Windows).
-----------------------------
1) change 06_backup.cmd file to have appropriate values to reflect the database we want to back up
   that command file can be run from command prompt

2) Set up a schedule task to run every 2 hours (120 minutes) change the below line to appropriate values and run in command prompt
C:\>schtasks /create /tn "MyTaskTaxCollectionBackup" /sc minute /mo 120 /tr "C:\Users\Tin Tran\project-tin-do-duc-tran\project1\06_backup.cmd"
   this is referenced from http://superuser.com/questions/850299/windows-scheduler-that-runs-every-x-minutes-daily-on-the-command-line
  
3) To stop the automated back up task just delete the task using command, you'll be prompted to delete the schedule task or not
C:\>schtasks /delete /tn "MyTaskTaxCollectionBackup"

That's complete for database auto back up every 2 hours.
--------------------------------------------------------

To restore it to any copy just use below command with change the file to appropriate .sql back up file.
command example:
C:> mysql -u root -p -h localhost -P 3310 taxcollection < C:\Temp\backup-2016-08-19-12H12M02S.sql