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! |
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
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
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.
