How To Save MySQL query output to file or export to CSV

How To Save MySQL query output to file or export to CSV

Save MySQL or MariaDB query results into a plain text file


MySQL and MariaDB provides an easy mechanism for writing the results of a select statement into a text file on the server. All you have to do is use the INTO OUTFILE syntax from the MySQL client.

mysql> SELECT * FROM table_name INTO OUTFILE '/tmp/mysql_output_file.txt';

This example query creates a new plain text file in the /tmp directory named mysql_output_file.txt with output from mysql query.

example:

mysql> SELECT * FROM radreply INTO OUTFILE '/tmp/mysql_output_file.txt';
~] cat /tmp/mysql_output_file.txt
1       420724724470    Framed-IP-Address       =       172.16.0.6
2       420724724471    Framed-IP-Address       =       172.16.0.7
3       420724724472    Framed-IP-Address       =       172.16.0.8
...

How to save MySQL or MariaDB query output to excel or CSV file


Using extended options of the INTO OUTFILE statement, it is possible to create a comma separated value - CSV file which can be imported into a spreadsheet program such as Microsoft Excel, LibreOffice, Google Sheets or any other application which accepts data in CSV format.

mysql> SELECT column_names From table_name INTO OUTFILE 'filename.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

example:

mysql> SELECT id, username, value FROM radreply INTO OUTFILE '/tmp/radreply.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
~] cat /tmp/radreply.csv
"1","420724724470","172.16.0.6"
"2","420724724471","172.16.0.7"
"3","420724724472","172.16.0.8"
...

How to list MySQL database table column names without the table formatting


If you want to list all of the MySQL or MariaDB database table column names (field names) as a simple list of names without table formatting. All you have to do is start MySQL with the -sN options, like this:

~] mysql -sN -u root -p
-s, --silent  Be more silent. Print results with a tab as separator,
              each row on new line.

-N, --skip-column-names
              Don't write column names in results.

example default mysql output without -sN options:

~] mysql -u root -p
Enter password: 

mysql> use radius;
Database changed

mysql> SELECT id, username, value FROM radreply;
+-------+--------------+---------------+
| id    | username     | value         |
+-------+--------------+---------------+
|     1 | 420724724470 | 172.16.0.6    |
|     2 | 420724724471 | 172.16.0.7    |
|     3 | 420724724472 | 172.16.0.8    |
|     4 | 420724724473 | 172.16.0.9    |

example mysql output with -sN options:

~] mysql -sN -u root -p
Enter password: 

mysql>  use radius;
mysql> SELECT id, username, value FROM radreply;
1       420724724470    172.16.0.6
2       420724724471    172.16.0.7
3       420724724472    172.16.0.8
4       420724724473    172.16.0.9

How to log the output from an entire MySQL client session


If you want to keep a record of what you did in a mysql session, then redirecting will be useful. Please note that redirecting allows you to write the data into a file in your local host i.e. on your local/client machine and not in the MySQL server location.

~] mysql --user=root -p --tee=/tmp/mysql_output.txt

By using \T and \t commands we can control on what to record and what not to record into file.

Issue \T command to log the session of your mysql interaction to a file. On issuing this command you will get a message something like this "Logging to file '/tmp/mysql_output.txt'"

~] mysql --user=root -p --tee=/tmp/mysql_output.txt
Logging to file '/tmp/mysql_output.txt'
Enter password: 

mysql> \T
Currently logging to file '/tmp/mysql_output.txt'
mysql> \t
Outfile disabled.
mysql> \T
Logging to file '/tmp/mysql_output.txt'
mysql>