Solved

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

Posted on 2011-09-12
11
447 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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 400 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 100 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
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…

690 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