# Database Backup & Restore — Sylhet Model Madrasah ERP

> সিলেট মডেল মাদরাসা — ব্যাকআপ ও রিস্টোর নির্দেশিকা
> Last verified: 2026-10-27 (Phase 12) — real dump, checksum and disposable-database restore drill completed.

## ১. সারসংক্ষেপ (বাংলা)

- ব্যাকআপ শুধুমাত্র **প্রাইভেট ডিস্ক** (`storage/app/private/backups`) এ লেখা হয়; কখনো `public/` এ নয়।
- প্রতিটি ব্যাকআপ gzip করা হয় এবং **SHA-256 চেকসাম** ডাটাবেসে সংরক্ষিত থাকে।
- ব্রাউজার থেকে কেবল তৈরি, যাচাই, ডাউনলোড ও মুছে ফেলা যায়। **রিস্টোর কেবল সার্ভার কনসোলে**, কোনো কমান্ড-এক্সিকিউশন UI নেই।
- রিস্টোরের আগে ফাইল যাচাই হয়, তারপর **আলাদা ডাটাবেসে** রিস্টোর হয়; লাইভ ডাটাবেসের নাম সবসময় প্রত্যাখ্যাত।
- `backup:test-restore` একটি অস্থায়ী ডাটাবেস তৈরি করে, রিস্টোর করে, টেবিল ও সারি-সংখ্যা মিলিয়ে দেখে, তারপর সেই ডাটাবেস **DROP** করে।
- ডাটাবেস পাসওয়ার্ড কখনো কমান্ড-লাইনে যায় না; কেবল ০৬০০ অনুমতির অস্থায়ী `[client]` ফাইলে যায় এবং কাজ শেষে মুছে ফেলা হয়।
- এই স্তরটি ডিস্ক-লেভেল এনক্রিপশন করে না; সার্ভারের ডিস্ক এনক্রিপশন (BitLocker/LUKS) এবং প্রাইভেট ফোল্ডারের ACL ব্যবহার করুন।

---

## 2. Storage and configuration

| Setting | Default | Purpose |
|---|---|---|
| `BACKUP_DISK` | `local` | Must be a `local` disk; public disks are rejected at runtime. |
| `BACKUP_PATH` | `backups` | Path inside the private disk. |
| `DB_DUMP_BINARY` | `mysqldump` | Absolute path recommended on Windows. |
| `DB_RESTORE_BINARY` | `mysql` | Absolute path recommended on Windows. |
| `BACKUP_TIMEOUT` | `600` | Per-process timeout in seconds. |
| `BACKUP_RETENTION_DAYS` | `30` | Initial default; editable in the UI. |
| `BACKUP_RETENTION_COUNT` | `20` | Newest usable copies always retained. |
| `BACKUP_INCLUDE_ROUTINES` | `false` | Enable only if the account has `EXECUTE`/`SHOW_ROUTINE`. |
| `BACKUP_INCLUDE_EVENTS` | `false` | Enable only if the account has `EVENT`. |
| `BACKUP_RESTORE_HOST` | live host | Restore connection host. |
| `BACKUP_RESTORE_PORT` | live port | Restore connection port. |
| `BACKUP_RESTORE_USERNAME` | live user | Use a dedicated restore account in production. |
| `BACKUP_RESTORE_PASSWORD` | live password | Never commit this value. |
| `BACKUP_RESTORE_DATABASE` | empty | Target database **name** for `backup:restore`; never the live database. |
| `BACKUP_TEST_DB_PREFIX` | `smm_restore_test_` | Prefix for disposable databases created by `backup:test-restore`. |

Local development (`.env`, never committed):

```dotenv
DB_DUMP_BINARY="C:/Users/user/mysql-8.0.46-winx64/bin/mysqldump.exe"
DB_RESTORE_BINARY="C:/Users/user/mysql-8.0.46-winx64/bin/mysql.exe"
```

The application database account is intentionally least-privilege and **cannot create databases**. That is why restore testing fails with `Access denied ... CREATE DATABASE` until an administrator provisions a dedicated restore account (section 5).

---

## 3. Creating a backup

Web UI: **Settings → Backups → Create backup**
Permissions: `backup.view` to open the page; `backup.create` to create.

CLI (recommended for schedules and large databases):

```bash
php artisan backup:create
```

Output includes the filename, size and SHA-256. The row in `database_backups` records the manifest (table list, core row counts, server version). A failed dump is recorded as `failed` with a short reason and leaves no partial file.

### Scheduling

Linux cron (daily 01:15):

```cron
15 1 * * * cd /var/www/smm && php artisan backup:create >> storage/logs/backup.log 2>&1
```

Windows Task Scheduler:

```
Program:   C:\path\to\php.exe
Arguments: artisan backup:create
Start in:  C:\path\to\project
```

Do not run the web UI backup action for very large databases; it holds a PHP request open until the dump completes. Use the CLI/cron for production-scale dumps.

---

## 4. Verifying integrity (no restore)

Web UI: **Verify** on any completed/verified row.
CLI equivalent (also used implicitly before every restore):

```bash
php artisan backup:test-restore <filename>
```

`Verify` recomputes SHA-256, checks the gzip header, and records `verified_by` / `verified_at`. A mismatch marks the backup `failed` and never silently “repairs” it. The UI and export never expose raw credentials.

---

## 5. Restore into a disposable database (proven drill)

`backup:test-restore` is the required integrity drill:

1. Checks the checksum and gzip stream.
2. Creates a new database named `<BACKUP_TEST_DB_PREFIX><timestamp>_<random>`.
3. Restores the dump with the `mysql` client.
4. Compares restored table names with the manifest.
5. Compares `migrations`, `users`, `students`, `academic_years` row counts.
6. Drops the disposable database (even when a step fails).
7. Marks the backup `verified` and stores the drill timestamp in its manifest.

Run it against a dedicated restore account. Example provisioning (adapt the password and wildcard escaping to your server; run as a MySQL administrator):

```sql
CREATE USER 'smm_restore'@'127.0.0.1' IDENTIFIED BY '<strong-random-password>';

-- Allow creating/dropping only disposable restore databases.
GRANT ALL PRIVILEGES ON `smm\_restore\_test\_%`.* TO 'smm_restore'@'127.0.0.1';
```

If the wildcard form is not accepted by your MySQL build, create the disposable database manually with the prefix and grant `ALL` on that exact database, or (less preferred) grant `CREATE, DROP` on `*.*` to this dedicated account. The account must **never** have privileges on the live database.

Then configure the server environment (never commit):

```dotenv
BACKUP_RESTORE_HOST=127.0.0.1
BACKUP_RESTORE_PORT=3306
BACKUP_RESTORE_USERNAME=smm_restore
BACKUP_RESTORE_PASSWORD=<strong-random-password>
```

Run the drill:

```bash
php artisan backup:test-restore            # newest usable backup
php artisan backup:test-restore <filename> # a specific backup
```

A successful run ends with `Restore verified in disposable database ...` and `The disposable database was dropped.` If it fails, the command says explicitly: **“Do not treat this backup as restore-verified.”**

### Drill evidence (2026-10-27)

- Backup: `sylhet_model_madrasah_2026_10_27_114406.sql.gz`
- SHA-256: `9d9c73a8b4d6aad36b1e84fdd382d0f2891c60baaf224b08ddb4a1c92a7bc18e`
- Restored tables: **114** (matches manifest)
- Verified core counts: `users=1`, `students=0`, `migrations=107`, `academic_years=0`
- Disposable database was confirmed dropped; the isolated MySQL instance and its data directory were then removed.
- No fake students or transactions were created for this drill.

The drill used a separate, disposable MySQL instance on `127.0.0.1:3307` so the live schema was never a restore target. That instance has been shut down and deleted.

---

## 6. Restore into a real target database (disaster recovery)

Restoration is a **server-console operation only** and deliberately absent from the browser.

1. Announce a maintenance window and enable maintenance mode:
   ```bash
   php artisan down
   ```
2. Stop queue workers and schedulers so they cannot write during the restore.
3. Provision an empty target database and grant the restore account privileges **only** on it:
   ```sql
   CREATE DATABASE smm_restore_target CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
   GRANT ALL PRIVILEGES ON `smm_restore_target`.* TO 'smm_restore'@'127.0.0.1';
   ```
4. Confirm the backup is verified (or let the command verify it):
   ```bash
   php artisan backup:test-restore <filename>
   ```
5. Restore into the target (the command verifies the checksum first, refuses the live database name, and prints row-count comparisons):
   ```bash
   php artisan backup:restore <filename> --database=smm_restore_target --force
   ```
   Or set `BACKUP_RESTORE_DATABASE=smm_restore_target` and omit `--database`.
6. Perform smoke tests against the restored data (login, dashboard totals, one report, one fee receipt view). Do **not** point the live application at the restored database until these pass.
7. To make the restored database live, switch `DB_DATABASE`/credentials deliberately, restart workers, and clear stale caches:
   ```bash
   php artisan optimize:clear
   php artisan up
   ```
8. Keep the old database untouched until the new one has been accepted; take a fresh backup immediately after recovery.

Never restore over a running live database and never expose these commands through the web UI.

---

## 7. Retention

- Retention runs automatically after every successful backup.
- The newest `BACKUP_RETENTION_COUNT` usable copies are never deleted.
- Beyond that, copies older than `BACKUP_RETENTION_DAYS` are removed (file + row).
- The newest copy is retained even when it is older than the age threshold, so a quiet system never loses its last backup.
- Failed/pending rows are not deleted automatically; investigate and delete them explicitly.
- Retention changes are audited as `settings.backup_retention_updated`.

---

## 8. Security controls

| Control | Implementation |
|---|---|
| Private storage | Local disk root must be outside `public_path()`; `BACKUP_PATH` rejects traversal and drive/absolute paths. |
| No secrets in commands | MySQL credentials are written to a temporary 0600 `[client]` file, referenced by `--defaults-extra-file`, and deleted in `finally`. |
| No arbitrary execution | The UI has no restore/command action; only fixed `mysqldump`/`mysql` invocations with array arguments (no shell). |
| Authorization | `backup.view/create/verify/download/delete`; the download route also checks the record status and storage path. |
| Integrity | SHA-256 + gzip header validation; restore compares table names and core row counts. |
| Safe targets | Live and system database names are rejected; disposable databases must match the configured prefix before being dropped. |
| Audit trail | `backup.created`, `backup.verified`, `backup.downloaded`, `backup.deleted`, `backup.restored`, `backup.restore_tested`, `backup.retention_applied`. |
| Browser safety | Downloads are `application/gzip` with `nosniff`; no inline preview; errors shown to users are translated, not raw server output. |

**Not covered:** backups are not encrypted by this layer. Use full-disk encryption and restricted filesystem ACLs, or encrypt the backup directory with a managed tool. Do not copy backups to public/synced folders.

---

## 9. Troubleshooting

| Symptom | Fix |
|---|---|
| `The MySQL dump binary is not available` | Set `DB_DUMP_BINARY` to the absolute `mysqldump` path and clear config cache. |
| `Access denied ... CREATE DATABASE` on test-restore | Provision the dedicated restore account (section 5). The app account is least-privilege by design. |
| `Refusing to restore over the live or a system database` | Choose a different `--database` / `BACKUP_RESTORE_DATABASE`. |
| `The restored database is missing tables` | The dump is incomplete/corrupt; restore an older verified copy and investigate the disk/logs. |
| `Row count mismatch` | Do not promote the restore; check for partial dumps or a restore client error. |
| Checksum mismatch | Treat the file as corrupt; delete it after confirming a newer verified copy exists. |
| Restore is slow | Use CLI, not the browser; increase `BACKUP_TIMEOUT` for very large dumps. |
| Gzip/disk full | Check `storage/app/private` free space; failed dumps leave no partial file. |