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().
via Planet MySQL
Automatic Logging of Table Data Changes and Creation of Backups via a Stored Procedure