# MySQL Backup Manager
A single-file PHP script that lists all your MySQL databases and lets you back them up with one click. Each backup is saved as a date-stamped `.sql` file using `mysqldump`.
---
## ✨ Features
- 📋 Lists all **user databases** (skips `information_schema`, `performance_schema`, `mysql`, `sys`, `test`)
- 💾 **One-click backup** per database
- 💾 **Backup All** button to dump every database at once
- 📅 Backups saved as `{db_name}_{YYYYMMDD}.sql` (e.g. `shop_20260101.sql`)
- ⚙️ Configurable `mysqldump` path via a built-in settings input
- 💽 Settings persist in `backup_config.json` — enter the path only once
- ✅ Live status check showing whether `mysqldump.exe` was found
- 🔔 Clear success / error messages after each backup
- 🎨 Clean, dependency-free UI (plain PHP + CSS, no frameworks)
- 🌍 Works on Windows, Linux, and macOS
---
## 🖼️ Preview
The interface consists of:
1. **A settings box** at the top where you enter the folder containing `mysqldump.exe`
2. **A table of databases** with a **💾 Backup** button on each row
3. **A large "Backup All Databases" button** at the bottom
Each database row shows the exact filename that will be created, like `yourdatabase_db_20260101.sql`.
---
## 📁 Files Created
```
your-folder/
├── list_databases.php ← the main script
├── backup_config.json ← auto-created (stores your mysqldump path)
├── README.md ← this file
└── backups/ ← auto-created
├── yourdatabase_db_20260101.sql
├── yourotherdatabase_db_20260101.sql
└── ...
```
---
## 🚀 Requirements
| Requirement | Notes |
|---|---|
| **PHP 7.4+** | With `exec()` enabled |
| **MySQL server** | 5.7 or 8.x |
| **mysqldump** | Ships with MySQL / XAMPP / WAMP / Laragon |
| **Web server** | Apache, Nginx, or PHP built-in server |
| **Write permission** | Script folder must be writable (for `/backups` and config file) |
> ⚠️ **Important:** `exec()` must **not** be in `disable_functions` in your `php.ini`. See [Troubleshooting](#-troubleshooting).
---
## 🛠️ Installation
### Step 1 — Place the file
Put `list_databases.php` in your web server directory:
| Stack | Suggested path |
|---|---|
| **XAMPP (Windows)** | `C:\xampp\htdocs\backup\` |
| **WAMP** | `C:\wamp64\www\backup\` |
| **Laragon** | `C:\laragon\www\backup\` |
| **MAMP** | `/Applications/MAMP/htdocs/backup/` |
| **Linux (Apache)** | `/var/www/html/backup/` |
### Step 2 — Configure database credentials
Open `list_databases.php` and edit the top section:
```php
// ===== Database connection settings =====
$db_host = 'localhost';
$db_user = 'root'; // Change to your MySQL username
$db_pass = ''; // Change to your MySQL password
$db_port = 3306; // Default MySQL port
// ========================================
```
### Step 3 — Open in browser
```
http://localhost/backup/list_databases.php
```
### Step 4 — Set the `mysqldump` path
In the **settings box** at the top, enter the folder that contains `mysqldump.exe`. For XAMPP on drive `E:`, that's:
```
e:\xampp\mysql\bin\
```
Click **Save**. You should see:
```
Full executable used: e:\xampp\mysql\bin\mysqldump.exe ✓ found
```
If it says `✗ not found`, see [Finding mysqldump.exe](#-finding-mysqldumpexe).
---
## 📖 Usage
### Back up a single database
Click the **💾 Backup** button on the row of the database you want.
A file named `{db_name}_{YYYYMMDD}.sql` will be created in the `/backups` folder.
Example: backing up `helpdesk_db` on 1 January 2026 produces:
```
backups/helpdesk_db_20260101.sql
```
### Back up all databases
Click the big green **💾 Backup All Databases** button at the bottom of the page.
A confirmation dialog appears, then every user database is dumped into its own date-stamped file.
### After the backup
A green success message shows how many databases were saved:
> Backup completed: 3 database(s) saved to `/backups` (20260101).
If a database fails, a red error message shows the exact `mysqldump` output so you can diagnose it.
---
## ⚙️ Configuration File
After you save the `mysqldump` path, the script creates `backup_config.json` in the same folder:
```json
{
"mysqldump_path": "e:\\xampp\\mysql\\bin\\"
}
```
- The file is created automatically — you don't need to make it.
- Edit it manually if you want to change the path without using the UI.
- **Delete it** to reset to the default value.
- The file contains **no credentials** — only the path.
---
## 🔍 Finding `mysqldump.exe`
Not sure where it is? Common locations:
| Stack | Typical path |
|---|---|
| **XAMPP (default)** | `C:\xampp\mysql\bin\mysqldump.exe` |
| **XAMPP (custom drive)** | `E:\xampp\mysql\bin\mysqldump.exe` |
| **WAMP** | `C:\wamp64\bin\mysql\mysql8.0.31\bin\mysqldump.exe` |
| **Laragon** | `C:\laragon\bin\mysql\mysql-8.0.30-winx64\bin\mysqldump.exe` |
| **MySQL Installer** | `C:\Program Files\MySQL\MySQL Server 8.0\bin\mysqldump.exe` |
| **Linux (apt/yum)** | `/usr/bin/mysqldump` |
| **macOS (Homebrew)** | `/usr/local/bin/mysqldump` or `/opt/homebrew/bin/mysqldump` |
### Search from Command Prompt (Windows)
```cmd
where /r "C:\xampp" mysqldump.exe
where /r "C:\Program Files" mysqldump.exe
where /r "E:\xampp" mysqldump.exe
```
### Search from Terminal (Linux/macOS)
```bash
which mysqldump
find / -name mysqldump 2>/dev/null
```
Once you find it, paste the **folder** (not the file) into the settings box, e.g.:
```
E:\xampp\mysql\bin\
```
---
## 🧩 How It Works
1. **Connect** to MySQL and run `SHOW DATABASES`.
2. **Filter** out system databases: `information_schema`, `performance_schema`, `mysql`, `sys`, and `test`.
3. **Render** a table with a **Backup** button per row.
4. On click, PHP builds a `mysqldump` command:
```
"e:\xampp\mysql\bin\mysqldump.exe" \
--host=localhost --port=3306 \
--user=root --password=... \
--result-file="backups\helpdesk_db_20260101.sql" \
helpdesk_db
```
5. **Execute** it via `exec()`.
6. **Check** the file was created and is not empty.
7. **Report** success or the exact error output.
The script uses `--result-file` instead of `>` redirection because it's more reliable on Windows.
---
## 📅 Restoring a Backup
### From the command line
```bash
mysql -u root -p your_db < backups/your_db_20260101.sql
```
### From phpMyAdmin
1. Select the target database (create it first if needed).
2. Go to the **Import** tab.
3. Choose the `.sql` file.
4. Click **Go**.
### Restore a single table
Backups contain full `CREATE TABLE` + `INSERT` statements, so you can also restore individual tables by extracting that section of the file.
---
## 🐛 Troubleshooting
### `'mysqldump' is not recognized as an internal or external command`
**Cause:** The path in the settings box is wrong or empty.
**Fix:** Enter the full folder path (with a trailing `\`), e.g. `e:\xampp\mysql\bin\`. Save and re-run. Confirm the page shows `✓ found`.
---
### Backup file is created but empty (0 bytes)
**Cause:** `mysqldump` ran but returned an error.
**Fix:** Test manually in Command Prompt:
```cmd
"e:\xampp\mysql\bin\mysqldump.exe" --user=root --password= your_db > test.sql
```
If it asks for a password and creates a valid file, your path is correct. Otherwise check the credentials in the PHP script.
---
### `Access denied for user 'root'@'localhost'`
**Cause:** Wrong username or password.
**Fix:** Update `$db_user` and `$db_pass` at the top of the script.
---
### Nothing happens when clicking Backup
**Cause:** `exec()` is disabled.
**Fix:** Open `php.ini`, find `disable_functions`, and remove `exec` from the list. Then restart your web server.
| Stack | php.ini location |
|---|---|
| **XAMPP** | `C:\xampp\php\php.ini` |
| **WAMP** | Tray icon → PHP → php.ini |
| **Laragon** | Menu → PHP → php.ini |
| **Linux** | `/etc/php/8.x/apache2/php.ini` |
---
### Cannot create `/backups` folder
**Cause:** PHP doesn't have write permission.
**Fix:**
- **Windows:** Right-click the script folder → Properties → Security → give `Everyone` write access (or the user Apache runs as).
- **Linux:** `sudo chown -R www-data:www-data /var/www/html/backup`
---
### Port is not 3306
**Cause:** MySQL is running on a custom port.
**Fix:** Change `$db_port = 3306;` at the top of the script.
---
### Password contains special characters
**Cause:** Shell escaping issues.
**Fix:** `escapeshellarg()` handles most cases automatically. If your password contains `"`, `\`, or `%`, consider using a dedicated backup user with a simpler password.
---
### Backup is very large / times out
**Cause:** PHP `max_execution_time` limit.
**Fix:** Add at the top of `list_databases.php`:
```php
set_time_limit(0);
ini_set('memory_limit', '512M');
```
---
## 🔒 Security
This script gives **full read access to all your databases** and can write `.sql` dumps. Treat it accordingly.
### Recommended protections
#### 1. Add HTTP Basic Auth
Insert at the **very top** of `list_databases.php`, before any other code:
```php
if (!isset($_SERVER['PHP_AUTH_USER']) ||
$_SERVER['PHP_AUTH_USER'] !== 'admin' ||
$_SERVER['PHP_AUTH_PW'] !== 'change-this-strong-password') {
header('WWW-Authenticate: Basic realm="DB Backup"');
header('HTTP/1.0 401 Unauthorized');
exit('Unauthorized');
}
```
#### 2. Protect the `/backups` folder
Create `backups/.htaccess`:
```apache
Require all denied
Order allow,deny
Deny from all
```
For Nginx, add to your site config:
```nginx
location ~ ^/backup/backups/ {
deny all;
return 403;
}
```
#### 3. Use a dedicated MySQL user
Don't use `root`. Create a backup-only user:
```sql
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;
```
Then set `$db_user = 'backup_user';` in the script.
#### 4. Delete or move the script when done
- Remove `list_databases.php` from the web root once you've finished.
- Or move it outside the document root and access it via CLI only.
#### 5. Never commit credentials
Keep `$db_pass` out of version control. Use environment variables or an untracked config file.
#### 6. Back up the backups
The `/backups` folder itself should be included in your regular off-site backup routine.