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>
