Monitoring MySQL data changes in Real-time via Nodejs & binary logs

You might want to capture the changes in MySQL data tables to notify an application or sending an email without changing a previous codebase.

I had this use case once and there is not a lot online that could help you, so after googling and trying things for hours I came across a library called “mysql-events”, which watches binary logs for MySQL.

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.

Certain data recovery operations require the use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.

— — source

Before we start we need to enable binary logs in MySQL, by changing my.ini file in windows and my.cnf in ubuntu.

  • The location of these files might be different, based on how you install mysql.

we need to add the following lines under [mysqld] section, and then restart mysql.

log-bin=bin.log
log-bin-index=bin-log.index
max_binlog_size=100M
binlog_format=row
socket=mysql.sock

Read more here & here.

Now the NodeJs script this is pretty simple thanks to ‘mysql-events’.

  • I will start in an empty directory and type npm init -y.
  • npm i @rodrigogs/mysql-events
  • I will create an index.js file, that monitors mysql binary logs events.
  • I will keep this very simple because what you do with the events is up to you, logging them via console.log will be enough.

In MySQL 8+ binary logs are enabled by default.

The result of the above code :

  • I have a database called TEST and a table called test_table (name: varchar(50), age int)
  • On Insert :
SQL was `INSERT INTO test_table VALUES(‘rowad’, 1)`
  • On update :
Image for post
Image for post
SQL was `UPDATE test_table SET age = 3 WHERE age = 1;` and there was 5 rows
  • On delete :
Image for post
Image for post
SQL was `DELETE test_table;` which deleted all the 19 rows

I found this to be really useful when I need to update some UI in real-time using web-sockets or calling another app using a message broadcast tool like Redis’ Pub/Sub or RabbitMQ Pub/Sub.

I hope this was helpful to you.

Written by

Software engineer at Edraak https://www.edraak.org/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store