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:

mysql_native_password
caching_sha2_password

Any idea how to overcome this?

Thank you.

Is node connecting as user root, without a password? If so, see: stackoverflow.com/a/62056425/724039 — Luuk
– Luuk, Commented Dec 2, 2023 at 12:18
Thank you for pointing this article out. No, my initial Node.js program was connecting with a less privileged user, which had a password. — Ioan
– Ioan, Commented Dec 3, 2023 at 14:33

user1191247 · Accepted Answer · 2023-12-02 19:25:44Z

0

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

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';

Install mysql and @rodrigogs/mysql-events:

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

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'
}

answered Dec 2, 2023 at 19:25

user1191247

13k2 gold badges25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ioan Over a year ago

This worked. I only needed to execute the following additional instructions: ` CREATE DATABASE test; SET NAMES 'utf8'; USE test; CREATE TABLE articles ( id INT UNSIGNED DEFAULT NULL, catid INT UNSIGNED DEFAULT NULL, subject VARCHAR(255) DEFAULT NULL, content VARCHAR(255) DEFAULT NULL ) ENGINE = INNODB, CHARACTER SET utf8mb4, COLLATE utf8mb4_0900_ai_ci, ROW_FORMAT = DYNAMIC; ` Thank you very much.

Dedmin25 Oct 10 at 3:43

I just want to say thank you, I search around for make it happen and your code is work on me. Thank you so much

Collectives™ on Stack Overflow

Live monitoring of MySQL from Node.js

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related