Logging Interactive mysql Sessions

1.30.1 Problem

You want to keep a record of what you did in a mysql session.

1.30.2 Solution

Create a tee file.

1.30.3 Discussion

If you maintain a log of an interactive MySQL session, you can refer back to it later to see what you did and how. Under Unix, you can use the script program to save a log of a terminal session. This works for arbitrary commands, so it works for interactive mysql sessions, too. However, script also adds a carriage return to every line of the transcript, and it includes any backspacing and corrections you make as you're typing. A method of logging an interactive mysql session that doesn't add extra messy junk to the log file (and that works under both Unix and Windows) is to start mysql with a --tee option that specifies the name of the file in which to record the session:[10]

[10] It's called a "tee" because it's similar to the Unix tee utility. For more background, try this command:

% mysql --tee=tmp.out cookbook

To control session logging from within mysql, use T and to turn tee output on and off. This is useful if you want to record only parts of a session:

mysql> T tmp.out
Logging to file 'tmp.out'
mysql> 	
Outfile disabled.

A tee file contains the queries you enter as well as the output from those queries, so it's a convenient way to keep a complete record of them. It's useful, for example, when you want to print or mail a session or parts of it, or for capturing query output to include as an example in a document. It's also a good way to try out queries to make sure you have the syntax correct before putting them in a script file; you can create the script from the tee file later by editing it to remove everything except those queries you want to keep.

mysql appends session output to the end of the tee file rather than overwriting it. If you want an existing file to contain only the contents of a single session, remove it first before invoking mysql.

The ability to create tee files was introduced in MySQL 3.23.28.