Connecting and Automating n8n with MySQL Databases

Connecting n8n with a MySQL database allows you to create powerful, automated workflows that can read, write, and update your data without manual intervention. This integration is primarily achieved using the dedicated MySQL node within n8n, enabling you to perform actions like selecting records, inserting new rows from form submissions, updating inventory levels, or deleting outdated information. By linking your n8n MySQL database to other applications like Google Sheets, Slack, or your CRM, you can build sophisticated systems for data synchronization, real-time reporting, and process automation, transforming your static database into a dynamic, central hub for your business operations.

The Two Faces of n8n and MySQL: Node vs. Backend

When we talk about using an n8n MySQL database, it’s crucial to understand two very different contexts. Think of it like a workshop. You have the workshop’s foundation and the tools you use inside it.

1. The MySQL Node (The Tool): This is what 99% of users will mean. The MySQL node is a tool you drag onto your n8n canvas. It allows your workflows to connect to an external MySQL database to fetch, insert, or update data. This is where the real automation magic happens!

2. The Backend Database (The Foundation): For self-hosted n8n instances, n8n needs its own database to store your workflows, credentials, and execution logs. By default, it uses SQLite, but it can be configured to use a more robust database. MySQL was an option for this.

Now, let’s be transparent about this. Based on community discussions and statements from the n8n team, the focus for the n8n backend has shifted. Supporting multiple database types for the backend is resource-intensive. Since the vast majority of users who switch from SQLite opt for PostgreSQL, Postgres is now the recommended and future-proof choice for self-hosting n8n’s backend database.

So, for the rest of this guide, we’ll focus on the most common and powerful use case: using the MySQL node to automate tasks with your application’s database.

Getting Started: Connecting n8n to Your MySQL Database

Before you can start querying, you need to securely tell n8n how to access your database. This is done by creating a credential. I’ve seen countless people try to put their credentials directly in the node, but creating a dedicated credential is more secure and lets you reuse it across all your workflows.

Creating Your MySQL Credentials in n8n

It’s a straightforward process:

1. In your n8n sidebar, navigate to the Credentials section.
2. Click Add credential.
3. Search for MySQL and select it.
4. You’ll be presented with a form. Fill in the following details:
   • Host: The IP address or hostname of your database server (e.g., 127.0.0.1 or db.mycompany.com).
   • Database: The name of the specific database you want to connect to.
   • User: The username for accessing the database.
   • Password: The password for that user.
   • Port: The port your MySQL server is running on. The default is 3306.

A quick pro-tip: Please don’t use your root database user! It’s a huge security risk. Create a dedicated user for n8n with only the permissions it needs (e.g., SELECT, INSERT, UPDATE on specific tables). Also, if your database is behind a firewall, you’ll need to whitelist the IP address of your n8n instance (whether it’s n8n Cloud or your own server) to allow the connection.

Core Operations: What Can You Do with the MySQL Node?

So, you’re connected. Now what? The MySQL node is beautifully simple, offering a handful of core operations that cover almost any database task you can imagine.

Here’s a quick rundown:

Real-World Automation: From Raw Data to Actionable Insights

Let’s put this into practice with a common e-commerce scenario. We want to keep a master customer list in MySQL updated automatically whenever an order is placed in our Shopify store.

The Goal: When a new order is paid on Shopify, check our customers table in MySQL. If the customer is new, add them. If they’re a returning customer, update their last_purchase_date and total_spent.

The n8n Workflow would look like this:

1. Shopify Trigger Node: Set to trigger on the “Order Paid” event. This node will kick off our workflow and provide all the order data, including the customer’s email and the order total.

2. MySQL Node (Select): This is our first check. We’ll configure it to:

   • Operation: Select
   • Table: customers
   • Conditions: email = {{ $json.body.email }} (This expression dynamically pulls the customer’s email from the Shopify data).

3. IF Node: This node is our decision-maker. It will check the output of the previous step. The condition will be: {{ $node['MySQL'].json.length > 0 }}. In plain English, this asks, “Did the ‘Select’ node find at least one matching customer?”

4. True Path (Customer Exists): If the IF node is true, we connect it to another MySQL Node (Update).

   • Operation: Update
   • Table: customers
   • Conditions: email = {{ $json.body.email }}
   • Columns to Update:
     • last_purchase_date: {{ new Date().toISOString() }}
     • total_spent: {{ $node['MySQL'].json[0].total_spent + $json.body.total_price }} (Here we’re using an expression to add the new order total to the existing total spent).

5. False Path (New Customer): If the IF node is false, we connect it to a MySQL Node (Insert).

   • Operation: Insert
   • Table: customers
   • Columns to Insert: name, email, total_spent, first_purchase_date with values mapped from the Shopify Trigger node.

Once activated, this workflow runs silently in the background, ensuring your central customer database is always perfectly in sync with your sales, no copy-pasting required!

Pro-Tips and Common Pitfalls

As you get more advanced, you might run into a few tricky spots. Here’s how to navigate them:

• Connection Errors: If you see PROTOCOL_CONNECTION_LOST or timeout errors, it’s almost always a network issue. Double-check your host, port, and password. Most importantly, ensure your database server’s firewall is allowing connections from your n8n instance’s IP address.
• Handling NULLs: Sometimes, incoming data might be missing a value. If you try to insert a blank field into a non-nullable database column, you’ll get an error. You can use an n8n expression to provide a default value, like {{ $json.phoneNumber || 'N/A' }}.
• Data Formatting: A classic gotcha! A value from Google Sheets might look like a number, but n8n sees it as a string ("123"). If your database column expects an integer, the query will fail. Use expressions like {{ parseInt($json.quantity) }} to convert the data to the correct type before sending it to the MySQL node.