Automating MySQL DB Creation with Specific date

Question

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

score 0 · Accepted Answer · edited Apr 13 '17 at 12:42

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 :-)

Automating MySQL DB Creation with Specific date

1 Answers1