MysqlRepl

A MySQL binlog listener running on Node.js.

This package is a pure JS implementation based on mysql. It has been tested to work in MySQL 5.5, 5.6, and 5.7.

Quick Start

let mysqlrepl = new MysqlRepl({ /* ... MySQL Connection Settings ... */ });

// Each change to the replication log results in an event
mysqlrepl.on('binlog', function(event) {
  event.dump();
});

// Binlog must be started, optionally pass in filters
mysqlrepl.start({
  includeEvents: ['tablemap', 'writerows', 'updaterows', 'deleterows']
});

Installation

• Requires Node.js v12+

  $ npm install mysqlrepl

• Enable MySQL binlog of type row in my.cnf, restart MySQL server after making the changes.

  # Must be unique integer from 1-2^32
  server-id        = 1
  # Row format required for MysqlRepl
  binlog_format    = row
  # Directory must exist. This path works for Linux. Other OS may require
  #   different path.
  log_bin          = /var/log/mysql/mysql-bin.log
  
  binlog_do_db     = employees   # Optional, limit which databases to log
  expire_logs_days = 10          # Optional, purge old logs
  max_binlog_size  = 100M        # Optional, limit log size

• Create an account with replication privileges, e.g. given privileges to account mysqlrepl (or any account that you use to read binary logs)

  GRANT REPLICATION SLAVE, REPLICATION CLIENT, SELECT ON *.* TO 'mysqlrepl'@'localhost'

MysqlRepl Class

The MysqlRepl constructor accepts one argument of either:

• An object containing MySQL connection details in the same format as used by package mysql
• Or, a mysql Connection or Pool object that will be used for querying column information.

If a Connection or Pool object is passed to the constructor, it will not be destroyed/ended by MysqlRepl's stop() method.

If there is a dateStrings mysql configuration option in the connection details or connection, MysqlRepl will follow it.

Each instance includes the following methods:

Method Name | Arguments | Description ------------|-----------|------------------------ start | options | Start receiving replication events, see options listed below stop | None | Disconnect from MySQL server, stop receiving events on | eventName, handler | Add a listener to the binlog or error event. Each handler function accepts one argument.

Some events can be emitted in different phases:

Event Name | Description -----------|------------------------ ready | This event is occurred right after MysqlRepl successfully established a connection, setup slave status, and set binlog position. binlog | Once a binlog is received and passes the filter, it will bubble up with this event. error | Every error will be caught by this event. stopped | Emitted when MysqlRepl connection is stopped (MysqlRepl#stop is called).

Options available:

Option Name | Type | Description ------------|------|------------------------------- serverId | integer | Unique number (1 - 232) to identify this replication slave instance. Must be specified if running more than one instance of MysqlRepl. Must be used in start() method for effect.Default: 1 startAtEnd | boolean | Pass true to only emit binlog events that occur after MysqlRepl's instantiation. Must be used in start() method for effect.Default: false filename | string | Begin reading events from this binlog file. If specified together with position, will take precedence over startAtEnd. position | integer | Begin reading events from this position. Must be included with filename. includeEvents | [string] | Array of event names to includeExample: ['writerows', 'updaterows', 'deleterows'] excludeEvents | [string] | Array of event names to excludeExample: ['rotate', 'tablemap'] includeSchema | object | Object describing which databases and tables to include (Only for row events). Use database names as the key and pass an array of table names or true (for the entire database).Example: { 'my_database': ['allow_table', 'another_table'], 'another_db': true } excludeSchema | object | Object describing which databases and tables to exclude (Same format as includeSchema)Example: { 'other_db': ['disallowed_table'], 'ex_db': true }

• By default, all events and schema are emitted.
• excludeSchema and excludeEvents take precedence over includeSchema and includeEvents, respectively.

Supported Binlog Events:

Event name | Description ------------|--------------- unknown | Catch any other events query | Insert/Update/Delete Query intvar | Autoincrement and LAST_INSERT_ID rotate | New Binlog file Not required to be included to rotate to new files, but it is required to be included in order to keep the filename and position properties updated with current values for graceful restarting on errors. format | Format Description xid | Transaction ID tablemap | Before any row event (must be included for any other row events) writerows | Rows inserted, row data array available as rows property on event object updaterows | Rows changed, row data array available as rows property on event object deleterows | Rows deleted, row data array available as rows property on event object

Event Methods

Neither method requires any arguments.

Name | Description -------|--------------------------- dump | Log a description of the event to the console getEventName | Return the name of the event

Important Notes

• :star2: All types allowed by mysql are supported by this package.
• :speak_no_evil: 64-bit integer is supported via package big-integer(see #108). If an integer is within the safe range of JS number (-2^53, 2^53), a Number object will returned, otherwise, will return as String.
• :point_right: TRUNCATE statement does not cause corresponding DeleteRows event. Use unqualified DELETE FROM for same effect.
• When using fractional seconds with DATETIME and TIMESTAMP data types in MySQL > 5.6.4, only millisecond precision is available due to the limit of Javascript's Date object.

Run Tests

• install Docker
• run docker-compose up and then ./docker-test.sh

Reference

• https://github.com/mysqljs/mysql
• https://github.com/felixge/faster-than-c/
• http://intuitive-search.blogspot.co.uk/2011/07/binary-log-api-and-replication-listener.html
• https://github.com/Sannis/node-mysql-libmysqlclient
• https://kkaefer.com/node-cpp-modules/
• http://dev.mysql.com/doc/internals/en/replication-protocol.html
• http://www.cs.wichita.edu/~chang/lecture/cs742/program/how-mysql-c-api.html
• https://github.com/jeremycole/mysql_binlog (Ruby implemenation of MySQL binlog parser)
• http://dev.mysql.com/doc/internals/en/date-and-time-data-type-representation.html

License

MIT