How to auto-refresh/re-run a query every few seconds?

I have a script running against a database with no GUI. This simple query lets me see its progress:

select
    *,
    (select count(*) from domains) as count,
    (select 456976-count(*) from domains) as remaining
from domains
order by domain desc
limit 0, 1;

Using MySQL Workbench, how can I automatically refresh/re-run this query every few seconds?

I tried Googling, but didn't come up with anything relevant.

Aside: the magic number 456,976 is 26^4; it has to do with the script.

You don't state what OS you are using, but under Linux/OSX and other Unix-a-likes you can use the mysql command line tools with the pretty standard watch utility to do this. Something like:

watch -n 10 'mysql --database=dbname --user=username --password=$(cat /path/to/file/with/mysql/password) --execute="select *, (select count(*) from domains) as count, (select 456976-count(*) from domains) as remaining from domains order by domain desc limit 0, 1;"'

Breaking that down:

• watch -n 10 '' runs ` every ten seconds and echos anything that goes to stdout to your console window, the single quotes wrapped around the command(s) to run tell your shell not to run any wildcard globbing or other substitutions before giving the command to watch (we want this to happen when watch runs the command)






• mysql --database=dbname --user=username --password=password runs the command line tool against a given DB with the authentication details given






• Using command substitution ($(cat /path/to/file/with/mysql/password)) to read the password instead of giving it directly on the command line stops your password being presented to anyone looking at the output of top or ps (this is why we use the single quotes: without them this substitution will be done before watch is in control so the real password will end up on the process list as watch runs the resulting command)






• --execute= tells the command line tool to run then exit immediately (otherwise it logs in and waits for you to enter commands/queries/statements).






• The rest is just your SELECT statement. Each time watch runs the mysql command it will output the results of this and watch will draw them on your screen.




More detail:



• mysql command line syntax






• watch command man page






• watch command examples with animated example output






• some more examples of simple mysql` command-line use (the first of many in a simple search, so you will find a lot more examples if you need/want them)

StackExchange Database Administrators Q#51472, answer score: 7