mySQL backup automation


Someone truly said, admins are lazy. And yes it’s fun being lazy admin. If you’re admin, try being lazy. I’m talking of task automation today. Specifically, database backup( mySql database).
The scene is, you’ve got somekind of database( mySql ) that needs to be backed up periodically. Would you just sit till wee hours to do just that??

Well I’d definitely not..from now on.
This is for backing up mySql database. It’s a script that does as mentioned and your next task would be to create a schedule job to run it periodically as you require. However the code is quite raw, so anyone interested can refine it always.

i hope the code is self explained so cutting out excessive story( other than already stated above🙂 ).

set mysql_username="<mysql username>"
set mysql_password="<mysql password>"
set mysql_path="<mysql path>"

# this is applicable if you want to backup database hosted in another server
set mysql_host="<mysql host>"

set myDb="database to backup"
   
# Get timestamp to timestamp backup file
for /f "tokens=1,2,3 delims=/ " %%a in ('DATE /T') do set date=%%c_%%b_%%a
for /f "tokens=1,2 delims=:" %%a in ('TIME /T') do set time=%%a_%%b

set output_path="<backup path>\%date%"
IF NOT EXIST %output_path% (mkdir %output_path%)
%mysql_path%\mysqldump.exe --user %mysql_username% --password=%mysql_password% -h %mysql_host% %myDb% --opt > "%output_path%\%ivscDb%_%date%_%time%.bak"

#if you want to backup all the databases hosted in a particular server you can also use --all-databases #option instead of specific database

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s