How to Store the output of SHOW PROCESSLIST into a file in MySQL?

Question: I have used SPOOL command in Oracle SQL for storing query results into a file and when all is done, spool can be turned off. How can I achieve the same in MySQL? For example, In MySQL the ‘SHOW FULL PROCESSLIST’ query will list all MySQL processes that are currently running on to the screen (and if it’s a long list, you will see only the last few rows in the screen). I would like to know if there is SPOOL like command in MySQL for storing the query results into a file? or at least I should be able to pipe ( | ) the output to shell command like ‘more’.

In ORACLE SQL:

To store the output of the query result in a file, I use spool.

sqlplus> SPOOL /tmp/output.txt
sqlplus> select * from UserTable;
sqlplus> SPOOL off;

Answer: For MySQL

Method 1:

There is no SPOOL command in MySQL, but you can use the alternative command tee to turn On the logging and notee to turn Off the logging.

mysql> tee /tmp/test.txt
 Logging to file '/tmp/test.txt'
 mysql> show full processlist;
 +------+-----------+-----------+--------+---------+------+-------+-----------------------+
 | Id | User | Host | db | Command | Time | State | Info |
 +------+-----------+-----------+--------+---------+------+-------+-----------------------+
 | 7478 | sample | host.name| dbname | Sleep | 0 | | NULL |
 | 7479 | sample | host.name | NULL | Query | 0 | NULL | show full processlist |
 +------+-----------+-----------+--------+---------+------+-------+-----------------------+
 2 rows in set (0.00 sec)
 mysql> notee
 Outfile disabled.

Now, you can check the log file /tmp/text.txt.

Method 2: You can run MySQL query from the shell prompt and pipe the output to more.

$ mysql -u root -p -e "show full processlist" | more

Note: You need privileges to execute processlist query.

Method 3: You can run the query from shell prompt and redirect the output to a file as shown below:

$ mysql -u root -p -e "show full processlist" | tee /tmp/out.txt

(or)

$ mysql -u root -p -e "show full processlist" > /tmp/out.txt

Note: You need privileges to execute processlist query.

SELECT INTO OUTFILE used for SELECT queries:

If you are executing a select query, then you may use SELECT INTO OUTFILE as shown below:

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

Hope it helps.