This article introduces how to execute MySQL queries from Linux CLI and output the results to a CSV file.

Command to Execute MySQL Query and Output to CSV File

The command to execute MySQL queries and output to CSV file is as follows:

mysql -u [username] -p -h [host] [dbname] -e "[query to execute]" \\
| sed -e 's/\  /,/g' \\
> [Output CSV file path]

Process explanation:

1. Execute query from CLI using mysql -e option
2. Replace tab characters \ with , using sed command on query results
3. Output sed replacement results to file using redirect operator >

Practical Example with Headers

Sample command with headers in the first row:

mysql -p test -e \\
"select utm_campaign, count(*), sum(num_purchase) from customers where utm_campaign = 'hoge';" \\
> output.csv

Practical Example without Headers

Sample command without headers in the first row. Use the -N option to suppress header display.

mysql -u [username] -p -h [host] [dbname] -N -e \\
"select utm_campaign, count(*), sum(num_purchase) from customers where utm_campaign = 'hoge';" \\
| sed -e "s/\  /,/g" \\
>> output.csv

That’s all from the Gemba on saving MySQL query execution results to CSV files.