gitea-actions[bot]
177 lines
3.8 KiB
Markdown
177 lines
3.8 KiB
Markdown
[← 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 |
|