14

It was decided that we should move to using a (MySQL) database for our application logs (it is a Java app using the logback lib). I am hoping to find something like tail -f that I can use with a specific table in that database that will show me new rows as they are added (similar to how tail -f worked on log files).

1
  • I'm looking for this too. Clarification on the subject: Of course we can poll the database or binlog periodically to detect data changes. That is a no brainer. But is there someway one can tail -f the bin log, so to be notifiied when changes occur, that avoids periodic polling. The difficulty with polling is especially determing the polling interval. Too short and you're wasting resources. Too long and you introduce delay into the system. Apr 28, 2020 at 10:42

9 Answers 9

5

Turn on MySQL binary logging. Then you can use the mysqlbinlog command to see all data-modifying statements.

2
  • can you show an example of how to do this in the language of your choice or with bash? Jan 28, 2019 at 0:52
  • 1
    This does not tail -f (follow) the binlog to show rows "as they are added". It allows for a polling approach, but then we might as well use a periodic query. Apr 28, 2020 at 10:46
7

I don't think some people understand the question (or I don't). You don't want to log the queries against the DB; rather a log from an application is going into a DB. If it were a file you could tail the log. How do you tail a table so that when a new row is added it is output?

It shouldn't be to hard to write a simple loop to handle this, assuming you have a unique field that monotonically increases over time (e.g., a sequence number).

current_pos = select max(seq) from table
while true
  new_pos = select max(seq) from table
  if new_pos > current_pos
    select * from table where seq > current_pos
    current_pos = new_pos
  endif
  sleep 1
endwhile
1
  • this is called polling and it's not that much fun :) but a good technique I suppose, if you must use polling. Jan 28, 2019 at 0:53
3

It appears that many of us don't quite understand your question. What do you mean by "logging database", which isn't a standard MySQL term.

Use the MySQL General Query Log, which logs each statement received from a client.

You can then set log_output = TABLE in your my.cnf . The file will be written to $mysql_data_directory/general_log.CSV . You can tail -f this file to view queries in real time.

2

Here's what I use. Seems the simplest solution, though it's not very efficient:

watch "mysql db_name -e '(SELECT * FROM my_table ORDER BY id DESC LIMIT 10) ORDER BY id ASC;'"

1
  • watch does not tail -f (follow) the binlog to show rows "as they are added". It allows for a polling approach, but then we might as well use a periodic query. Apr 28, 2020 at 10:47
2

If you have binary logging enabled in MySQL, you can use this method from https://coderwall.com/p/0tmhla/tailing-mysql-binary-log to tail queries:

export D=$(date +"%Y-%m-%d %H:%M:%S" --date="30 minutes ago"); watch "mysqlbinlog --start-datetime=\"$D\" /var/log/mysql/mysql-bin.000001|tail"

It will watch the binary log and updates the output every 2 seconds.

1

I suggest adding a timestamp field to any table you want to tail. That will allow you to get the desired results very easily with a simple query.

1
  • This does not tail -f (follow) the binlog to show rows "as they are added". It allows for a polling approach, but then we might as well use a periodic query. Apr 28, 2020 at 10:48
1

You might like to give this a go:

http://www.jetprofiler.com/blog/10/tail--f-table-with-myterm/#comments

It's old and hasn't been touched for 3 years - but I just tried it out and it works OK. Clone the BZR repo and read the README.

1
  • This polls the database "every second" and does not tail -f (follow) the binlog to show rows "as they are added". It allows for a polling approach, but then we might as well use a periodic query. Apr 28, 2020 at 10:49
1

You could do it a hacky way by using tail -f on the database files in /var/lib/mysql/database_name/table_name.MY* and then running your query every time a line is read.

0

Here is a java library that can do a "real" tail -f of the binlog:

shyiko/mysql-binlog-connector-java: MySQL Binary Log connector

With mysql-binlog-connector-java you can actually react asynchronously to changes in the binlog without periodic polling.

It allows you to:

BinaryLogClient client = new BinaryLogClient("hostname", 3306, "username", "password");
EventDeserializer eventDeserializer = new EventDeserializer();
...
client.setEventDeserializer(eventDeserializer);
client.registerEventListener(new EventListener() {

    @Override
    public void onEvent(Event event) {
        ...
    }
});
client.connect();

Note that the data in Event event is not a super-handy as you've come to expect from your faviorite MySQL client, as the data is lower-level than that. See the link above for details.

Also, here is a list of projects that will let you do replication from MySQL to Kafka where I found mysql-binlog-connector-java. You can also find equivalents in at least go and python.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .