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

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.
rleyba828Asked:
Who is Participating?
 
skymoConnect With a Mentor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
skymoCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
Good one. I didn't see the "from a bash script." part.
0
 
rleyba828Author Commented:
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
 
skymoCommented:
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
 
Swadhin RayConnect With a Mentor Senior Technical Engineer Commented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
skymoCommented:
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
 
rleyba828Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.