How to save the output from a MySQL query to a file

I had a problem with this website last Wednesday, and as part of troubleshooting the problem I needed to look at one of the Drupal database tables, specifically the watchdog table. This table had thousands of records in it, and I couldn't find what I needed with SQL SELECT queries, so I finally dug through the MySQL documentation, and found that I could save the output from a SELECT query to a text file.

In this tutorial I'll show you both (a) how to save the results of a MySQL query to a text file, and also (b) how to log your entire MySQL session to a text file.

How to save the output of one MySQL query to a text file

To save the output from a SQL SELECT query to a text file with MySQL, all you have to do is use the "INTO OUTFILE" syntax from the MySQL client, like this:

mysql> select * from watchdog into outfile '/tmp/watchdog.out';

This query creates a new plain text file in the /tmp directory on my Linux system named watchdog.out. That file contains the output from my query

If you just want to log the output from one query to a text file with MySQL, that's all you have to do.

How to log the output from an entire MySQL client session

If you want to log the output from more than one query -- either an entire MySQL client session, or part of a session -- it's easier to use the MySQL tee command to send output to both (a) your console and (b) a text file.

To get the logging process started, just use the tee command at the MySQL client prompt, like this:

mysql> tee /tmp/my.out;

That command tells MySQL to log both the input and output of your current MySQL login session to a file named /tmp/my.out.

After you issue this tee command, you should see MySQL respond like this:

Logging to file '/tmp/my.out'

I just verified this with my MySQL client (version 5.0.x), and it works as advertised. Everything I typed in, and everything MySQL prints back to me, was saved to my log file.