Backup Database

Creates a backup of the MySQL database to a file. Exports the entire database structure and data to a SQL file.

Common Properties

• Name - The custom name of the node.
• Color - The custom color of the node.
• Delay Before (sec) - Waits in seconds before executing the node.
• Delay After (sec) - Waits in seconds after executing node.
• Continue On Error - Automation will continue regardless of any error. The default value is false.

info

If the ContinueOnError property is true, no error is caught when the project is executed, even if a Catch node is used.

Inputs

• Connection Id - The unique identifier of the database connection to use (optional if credentials provided directly).
• Backup File Path - The full file path where the backup will be saved. For example: C:\backups\mydb_2024-01-15.sql or /home/user/backups/mydb.sql

Options

• Credentials - MySQL database credentials (optional). Use this if you want to backup without a Connect node.
• Parameters - Additional connection string parameters (optional). Only used when credentials are provided.

Output

• None - The backup file is created at the specified path.

How It Works

The Backup Database node creates a complete database backup. When executed, the node:

1. Retrieves the database connection (either from connection ID or creates new connection from credentials)
2. Validates the backup file path is not empty
3. Validates the backup directory exists
4. Creates a MySqlBackup object
5. Exports the database to the specified file path
6. Saves all tables, data, and structure to the SQL file

Requirements

• Either: A valid connection ID from Connect node OR valid database credentials
• Valid backup file path
• The backup directory must exist
• Write permissions on the backup directory
• Sufficient disk space for the backup file
• Database user must have SELECT permission on all tables

Error Handling

The node will return specific errors in the following cases:

• ErrInvalidArg - Backup file path is empty or directory doesn't exist
• ErrNotFound - Connection ID not found
• ErrRuntime - Backup operation failed (permissions, disk space, database access issues)

Backup File Format

The backup creates a standard MySQL dump file (.sql) containing:

• CREATE TABLE statements
• INSERT statements with data
• Database structure (indexes, constraints)
• Comments and metadata

Usage Examples

Basic Backup

// Set backup file path with timestamp
const date = new Date().toISOString().slice(0, 10); // 2024-01-15
const backupPath = `C:\\backups\\mydb_${date}.sql`;

Daily Backup

Schedule Trigger (daily at 2 AM)
  ↓
Connect
  ↓
Backup Database
  - Backup File Path: C:\backups\daily\mydb_{{date}}.sql
  ↓
Disconnect
  ↓
Send Email (backup complete notification)

Backup Before Maintenance

Connect
  ↓
Backup Database
  ↓
Non Query (maintenance operations)
  ↓
Disconnect

Multi-Database Backup

Loop (over database list)
  ↓
  Connect (to database)
    ↓
  Backup Database
    - File: C:\backups\{{dbname}}_{{timestamp}}.sql
    ↓
  Disconnect

Common Use Cases

1. Scheduled Backups - Automate daily, weekly, or monthly database backups
2. Pre-Deployment Backups - Backup before applying database changes
3. Disaster Recovery - Create backups for disaster recovery procedures
4. Data Archival - Archive database state at specific points in time
5. Development Snapshots - Capture database state before testing
6. Migration Preparation - Backup before migrating to new server
7. Compliance - Regular backups for regulatory compliance

Backup Strategies

Daily Backup with Rotation

// Keep last 7 days of backups
const daysOfWeek = ['Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday'];
const today = new Date().getDay();
const dayName = daysOfWeek[today];
const backupPath = `C:\\backups\\daily_${dayName}.sql`;

// This will overwrite last week's backup for the same day

Timestamped Backups

// Create unique backup file with timestamp
const timestamp = new Date().toISOString().replace(/[:.]/g, '-').slice(0, 19);
const backupPath = `C:\\backups\\mydb_${timestamp}.sql`;
// Example: mydb_2024-01-15T14-30-00.sql

Incremental Backup Strategy

Full Backup (Sunday)
  - File: C:\backups\full\mydb_sunday.sql

Incremental Backups (Monday-Saturday)
  - Use Query to export only changed records
  - Or use binary log replay

Remote Backup

Backup Database (local file)
  ↓
FTP Upload (upload to remote server)
  ↓
Delete Local Backup (cleanup)

Tips for Effective Use

• File Naming - Use timestamps or dates in backup filenames for easy identification
• Directory Structure - Organize backups by date, database, or purpose
• Backup Rotation - Implement rotation to manage disk space
• Compression - Compress backup files to save disk space (use separate compression node)
• Verification - Test restore from backups periodically
• Offsite Storage - Copy backups to remote location for disaster recovery
• Monitoring - Track backup success/failure and file sizes
• Retention Policy - Define how long to keep backups

Best Practices

Backup Naming Convention

// Organization-Date-Time-Database.sql
const timestamp = new Date().toISOString().slice(0, 19).replace(/[T:]/g, '-');
const dbName = 'myapp';
const backupPath = `C:\\backups\\${dbName}_${timestamp}.sql`;
// Example: myapp_2024-01-15-14-30-00.sql

Error Handling

Try
  ├─ Connect
  ├─ Backup Database
  ├─ Verify Backup File Exists
  └─ Disconnect
Catch
  ├─ Log Error
  └─ Send Alert Email

Backup Verification

Backup Database (create backup.sql)
  ↓
File Exists (verify file created)
  ↓
Get File Size (check size > 0)
  ↓
If (size > minimum expected)
  └─ Log Success
Else
  └─ Alert (backup may be incomplete)

Scheduled Backup Workflow

Schedule Trigger (daily at 2 AM)
  ↓
JavaScript (generate backup filename)
  ↓
Connect
  ↓
Try
    ├─ Backup Database
    ├─ Compress Backup File
    ├─ Upload to Cloud Storage
    └─ Delete Local Backup
  Catch
    └─ Send Alert Email
  Finally
    └─ Disconnect

Performance Considerations

• Database Size - Large databases take longer to backup
• Table Locks - Backup may lock tables briefly (depends on storage engine)
• Disk I/O - Backup consumes disk I/O; schedule during off-peak hours
• Network - If database is remote, network speed affects backup time
• Compression - Consider compressing backups to save disk space and transfer time

Backup Time Estimation

Database Size	Estimated Time
< 1 GB	1-5 minutes
1-10 GB	5-30 minutes
10-100 GB	30-180 minutes
> 100 GB	Hours

Common Errors and Solutions

Backup File Path Cannot Be Empty

Error: ErrInvalidArg: Backup file path cannot be empty

Solutions:

• Provide a valid file path in the Backup File Path field
• Ensure the path variable has a value
• Use absolute path (e.g., C:\backups\db.sql, not backups\db.sql)

Backup Directory Does Not Exist

Error: ErrInvalidArg: Backup directory does not exist

Solutions:

• Create the backup directory before running backup
• Use File System nodes to create directory
• Verify the directory path is correct

Database Backup Failed

Error: ErrRuntime: Database backup failed

Common Causes:

• Insufficient disk space
• No write permission on backup directory
• Database user lacks SELECT permission on tables
• Connection lost during backup
• File path contains invalid characters

Solutions:

• Check available disk space
• Verify write permissions on backup directory
• Ensure database user has SELECT permission on all tables
• Use stable connection
• Avoid special characters in file path

Access Denied

Error: Permission denied when writing backup file

Solutions:

• Run robot with appropriate file system permissions
• Choose a directory where the robot has write access
• On Windows, avoid writing to system directories
• On Linux, ensure proper ownership and permissions

Backup File Management

Cleanup Old Backups

// JavaScript node to identify old backups
const fs = require('fs');
const path = require('path');
const backupDir = 'C:\\backups';
const maxAge = 30; // days

const files = fs.readdirSync(backupDir);
const now = Date.now();

files.forEach(file => {
  const filePath = path.join(backupDir, file);
  const stats = fs.statSync(filePath);
  const ageInDays = (now - stats.mtimeMs) / (1000 * 60 * 60 * 24);

  if (ageInDays > maxAge) {
    fs.unlinkSync(filePath); // Delete old backup
  }
});

Compress Backups

Backup Database (create .sql file)
  ↓
Shell Command: gzip backup.sql (creates backup.sql.gz)
  ↓
Delete Original .sql File

Or on Windows:

Backup Database
  ↓
Shell Command: 7z a backup.7z backup.sql
  ↓
Delete Original .sql File

Upload to Cloud

Backup Database (local file)
  ↓
AWS S3 Upload / FTP Upload / Cloud Storage
  ↓
Delete Local Backup (if storage is limited)

Restore from Backup

To restore a backup, use the Restore Database node:

Connect
  ↓
Restore Database
  - Backup File Path: C:\backups\mydb_2024-01-15.sql
  ↓
Disconnect

Advanced Examples

Differential Backup Pattern

// Full backup on Sunday, differential on other days
const day = new Date().getDay();

if (day === 0) {
  // Full backup on Sunday
  backupPath = 'C:\\backups\\full\\mydb_full.sql';
} else {
  // Differential backup on weekdays
  // Query changed records since last full backup
  // Export only those records
}

Multi-Database Backup

// Backup multiple databases
const databases = ['db1', 'db2', 'db3'];

databases.forEach(dbName => {
  // Connect to each database
  // Backup to separate file
  const backupPath = `C:\\backups\\${dbName}_${timestamp}.sql`;
});

Backup with Notification

Connect
  ↓
Backup Database
  ↓
Get File Size
  ↓
JavaScript (format message)
  ↓
Send Email
  - Subject: Database Backup Complete
  - Body: Backup size: {{size}} MB, File: {{filename}}
  ↓
Disconnect

Backup Before Schema Changes

Connect
  ↓
Backup Database (safety backup)
  ↓
Non Query (ALTER TABLE ...)
  ↓
Non Query (CREATE INDEX ...)
  ↓
Test Changes
  ↓
If (tests pass)
  └─ Keep Changes
Else
  └─ Restore Database (rollback)
  ↓
Disconnect

Platform-Specific Paths

Windows

const backupPath = 'C:\\backups\\mydb.sql';
// or
const backupPath = 'C:/backups/mydb.sql'; // also works

Linux/macOS

const backupPath = '/home/user/backups/mydb.sql';
// or
const backupPath = '/var/backups/mysql/mydb.sql';

See Also

• Restore - Restore database from backup
• Connect - Establish database connections
• Disconnect - Close connections