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().
