[← Back to Home](Home)

# MySQL Backup

backup-mcp supports MySQL (and PostgreSQL) database backups over SSH using `mysqldump` (or `pg_dump`).

## How It Works

1. The tool connects to the remote server via SSH
2. Runs `mysqldump` on the remote server, piping through `gzip`
3. Streams the compressed output back to the local machine
4. Saves the result to `localBackupDir` with a timestamped filename

The dump command executed on the remote server:

```bash
mysqldump -u {dbUser} -p'{dbPassword}' {database} | gzip
```

For PostgreSQL targets:

```bash
PGPASSWORD='{dbPassword}' pg_dump -U {dbUser} {database} | gzip
```

## Configuration

Add a `mysql` target to `~/.backup-mcp.json`:

```json
{
  "targets": {
    "mysite-db": {
      "name": "mysite",
      "type": "mysql",
      "sshHost": "server.example.com",
      "sshPort": 22,
      "sshUser": "deploy",
      "sshKeyPath": "~/.ssh/id_ed25519",
      "database": "joomla_db",
      "dbUser": "joomla",
      "dbPassword": "your-password",
      "localBackupDir": "~/backups/mysite"
    }
  }
}
```

See [Configuration](Configuration) for all field descriptions.

## Running a Backup

Use the `backup_database` tool:

```
backup_database(target: "mysite-db")
```

Output:
```json
{
  "success": true,
  "message": "Database backup: mysite-db-2026-05-09T12-00-00.sql.gz",
  "filePath": "/home/user/backups/mysite/mysite-db-2026-05-09T12-00-00.sql.gz",
  "sizeBytes": 1048576
}
```

## Listing Backups

```
backup_list(target: "mysite-db")
```

Lists all local backups for the target, sorted by date (newest first):

```
mysite-db-2026-05-09T12-00-00.sql.gz  15.2 MB  2026-05-09T12:00:00.000Z
mysite-db-2026-05-08T12-00-00.sql.gz  14.8 MB  2026-05-08T12:00:00.000Z
```

## Checking Backup Status

```
backup_status(target: "mysite-db")
```

Shows total count, disk usage, and last backup info:

```
Backups: 5
Total size: 73.2 MB
Last backup: mysite-db-2026-05-09T12-00-00.sql.gz (2026-05-09T12:00:00.000Z)
```

## Pruning Old Backups

Delete backups older than a specified number of days:

```
backup_prune(target: "mysite-db", days: 30)
```

Output:
```
Pruned 3 backups older than 30 days
```

## Restoring a Database Backup

Database restore is a manual process using SSH and the backup file.

### Step 1: Locate the backup

```
backup_list(target: "mysite-db")
```

### Step 2: Copy the backup to the server

Use deploy-mcp or SCP:

```bash
scp ~/backups/mysite/mysite-db-2026-05-09T12-00-00.sql.gz deploy@server.example.com:/tmp/
```

### Step 3: Restore on the remote server

```bash
ssh deploy@server.example.com
gunzip < /tmp/mysite-db-2026-05-09T12-00-00.sql.gz | mysql -u joomla -p joomla_db
```

### Step 4: Verify

```bash
mysql -u joomla -p joomla_db -e "SHOW TABLES;"
```

## Backup Scheduling

backup-mcp does not include a built-in scheduler. Use external scheduling:

### Linux (cron)

Call the MCP tool via Claude Code on a schedule, or use a wrapper script:

```bash
# Example: daily database backup at 2 AM
0 2 * * * cd /path/to/project && npx backup-mcp backup_database --target mysite-db
```

### Windows (Task Scheduler)

Create a scheduled task that invokes the backup through Claude Code or a script.

## Timeout and Buffer Limits

| Setting | Value |
|---------|-------|
| SSH timeout | 5 minutes (300,000 ms) |
| Max buffer | 500 MB |

Large databases that exceed the buffer or timeout will fail. For very large databases, consider running `mysqldump` directly on the server and downloading the result separately.

---

> Built on [MokoStandards](https://git.mokoconsulting.tech/MokoConsulting/MokoStandards-API)

---

*Repo: [backup-mcp](https://git.mokoconsulting.tech/MokoConsulting/backup-mcp) · [MokoStandards](https://git.mokoconsulting.tech/MokoConsulting/moko-platform/wiki/Home)*

| Revision | Date | Author | Description |
|---|---|---|---|
| 1.0 | 2026-05-09 | Moko Consulting | Initial version |