3

Is there a way to listen for database changes in MySQL/MariaDB, in the same way you can tail the MongoDB oplog? I am having trouble finding information on this online. Perhaps it's only available in Postgres and not with MySQL.

Stennie
  • 63,885
  • 14
  • 149
  • 175
Alexander Mills
  • 90,741
  • 139
  • 482
  • 817

3 Answers3

4

Plan A: Turn on the "general log". All queries will be written to that log. Be sure to turn it off pretty quickly; else the disk will fill up.

Plan B: Turn on the binlog. That contains all "writes". There is a program to display the contents of that log.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • do you have a recommended way of tailing the bin log? – Alexander Mills Feb 02 '16 at 07:26
  • 1
    Perhaps it would work to use `mysqldumpbinlog` (or whatever it is called), but I don't think you can use it in a "tailing" setup. You need that program to parse the file. However, it can start at any given offset, and the offsets are included in the log. – Rick James Feb 02 '16 at 15:01
2

You can use the mysqlbinlog utility to tail the binary log. To remotely tail a server for changes, use the following command.

mysqlbinlog -R -t --stop-never -h <hostname> -u <user> -p  <binlog file name>

To get the binlog file name, execute SHOW MASTER STATUS on the server you are tailing. If you want to decode row events, add the -v --base64-output=decode-rows flags.

mysqlbinlog requires that the binary log is enabled. It can be enabled by adding log-bin to the configuration file. The filename can be controlled by adding it as a value to log-bin or by using log-bin-basename. For example, log-bin=my-binlog would generate my-binlog.000001.

markusjm
  • 2,358
  • 1
  • 11
  • 23
1

For my project, I use InfluxDB to monitor things and plot them on Grafana. general_log is a good option but your space gets wasted due to that.

Here is a link that will help you out: https://dzone.com/articles/mysql-monitoring-with-telegraf-influxdb-amp-grafan

Phoenix
  • 321
  • 3
  • 12