Tail -f table with myterm
Tail
Ever found yourself running the same query over and over again to see if a table has got new content, just waiting for that magic row to appear? The tail command will watch for new lines in a table, just like you might tail a log file in Linux. Example:
- myterm> tail -f bid
- +--------+---------+-----------+--------+---------------------+
- | bid_id | item_id | bidder_id | price | created |
- +--------+---------+-----------+--------+---------------------+
- | 67925 | 107751 | 81594 | 92500 | 2010-01-20 19:12:58 |
- | 67926 | 34248 | 32530 | 10000 | 2010-01-20 19:12:59 |
- | 67927 | 111211 | 108032 | 22900 | 2010-01-20 19:12:59 |
- | 67928 | 116016 | 46720 | 90400 | 2010-01-20 19:13:00 |
- | 67929 | 129101 | 98949 | 61300 | 2010-01-20 19:13:03 |
- | 67930 | 44235 | 91806 | 46400 | 2010-01-20 19:35:03 |
- | 67931 | 59403 | 114210 | 30700 | 2010-01-20 19:35:06 |
- | 67932 | 110582 | 101572 | 41800 | 2010-01-20 19:35:07 |
- | 67933 | 46759 | 73844 | 32600 | 2010-01-20 19:35:07 |
- | 67934 | 122248 | 18709 | 105600 | 2010-01-20 19:35:09 |
- | 67935 | 87468 | 70444 | 28500 | 2010-01-20 19:35:17 |
- ... (more rows appear over time)
The command will ask the server for the primary or unique key(s) of the table. It will then ask for new rows every second, remembering the maximum primary key value returned last time. This works if you insert data by ascending primary key (e.g. auto_increment). The query for the above would be:
- SELECT * FROM bid WHERE bid_id > ? ORDER BY bid_id
You can also specify the column to use as key using the -k option, e.g.
- tail -f -kcreated bid
which will run:
- SELECT * FROM bid WHERE created > ? ORDER BY created
Repeat
Similar but different, repeat allows you to run a query multiple times with some delay.
- repeat SELECT COUNT(*) FROM some_table;
By default, it will repeat 10 times and sleep 1 second in-between, but you can adjust this. Go forever and sleep 100 ms in-between:
- repeat -n0 -i0.1 SELECT COUNT(*) FROM some_table;
Say you want to watch some global status variable, option -t adds a timestamp as first column so you can see over time:
- myterm> repeat -t SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Questions';
- +---------------------+---------------+----------------+
- | Time | VARIABLE_NAME | VARIABLE_VALUE |
- +---------------------+---------------+----------------+
- | 2010-01-20 19:11:25 | QUESTIONS | 8394418 |
- | 2010-01-20 19:11:26 | QUESTIONS | 8394420 |
- | 2010-01-20 19:11:27 | QUESTIONS | 8394422 |
- | 2010-01-20 19:11:28 | QUESTIONS | 8394424 |
- | 2010-01-20 19:11:29 | QUESTIONS | 8394426 |
- | 2010-01-20 19:11:30 | QUESTIONS | 8394428 |
- | 2010-01-20 19:11:31 | QUESTIONS | 8394430 |
- | 2010-01-20 19:11:32 | QUESTIONS | 8394432 |
- | 2010-01-20 19:11:33 | QUESTIONS | 8394434 |
- | 2010-01-20 19:11:34 | QUESTIONS | 8394436 |
- +---------------------+---------------+----------------+
- 10 rows in set (0.013 sec)
More or less tailor-made for this purpose, -d allows you to calculate a delta value for some column:
- myterm> repeat -t -d3 SELECT * FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME LIKE 'Questions';
- +---------------------+---------------+----------------+----------------------+
- | Time | VARIABLE_NAME | VARIABLE_VALUE | Delta_VARIABLE_VALUE |
- +---------------------+---------------+----------------+----------------------+
- | 2010-01-20 19:12:52 | QUESTIONS | 8395641 | 0 |
- | 2010-01-20 19:12:53 | QUESTIONS | 8396281 | 640 |
- | 2010-01-20 19:12:54 | QUESTIONS | 8396729 | 448 |
- | 2010-01-20 19:12:55 | QUESTIONS | 8397162 | 433 |
- | 2010-01-20 19:12:56 | QUESTIONS | 8397634 | 472 |
- | 2010-01-20 19:12:57 | QUESTIONS | 8398254 | 620 |
- | 2010-01-20 19:12:58 | QUESTIONS | 8398919 | 665 |
- | 2010-01-20 19:12:59 | QUESTIONS | 8399470 | 551 |
- | 2010-01-20 19:13:00 | QUESTIONS | 8399994 | 524 |
- | 2010-01-20 19:13:01 | QUESTIONS | 8400509 | 515 |
- +---------------------+---------------+----------------+----------------------+
- 10 rows in set (0.004 sec)
Exercise for the reader: see if you can modify the command above to show the result as a line chart.
The repeat command can also do updates or deletes. You can tell it to repeat until no more rows are affected using the -a option. Useful if you want to delete some data chunk by chunk:
- repeat -a DELETE FROM message WHERE created < DATE_SUB(NOW(), INTERVAL 30 DAY) LIMIT 1000;
Named connections
If you often connect to the same db machines, you can name them in your configuration file. You can then specify the name when starting myterm, e.g.
- ./myterm @workhorse1
Your conf file .myterm contains the login credentials:
- con_workhorse1_user = "admin_john_doe"
- con_workhorse1_host = "192.168.23.25"
- ..
Abbreviations
Abbreviations enables you do edit the word list for auto completion. Some abbreviations are added by default. For instance, if you type:
- myterm> sel (TAB)
the abbreviation sel will auto expand into:
- myterm> select * from
You can see the list of existing abbreviations by typing abbrev list. You can also add your own abbreviations by typing abbrev add.
Download
Myterm is still in alpha, get it here: https://launchpad.net/myterm
See also this post.