Truemag

  • Categories
    • Tips And Tricks
    • Internet
    • PHP
    • Javascript
    • CSharp
    • SQL Server
    • Linux
  • Lastest Videos
  • Our Demos
  • About
  • Contact
  • Home
  • Write With Us
  • Job Request
Home MySQL Schedule Backup And Zip MySQL Database In Windows

Schedule Backup And Zip MySQL Database In Windows

Creating a job to schedule backup databases automatically is a very important task for each database administrator. Database is always irreplaceable and is the heart of all online business therefore this process must be the first thing in the process of database administrating to ensure it’s super safe.

In this tutorial, I’ll share with you how to write a .BAT script to backup your MySQL database, use 7-zip to compress the backup file. After finish this .BAT script, you can use the Task Scheduler in System Tools to regularly backup your MySQL database automatically.

.BAT script to backup and compress a MySQL database

Below is my .BAT script content which will do:

  • Init with current date and time of the backup file
  • Export a particular database to a .SQL file
  • Compress the .SQL file to a .ZIP file by using 7-zip
  • Finally, delete the .SQL file to save hard drive space
@echo off
 
CLS 
 
SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2%
 
echo %backuptime%
 
echo Running dump ...
 
set 7zip_path=
 
"C:\xampp\mysql\bin\mysqldump.exe" --host="localhost" --port="3306" --user="hoan" --password="123456" -Q --result-file="C:\xampp\mysql\data\bk_%backuptime%.sql" rapiddev
 
echo Zipping ...
 
"C:\Program Files\7-Zip\7z.exe" a -tzip "C:\xampp\mysql\data\bk_%backuptime%.zip" "C:\xampp\mysql\data\bk_%backuptime%.sql"
 
echo Deleting the SQL file ...
 
del "C:\xampp\mysql\data\bk_%backuptime%.sql"
 
echo Done!

@echo off CLS SET backuptime=%DATE:~10,4%-%DATE:~7,2%-%DATE:~4,2%-%TIME:~0,2%-%TIME:~3,2% echo %backuptime% echo Running dump ... set 7zip_path= "C:\xampp\mysql\bin\mysqldump.exe" --host="localhost" --port="3306" --user="hoan" --password="123456" -Q --result-file="C:\xampp\mysql\data\bk_%backuptime%.sql" rapiddev echo Zipping ... "C:\Program Files\7-Zip\7z.exe" a -tzip "C:\xampp\mysql\data\bk_%backuptime%.zip" "C:\xampp\mysql\data\bk_%backuptime%.sql" echo Deleting the SQL file ... del "C:\xampp\mysql\data\bk_%backuptime%.sql" echo Done!

The .BAT script above assumes that you want to backup the database named rapiddev and you’re required to:

  • Update the mysqldump.exe file path
  • Download 7-zip and update your 7-zip installation path
  • Update your MySQL login detail include host, port, username and password as well as the database name
  • Specify where you want to save the zipped backup file
Run Backup Bat Script

Run Backup Bat Script

Creating schedule to run the .BAT script

After finish updating some configurations relate to MySQL credentials, executable path and testing carefully, next you need to create a job/task which is scheduled to run the .BAT script to backup your database automatically. On Windows, you should to use Task Scheduler tool by going to Start menu, point to Accessories and point to System Tools.

Task-Schduler-Backup_MySQL-Database

Task-Schduler-Backup_MySQL-Database

Congratulation! That’s all you need to for your MySQL database protection. You’re free to download the .BAT script above to run on your server. From my recommend, you should setup the job runs daily in the time of lowest traffic to avoid impact your website performance.

Bonus: there is a tutorial focus on how to create a shell script to backup MySQL database on Linux for your reference.

May 10, 2011Hoan Huynh
ASP.Net C# Download Or Save Image File From URLValidate Email Address Format Using PHP Regular Expression preg_match
You Might Also Like:
  • MySql backup database with gzip compression
  • Shell script backup all Mysql Databases in Linux
  • Mysql restore database from dump file with GZIP compression
  • MySQL dump table
  • PHP Store Session In MySQL Database
  • How To Check And Start An Application If It Is Not Running In Windows
  • PHP Load Facebook Albums And Save To MySQL Database
  • Where MySQL Data Dir And Innodb Data Home Dir Location
  • Backup iPhone 4s In iTunes
  • MySQL Incorrect Data Type Value In Column
Hoan Huynh

Hoan Huynh is the founder and head of 4rapiddev.com. Reach him at [email protected]

11 years ago MySQL7-zip, backup database, Database, How To, mysqldump, Task Scheduler2,232
0
GooglePlus
0
Facebook
0
Twitter
0
Digg
0
Delicious
0
Stumbleupon
0
Linkedin
0
Pinterest
Most Viewed
PHP Download Image Or File From URL
24,537 views
Notepad Plus Plus Compare Plugin
How To Install Compare Text Plugin In Notepad Plus Plus
21,878 views
Microsoft SQL Server 2008 Attach Remove Log
Delete, Shrink, Eliminate Transaction Log .LDF File
17,734 views
JQuery Allow only numeric characters or only alphabet characters in textbox
15,058 views
C# Read Json From URL And Parse/Deserialize Json
11,786 views
4 Rapid Development is a central page that is targeted at newbie and professional programmers, database administrators, system admin, web masters and bloggers.
Recent Posts
  • Things to Learn about Installingderm Loan Type S
  • Online Photo Editor – Free Photoediting Software
  • A Guide to Finding the Best Paper Sellers
  • Photoediting in Home Isn’t Hard to Do!

  • Free Photo Editor Online
Categories
  • CSharp (45)
  • Facebook Graph API (19)
  • Google API (7)
  • Internet (87)
  • iPhone XCode (8)
  • Javascript (35)
  • Linux (27)
  • MySQL (16)
  • PHP (84)
  • Problem Issue Error (29)
  • Resources (32)
  • SQL Server (25)
  • Timeline (5)
  • Tips And Tricks (141)
  • Uncategorized (647)
Recommended
  • Custom Software Development Company
  • Online Useful Tools
  • Premium Themes
  • VPS
2014 © 4 Rapid Development