Schedule mysql backup on windows

This tutorial describes How to Schedule mysql backup on windows 7

My Scenario:- We are running IT Management System in windows our team members are  very much worried about DB redundancy in case of system fail and the DB Backup task is assigned to me :'(.

After some Best Practices I Completed my task. Let's move on to the MYSQL backup tutorial

Simple mysql dump script:

Syntax:-

mysqldump -u [username] -p[mysql password] -h localhost [databasename]<[Backupfilename].sql

Example:- 

mysqldump -u root -p;.p645HtZNt3,bT*6 -h localhost Management <D:\%date%.sql

The above stuff is backup of my DB manually But i want to automate the process because running the script manually and deleting the old DB files its really painful task.

Here is the MYSQL Backup batch file:-  (Save As.. .bat)

@echo off
echo Starting Backup of Mysql DB on Management server 
For /f "tokens=2-4 delims=/ " %%a in ('date /t') do (set dt=%%c-%%a-%%b)
For /f "tokens=1-4 delims=:." %%a in ('echo %time%') do (set tm=%%a%%b%%c%%d)
set techlanda=%1 %dt% %tm%.sql
echo Backing up to file: %techlanda%
mysqldump  --routines -u root -p;.p645HtZNt3,bT*6 -h localhost Management >z:"Management -%techlanda%.sql"
echo on
echo delete old backup
forfiles /p z: /s /m *.* /d -3 /c "cmd /c del @file : date >= 3days"
echo Backup Complete! 

Note:- Don't forgot to replace your DB information in the following fields -u -p, database name and change drive location(z:\) and I recommended you please don't keep the DB backup on the same server always try to keep your backup files in remote server for critical data.

Remote Backup:-

I created share folder on remote machine and it was mounted on MYSQL database server 

Map Script: 

Go to C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup

[Start->Notepad-(Copy and Paste the below script)]

@echo off
net use * /d /y
net use z: \\192.168.1.3\MySQLDUMP password /user:Administrator

Save As.. .bat file in the following location "C:\ProgramData\Microsoft\Windows\Start Menu\Programs\Startup" It will mount automatically during the system bootup.


Schedule Task:

[Start->Accessories->System Tools->Task Scheduler]

Right side Actions menu:- Create Basic Task...-> Enter Name and Description-> Task Trigger (When do you want the task start?) Daily-> Choose the time frame-> Action(Select a Start a program because we run a batch file)-> Start a Program (browse the batch script)-> Finish to complete.

To Test a Task(Click RUN button to test)

Test a Task Techlanda.com 

Check the share folder whether the DB Backup Schedule is run or not?

Share Folder - Techlanda.com

That's all

I hope this information is useful for you. Please forgive any typos or incomplete sentences.