How to connect n8n to MySQL?

Connecting n8n to MySQL involves setting up a MySQL connection node, configuring the database credentials, and testing the connection to ensure n8n can interact with your MySQL database. This allows you to perform operations such as querying data, inserting records, updating tables, and more within your n8n workflows.

 

Step 1: Prerequisites

 

Before connecting n8n to MySQL, ensure you have the following:

• n8n installed and running (either locally, via Docker, or on n8n cloud)
• A running MySQL database
• MySQL database credentials (host, port, username, password, database name)
• MySQL server accessible from the machine running n8n

 

Step 2: Add a MySQL Node to Your Workflow

 

• Open your n8n workflow dashboard
• Create a new workflow or open an existing one where you want to add MySQL connectivity
• In the workflow editor, click on the "+" button to add a new node
• Search for "MySQL" in the node search bar
• Click on the MySQL node to add it to your workflow

 

Step 3: Configure the MySQL Connection

 

Once you've added the MySQL node, you need to configure the connection:

• Click on the MySQL node to open its settings
• Click on "Create new" next to the Credentials field

Fill in the following connection details:

• Credentials Name: Give your connection a name (e.g., "My MySQL Connection")
• Host: Enter your MySQL server hostname or IP address
• Database: Enter the name of the database you want to connect to
• User: Enter the MySQL username
• Password: Enter the MySQL password
• Port: Enter the MySQL port (default is 3306)
• SSL: Toggle this on if your MySQL connection requires SSL

Click on "Create" to save the credentials.

 

Step 4: Test the MySQL Connection

 

After creating the credentials, test the connection to ensure it works properly:

• In the MySQL node configuration, you'll see a "Test" button near the credentials field
• Click "Test" to verify the connection
• If successful, you'll see a green success message
• If there's an error, review your connection details and make necessary adjustments

 

Step 5: Configure the MySQL Operation

 

Now that you have successfully connected to MySQL, configure the operation you want to perform:

• In the MySQL node settings, select an operation from the "Operation" dropdown
• Available operations include:
  • Execute Query: Run a custom SQL query
  • Insert: Add new records to a table
  • Update: Modify existing records in a table
  • Delete: Remove records from a table
  • Select: Retrieve data from a table

 

Step 6: Executing a SQL Query (Example)

 

Let's configure the node to execute a simple SQL query:

• Set "Operation" to "Execute Query"
• In the "Query" field, enter your SQL statement. For example:

SELECT \* FROM customers WHERE country = 'USA' LIMIT 10;

• You can also use expressions to make dynamic queries:

SELECT \* FROM customers WHERE country = '{{$json["country"]}}' LIMIT {{$json["limit"]}};

 

Step 7: Selecting Data from a Table (Example)

 

To retrieve data using the Select operation:

• Set "Operation" to "Select"
• Specify the "Table" you want to query (e.g., "customers")
• In the "Options" section, you can configure:
  • Columns: Specify which columns to return (leave empty for all columns)
  • Where: Add filtering conditions
  • Sort: Define the sorting order
  • Limit: Set a maximum number of returned rows

 

Step 8: Inserting Data into a Table (Example)

 

To insert data into a table:

• Set "Operation" to "Insert"
• Specify the "Table" where you want to insert data (e.g., "customers")
• Under "Columns", click "Add Column" to specify column name and value pairs
• For each column:
  • Enter the "Column" name (e.g., "first\_name")
  • Set the "Value" for the column (e.g., "John")
  • You can use expressions for dynamic values: {{$json["first\_name"]}}

 

Step 9: Updating Data in a Table (Example)

 

To update existing records:

• Set "Operation" to "Update"
• Specify the "Table" you want to update
• Add the columns and values you want to update using "Add Column"
• Under "Options", set the "Where" clause to determine which records to update:
  • Example: id = 5 or email = '{{$json["email"]}}'

 

Step 10: Deleting Data from a Table (Example)

 

To delete records:

• Set "Operation" to "Delete"
• Specify the "Table" from which you want to delete records
• Under "Options", set the "Where" clause to determine which records to delete:
  • Example: id = {{$json["id_to_delete"]}}
• Important: Be cautious with delete operations. Without a Where clause, all records in the table will be deleted!

 

Step 11: Handling Multiple Queries with Multi-Query Node

 

If you need to execute multiple SQL statements:

• Use the "MySQL Multi Query" node instead of the regular MySQL node
• Configure the connection credentials as described earlier
• Add multiple queries separated by semicolons:

INSERT INTO log (action) VALUES ('start');
UPDATE products SET price = price \* 1.1 WHERE category = 'electronics';
INSERT INTO log (action) VALUES ('end');

 

Step 12: Working with Query Parameters

 

For better security and to prevent SQL injection, use parameterized queries:

• In the "Execute Query" operation, use question marks (?) as placeholders:

SELECT \* FROM customers WHERE country = ? AND status = ?;

• Then, provide the parameter values in the "Query Parameters" section:
  • Click "Add Parameter" for each placeholder
  • Enter values in the same order as they appear in the query
  • You can use expressions: {{$json["country"]}}

 

Step 13: Connecting MySQL to Other Nodes

 

To create a complete workflow:

• Connect trigger nodes (like HTTP Request, Schedule, or Webhook) to initiate your workflow
• Process data before sending it to MySQL using Function nodes or other transformation nodes
• Connect the output of the MySQL node to subsequent nodes to process the query results
• Example flow: Webhook → Function Node → MySQL → Send Email

 

Step 14: Error Handling for MySQL Operations

 

Add error handling to your MySQL workflows:

• Use an "Error Trigger" node to catch and handle database errors
• Connect your MySQL node to an "IF" node to check for successful execution
• Use a "Function" node to validate data before sending it to MySQL

Example function for data validation:

// Check if required fields exist
if (!$input.json["customer\_name"] || !$input.json["email"]) {
  return [];  // Return empty to stop execution
}

// Sanitize data
const sanitized = {
  customer_name: $input.json["customer_name"].trim(),
  email: $input.json["email"].toLowerCase().trim()
};

return { json: sanitized };

 

Step 15: Creating Transactions for Multiple Operations

 

For operations that need to be executed as a single transaction:

• Use the "Execute Query" operation with transaction statements:

START TRANSACTION;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (42, 101, 2);
UPDATE inventory SET stock = stock - 2 WHERE product\_id = 101;
COMMIT;

• To handle potential errors and roll back:

START TRANSACTION;
INSERT INTO orders (customer_id, product_id, quantity) VALUES (42, 101, 2);
UPDATE inventory SET stock = stock - 2 WHERE product\_id = 101;
-- Check if stock went negative
SELECT stock FROM inventory WHERE product_id = 101 INTO @current_stock;
-- Rollback if we have negative stock
SET @continue = IF(@current\_stock >= 0, 1, 0);
-- Conditionally commit or rollback
COMMIT AND CHAIN IF @continue = 1;
ROLLBACK IF @continue = 0;

 

Step 16: Performance Considerations

 

Optimize your MySQL operations for better performance:

• Use indexing for columns used in WHERE clauses
• Limit the number of rows returned by using LIMIT
• Select only the columns you need rather than using SELECT \*
• For large datasets, use pagination in your queries:

SELECT id, name, email FROM customers 
WHERE created\_at > '2023-01-01' 
ORDER BY id 
LIMIT 100 OFFSET {{$json["page"] \* 100}};

 

Step 17: Securing Your MySQL Connection

 

Implement security best practices:

• Use SSL for connections if your MySQL server supports it
• Create a dedicated MySQL user with minimal privileges for n8n
• Store sensitive credentials securely in environment variables:
  • In your n8n environment, set environment variables like N8N_MYSQL_PASSWORD
  • Reference them in credential fields with expressions like {{$env.N8N_MYSQL_PASSWORD}}
• Regularly review MySQL audit logs to monitor for unusual activities

 

Step 18: Handling Blob and Binary Data

 

To work with BLOB data types:

• For inserting binary data, convert it to Base64:

INSERT INTO documents (filename, file\_data) 
VALUES ('report.pdf', FROM\_BASE64('{{$binary\["data"]\["base64"]}}'));

• For retrieving binary data:

SELECT id, filename, TO_BASE64(file_data) as file_data_base64 
FROM documents WHERE id = 123;

 

Step 19: Working with JSON Data in MySQL

 

If your MySQL database (version 5.7+) uses JSON columns:

• To insert JSON data:

INSERT INTO user_settings (user_id, preferences) 
VALUES (42, '{{JSON.stringify($json["preferences"])}}');

• To extract values from JSON columns:

SELECT id, user\_id, 
JSON\_EXTRACT(preferences, '$.theme') as theme,
JSON_EXTRACT(preferences, '$.notifications.email') as email_notifications
FROM user_settings WHERE user_id = 42;

 

Step 20: Troubleshooting Common Issues

 

If you encounter connection problems:

• Connection Refused: Verify the host and port are correct, and that your MySQL server allows connections from the n8n host
• Access Denied: Check your username and password
• Unknown Database: Verify the database name exists and the user has access to it
• Syntax Errors: Validate your SQL queries for proper syntax
• Connection Timeouts: Check network connectivity and MySQL server load
• SSL Issues: If using SSL, ensure certificates are valid and properly configured

Database-specific error solutions:

• If MySQL error code 1045: Check user privileges
• If MySQL error code 2003: Verify server is running and accessible
• If MySQL error code 1049: Create the database or check its name

 

Step 21: Advanced: Using MySQL with Other n8n Features

 

Combine MySQL with other n8n features for powerful workflows:

• Splitting: Use the "Split In Batches" node to process MySQL results in smaller chunks
• Mapping: Use the "Item Lists" node to transform MySQL result sets
• Aggregation: Use Function nodes to perform calculations on MySQL data
• Scheduling: Use Cron nodes to schedule regular MySQL operations (like database cleanups)

Example workflow for database backup notification:

• Cron node (daily trigger)
• MySQL node (execute SHOW TABLES query)
• Function node (prepare backup command)
• Execute Command node (run mysqldump)
• Send Email node (notify admin about backup status)

 

Step 22: Practical Example: Building a Complete MySQL Workflow

 

Let's build a complete workflow that imports data from an API into MySQL:

• Step 1: Add an HTTP Request node to fetch data from an API
• Step 2: Configure HTTP node to GET data from https://api.example.com/users
• Step 3: Add a Function node to transform the API data to match your database schema:

// Transform API data to database format
return $input.json.map(user => {
  return {
    json: {
      user\_id: user.id,
      full\_name: `${user.first_name} ${user.last_name}`,
      email: user.email.toLowerCase(),
      created\_at: new Date().toISOString().slice(0, 19).replace('T', ' ')
    }
  };
});

• Step 4: Add a MySQL node configured for "Insert" operation:
• Table: users
• Columns:
  • user_id: {{$json["user_id"]}}
  • full_name: {{$json["full_name"]}}
  • email: {{$json["email"]}}
  • created_at: {{$json["created_at"]}}
• Step 5: Add a Slack node to notify about the import status

Connect the nodes in sequence: HTTP Request → Function → MySQL → Slack

This workflow fetches user data from an API, transforms it to match your database schema, inserts it into your MySQL database, and sends a notification when complete.

 

Conclusion

 

You've now learned how to connect n8n to MySQL and perform various database operations. This integration enables you to build powerful workflows that interact with your MySQL database. You can read and write data, execute custom queries, and combine database operations with other n8n nodes for comprehensive automation solutions.

By following this guide, you should be able to incorporate MySQL database interactions into your n8n workflows, whether for data synchronization, reporting, user management, or any other database-related automation task.