Live monitoring of MySQL from Node.js

Question

I have a MySQL server (version 8.0.35-0ubuntu0.20.04.1) and would like to monitor UPDATE and INSERT operations in a particular MySQL table from a Node.js application. These changes should come from anywhere, not only from the Node.js application itself.

I found some documentation about an old NPM package called @rodrigogs/mysql-events.

When trying to implement the sample code in the documentation of this package, I got the following error:

ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client

I am aware that each MySQL users has a certain authentication method. I tried both of the following methods, but to no avail:

1. mysql_native_password
2. caching_sha2_password

Any idea how to overcome this?

Thank you.

Answer 1

I ran through the following steps and it worked fine for me.

1. Create new user with replication and select privileges on the target database:

CREATE USER 'test_native_user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test_native_password';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'test_native_user'@'localhost';
GRANT SELECT ON test.* TO 'test_native_user'@'localhost';

2. Install mysql and @rodrigogs/mysql-events:

npm install --save mysql @rodrigogs/mysql-events

3. Used the following code from the Quick Start example to receive events for test.articles table:

const mysql = require('mysql');
const MySQLEvents = require('@rodrigogs/mysql-events');

const program = async () => {
  const connection = mysql.createConnection({
    host: 'localhost',
    user: 'test_native_user',
    password: 'test_native_password'
  });

  const instance = new MySQLEvents(connection, {
    startAtEnd: true,
    includeSchema: {
        'test': ['articles']
    }
  });

  await instance.start();

  instance.addTrigger({
    name: 'TEST',
    expression: '*',
    statement: MySQLEvents.STATEMENTS.ALL,
    onEvent: (event) => { // You will receive the events here
      console.log(event);
    }
  });

  instance.on(MySQLEvents.EVENTS.CONNECTION_ERROR, console.error);
  instance.on(MySQLEvents.EVENTS.ZONGJI_ERROR, console.error);
};

program()
  .then(() => console.log('Waiting for database events...'))
  .catch(console.error);

Running the following three queries against the test.articles table:

INSERT INTO articles VALUES (7, 2, 'subject 7', 'content 7');
UPDATE articles SET subject = 'subject 77' WHERE id = 7;
DELETE FROM articles WHERE id = 7;

I received the following output to the console:

>node run.js
Waiting for database events...
{
  type: 'INSERT',
  schema: 'test',
  table: 'articles',
  affectedRows: [ { after: [Object], before: undefined } ],
  affectedColumns: [ 'id', 'catid', 'subject', 'content' ],
  timestamp: 1701544533000,
  nextPosition: 691841809,
  binlogName: 'bin.000132'
}
{
  type: 'UPDATE',
  schema: 'test',
  table: 'articles',
  affectedRows: [ { after: [Object], before: [Object] } ],
  affectedColumns: [ 'subject' ],
  timestamp: 1701544536000,
  nextPosition: 691842155,
  binlogName: 'bin.000132'
}
{
  type: 'DELETE',
  schema: 'test',
  table: 'articles',
  affectedRows: [ { after: undefined, before: [Object] } ],
  affectedColumns: [ 'id', 'catid', 'subject', 'content' ],
  timestamp: 1701544540000,
  nextPosition: 691842466,
  binlogName: 'bin.000132'
}