database-backupDatabase Backup
Automated database backup solution supporting multiple database types. Schedule regular backups of SQLite, PostgreSQL, and MySQL databases, compress backups to save storage space, encrypt sensitive data, and restore from backup points when needed.
Key Features
- •Support for SQLite, PostgreSQL, MySQL
- •Scheduled automated backups
- •Compression to reduce storage needs
- •Encryption for sensitive data
- •Point-in-time recovery options
Common Use Cases
- →Schedule daily backups of application databases
- →Archive old data before migrations
- →Create development snapshots
- →Comply with data retention policies
Custom Workflow Integration
This skill can be customized for your specific workflow as part of an SMF Works services engagement. Whether you need custom automation rules, integrations with your existing tools, or specialized configurations for your team, we can tailor this skill to fit your exact requirements.
Explore ServicesInstallation
# Install the skill (via TUI or CLI)
smfw install database-backup
# Get help
smfw run database-backup --help
💡 Tip: Install via the OpenClaw TUI skill manager for an interactive experience, or use the CLI command above.
Setup Guide
Database Backup — Setup Guide
Estimated setup time: 10–15 minutes
Difficulty: Easy to Moderate
Tier: Pro — requires SMF Works Pro subscription ($19.99/mo)
What You'll Need
| Requirement | Details | Cost |
|---|---|---|
| SMF Works Pro subscription | smfworks.com/subscribe | $19.99/mo |
| Python 3.8+ | Built into macOS 12+, available on Linux | Free |
| OpenClaw | Installed and authenticated | Free |
| smfworks-skills repository | Cloned via git | Included |
| For MySQL: mysqldump | MySQL client tools | Free |
| For PostgreSQL: pg_dump | PostgreSQL client tools | Free |
| For SQLite: nothing extra | Already in Python | Free |
Step 1 — Subscribe and Authenticate
Visit smfworks.com/subscribe.
openclaw auth status
Step 2 — Install Database Client Tools (if needed)
For MySQL/MariaDB backups:
# Ubuntu/Debian:
sudo apt install mysql-client
# macOS:
brew install mysql-client
For PostgreSQL backups:
# Ubuntu/Debian:
sudo apt install postgresql-client
# macOS:
brew install postgresql
For SQLite: No additional tools needed.
Step 3 — Get the Repository
git clone https://github.com/smfworks/smfworks-skills ~/smfworks-skills
Step 4 — Navigate to the Skill
cd ~/smfworks-skills/skills/database-backup
Step 5 — Verify
python3 main.py
Expected:
Usage: python main.py <command> [options]
Commands:
backup - Interactive backup wizard
list - List all backups
stats - Show backup statistics
restore <file> - Restore from backup (SQLite only)
cleanup [days] - Remove old backups (default: 30 days)
Verify Your Setup
Run a test backup of a SQLite database. If you have one:
python3 main.py backup
# Choose: sqlite
# Path: /path/to/your/database.db
Or create a test SQLite database:
python3 -c "import sqlite3; conn=sqlite3.connect('/tmp/test.db'); conn.execute('CREATE TABLE t(id int)'); conn.close()"
python3 main.py backup
# sqlite, /tmp/test.db
Expected: Success message with backup file path and size.
Configuration for Automation
For cron automation, set database credentials as environment variables instead of entering them interactively:
MySQL:
export MYSQL_HOST=localhost
export MYSQL_USER=myuser
export MYSQL_PASS=mypassword
export MYSQL_DB=mydatabase
PostgreSQL:
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=mypassword
export PGDATABASE=mydatabase
Add these to ~/.bashrc or use a secure env file in your cron setup.
Troubleshooting
Error: SMF Works Pro subscription required — Subscribe at smfworks.com/subscribe.
mysqldump: command not found — Install MySQL client: sudo apt install mysql-client or brew install mysql-client.
pg_dump: command not found — Install PostgreSQL client: sudo apt install postgresql-client or brew install postgresql.
Next Steps
Setup complete. See HOWTO.md for backup walkthroughs, restore procedures, and cron automation.
How-To Guide
Database Backup — How-To Guide
Prerequisites: SMF Works Pro subscription active. Database client tools installed (if using MySQL/PostgreSQL). Setup complete (see SETUP.md).
Table of Contents
- How to Back Up a SQLite Database
- How to Back Up a MySQL Database
- How to Back Up a PostgreSQL Database
- How to Restore a SQLite Backup
- How to List and Clean Up Backups
- Automating with Cron
- Combining with Other Skills
- Troubleshooting Common Issues
- Tips & Best Practices
1. How to Back Up a SQLite Database
When to use it: Your app uses a local SQLite database file that needs regular backup.
Steps
Step 1 — Navigate to the skill.
cd ~/smfworks-skills/skills/database-backup
Step 2 — Run the backup wizard.
python3 main.py backup
Database type (sqlite/mysql/postgres): sqlite
Database file path: /home/user/myapp/data/app.db
💾 Creating backup...
✅ Backup created: app-2024-03-15-090001.db.gz
Size: 2.4 MB
Location: ~/.smf/db-backups/app-2024-03-15-090001.db.gz
Result: Your SQLite database is safely backed up and compressed.
2. How to Back Up a MySQL Database
When to use it: Your WordPress, app, or web service uses MySQL or MariaDB.
Steps
Step 1 — Run the backup wizard.
python3 main.py backup
Database type: mysql
Host [localhost]: localhost
Port [3306]: 3306
Username: myuser
Password: [type password, hidden]
Database name: myapp_production
💾 Running mysqldump...
✅ Backup created: myapp_production-2024-03-15-020001.sql.gz
Size: 48.7 MB
Result: A compressed SQL dump of your entire MySQL database.
3. How to Back Up a PostgreSQL Database
When to use it: Your application uses PostgreSQL.
Steps
Step 1 — Run the backup wizard.
python3 main.py backup
Database type: postgres
Host [localhost]: localhost
Port [5432]: 5432
Username: postgres
Database name: myapp
💾 Running pg_dump...
✅ Backup created: myapp-2024-03-15-020001.sql.gz
Size: 34.2 MB
4. How to Restore a SQLite Backup
When to use it: After data loss, corruption, or accidental deletion.
Steps
Step 1 — List available backups.
python3 main.py list
📋 Database Backups (4 total):
1. app-2024-03-15-090001.db.gz — 2.4 MB — 2024-03-15 09:00
2. app-2024-03-14-090001.db.gz — 2.4 MB — 2024-03-14 09:00
Step 2 — Restore the desired backup.
python3 main.py restore ~/.smf/db-backups/app-2024-03-14-090001.db.gz
Output:
🔄 Restoring backup: app-2024-03-14-090001.db.gz
✅ Restore complete!
For MySQL/PostgreSQL restore (manual process):
# MySQL:
gunzip -c backup.sql.gz | mysql -u myuser -p myapp_production
# PostgreSQL:
gunzip -c backup.sql.gz | psql -U postgres myapp
5. How to List and Clean Up Backups
List all backups:
python3 main.py list
View statistics:
python3 main.py stats
Remove backups older than 30 days:
python3 main.py cleanup 30
Remove backups older than 7 days:
python3 main.py cleanup 7
6. Automating with Cron
Open crontab
crontab -e
Example: Daily MySQL backup at 2 AM using environment variables
First, create a secure env file:
cat > ~/.smf/db-backup.env << 'EOF'
export MYSQL_HOST=localhost
export MYSQL_USER=myuser
export MYSQL_PASS=mypassword
export MYSQL_DB=myapp_production
EOF
chmod 600 ~/.smf/db-backup.env
Then in crontab:
0 2 * * * source /home/yourname/.smf/db-backup.env && python3 /home/yourname/smfworks-skills/skills/database-backup/main.py backup >> /home/yourname/logs/db-backup.log 2>&1
Example: Weekly cleanup of old backups
0 3 * * 0 python3 /home/yourname/smfworks-skills/skills/database-backup/main.py cleanup 30 >> /home/yourname/logs/db-backup.log 2>&1
7. Combining with Other Skills
Database Backup + Claw System Backup: Combined protection strategy:
# Back up database AND system
python3 ~/smfworks-skills/skills/database-backup/main.py backup
python3 ~/smfworks-skills/skills/claw-system-backup/main.py
Database Backup + System Monitor: Check disk before backing up large databases:
python3 ~/smfworks-skills/skills/system-monitor/main.py disk
python3 ~/smfworks-skills/skills/database-backup/main.py backup
8. Troubleshooting Common Issues
mysqldump: command not found
Fix: Install MySQL client tools:
sudo apt install mysql-client # Ubuntu/Debian
brew install mysql-client # macOS
Access denied for user 'xxx'@'localhost'
Wrong credentials or insufficient privileges.
Fix: Verify credentials. Grant necessary privileges: GRANT SELECT, LOCK TABLES ON mydb.* TO 'myuser'@'localhost';
Backup file is 0 bytes or empty
The dump command failed.
Fix: Test your credentials manually: mysqldump -u myuser -p myapp_production --no-data 2>&1 — this shows any errors.
pg_dump: error: FATAL: password authentication failed
Wrong PostgreSQL password.
Fix: Check your password. PostgreSQL also accepts a .pgpass file for automation: echo "localhost:5432:mydb:myuser:mypassword" > ~/.pgpass && chmod 600 ~/.pgpass
9. Tips & Best Practices
Back up before any schema changes or data migrations. Always create a fresh backup immediately before running any ALTER TABLE, data import, or migration script. This is your rollback point.
Store database credentials securely. Never put plaintext passwords in cron jobs. Use environment variables or a .pgpass/.my.cnf file with restricted permissions (600).
Test restores periodically. A backup you've never restored from is an untested backup. Restore a copy to a test location quarterly to confirm the backup is actually usable.
Set cleanup intervals based on your change rate. A database that changes hourly needs more recent backups. A database that changes weekly can use 30-day retention. Set cleanup days accordingly.
Compress before storing. The skill compresses with gzip automatically. SQL dumps compress 5–10× typically, so a 500 MB dump becomes ~50 MB.