inertz.org

15 October 2022 No Comment

Previously I have created bash script to backup MySQL database using bash script Linux. New incident happen in the Windows server which is where the normal backup cannot be used, so I have to plan a second backup for MySQL database using batch file.
This script is useful if you have many databases in the server and automation can give a lot of help. Using scheduled task Windows, you can schedule the task to run every day or every week based on requirement. Of course this script is very simple and can be modified further for example if you want to backup based on date and time, but my script just overwrite existing copy of the dumped database.
There is also a way for the database to be zipped, but I not cover it here.

Below is the batch script.

@echo off
set basedir=D:
set workdir=D:\Backup\Mysql

set mysql="C:\Program Files\MySQL\MySQL Server 5.7\bin\mysql.exe"
set mysqldump="C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqldump.exe"
set mysqluser=dbuser
set mysqlpassword=dbpassword

%mysql% -u%mysqluser% -p%mysqlpassword% -s -N -e "SHOW DATABASES" | for /F "usebackq" %%D in (`findstr /V "information_schema performance_schema"`) do %mysqldump% %%D --single-transaction -u%mysqluser% -p%mysqlpassword% > %workdir%\%%D.sql

Just save the file as mysqlbackup.bat and put in D drive.