Batch file Script for taking MySQL backup of a database
1. Open notepad copy and paste the below code in blue color and save the file as "backup.bat"
Change the path of MYSQL location according to your system where MySQL is installed
cd C:\Program Files\MySQL\MySQL Server 8.0\bin
:: SETTINGS AND PATHS
:: Note: Do not put spaces before the equal signs or variables will fail
:: Name of the database user with rights to all tables
set dbuser=root
:: Password for the database user special charaters are to be escaped with ^
set "dbpass=ykrp^^1971"
:: Error log path - Important in debugging your issues
set errorLogPath="C:\Program Files\MySQL\MySQL Server 8.0\bin\dumperrors.txt"
:: MySQL EXE Path
set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
:: Error log path
:: set backupfldr="C:\Program Files\MySQL\MySQL Server 8.0\bin\"
set backupfldr="D:\TESGROUPA\KBC\"
:: Path to data folder which may differ from install dir
set datafldr="C:\Program Files\MySQL\MySQL Server 8.0\bin"
:: turn on if you are debugging
@echo on
for /f "delims=" %%a in ('wmic OS Get localdatetime ^| find "."') do set DateTime=%%a
set Yr=%DateTime:~0,4%
set Mon=%DateTime:~4,2%
set Day=%DateTime:~6,2%
set Hr=%DateTime:~8,2%
set Min=%DateTime:~10,2%
set Sec=%DateTime:~12,2%
set BackupName= %backupfldr%Test_Backup__%Yr%-%Mon%-%Day%_(%Hr%-%Min%-%Sec%)
:: Note: Do not put spaces before the equal signs or variables will fail
:: Name of the database user with rights to all tables
set dbuser=root
:: Password for the database user special charaters are to be escaped with ^
set "dbpass=ykrp^^1971"
:: Error log path - Important in debugging your issues
set errorLogPath="C:\Program Files\MySQL\MySQL Server 8.0\bin\dumperrors.txt"
:: MySQL EXE Path
set mysqldumpexe="C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe"
:: Error log path
:: set backupfldr="C:\Program Files\MySQL\MySQL Server 8.0\bin\"
set backupfldr="D:\TESGROUPA\KBC\"
:: Path to data folder which may differ from install dir
set datafldr="C:\Program Files\MySQL\MySQL Server 8.0\bin"
:: turn on if you are debugging
@echo on
for /f "delims=" %%a in ('wmic OS Get localdatetime ^| find "."') do set DateTime=%%a
set Yr=%DateTime:~0,4%
set Mon=%DateTime:~4,2%
set Day=%DateTime:~6,2%
set Hr=%DateTime:~8,2%
set Min=%DateTime:~10,2%
set Sec=%DateTime:~12,2%
set BackupName= %backupfldr%Test_Backup__%Yr%-%Mon%-%Day%_(%Hr%-%Min%-%Sec%)
mysqldump --user=%dbuser% --password=%dbpass% --all-databases > %BackupName%.sql
2. And run the batch file from the command prompt in windows. Then it will create the backup file in the file mentioned above i.e backupfldr="D:\"
3. This can be scheduled in Windows to run at a particular time
Tags:
Mysql