LinuxQuestions.org

LinuxQuestions.org (/questions/)
-   Linux - Newbie (http://www.linuxquestions.org/questions/linux-newbie-8/)
-   -   Script to dump SQL tables (http://www.linuxquestions.org/questions/linux-newbie-8/script-to-dump-sql-tables-920866/)

Alexrkkl 12-28-2011 08:22 AM

Script to dump SQL tables
 
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 08: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 08: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 08: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 08:48 AM

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

Quote:

Originally Posted by tronayne (Post 4559986)
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 12:10 PM

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 (Post 4559998)
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


All times are GMT -5. The time now is 12:28 PM.