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

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

Answer 2

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

Answer 3

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 4

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 5

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.