LinuxQuestions.org
Welcome to the most active Linux Forum on the web.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 12-28-2011, 07:22 AM   #1
Alexrkkl
LQ Newbie
 
Registered: Jun 2011
Location: Birmingham, England
Distribution: RedHat Enterprise 5.6
Posts: 27

Rep: Reputation: Disabled
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
 
Old 12-28-2011, 07:32 AM   #2
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,075

Rep: Reputation: 777Reputation: 777Reputation: 777Reputation: 777Reputation: 777Reputation: 777Reputation: 777
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.
 
1 members found this post helpful.
Old 12-28-2011, 07:37 AM   #3
zQUEz
Member
 
Registered: Jun 2007
Distribution: Fedora, RHEL, Centos
Posts: 294

Rep: Reputation: 53
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"

Last edited by zQUEz; 12-28-2011 at 07:41 AM.
 
1 members found this post helpful.
Old 12-28-2011, 07:44 AM   #4
Cedrik
Senior Member
 
Registered: Jul 2004
Distribution: Slackware
Posts: 2,140

Rep: Reputation: 242Reputation: 242Reputation: 242
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"
 
1 members found this post helpful.
Old 12-28-2011, 07:48 AM   #5
Alexrkkl
LQ Newbie
 
Registered: Jun 2011
Location: Birmingham, England
Distribution: RedHat Enterprise 5.6
Posts: 27

Original Poster
Rep: Reputation: Disabled
Thanks that was brilliant just what I was looking for thank you.

Quote:
Originally Posted by tronayne View Post
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.
 
Old 12-28-2011, 11:10 AM   #6
lithos
Senior Member
 
Registered: Jan 2010
Location: SI : 45.9531, 15.4894
Distribution: CentOS, OpenNA/Trustix, testing desktop openSuse 12.1 /Cinnamon/KDE4.8
Posts: 1,144

Rep: Reputation: 217Reputation: 217Reputation: 217
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 View Post
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

Last edited by lithos; 12-28-2011 at 11:12 AM.
 
1 members found this post helpful.
  


Reply

Tags
dump, mysql, script


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to compare records in two tables in seperate My Sql database using perl script chandanperl Programming 1 08-22-2008 09:33 AM
mysqldump: dump all tables except.... Niceman2005 Linux - Software 2 06-17-2008 09:37 PM
In Memory SQL type tables koodoo Programming 2 08-05-2006 03:55 AM
SQL: old sql dump not loading into new db BrianK Linux - Software 1 07-25-2006 11:55 PM
How to compare records in two tables in seperate My Sql database using shell script sumitarun Programming 5 04-14-2005 09:45 AM


All times are GMT -5. The time now is 05:13 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration