Automatic Logging of Table Data Changes and Creation of Backups via a Stored Procedure

Permalink: http://bit.ly/1rNFRsO



The stripped down stored procedure shown below will accept any Data Manipulation Language statement as its parameter and automatically log the statement and create table backup copies before the statement is executed. The logging functionality is similar to MySQL's binary log but exclusive to DML statements and is useful for table data recovery operations, such as undoing the last table data change or to revert databases back to a certain point in time. All this is done exclusively using stored routines (procedures and functions).

Its assumed that the databases and tables that will be used are already formed to specific business requirements since DDL statements will not be logged by the stored procedure. Though logging of table data changes can also be achieved using triggers, it is not practical to alter each and every trigger of every table if there are numerous tables to consider. Using a stored procedure allows the functionality to be portable to any existing database since no alteration is needed.

DELIMITER $$
DROP PROCEDURE IF EXISTS `$DML` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `$DML`(
  IN dmlString TEXT)
$dml: BEGIN
   DECLARE clearLogs BOOLEAN DEFAULT TRUE;
   DECLARE backupAllDB BOOLEAN DEFAULT TRUE;
   DECLARE dmlFilter BOOLEAN DEFAULT TRUE;
   DECLARE EXIT HANDLER
     FOR SQLWARNING, SQLEXCEPTION, NOT FOUND
     ROLLBACK;
   SET dmlString = TRIM(dmlString);
   IF (dmlString LIKE ('UNDO%'))
   THEN
     CALL $UNDO(dmlString);
     LEAVE $dml;
   END IF;
   initialize: BEGIN
   DECLARE selectStmtBackupAllDB  TEXT;
     IF (
       IF((
         EXISTS(
           SELECT *
           FROM `information_schema`.`TABLES`
           WHERE (`TABLE_SCHEMA` = '$backup'))
         ),
       1, 0)
     )
     THEN
       LEAVE initialize;
     END IF;
     CREATE DATABASE `$backup`;
     CREATE TABLE `$backup`.`history` (
       `dmlStringId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `user` VARCHAR(255) NOT NULL,
       `dmlString` TEXT NOT NULL,
       `timestamp` TIMESTAMP NOT NULL DEFAULT NOW(),
       PRIMARY KEY (`dmlStringId`)
     )
     ENGINE = InnoDB;
     CREATE TABLE `$backup`.`tableref` (
       `tableRefId` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
       `dbName` VARCHAR(64) NOT NULL,
       `tableName` VARCHAR(64) NOT NULL,
       `tableAlias` VARCHAR(64) NOT NULL,
       `backupOf` INTEGER UNSIGNED,
       PRIMARY KEY (`tableRefId`)
     )
     ENGINE = InnoDB;
     IF (backupAllDB)
     THEN
       SET selectStmtBackupAllDB =
         "SELECT T.`TABLE_SCHEMA`, T.`TABLE_NAME`
           FROM `information_schema`.`TABLES` T
           WHERE (`TABLE_SCHEMA` != 'information_schema')
             AND (`TABLE_SCHEMA` != 'mysql')
             AND (`TABLE_SCHEMA` != 'performance_schema')
             AND (`TABLE_SCHEMA` != '$backup')
             AND (`TABLE_NAME` NOT LIKE 'dynamic_cursor%')";
     ELSE
       SET selectStmtBackupAllDB =
         "SELECT T.`TABLE_SCHEMA`, T.`TABLE_NAME`
           FROM `information_schema`.`TABLES` T
           WHERE (`TABLE_SCHEMA` =  DATABASE())
             AND (`TABLE_NAME` NOT LIKE 'dynamic_cursor%')";
     END IF;
     CALL dynamicCursor(
       selectStmtBackupAllDB,
       'initialize', @NULL, logging
     );
   END initialize;
   IF (dmlFilter)
     AND (dmlString NOT LIKE ('INSERT%'))
     AND (dmlString NOT LIKE ('UPDATE%'))
     AND (dmlString NOT LIKE ('DELETE%'))
     AND (dmlString NOT LIKE ('REPLACE%'))
     AND (dmlString NOT LIKE ('SELECT%'))
     AND (dmlString NOT LIKE ('SHOW%'))
   THEN
     SET dmlString = NULL;
   END IF;
   START TRANSACTION;
     SET @dmlStringStmt = dmlString;
     PREPARE dmlStringStmt FROM @dmlStringStmt;
     EXECUTE dmlStringStmt;
     DEALLOCATE PREPARE dmlStringStmt;
   COMMIT;
   IF (dmlString LIKE ('SELECT%'))
     OR (dmlString LIKE ('SHOW%'))
   THEN
     LEAVE $dml;
   ELSE
     INSERT `$backup`.`history`
     VALUES (DEFAULT, USER(), dmlString, DEFAULT);
   END IF;
END $dml $$
DELIMITER ;

The stored procedure is part of a small project, Safe DML, $DML().