Here’s a question from one of our regular reader Mr. Waseem. He wants to know how to redirect output of MySQL source command to log file?
I use INTO OUTFILE with SELECT query in MySQL to store the results into a file. However, I’m not able to do the same with source command. I would like to redirect output of MySQL source command to a log file? Is that possible using INTO clause?
Well, here’s the answer to Waseem’s question.
How to redirect output of MySQL source command to log file?
Generally, INTO clause is used along with SELECT query to enable the results to be stored in a variable or a file. The syntax of it goes as below.
SELECT * INTO OUTFILE '/tmp/result.txt' FROM tablename;
But, INTO clause cannot be used with source command. So if you want to redirect output of MySQL source command into a log file, then you need to use ‘tee‘. Here’s how it works.
Step 1: Login to MySQL
Step 2: In the MySQL command prompt, type the below command.
mysql> tee log.out Logging to file 'log.out' mysql>
You should see a message “Logging to file” displayed on the screen.
Step 3: Now, source the SQL file
mysql> source sample.sql
Step 4: The output of source command would have been logged into a file log.out.
Step 5: View the log.out
$ more log.out
That’s it!
Why would you (or Mr. Waseem) wants to log the output of source SQL? For example, the SQL file that you would like to import might come with long list of tables & records and while sourcing it, there are chances few of the records might not get imported. Now what if you want to know which of those records failed during import? The log file will help you to find that.
Note: 'tee' command can be used before executing any MySQL statement. The output of any query after logging the session will be stored in the specified file.
For example:
mysql> tee showdboutput.out Logging to file 'showdboutput.out' mysql> show databases;
The output of the above query will be stored in showdboutput.out file. Another example is here.
