SQL Language Categories, Backup & Recovery Types, and MySQL Backup Methods with Flashback Recovery

SQL Language Categories

1. DDL (Data Definition Language)

CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

2. DML (Data Manipulation Language)

SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE

3. DCL (Data Control Language)

GRANT, REVOKE

4. TCL (Transaction Control Language)

SAVEPOINT, ROLLBACK, SET TRANSACTION (usable only within a transaction and effective for DML)

Backup and Recovery Types

Physical vs. logical backup, online vs. offline backup, local vs. remote backup, snapshot backup, full vs. incremental backup, complete vs. point‑in‑time recovery, table maintenance, backup scheduling, compression and encryption.

Common MySQL Backup Methods

1. MySQL Enterprise Backup

Supports hot, incremental, selective, cloud, encrypted, and compressed backups; optimized for InnoDB but works with all engines; uses block‑level parallel backup for high performance.

2. mysqldump

Community‑provided logical backup tool that exports SQL statements to a text file.

3. Table Copy Backup

Not suitable for InnoDB tables due to unflushed cache data.

4. Backup of Data‑Containing Split Text Files

...

5. Binary Log for Incremental Backup

Records events that modify the database; can be enabled with --log-bin=ON ; important parameters include --log-bin , --sync_binlog , --binlog_encryption , --binlog_transaction_compression , --binlog-format , and --binlog_row_image .

6. Replica‑Based Backup

...

7. Repairing Corrupted Tables

Use REPAIR TABLE or myisamchk -r for MyISAM tables.

8. Filesystem Snapshots (Veritas, LVM, ZFS)

...

Flashback Recovery

Traditional recovery using backups and binlog is resource‑intensive; flashback technology allows rolling back to a previous point without full backups, enabling fast data restoration.

Common Flashback Tools

(1) mysqlbinlog

Pros: built‑in, supports offline and online parsing. Cons: limited by MySQL version, issues with encrypted/compressed logs, poor efficiency.

(2) binlog2sql

Pros: parses binary logs cleanly, supports DML and DDL. Cons: requires Python environment and enabled binary logging.

(3) MyFlash

Pros: DDL rollback via table rename, supports offline parsing. Cons: needs compilation, works only with binary logs.

(4) my2sql

Pros: standalone binary, generates standard SQL, supports flashback, forward‑roll, and transaction analysis. Cons: requires enabled binary logs in ROW format, only DML rollback, no offline parsing.

References

[1] SQL四种语言：DDL,DML,DCL,TCL – https://www.cnblogs.com/henryhappier/archive/2010/07/05/1771295.html

[2] MySQL误操作闪回恢复利器之my2sql – https://cloud.tencent.com/developer/article/1806352

[3] 备份与恢复 – https://dev.mysql.com/doc/refman/8.0/en/backup-and-recovery.html