Mysql – Automating MySQL DB Creation with Specific date

MySQL

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

Best Answer

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

HISTORICAL EXAMPLES

Years ago when I worked at a website firm, I used --skip-extended-insert to load a DB Server that had 2GB RAM. That made a whole world of sense to me because the server I dumped it from had 64GB RAM and the client wanted the data on a smaller machine.

At my current placement of employment, an outside vendor dumped data from SQL Server and produced a mysqldump compatible dumpfile. The problem with that dump file was each table had a single insert with all the rows. One table had 3.5 million. They could not be loaded into in-house VM will less than 8GB RAM configured. Shockingly, the solution to that was to scp the file to may laptop, load it into MySQL 5.5.37 on my laptop, mysqldump from it, send the new mysqldump back to the Linux CentOS VM, and load the mysqldump.

BTW, the 3.5 million row table loaded as a single insert into my Windows 7 laptop in 4.5 hours without changing any MySQL settings. To be honest with you, I did believe that "shot in the dark" would work because I did not reconfigure mysqldump on the laptop to work with the dump. My guess is that it probably loaded because the undo log in ibdata1 just grew on disk and held everything.

You should not have to live to skip-extended-insert if you dump on reload on the same machine.

SUGGESTIONS

While I was typing my answer, I saw you inject the following into the question

According to the a query I ran, the table clocks in at around 1.7GB, but it's MyISAM and apparently that number is unreliable (but it's a big table, anyway).

SUGGESTION #1

If that one MyISAM table has 1.7GB and has the 11 million rows, my suggestion would be for you to increase the size of the bulk insert buffer. The bulk insert buffer is for MyISAM usage only and supports extended inserts. Set bulk_insert_buffer_size to 256M.

Go to my.cnf or my.ini and add this

bulk_insert_buffer_size = 256M

Then, login to MySQL and run

mysql> SET GLOBAL bulk_insert_buffer_size = 1024 * 1024 * 256;

SUGGESTION #2

As for your InnoDB, you need to use a bigger log buffer. Set it to 64M by adding this to my.cnf

innodb_log_buffer_size = 64M

You must restart MySQL to use that new value.

SUGGESTION #3

You may also need bigger InnoDB Logs by increasing innodb_log_file_size. This require manual intervention as follows:

STEP 01: Run this command as root

mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 02: Shutdown mysql

Linux : service mysql stop
Windows (as Administrator) : net stop mysql

STEP 03: Add this option to my.cnf or my.ini

innodb_log_file_size = 1G

STEP 04: In the OS, goto the folder where ib_logfile0 and ib_logfile1 are located and run

Linux

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

Windows

rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

STEP 05: Start mysql (the log files get recreated so start will take an extra 1-2 min)

Linux : service mysql start
Windows (as Administrator) : net start mysql

After making one or more of these suggested changes, try dumping and reloading. It goes go much smoother. Start with just SUGGESTION #1 and try it. Try the other other two to see if it improves more.

SUMMARY

You need these options for your target MySQL DB Server in your my.cnf or my.ini

bulk_insert_buffer_size = 256M
innodb_log_buffer_size = 64M
innodb_log_file_size = 1G
max_allowed_packet = 1G

GIVE IT A TRY !!!

MySQL – Script to Automate Creation of Audit Tables and Triggers

I just wrote this earlier today. It's a select statement working off the information_schema database, which produces the schema for the audit tables and the triggers.

SET GLOBAL group_concat_max_len = 1000;

SET @dbName = "[[[your_db_name_here]]]";

SELECT concat("DROP TABLE IF EXISTS `", @dbName, "`.`", table_data.audit_table, "`;\r",
          "CREATE TABLE `", @dbName, "`.`", table_data.audit_table, "`\r",
          "(\r",
          "  `auditAction` ENUM ('INSERT', 'UPDATE', 'DELETE'),\r",
          "  `auditTimestamp` DATETIME DEFAULT CURRENT_TIMESTAMP,\r",
          "  `auditId` INT(14) AUTO_INCREMENT,",
          column_defs, ",\r"
          "  PRIMARY KEY (`auditId`),\r",
          "  INDEX (`auditTimestamp`)\r",
          ")\r",
          "  ENGINE = InnoDB;\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.insert_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.insert_trigger, "`\r",
          "  AFTER INSERT ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('INSERT',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.update_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.update_trigger, "`\r",
          "  AFTER UPDATE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('UPDATE',", table_data.NEWcolumn_names, ");\r\r",
          "DROP TRIGGER IF EXISTS `", @dbName, "`.`", table_data.delete_trigger, "`;\r",
          "CREATE TRIGGER `", @dbName, "`.`", table_data.delete_trigger, "`\r",
          "  AFTER DELETE ON `", @dbName, "`.`", table_data.db_table, "`\r",
          "  FOR EACH ROW INSERT INTO `", @dbName, "`.`", table_data.audit_table, "`\r",
          "     (`auditAction`,", table_data.column_names, ")\r",
          "  VALUES\r",
          "     ('DELETE',", table_data.OLDcolumn_names, ");\r\r"
)
FROM (
   # This select builds a derived table of table names with ordered and grouped column information in different
   # formats as needed for audit table definitions and trigger definitions.
   SELECT
     table_order_key,
     table_name                                                                      AS db_table,
     concat("audit_", table_name)                                                    AS audit_table,
     concat(table_name, "_inserts")                                                  AS insert_trigger,
     concat(table_name, "_updates")                                                  AS update_trigger,
     concat(table_name, "_deletes")                                                  AS delete_trigger,
     group_concat("\r  `", column_name, "` ", column_type ORDER BY column_order_key) AS column_defs,
     group_concat("`", column_name, "`" ORDER BY column_order_key)                   AS column_names,
     group_concat("`NEW.", column_name, "`" ORDER BY column_order_key)               AS NEWcolumn_names,
     group_concat("`OLD.", column_name, "`" ORDER BY column_order_key)               AS OLDcolumn_names
   FROM
     (
       # This select builds a derived table of table names, column names and column types for
       # non-audit tables of the specified db, along with ordering keys for later order by.
       # The ordering must be done outside this select, as tables (including derived tables)
       # are by definition unordered.
       # We're only ordering so that the generated audit schema maintains a resemblance to the
       # main schema.
       SELECT
         information_schema.tables.table_name        AS table_name,
         information_schema.columns.column_name      AS column_name,
         information_schema.columns.column_type      AS column_type,
         information_schema.tables.create_time       AS table_order_key,
         information_schema.columns.ordinal_position AS column_order_key
       FROM information_schema.tables
         JOIN information_schema.columns
           ON information_schema.tables.table_name = information_schema.columns.table_name
       WHERE information_schema.tables.table_schema = @dbName
             AND information_schema.columns.table_schema = @dbName
             AND information_schema.tables.table_name NOT LIKE "audit\_%"
     ) table_column_ordering_info
   GROUP BY table_name
 ) table_data
ORDER BY table_order_key
INTO OUTFILE "[[[your_output_file]]]"