?
Solved

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

Posted on 2011-09-12
11
Medium Priority
?
449 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +2
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36522284
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 60

Expert Comment

by:Kevin Cross
ID: 36522463
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
ID: 36523759
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 60

Expert Comment

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

Author Comment

by:rleyba828
ID: 36528861
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
 
LVL 3

Accepted Solution

by:
skymo earned 1600 total points
ID: 36530214
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
ID: 36530313
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 400 total points
ID: 36531627
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 60

Expert Comment

by:Kevin Cross
ID: 36531893
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
ID: 36534562
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
ID: 36542371
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

770 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