Script to dump SQL tables

Alexrkkl · 12-28-2011, 07:22 AM

I have created a small script that runs at midnight every night that dumps important individual tables off a database as a backup here is the code:

Code:

#!/bin/bash
#dump tables
mysqldump -uroot -pmypassword database table > /file location/file.sql
mysqldump -uroot -pmypassword database table > /file location/file.sql

What I want to know is if there is a way that I can record the time of the MySQLdump execution and completion.

Thanks Alex

tronayne · 12-28-2011, 07:32 AM

Well, yeah... something like

Code:

#!/bin/bash
echo "Starting back up of data base blah, table blew at " `date` > logfile
mysqldump ...
echo "Finished back up of data base blah, table blew at " `date` >> logfile
. 
.
.

Note the first redirect creates or empties and existing log file, the second and subsequent redirects appends to that log file.

Hope this helps some.

zQUEz · 12-28-2011, 07:37 AM

Or, if you want to keep logs together, send it to syslog (default is /var/log/messages for RHEL) with:

Code:

#!/bin/bash
/bin/logger "Starting mysql backup"
#dump tables
mysqldump -uroot -pmypassword database table > /file location/file.sql
mysqldump -uroot -pmypassword database table > /file location/file.sql

/bin/logger "Completing mysql backup"

Cedrik · 12-28-2011, 07:44 AM

You could use time built-in bash command or

Code:

#!/bin/bash

start=$SECONDS

#dump tables
mysqldump -uroot -pmypassword database table > /file location/file.sql
mysqldump -uroot -pmypassword database table > /file location/file.sql

end=$SECONDS

duration=$(($end - $start))
echo "script executed in $duration seconds"

Alexrkkl · 12-28-2011, 07:48 AM

Thanks that was brilliant just what I was looking for thank you.

Quote:

Originally Posted by tronayne

Well, yeah... something like

Code:

#!/bin/bash
echo "Starting back up of data base blah, table blew at " `date` > logfile
mysqldump ...
echo "Finished back up of data base blah, table blew at " `date` >> logfile
. 
.
.

Note the first redirect creates or empties and existing log file, the second and subsequent redirects appends to that log file.

Hope this helps some.

lithos · 12-28-2011, 11:10 AM

Hi,

I would append the time of file creation at the filename, so it's quickly visible when you list a directory with backup files, like:

Quote:

Originally Posted by Cedrik

You could use time built-in bash command or

Code:

#!/bin/bash

start=$SECONDS

#dump tables
mysqldump -uroot -pmypassword database table > /file location/file-$(date +"%F_%H-%M-%S"h).sql
mysqldump -uroot -pmypassword database table > /file location/file-$(date +"%F_%H-%M-%S"h).sql

end=$SECONDS

duration=$(($end - $start))
echo "script executed in $duration seconds"

It will give the filename like:

Code:

file-2011-12-28_16-09-46h.sql