wiki/backup-mcp/MySQL-Backup.-.md

← Back to 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:

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

For PostgreSQL targets:

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

Configuration

Add a mysql target to ~/.backup-mcp.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 for all field descriptions.

Running a Backup

Use the backup_database tool:

backup_database(target: "mysite-db")

Output:

{
  "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:

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

Step 3: Restore on the remote server

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

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:

# 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

---

Repo: backup-mcp · MokoStandards

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