MySQL Database backup automatically on a windows server

Best way to do this would be

mysqldump.exe --user=YourUserName --password=YourPassword --host=localhost --port=3306 --result-file="Path\dump.sql" --databases "DatabaseName1" "Database2"


mysqldump.exe --user=root --password=root  --host=localhost --port=3306 --result-file="c:\www\db\backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql" --default-character-set=utf8 --single-transaction=TRUE --databases "dbtest1" "dbtest2"

The pattern backup.%date:~10,4%%date:~7,2%%date:~4,2%.sql will create a unique name (backup20131010.sql) each time it will run

Now you just need to call this command in your task scheduler. That's it. :)

You can add one of these commands to Windows task scheduler:

mysqldump –-user [username] –-password=[password] [database name] > [dump file]

or in a compact way:

mysqldump –u[username] –p[password] [database name] > [dump file]

or:

mysqldump -u[user] -p[password] --result-file="c:\<path>\backup.%DATE:~0,3%.sql" [database]

I did the work, similar to what other people explained through... but with little difference and extra work:

1) I made a batch file
2) Ran that batch file through windows scheduler
3) Made appropriate schedule for that task
4) Inside the batch file these steps are executed:

• 4-1) Prepared a file name based on current date
• 4-2) Got a backup by mysqldump.exe in the corresponding directory & file name
• 4-3) Made a compress file through 7-Zip app(install it) & then delete the uncompressed backup
• 4-4) Put a copy on our network File-Server

Here is a script(.bat) sample:

@echo off
set current=%date:~10,4%%date:~4,2%%date:~7,2%
set filename="E:\MySQL Backups\DBName-%current%.sql"
set filename2="E:\MySQL Backups\DBName-%current%.zip"
echo %filename%
cd "E:\MySQL Backups"
C:\"Program Files"\MySQL\"MySQL Server 5.5"\bin\mysqldump.exe db_name --user=root --password=rootpass --host="127.0.0.1" --port=instancePort --result-file=%filename% --default-character-set=utf8 --single-transaction=TRUE
echo backup-finished

if exist %filename% (
    "C:\Program Files\7-Zip\7z.exe" a %filename2% %filename%
    echo zip-finished
    del %filename%
)
if exist %filename2% (
    copy %filename2% "\\192.168.x.x\MySQL Backups"
    echo copy-finished
)

databaseW.2016,06,29-22,31,48-15.sql

@echo off
rem Backup Database (Daily,via Task Scheduler)
rem databaseW
set filename="c:\xampp\dbk\databaseW.%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%.sql"
c:\xampp\mysql\bin\mysqldump.exe --user=root --password=dell@root --host=localhost --port=3306 --result-file=%filename% --default-character-set=utf8 --single-transaction=TRUE --databases "databaseW"

To create file whose name is based on the date and time, use %date% and %time%. Note that the 2 variables are based on locale and cmd shell version

• open the cmd windows
• input echo %time% and echo %date% mine is 22:11:16.80,06/29/2016 Wed
• substr the variable through %variable:~startpos,length% I want the time delimited by comma, so the cmd goes echo %time:~0,2%,%time:~3,2%,%time:~6,2%,%time:~9,2%
• to get a filename like databaseW.2016,06,29-22,31,48-15.sql use set filename="databaseW.%date:~6,4%,%date:~0,2%,%date:~3,2%-%time:~0,2%,%time:~3,2%,%time:~6,2%-%time:~9,2%.sql"
• check the date and time in advance
• use the --result-file option instead of >; According to the Mysql Manuel, the charset of file saved using ">" is UTF-16, while the --result-file follows the --default-character-set
• save to file BackpDay-databaseW.cmd
• add it to a new task Action and set a trigger (Windows Task Scheduler)