CODEWITHSUNDEEP
mysqldatabasebackup
Coderwannabe
Coderwannabe

Reputation: 407

MySQL Database backup automatically on a windows server

Is there a way to back up MySQL database automatically at certain times of the day for designated servers or send an email with an attachment.. Which ever do you think is the best and safest way to achieve this?

Upvotes: 37

Views: 85965

Answers (5)

MShNajar
MShNajar

Reputation: 76

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
)

Upvotes: 5

Tarun Gupta
Tarun Gupta

Reputation: 6403

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. :)

Upvotes: 31

Mitoxys
Mitoxys

Reputation: 307

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)

Upvotes: 4

Jess Stone
Jess Stone

Reputation: 677

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]

Upvotes: 7

martin
martin

Reputation: 2638

I would use Windows Task Scehduler/cron (depending on your system) and mysqldump. Scroll down in the link, it includes some insights how to achieve what you want.

Upvotes: 25

Related Questions