Automate Database Management on Linux Servers

This n8n workflow provides a framework for automating database management tasks (specifically PostgreSQL and MySQL) on Linux servers using SSH commands. It allows for conditional execution of different database operations based on predefined criteria, making it a flexible solution for various administrative needs.

What it does

This workflow simplifies database administration by:

1. Triggering Manually: The workflow is initiated manually, allowing for on-demand execution of database tasks.
2. Defining Database Type: It sets a variable to specify the database type (e.g., "PostgreSQL" or "MySQL") for subsequent operations.
3. Conditional Logic: It uses an "If" node to branch the workflow based on the defined database type.
   • If PostgreSQL: If the database type is "PostgreSQL", it proceeds to execute PostgreSQL-specific commands via SSH.
   • If MySQL: If the database type is "MySQL", it proceeds to execute MySQL-specific commands via SSH.
4. Executing SSH Commands: It connects to a Linux server via SSH to execute database management commands. The specific commands for PostgreSQL and MySQL are placeholders, ready to be configured for tasks like backup, restore, user management, or status checks.
5. Providing Contextual Notes: Sticky notes are included to provide guidance and explain the purpose of different parts of the workflow.

Prerequisites/Requirements

• n8n Instance: A running n8n instance.
• SSH Credentials: SSH credentials configured in n8n for connecting to your Linux servers. These credentials should have sufficient permissions to execute database commands.
• Linux Servers: Access to Linux servers with PostgreSQL and/or MySQL installed.
• Database Management Commands: Knowledge of the specific PostgreSQL and MySQL commands you wish to automate.

Setup/Usage

1. Import the workflow: Import the provided JSON into your n8n instance.
2. Configure SSH Credentials:
   • Locate the "SSH" nodes (one for PostgreSQL and one for MySQL).
   • Click on the "SSH" node and then on the "Credentials" field.
   • Select or create new SSH credentials that can access your Linux server.
3. Define Database Type:
   • Locate the "Edit Fields" (Set) node.
   • In the "Value" field for the databaseType variable, set it to either "PostgreSQL" or "MySQL" depending on the database you want to manage.
4. Customize SSH Commands:
   • In each "SSH" node (for PostgreSQL and MySQL branches), replace the placeholder commands with your actual database management commands.
   • For example, for PostgreSQL, you might add pg_dump -U your_user -d your_database > /path/to/backup.sql.
   • For MySQL, you might add mysqldump -u your_user -p'your_password' your_database > /path/to/backup.sql.
5. Execute the Workflow: Click the "Execute Workflow" button in the n8n editor to run the workflow manually.

This workflow serves as a robust starting point for building sophisticated database automation routines. You can expand it further by adding more conditional logic, error handling, notifications (e.g., Slack, email), and scheduling capabilities.