新手求助：基于Node.js实现全MySQL数据库insert/update/delete操作追踪模块

Hey there! As a fellow Node.js developer who's built something similar before, let me walk you through exactly how to create this MySQL operation tracker. Even if you're new to Node, this is totally manageable—let's break it down step by step.

核心思路 #

To capture every INSERT/UPDATE/DELETE operation (no matter which client executes it, like MySQL Workbench), we'll leverage MySQL's Binary Log (binlog). This log records all data changes at the row level, making it perfect for getting detailed, real-time operation data.

Step 1: Configure MySQL to Enable Binlog #

First, you need to make sure MySQL is set up to generate binlogs. Here's what to do:

• Open your MySQL configuration file (usually my.cnf on Linux, my.ini on Windows).
• Add or update these settings:

  log_bin = /var/log/mysql/mysql-bin.log  # Adjust the path to match your system
  binlog_format = ROW                     # Critical: This captures row-level changes
  server_id = 1                           # Must be a unique number (1-2^32-1)
  

• Restart your MySQL service to apply the changes.

Step 2: Create a MySQL User with Required Permissions #

Your Node.js app needs a user with replication privileges to read the binlog. Run these SQL commands in MySQL Workbench:

CREATE USER 'binlog_reader'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'binlog_reader'@'localhost';
FLUSH PRIVILEGES;

Step 3: Set Up the Node.js Project #

1. Initialize a new Node project (if you haven't already):

   npm init -y
   

2. Install the mysql2 package—it has built-in support for reading MySQL binlogs:

   npm install mysql2
   

Step 4: Write the Tracking Code #

Create a file (e.g., mysql-tracker.js) with this code. I've added detailed comments to explain each part:

const mysql = require('mysql2/promise');
const { Binlog } = require('mysql2/binlog');

async function startOperationTracker() {
  // Connect to MySQL to get the latest binlog position (optional but recommended)
  const dbConnection = await mysql.createConnection({
    host: 'localhost',
    user: 'binlog_reader',
    password: 'your_secure_password',
    database: 'my_work'
  });

  // Initialize the binlog listener
  const binlog = new Binlog({
    serverId: 2,  // Must be different from MySQL's server_id (we used 1 earlier)
    host: 'localhost',
    user: 'binlog_reader',
    password: 'your_secure_password'
  });

  // Listen for row-level change events
  binlog.on('row', (event) => {
    // Only care about INSERT/UPDATE/DELETE operations
    const relevantEvents = ['WRITE_ROWS', 'UPDATE_ROWS', 'DELETE_ROWS'];
    if (!relevantEvents.includes(event.eventName)) return;

    // Map binlog event names to user-friendly operation labels
    const operationLabels = {
      WRITE_ROWS: 'INSERT',
      UPDATE_ROWS: 'UPDATE',
      DELETE_ROWS: 'DELETE'
    };

    // Extract key details from the event
    const operation = operationLabels[event.eventName];
    const database = event.table.schema;
    const table = event.table.name;
    const rows = event.rows;

    // Print the details to the console in a readable format
    console.log('\n=== 🔍 数据库操作捕获 ===');
    console.log(`操作类型: ${operation}`);
    console.log(`数据库: ${database}`);
    console.log(`表名: ${table}`);
    console.log(`操作数据:`);
    
    if (operation === 'UPDATE') {
      // For updates, show both old and new row data
      rows.forEach((row, index) => {
        console.log(`  行 ${index + 1}:`);
        console.log(`    旧数据:`, row.before);
        console.log(`    新数据:`, row.after);
      });
    } else {
      // For inserts/deletes, show the affected row data
      rows.forEach((row, index) => {
        console.log(`  行 ${index + 1}:`, row[operation.toLowerCase()]);
      });
    }
    console.log(`=====================\n`);
  });

  // Handle errors gracefully
  binlog.on('error', (error) => {
    console.error('❌ Binlog监听出错:', error);
  });

  // Get the latest binlog position to start tracking from now
  const [masterStatus] = await dbConnection.execute('SHOW MASTER STATUS');
  const startOptions = {
    filename: masterStatus[0].File,
    position: masterStatus[0].Position,
    // Optional: Filter to only track the 'my_work' database
    filter: {
      schema: 'my_work'
    }
  };

  // Start the tracker
  await binlog.start(startOptions);
  console.log('✅ MySQL操作追踪器已启动，正在监控my_work数据库的变更...');
}

// Start the tracker and handle any startup errors
startOperationTracker().catch((error) => {
  console.error('❌ 追踪器启动失败:', error);
});

Step 5: Test It Out #

1. Run your Node.js app:

   node mysql-tracker.js
   

2. Open MySQL Workbench, connect to your my_work database, and run an INSERT, UPDATE, or DELETE query. You should see the detailed operation data pop up in your Node.js console immediately!

Important Notes #

• Binlog Format: Make sure binlog_format is set to ROW—this is the only way to get the actual row data (not just the SQL query).
• Permissions: The MySQL user must have REPLICATION SLAVE and REPLICATION CLIENT permissions to read the binlog.
• Server ID: The serverId in your Node code must be unique (different from MySQL's own server_id).
• Filtering: If you want to track all databases, remove the filter option from the binlog.start() call.

内容的提问来源于stack exchange，提问作者Sumit Sharma