Categories
Uncategorized

How to Output MySQL Results to a File

See how to output MySQL results to a file. I wanted to create a file listing table fields as output by Desc and found a number of methods.

I needed to figure out how to output MySQL results to a file so I could have a permanent record of the field names and details of a table. After some investigation into this I found a number of methods.
Some methods to redirect MySQL output to a file involved using OS shell commands while another technique allowed the file to be produced from a command executed from within the MySQL client.

MySQL Redirect to File OS Shell Commands

I’ve tested 3 methods whereby a MySQL table description is written to a file after issuing a command from the OS command line interface.

Output to File Using mysqlshow

The first uses the mysqlshow client. This tool displays database, table, and column information.
I used the following command…
mysqlshow -u user_name -p database_name table_name > tmp/table_name-desc.txt
Which produced this output…
mysqlshow output

Output to File Using mysql execute option

The second method uses the mysql client’s execute and quit option. When the MySQL client is run with the ‘-e’ option, the client starts, executes the command and quits. The output is redirected to a file with the ‘>’ character.
I entered the following…
[~]# mysql -e "DESC companies" -u user_name -p database_name > tmp/table_name-desc.txt
Here’s what the file looked like…
mysql execute option output

Output to File Using mysqldump

The last OS shell command I tested to redirect MySQL output to a file used the MySQL Dump client which can be used to dump a database or a collection of databases for backup. I utilised the available options to narrow down the data produced to the table I required with none of its data.
The command I used was…
[~]# mysqldump --no-data -u user_name -p database_name companies > tmp/table_name-desc.txt
This produced the file shown below…
mysqldump redirect output

Redirect to File From Within the MySQL Client

Output to File Using Tee

The final method I’m going to share with you sends MySQL output to a file after executing a command from within the MySQL client itself. The technique use the ‘tee’ command which logs statements and their output to a given file.
The code I used to produce my table description file was…
[~]# mysql> tee tmp/compdesc-tee.txt
mysql> desc table_name;

And the file content…
MySQL Client Tee Output

No doubt, there are probably other and perhaps better methods to output MySQL results to a file. If you know of any why not leave a comment below.

Leave a Reply

Your email address will not be published. Required fields are marked *