Set Up MySQL Table Update Alerts with Email: Step-by-Step Guide

Published Dec 18, 2025

Learn practical ways to send email alerts for MySQL table updates using triggers, polling, binlog CDC, and Python or Node scripts.

Set Up MySQL Table Update Alerts with Email: Step-by-Step Guide

Whether you manage operational dashboards or need to notify stakeholders when critical data changes, creating MySQL table update alerts with email is a straightforward way to turn database activity into actionable signals. This guide walks through practical, production-friendly patterns you can implement today—ranging from lightweight polling to trigger-based outbox tables and change data capture (CDC) via the binary log. You’ll see code examples in Python and Node.js, schema snippets, and best practices for performance, security, and deliverability.

What do we mean by “MySQL table update alerts with email”?

In most teams, an “alert” means an automated message when something important changes. For MySQL, that typically includes:

  • UPDATE: Row values changed (for example, status moved to failed).
  • INSERT: New rows matching a condition (for example, a purchase above $10,000).
  • DELETE: Rows removed that you need to audit.

MySQL does not natively send emails. Instead, you pair MySQL with an external worker that detects changes and dispatches email via SMTP or an email API.

Common architectures for MySQL update email alerts

Approach Setup Time Pros Cons Best For
Polling query (updated_at watermark) Low Simple, no DB changes beyond an index; easy to tune Small delay between checks; requires an updated_at column Small teams, quick wins, single table
Trigger + Outbox/Audit table Medium Guaranteed capture of changes; minimal polling cost More schema objects; trigger maintenance Compliance/auditing, multiple tables
Binlog CDC (e.g., Debezium) Higher Near real-time, scalable across many tables Infrastructure overhead (Kafka/Connect); operational complexity High-traffic systems, enterprise streaming

Prerequisites and planning

  • Decide what “important change” means: which table(s), which columns, and which conditions (e.g., status = 'failed').
  • Choose an email channel: SMTP (Gmail, Microsoft 365, Postfix) or API (Amazon SES, SendGrid, Mailgun).
  • Pick an alert strategy: immediate one-by-one emails or aggregated digests (every 5 minutes/hourly).
  • Plan for idempotency: ensure each change triggers at most one email.

Option 1: Lightweight polling with an updated_at watermark

This method periodically queries rows updated since the last check and then emails any that match your criteria. It’s simple, reliable, and great for most teams.

Step 1: Ensure your table has an updated_at column and an index

ALTER TABLE orders
  ADD COLUMN updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  ADD INDEX idx_orders_updated_at (updated_at),
  ADD INDEX idx_orders_status_updated_at (status, updated_at);

The compound index lets you filter by a status and time window efficiently.

Step 2: Write a polling script

The Python example below checks for orders updated since the last run where status = 'failed'. It sends a single aggregated email when new rows are found.

# requirements: mysql-connector-python, requests (if using an email API), or none for SMTP
import os
import smtplib
import ssl
from datetime import datetime, timezone
from email.mime.text import MIMEText
import mysql.connector

STATE_FILE = ".last_seen.txt"
LAST_SEEN_DEFAULT = "1970-01-01T00:00:00Z"

DB_CFG = {
    "host": os.getenv("DB_HOST", "127.0.0.1"),
    "user": os.getenv("DB_USER", "readonly"),
    "password": os.getenv("DB_PASS"),
    "database": os.getenv("DB_NAME", "appdb"),
}

SMTP_CFG = {
    "host": os.getenv("SMTP_HOST", "smtp.gmail.com"),
    "port": int(os.getenv("SMTP_PORT", "587")),
    "user": os.getenv("SMTP_USER"),
    "password": os.getenv("SMTP_PASS"),
    "from_addr": os.getenv("FROM_EMAIL", "alerts@example.com"),
    "to_addrs": os.getenv("TO_EMAILS", "ops@example.com").split(","),
}


def read_last_seen():
    if not os.path.exists(STATE_FILE):
        return LAST_SEEN_DEFAULT
    return open(STATE_FILE).read().strip()


def write_last_seen(ts):
    with open(STATE_FILE, "w") as f:
        f.write(ts)


def fetch_updates(since_iso):
    cnx = mysql.connector.connect(**DB_CFG)
    cur = cnx.cursor(dictionary=True)
    q = (
        "SELECT id, customer_email, status, amount, updated_at "
        "FROM orders WHERE status = 'failed' AND updated_at > %s "
        "ORDER BY updated_at ASC LIMIT 1000"
    )
    cur.execute(q, (since_iso.replace("Z", "").replace("T", " "),))
    rows = cur.fetchall()
    cur.close()
    cnx.close()
    return rows


def send_email(subject, body):
    msg = MIMEText(body)
    msg["Subject"] = subject
    msg["From"] = SMTP_CFG["from_addr"]
    msg["To"] = ", ".join(SMTP_CFG["to_addrs"])

    context = ssl.create_default_context()
    with smtplib.SMTP(SMTP_CFG["host"], SMTP_CFG["port"]) as server:
        server.starttls(context=context)
        server.login(SMTP_CFG["user"], SMTP_CFG["password"])
        server.sendmail(SMTP_CFG["from_addr"], SMTP_CFG["to_addrs"], msg.as_string())


def main():
    last_seen = read_last_seen()
    rows = fetch_updates(last_seen)
    if not rows:
        print("No updates")
        return

    lines = [
        "New failed orders detected:",
        "",  # blank line
    ]
    for r in rows:
        lines.append(f"- id={r['id']}, amount={r['amount']}, updated_at={r['updated_at']}, customer={r['customer_email']}")

    send_email("[ALERT] Failed orders updated", "\n".join(lines))

    # Advance watermark to the newest row's timestamp
    newest_ts = max(r["updated_at"] for r in rows)
    newest_iso = newest_ts.replace(tzinfo=timezone.utc).isoformat().replace("+00:00", "Z")
    write_last_seen(newest_iso)


if __name__ == "__main__":
    main()

Deploy this as a cron job or a scheduled task (for example, every minute). For higher rate tables, consider batching and sending digest emails every 5–15 minutes.

Optional: Node.js email instead of SMTP

If you prefer Node, use nodemailer with an email provider:

// npm i mysql2 nodemailer
const mysql = require('mysql2/promise');
const nodemailer = require('nodemailer');

async function main() {
  const conn = await mysql.createConnection({host: '127.0.0.1', user: 'readonly', database: 'appdb', password: process.env.DB_PASS});
  const [rows] = await conn.execute("SELECT id, updated_at FROM orders WHERE status='failed' AND updated_at > NOW() - INTERVAL 5 MINUTE ORDER BY updated_at ASC");
  await conn.end();

  if (!rows.length) return;

  const transporter = nodemailer.createTransport({
    host: process.env.SMTP_HOST,
    port: 587,
    secure: false,
    auth: { user: process.env.SMTP_USER, pass: process.env.SMTP_PASS }
  });

  await transporter.sendMail({
    from: 'alerts@example.com',
    to: 'ops@example.com',
    subject: '[ALERT] Recent failed orders',
    text: rows.map(r => `id=${r.id} @ ${r.updated_at.toISOString()}`).join('\n')
  });
}

main().catch(console.error);

Option 2: Trigger-based outbox/audit table

Triggers capture changes at write time, inserting a normalized record into an audit or outbox table. A notifier process then reads the outbox and sends emails. This reduces polling complexity and avoids missing updates even if the notifier is briefly down.

Step 1: Create an outbox table

CREATE TABLE order_update_outbox (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  order_id BIGINT NOT NULL,
  event_type ENUM('insert','update','delete') NOT NULL,
  payload JSON NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  processed TINYINT(1) NOT NULL DEFAULT 0,
  INDEX idx_processed_created (processed, created_at),
  INDEX idx_created_at (created_at)
);

Step 2: Add triggers on the source table

DELIMITER //
CREATE TRIGGER orders_after_update
AFTER UPDATE ON orders FOR EACH ROW
BEGIN
  IF NEW.status <> OLD.status THEN
    INSERT INTO order_update_outbox (order_id, event_type, payload)
    VALUES (
      NEW.id,
      'update',
      JSON_OBJECT(
        'status_before', OLD.status,
        'status_after', NEW.status,
        'amount', NEW.amount,
        'updated_at', NOW()
      )
    );
  END IF;
END//
DELIMITER ;

You can also add AFTER INSERT or AFTER DELETE triggers depending on your needs.

Step 3: Notifier processes and sends emails

-- pseudo-SQL loop the notifier would implement
SELECT id, order_id, payload FROM order_update_outbox
WHERE processed = 0 ORDER BY created_at ASC LIMIT 100;
-- send emails
UPDATE order_update_outbox SET processed = 1 WHERE id IN (...);

Use the same SMTP or provider code as in Option 1, but fetch from the outbox table instead of the source table. This pattern is robust and minimizes re-querying large source tables.

Option 3: Binlog CDC with Debezium

For high throughput or many tables, consuming MySQL’s binary log (binlog) gives near real-time streams of changes without modifying schemas. A common path is MySQL → Debezium (Kafka Connect) → Kafka topic → alert function (serverless or container) → email.

Minimal setup outline

  1. Enable row-based binlog on MySQL: binlog_format=ROW, set a unique server_id, and ensure a CDC user with REPLICATION privileges.
  2. Run Kafka, Zookeeper (if needed), and Kafka Connect with the Debezium MySQL connector.
  3. Configure the connector to watch your database and tables; changes flow into Kafka topics per table.
  4. Write a small consumer that filters for your condition (e.g., status transitions) and sends emails.
{
  "name": "mysql-orders-connector",
  "config": {
    "connector.class": "io.debezium.connector.mysql.MySqlConnector",
    "database.hostname": "mysql",
    "database.user": "cdc",
    "database.password": "<redacted>",
    "database.server.id": "184054",
    "database.server.name": "db",
    "database.include.list": "appdb",
    "table.include.list": "appdb.orders",
    "include.schema.changes": "false",
    "tombstones.on.delete": "false",
    "decimal.handling.mode": "double"
  }
}

This approach scales well and decouples producers (database) from consumers (alerting), but it introduces more infrastructure to manage.

Email design: content, batching, and throttling

  • Subject lines: make the signal obvious. Example: [ALERT] orders.status → failed (12 new).
  • Batching: avoid inbox spam. Aggregate changes over 1–15 minutes or use thresholds (send only if >= N changes).
  • Digest vs. immediate: immediate for time-sensitive failures; digests for routine updates.
  • Rate limits: implement a minimum interval between emails and exponential backoff on provider errors.
  • Plain text fallback: even if you send HTML, include a text version for deliverability.

Sample plain-text email template

Subject: [ALERT] orders status changed to failed ({{count}})

The following orders changed status to failed:

{{#each rows}}
- id={{id}} amount={{amount}} updated_at={{updated_at}} customer={{customer_email}}
{{/each}}

Action: Investigate payment gateway or retry logic.
Runbook: https://internal/wiki/runbooks/payments

Security and performance best practices

  • Least privilege: use a dedicated read-only MySQL user for polling; for outbox patterns, grant only INSERT/SELECT/UPDATE on the outbox table.
  • Prepared statements: avoid interpolation to prevent SQL injection in dynamic filters.
  • Indexing: ensure selective indexes match your WHERE clause, e.g., (status, updated_at) or (updated_at).
  • Small pages: cap each polling cycle (e.g., LIMIT 1000) and loop if more results exist.
  • Clock skew: store watermarks from the database time (e.g., NOW()) and use inclusive/exclusive bounds carefully to avoid missing rows.
  • Idempotency: mark processed events (outbox processed=1) or store a high-water mark to prevent duplicate emails.
  • Secrets: keep SMTP/API keys in environment variables or a secret manager. Do not hardcode credentials.
  • Deliverability: use SPF, DKIM, and DMARC; consider a dedicated subdomain (e.g., alerts.example.com).

Testing checklist

  • Unit test your query filters: verify only the intended updates get picked up.
  • Load test with a burst of updates: confirm batching and throttling logic works.
  • Failure injection: simulate SMTP outage; ensure retries and alert suppression avoid floods.
  • Observability: log counts, last watermark, and email response codes; add metrics (successes, failures, latency).
  • Runbook link in emails: include where to look next when an alert fires.

Choosing the right approach

If you need something running today with minimal effort, start with polling on an updated_at index and send an aggregated email. If you require stricter guarantees and auditing, implement triggers with an outbox table. For large, multi-team systems with many tables, consider binlog CDC. Each pattern can evolve into the next as your needs grow.

Tip: Keep alerts short, actionable, and tied to a runbook. Your future self (and your on-call rotation) will thank you.

Prefer a managed experience on mobile? The iOS app Query Alert connects to MySQL and other databases, runs read-only checks on a schedule, and can send push or email notifications when your query conditions change.