Solved

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

Posted on 2011-09-12
11
437 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]
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 59

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 59

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 59

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 52
How to join on ID, with prefix? 15 58
powershell function location 6 36
T-SQL: New to using transactions 9 22
SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Utilizing an array to gracefully append to a list of EmailAddresses
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…
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…

776 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