Automating MySQL Backups

Backing up a MySQL database running on production is crucial to ensure data integrity and availability. In the event of hardware failures, software bugs, human errors, or malicious attacks, a backup provides a reliable way to restore lost or corrupted data, minimizing downtime and operational disruption. Regular backups also support data recovery during unexpected incidents and protecting valuable information. By implementing a consistent backup strategy, we can safeguard their critical data, maintain user trust, and comply with regulatory requirements, ultimately contributing to a robust and resilient system.

A simple backup of a MySQL database can be taken manually by simply exporting the database from the MySQL Workbench, however we would not be exploring that in this article.

A MySQL database is backed up by mysqldump.exe which is located in MySQL\bin\.
mysqldump.exe creates a logical backup by exporting the database structure and data into a SQL script file that can be used to recreate the database. This can be done manually using a gui where you can select which databases and which tables to backup or we can write a script to do that.

Create backupscript.bat and paste the following code in it.

@echo off
cd "path\to\mysql\bin"

for /f "tokens=2 delims= " %%a in ('date /t') do set mydate=%%a
set mydate=%mydate:/=-%
set mydate=%mydate: =%
set mydate=%mydate:~10,4%%mydate:~4,2%%mydate:~7,2%

set backup_path=path/to/backup_folder
set backup_name=<database_name>_%mydate%

mysqldump --defaults-extra-file=path\to\my.cnf --all-databases --routines --events --result-file="%backup_path%\%backup_name%.sql"

if %ERRORLEVEL% neq 0 (
    echo [%date%] Backup failed: error during dump creation >> "%backup_path%\mysql_backup_log.txt"
) else (
    echo [%date%] Backup successful >> "%backup_path%\mysql_backup_log.txt"
)

NOTE: Make sure to replace all the paths and the database names.

Now create my.cnf and add the following to it.

[client]
user=username
password=password

Add your username and password. This is a config file which we will use to fetch details of the user to run the mysqldump.exe command. It is not recommended to enter sensitive information like passwords in the terminal hence this approach is better.

Now we can schedule to run this script using the task scheduler.

Open the tasak scheduler and click on Task Scheduler Library in the left panel. Then in the right panel select Create Basic Task… Enter the basic information and select on run script and select the backupscript.bat. Enter your desired interval for backups and we’re done!

Now your database will be backed up and you will also have a log of all the backups. If ever you accidentally lose your data you would regret not doing this. So if you’re running any projects in production make sure you backup your database. It takes less than 5 minutes and you’d be better safe than sorry.