206 lines
6.0 KiB
Markdown
206 lines
6.0 KiB
Markdown
# Database Backup System Documentation
|
|
|
|
## Overview
|
|
The Quality Recticel application now includes a comprehensive database backup management system accessible from the Settings page for superadmin and admin users.
|
|
|
|
## Features
|
|
|
|
### 1. Manual Backup
|
|
- **Backup Now** button creates an immediate full database backup
|
|
- Uses `mysqldump` to create complete SQL export
|
|
- Includes all tables, triggers, routines, and events
|
|
- Each backup is timestamped: `backup_trasabilitate_YYYYMMDD_HHMMSS.sql`
|
|
|
|
### 2. Scheduled Backups
|
|
Configure automated backups with:
|
|
- **Enable/Disable**: Toggle scheduled backups on/off
|
|
- **Backup Time**: Set time of day for automatic backup (default: 02:00)
|
|
- **Frequency**: Choose Daily, Weekly, or Monthly backups
|
|
- **Retention Period**: Automatically delete backups older than N days (default: 30 days)
|
|
|
|
### 3. Backup Management
|
|
- **List Backups**: View all available backup files with size and creation date
|
|
- **Download**: Download any backup file to your local computer
|
|
- **Delete**: Remove old or unnecessary backup files
|
|
- **Restore**: (Superadmin only) Restore database from a backup file
|
|
|
|
## Configuration
|
|
|
|
### Backup Path
|
|
The backup location can be configured in three ways (priority order):
|
|
|
|
1. **Environment Variable** (Docker):
|
|
```yaml
|
|
# docker-compose.yml
|
|
environment:
|
|
BACKUP_PATH: /srv/quality_recticel/backups
|
|
volumes:
|
|
- /srv/docker-test/backups:/srv/quality_recticel/backups
|
|
```
|
|
|
|
2. **Configuration File**:
|
|
```ini
|
|
# py_app/instance/external_server.conf
|
|
backup_path=/srv/quality_app/backups
|
|
```
|
|
|
|
3. **Default Path**: `/srv/quality_app/backups`
|
|
|
|
### .env Configuration
|
|
Add to your `.env` file:
|
|
```bash
|
|
BACKUP_PATH=/srv/docker-test/backups
|
|
```
|
|
|
|
## Usage
|
|
|
|
### Access Backup Management
|
|
1. Login as **superadmin** or **admin**
|
|
2. Navigate to **Settings** page
|
|
3. Scroll to **💾 Database Backup Management** card
|
|
4. The backup management interface is only visible to superadmin/admin users
|
|
|
|
### Create Manual Backup
|
|
1. Click **⚡ Backup Now** button
|
|
2. Wait for confirmation message
|
|
3. New backup appears in the list
|
|
|
|
### Configure Scheduled Backups
|
|
1. Check **Enable Scheduled Backups**
|
|
2. Set desired backup time (24-hour format)
|
|
3. Select frequency (Daily/Weekly/Monthly)
|
|
4. Set retention period (days to keep backups)
|
|
5. Click **💾 Save Schedule**
|
|
|
|
### Download Backup
|
|
1. Locate backup in the list
|
|
2. Click **⬇️ Download** button
|
|
3. File downloads to your computer
|
|
|
|
### Delete Backup
|
|
1. Locate backup in the list
|
|
2. Click **🗑️ Delete** button
|
|
3. Confirm deletion
|
|
|
|
### Restore Backup (Superadmin Only)
|
|
⚠️ **WARNING**: Restore will replace current database!
|
|
1. This feature requires superadmin privileges
|
|
2. API endpoint: `/api/backup/restore/<filename>`
|
|
3. Use with extreme caution
|
|
|
|
## Technical Details
|
|
|
|
### Backup Module
|
|
Location: `py_app/app/database_backup.py`
|
|
|
|
Key Class: `DatabaseBackupManager`
|
|
|
|
Methods:
|
|
- `create_backup()`: Create new backup
|
|
- `list_backups()`: Get all backup files
|
|
- `delete_backup(filename)`: Remove backup file
|
|
- `restore_backup(filename)`: Restore from backup
|
|
- `get_backup_schedule()`: Get current schedule
|
|
- `save_backup_schedule(schedule)`: Update schedule
|
|
- `cleanup_old_backups(days)`: Remove old backups
|
|
|
|
### API Endpoints
|
|
|
|
| Endpoint | Method | Access | Description |
|
|
|----------|--------|--------|-------------|
|
|
| `/api/backup/create` | POST | Admin+ | Create new backup |
|
|
| `/api/backup/list` | GET | Admin+ | List all backups |
|
|
| `/api/backup/download/<filename>` | GET | Admin+ | Download backup file |
|
|
| `/api/backup/delete/<filename>` | DELETE | Admin+ | Delete backup file |
|
|
| `/api/backup/schedule` | GET/POST | Admin+ | Get/Set backup schedule |
|
|
| `/api/backup/restore/<filename>` | POST | Superadmin | Restore from backup |
|
|
|
|
### Backup File Format
|
|
- **Format**: SQL dump file (`.sql`)
|
|
- **Compression**: Not compressed (can be gzip manually if needed)
|
|
- **Contents**: Complete database with structure and data
|
|
- **Metadata**: Stored in `backups_metadata.json`
|
|
|
|
### Schedule Storage
|
|
Schedule configuration stored in: `{BACKUP_PATH}/backup_schedule.json`
|
|
|
|
Example:
|
|
```json
|
|
{
|
|
"enabled": true,
|
|
"time": "02:00",
|
|
"frequency": "daily",
|
|
"retention_days": 30
|
|
}
|
|
```
|
|
|
|
## Security Considerations
|
|
|
|
1. **Access Control**: Backup features restricted to admin and superadmin users
|
|
2. **Path Traversal Protection**: Filenames validated to prevent directory traversal attacks
|
|
3. **Credentials**: Database credentials read from `external_server.conf`
|
|
4. **Backup Location**: Should be on different mount point than application for safety
|
|
|
|
## Maintenance
|
|
|
|
### Disk Space
|
|
Monitor backup directory size:
|
|
```bash
|
|
du -sh /srv/quality_app/backups
|
|
```
|
|
|
|
### Manual Cleanup
|
|
Remove old backups manually:
|
|
```bash
|
|
find /srv/quality_app/backups -name "*.sql" -mtime +30 -delete
|
|
```
|
|
|
|
### Backup Verification
|
|
Test restore in development environment:
|
|
```bash
|
|
mysql -u root -p trasabilitate < backup_trasabilitate_20251103_020000.sql
|
|
```
|
|
|
|
## Troubleshooting
|
|
|
|
### Backup Fails
|
|
- Check database credentials in `external_server.conf`
|
|
- Ensure `mysqldump` is installed
|
|
- Verify write permissions on backup directory
|
|
- Check disk space availability
|
|
|
|
### Scheduled Backups Not Running
|
|
- TODO: Implement scheduled backup daemon/cron job
|
|
- Check backup schedule is enabled
|
|
- Verify time format is correct (HH:MM)
|
|
|
|
### Cannot Download Backup
|
|
- Check backup file exists
|
|
- Verify file permissions
|
|
- Ensure adequate network bandwidth
|
|
|
|
## Future Enhancements
|
|
|
|
### Planned Features (Task 4)
|
|
- [ ] Implement APScheduler for automated scheduled backups
|
|
- [ ] Add backup to external storage (S3, FTP, etc.)
|
|
- [ ] Email notifications for backup success/failure
|
|
- [ ] Backup compression (gzip)
|
|
- [ ] Incremental backups
|
|
- [ ] Backup encryption
|
|
- [ ] Backup verification tool
|
|
|
|
## Support
|
|
|
|
For issues or questions about the backup system:
|
|
1. Check application logs: `/srv/quality_app/logs/error.log`
|
|
2. Verify backup directory permissions
|
|
3. Test manual backup first before relying on scheduled backups
|
|
4. Keep at least 2 recent backups before deleting old ones
|
|
|
|
---
|
|
|
|
**Created**: November 3, 2025
|
|
**Module**: Database Backup Management
|
|
**Version**: 1.0.0
|