Linux-Server-Management-Toolkit/docs/MYSQL_RESTORE_QUICK_REFERENCE.md

MySQL Restore Script — Quick Reference Guide

Date: February 27, 2026 Phase: Phase 1 Implementation Complete Commit: bd43a6b

---

What Changed?

The MySQL restore script (/root/server-toolkit/modules/backup/mysql-restore-to-sql.sh) now has 3 critical validation functions that provide users with clear diagnostic information before and during recovery attempts.

---

The 3 New Functions

1. validate_backup_files(DATADIR)

Purpose: Validate all critical files BEFORE starting MySQL instance

What it checks:

• ibdata1 (InnoDB system tablespace) - REQUIRED
• Redo logs - version-specific (ib_logfile0/1 or #innodb_redo)
• mysql/ directory (system tables)
• Target database directory
• File readability and permissions

Called from: step5_create_dump() at line ~2080

User benefit: Know immediately if files are missing before waiting for MySQL startup

Example success:

[✓] ibdata1 found (2.1G)
[✓] ib_logfile0 found (512M)
[✓] mysql/ directory found (45 files)
[✓] Database 'yourloca_wp2' found (156 files)
[✓] Pre-flight validation PASSED

2. discover_and_report_databases(DATADIR, TARGET_DB)

Purpose: List databases found and explain why target might be missing

What it does:

1. Shows all databases in the second MySQL instance
2. Checks if target database exists
3. If missing, tests system tables (mysql.db, mysql.innodb_table_stats)
4. Explains root cause and suggests remediation

Called from: dump_database() at line ~1571

User benefit: Clear explanation of why recovery failed, not just "database not found"

Example success:

[INFO] Found the following databases:
  ▪ information_schema
  ▪ mysql
  ▪ performance_schema
  ✓ yourloca_wp2 (TARGET - FOUND)
[✓] Target database found and accessible

Example failure with diagnosis:

[ERROR] Target database 'yourloca_wp2' NOT FOUND

[INFO] Testing system table accessibility...
[✓] mysql.db table is accessible
[✗] mysql.innodb_table_stats table is NOT ACCESSIBLE or CORRUPTED

This explains why 'yourloca_wp2' is not visible:
  The mysql.innodb_table_stats table stores table metadata
  If corrupted, databases cannot be discovered

Recovery Recommendations:
  1. Try recovery mode 4 or higher (skip checksums/log)
  2. Or restore mysql/ directory from backup separately

3. test_system_tables(DATADIR)

Purpose: Validate critical system tables AFTER instance starts, BEFORE dump

What it tests:

• mysql.db (database metadata) - CRITICAL
• mysql.innodb_table_stats (InnoDB statistics) - IMPORTANT
• information_schema.schemata (database list) - CRITICAL

Called from: step5_create_dump() at line ~2184

User benefit: Detects system table corruption before attempting dump (prevents silent data loss)

Example output:

[INFO] Testing system table accessibility...
[✓] mysql.db table accessible
[✓] mysql.innodb_table_stats table accessible
[✓] information_schema.schemata accessible
[✓] All system table tests passed

If failures detected:

[ERROR] System table tests: 2 passed, 1 FAILED
[ERROR] System tables may be corrupted - recovery may fail

[?] Continue anyway? (y/n):

• User can choose to continue (knowing about issues) or cancel and try different recovery mode

---

Integration in Workflow

Before: Simple Linear Workflow

Check disk space
  ↓
Start MySQL instance
  ↓
Create dump
  ↓
Success/Failure (no diagnostics)

After: Intelligent Validation Workflow

Check disk space
  ↓
🆕 Validate backup files exist & readable
  ↓
Start MySQL instance
  ↓
🆕 Test system tables accessibility
  ↓
🆕 Discover databases & diagnose missing ones
  ↓
Create dump
  ↓
Success/Failure (with clear diagnostics)

---

When Functions are Called

1. validate_backup_files() → Before MySQL starts (fails fast)
2. test_system_tables() → After MySQL starts, before dump attempt
3. discover_and_report_databases() → During dump preparation

Result: Users know what's wrong immediately, not after waiting for failures

---

Documentation Files

For Understanding the Changes

• MYSQL_RESTORE_QUICK_REFERENCE.md ← You are here
  • Quick overview of changes
  • Function signatures
  • When they're called

For Implementation Details

• MYSQL_RESTORE_PHASE1_IMPLEMENTATION.md
  • Detailed function documentation
  • Code examples and output
  • Testing results
  • Next steps

For Complete Analysis

• MYSQL_RESTORE_SCRIPT_IMPROVEMENTS.md
  • All 7 issues analyzed
  • Implementation roadmap (Phases 1-3)
  • Effort estimates
  • Full technical breakdown

For Project Context

• SESSION_SUMMARY_MYSQL_RESTORE.md
  • Session overview
  • Technical decisions
  • Testing approach
  • Future roadmap

---

Next Steps: Phase 2 & 3

Phase 2 (75 minutes, labeled "Important")

• Issue #4: Real-time error log monitoring during recovery
• Issue #7: Replace exit calls with return statements (enables menu/retry)

Phase 3 (120 minutes, labeled "Enhancement")

• Issue #5: Recovery mode escalation suggestions
• Issue #6: Interactive menu loop for multiple recoveries

Total remaining effort: ~3.25 hours (for all phases)

---

Testing the Changes

To test Phase 1 improvements manually:

# Navigate to backup/recovery menu and select "MySQL File-Based Restore"
# The script will now show pre-flight validation before starting instance

# You should see:
# 1. File validation with specific file checks
# 2. Database discovery with list of found databases
# 3. System table tests after instance starts

What to verify:

• ✅ Pre-flight validation runs before instance startup
• ✅ Database discovery shows all found databases
• ✅ If database missing, see diagnostic output
• ✅ System table tests run after instance starts
• ✅ User can choose to continue despite warnings

---

Key Improvements Summary

Aspect	Before	After
File validation	None	Before instance (prevents waste)
Database discovery	Simple check	List all + diagnose missing
System table testing	None	After startup (prevents silent failure)
User feedback	Vague errors	Clear diagnostics + remediation
Root cause explanation	Not provided	Detailed analysis
Actionable guidance	Minimal	Specific recovery mode suggestions

---

File Locations

Modified Script:

/root/server-toolkit/modules/backup/mysql-restore-to-sql.sh
└─ Lines 321-436: validate_backup_files() function
└─ Lines 438-546: discover_and_report_databases() function
└─ Lines 548-602: test_system_tables() function

Documentation (all in /root/server-toolkit/docs/):

MYSQL_RESTORE_QUICK_REFERENCE.md ← You are here
MYSQL_RESTORE_PHASE1_IMPLEMENTATION.md
MYSQL_RESTORE_SCRIPT_IMPROVEMENTS.md
SESSION_SUMMARY_MYSQL_RESTORE.md

---

Git Information

Commit: bd43a6b Message: "MySQL Restore Script Phase 1: Critical Diagnostics & Validation" Files: 2 changed, 739 insertions Status: ✅ Ready for testing

---

Questions?

Refer to the full documentation files:

• How does it work? → MYSQL_RESTORE_PHASE1_IMPLEMENTATION.md
• What was analyzed? → MYSQL_RESTORE_SCRIPT_IMPROVEMENTS.md
• Why these decisions? → SESSION_SUMMARY_MYSQL_RESTORE.md
• Quick overview? → MYSQL_RESTORE_QUICK_REFERENCE.md (this file)

---

Status: ✅ Phase 1 Complete — Ready for Testing and Phase 2 Implementation

Date: February 27, 2026