2

Here i come again with another challenge . Basically i'm looking forward for the best way to have a windows based script to create a MySQLDB, or multiple MySQLDB, once a week having the following format : YYMMDD_HHMM_DBNAME.

For e.g , every saturday or sunday, the script will be running to create the DB having the previous Thursday date. The goal is then to restore a dump into that new DB.

If anybody have an idea on how this can be done, i will really appreciate.

With thks.

Rgds

0

This script will dynamically create the database prepended with the date format you want

SET @dbname = 'rolando';
SET @today = DATE(NOW());
SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
SET @ut2 = UNIX_TIMESTAMP(@today);
SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
PREPARE s FROM @sql;
EXECUTE s;
DEALLOCATE PREPARE s;

Just set the @dbname in the script.

If you want it as a Stored Procedure, here you go:

DELIMITER $$

DROP PROCEDURE IF EXISTS `MakeRestoreDB` $$
CREATE PROCEDURE `MakeRestoreDB`(GivenDBName VARCHAR(20))
BEGIN

    SET @dbname = GivenDBName ;
    SET @today = DATE(NOW());
    SET @this_thurs = @today - INTERVAL WEEKDAY(@today) DAY - INTERVAL 3 DAY;
    SET @ut1 = UNIX_TIMESTAMP(@this_thurs);
    SET @ut2 = UNIX_TIMESTAMP(@today);
    SET @last_thurs = @this_thurs - INTERVAL IF(@ut1=@ut2,1,0) WEEK;
    SET @newDBNAME = CONCAT(DATE_FORMAT(NOW(),'%y%m%d_%H%i_'),@dbname);
    SET @sql = CONCAT('CREATE DATABASE IF NOT EXISTS ',@newdbname);
    PREPARE s FROM @sql;
    EXECUTE s;
    DEALLOCATE PREPARE s;

END $$

DELIMITER ;

Then, just call MakeRestoreDB('rolando');, and you are good to go.

Give it a Try !!!

Hmmmmm ... some of these statements seem familiar :-)

1
  • Hi Rolando :) Indeed these statements are familiar ;) I'll give a try and get back.. Thanks again.
    – jerprosp
    Dec 12 '13 at 19:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.