MySQL: How to export the output for a non SQL query sentence?

Question

In MySQL 8 server community to export a SQL query's output is possible execute in the MySQL Shell the following command (as most basic):

SELECT * FROM cientifico INTO OUTFILE '/var/lib/mysql-files/cientifico-data.txt';

And it works as expected

But for a non SQL query as follows:

SHOW PROCESSLIST INTO OUTFILE '/var/lib/mysql-files/processlist.txt';

Throws the following error

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE '/var/lib/mysql-files/processlist.txt'' at line 1

Same as:

SHOW DATABASES INTO OUTFILE '/var/lib/mysql-files/databases.txt';

Giving

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTO OUTFILE '/var/lib/mysql-files/databases.txt'' at line 1

Therefore if is possible

Question

• How to export the output for a non SQL query sentence?

I am assuming that the INTO OUTFILE syntax is only for SQL sentences, but for non SQL sentences?

Answer 1

SHOW can not be combined with the INTO OUTFILE syntax. You use the INTO OUTFILE clause only with SELECT.

You can query from PERFORMANCE_SCHEMA.PROCESSLIST using SELECT, and that gives you the opportunity to capture the output.

Or you can develop your own client code in your favorite language that fetches the result of a SHOW command, and then save the results to a file yourself. If you need special handling for the format, for instance tabs vs commas, you'd have to write code for that yourself. But writing lines of text to a file in a loop isn't a difficult programming task.

Answer 2

The easiest way to put any mysql output to the file is to use mysql in a batch mode:

mysql -u user -ppass -e "show processlist;" > outfile.txt

If you want to parse the output it is useful to suppress some visual enchancements:

mysql -u user -ppass -BNe "show processlist;" > outfile.txt

• Manuel Jordan: Thank You ... but confirm pls by your side if is not possible through the MySQL Shell too. Anyway your approach helps ... I am going to do a research about the -e and -BNe options
• Kondybas: @ManuelJordan I have no experience with MySQL shell but I beleive it is an advanced version of the classic CLI mysql client, crafted for *NIX shells integration. It is possible to use mysql client in batch mode to interact with MySQL databases in all kinds of way. Via -e "statement;" you can make all CRUD operations except those returning multiple tables. Say you can write a stored routine that returns two or more resultsets from CALL MySP(x,y,z) and in case of plain mysql client result parsing could be a pain.