08. Technique to recover the committed changes

 

Technique to recover the committed changes

 1. Point-in-Time Recovery (PITR) using Binary Logs

 What is it?

• MySQL Binary Logs record every change (INSERT, UPDATE, DELETE).
• If you have a backup + binlogs, you can replay queries up to a specific point in time.

When it's useful: Recover accidentally deleted or modified data after a COMMIT.

 How it works (simplified):

1. Daily Backup: Take regular full backups (e.g., at midnight).

2. Enable Binary Logging: `--log-bin` must be enabled in MySQL.

3. If an unwanted change happens at 3:00 PM:

   * Restore the midnight backup.

   * Replay binlogs up to, say, 2:59:50 PM — skipping the bad transaction.

It’s a manual, time-consuming process and often used in production recovery scenarios.

 

 2. Triggers or Audit Tables

 What is it?

• You create triggers on your tables (BEFORE UPDATE or BEFORE DELETE).
• These triggers copy the old row to a history/audit table before it’s modified or deleted.

When it's useful: To keep a record of old values for tracking, debugging, or compliance. 

Example:

CREATE TRIGGER before_user_update
BEFORE UPDATE ON users
FOR EACH ROW
INSERT INTO user_history(user_id, name, old_email, changed_at)
VALUES (OLD.id, OLD.name, OLD.email, NOW());

 Easy to set up and useful for business-level tracking of changes.

 

 3. Temporal Tables

 What is it?

• A temporal table stores the full history of row changes over time.
• Not built into MySQL (like SQL Server or PostgreSQL), but you can simulate it.

 

 Manual way in MySQL:

• Add columns like `valid_from`, `valid_to`, `is_active`.
• On every UPDATE, instead of changing the row, archive the current row and insert a new one.

  Example:

-- Instead of UPDATE:

UPDATE employees SET valid_to = NOW(), is_active = 0 WHERE id = 101;

 

-- Insert new version

INSERT INTO employees (id, name, salary, valid_from, is_active)
VALUES (101, 'John', 90000, NOW(), 1);

 

This gives you a complete change history for reporting or auditing.