tail -f equivalent for MySQL logging database

Question

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).

Answer 1

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

Answer 2

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

Answer 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.

Answer 4

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.

Answer 5

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;'"

Answer 6

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.

Answer 7

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.

Answer 8

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.

Answer 9

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.