Solved

How to create a mySQL table where part of name is timestamp

Posted on 2011-09-12
11
432 Views
Last Modified: 2012-05-12
Hi Team, I just have a simple requirement for a mysql  table that I want to backup regularly from a bash script.

Basically I have a table called "devices".  
1. Every week I need to backup this table to something like "devices_backup_12_sep_2011" (i.e. structure Plus all records but the name should include current date when it was backed up)
2. I need to truncate the existing table "device"  (i.e. empty out records).
3. Repopulate this table with fresh records...load data infile....which I know how to do.

I just need some help with the bash script to run the sql commands that creates the new table with the current date as part of the name of the table.

Thanks very much.
0
Comment
Question by:rleyba828
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
the basic "request" hence is how to build a sql statement like:

create table devices_backup_xxxxxxx select * from devices_backup;

with the xxxxxx part being the date format as you need, right?
I think you should be able to echo such a "string" into a .sql file from command line, and then run that .sql file ...
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
I agree with a3. I try to avoid using dynamic SQL heavily, but that is one instance in SQL Server where I do. If you have this in a stored procedure in MySQL, you could do similar to what I do for a similar situation I have.

So:
1) run dynamic sql to backup the table
2) TRUNCATE TABLE devices
3) Load new data to devices

If you need an example of the dynamic SQL:
http://dev.mysql.com/doc/refman/5.5/en/sql-syntax-prepared-statements.html
0
 
LVL 3

Expert Comment

by:skymo
Comment Utility
Use date as follows : date '+%e_%b_%Y'

TABLE_NAME=devices_backup
TABLE_BKUP_FILE=${BASE_NAME}$(date '+%e_%b_%Y')

## you can switch %e with %d ( a padded day format , e.g; 06 rather then just 6 )

mysqldump -u root -h localhost -pmypassword  ${TABLENAME} | gzip -9 > ${TABLE_BKUP_NAME}.sql.gz

that will back your table out side of the database as a ascii file

if you need to have "history table for each table" that is an other story - if that is what you need i'll be glad to help you with that too.
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
Good one. I didn't see the "from a bash script." part.
0
 

Author Comment

by:rleyba828
Comment Utility
Hi skymo, your suggestion looks very promising. But..... how to leave the table in the same database and not do a dump outside of it? Reason is.... Our application would sometimes need to access these archived tables and they would still need to be inside the mysql database.

Thanks.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 3

Accepted Solution

by:
skymo earned 400 total points
Comment Utility
use mysql cmdlime tool


TABLE_NAME=devices_backup
TABLE_BKUP_FILE=${TABLE_NAME}_$(date '+%e_%b_%Y')
DB_NAME=your_db_name
mysql -u user -ppasswd ${DB_NAME} "create table ${TABLE_BKUP_NAME} like ${TABLE_NAME};insert into {TABLE_BKUP_NAME} select * from ${TABLE_NAME}"
rc=$?
if [ $? -ne 0 ] then
   echo "error in creating backup"
else
   echo "success - you can run your deletion sql script here as well"
fi
0
 
LVL 3

Expert Comment

by:skymo
Comment Utility
forgot the ';' at the end of the sql line and the cmdline option flag -e

mysql -u user -ppasswd ${DB_NAME} -e  "create table ${TABLE_BKUP_NAME} like ${TABLE_NAME};insert into {TABLE_BKUP_NAME} select * from ${TABLE_NAME};"
0
 
LVL 16

Assisted Solution

by:Swadhin Ray
Swadhin Ray earned 100 total points
Comment Utility
I prefer to handle this through SQL instead of Bash script.
 
## ceated the routine under test/ you may want to create it somewhere else
 
use test;
DELIMITER $$
DROP PROCEDURE IF EXISTS `uCreateBackupTable`$$
CREATE PROCEDURE `uCreateBackupTable`()
COMMENT 'This routne is used to backup table'
READS SQL DATA
BEGIN

SET @table = CONCAT('device_backup_',REPLACE(DATE_FORMAT(now(),'%d-%M-%Y'),'-','_'));
SET @stmt = CONCAT('create table ',@table,' select * from utest');
PREPARE stmt1 FROM @stmt;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
END$$
DELIMITER ;
 
## Usage - below call will create a table named , if called twice it would throw table already exists..make sure to drop the prev created table
call uCreateBackupTable();
0
 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
That was my recommendation here: http:#36522463 and you can mitigate the error by using CREATE TABLE IF NOT EXISTS or add some other value for uniqueness.
0
 
LVL 3

Expert Comment

by:skymo
Comment Utility
The guy was asking for bash script that creates a table name with timestamp

"I just need some help with the bash script to run the sql commands that creates the new table with the current date as part of the name of the table."

in addition:
with an external bash script running from cron you can easily switch the backup method and save the backup on external device, with out the need to use more advance db tool such as duplication etc.
if you stick to standard SQL the migration to other platforms is much easier. stored procedure are not helping with that.
if stored procedure are not a must - try to prevent using them, especially with such an easy job as backing up tables.
0
 

Author Comment

by:rleyba828
Comment Utility
Hi Team,

   Thanks for all your suggestions.   I DO prefer to go the BASH script method as i will be running the backup procedure as a cron job.  At any rate, slobaray's method might come in handy for other purposes too.

0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now