Friday, February 19, 2010

MySQL backup script on windows cmd

Just needed to create a windows backup script so thought I would share my findings:

You can download the MySQLBackupScript.zip (1816k) if you're ready to use it or just view the script, otherwise proceed to the explanation.

Dependencies: don't forget you will need mysql.exe and mysqldump.exe which can be obtained from the mysql website and are also provided in the zipped file download link above.

First we use @echo off to hide the commands executed and only display the text after the "echo" command.
Next, I display a message to deter the user from closing the window (since this stops the script) because I need it in my case - it pops up from the scheduled tasks and might be used at the time so it must be clear to the user to not get closed - but you can change this how ever needed, even no output text is possible by avoiding all the echo commands in the script provided.

Now to the meat of the script, the mysql commands;
mysqldump syntax is
mysqldump -uusername -ppassword -hhost.com databasename > backupfile

mysql syntax is
mysql -uusername2 -ppassword2 -hhost2.com databasename2 < backupfile Using the %date:~7,2%-%date:~4,2%-%date:~10,4% command will produce a day-month-year to help distinguish the backups (i.e. backup19-02-2010.sql as formatted in the script). Finally, schedule a task on the machine that will be doing this by going to control panel > scheduled tasks > right click > new > name it and go to properties, find the script location and set the schedule.

This will effectively place a backup on the system the script is in, and upload to the server of host2.com (example shown above), you may of course add as many servers to upload the backups to, simply repeat the line with mysql and change the settings as needed, you can also add the "pause" command if you wish to wait until user interaction to continue the script.

Let me know what you think!
Comment below or email theborisedu@gmail.com

UPDATE: decided to put my project publicly here: https://github.com/borisplotkin/mysql-backup feel free to contribute or download and use!

No comments: