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
DELETEwhich 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.
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 :
- On update :
- On delete :
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.