Dendory Network

Logo

Hi, my name is Patrick Lambert and I'm a tech worker, blogger, anime fan, digital artist and video gamer. I live in Montreal, Canada and have over 15 years of experience in technology.

Send me an email dendory@icloud.com

Connect with me on LinkedIn patrick-lambert-530383162

Chat with me on Discord TzrNgbx

Digital art galleries dendory

My YouTube channel elfguy

3D models on ShareCG elfguy


How to export a MySQL query result


MySQL (or these days, MariaDB) holds a lot of the data found on today’s web apps, and this data can be invaluable. Typically, web apps are made such that the important data is displayed in a useful and actionable way for the various users of the app. However, it’s not uncommon that a request may come in where you want the result of a specific query, something that isn’t available out of the box, and where you need to manually go in and run.

To run a query in MySQL, you can do so by simply logging in from a console terminal using the app’s username, selecting your database, and inputting your SQL query like this:

$ mysql --user=myapp -p
> USE mydb;
> SELECT * FROM mytable;

The textbook way

This is the most basic example of how to run a SELECT query. Of course, if you have millions of results, you may want to export them. This can be done using the INTO OUTFILE option. In order to properly have the above query saved in a CSV file so it can be opened in a spreadsheet application, you can use the following query instead:

> SELECT * FROM mytable INTO OUTFILE '/root/output.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

Permissions issues

One problem you may run into with the above query is a permission issue. Even if your user has ALL permissions, it turns out that the INTO OUTFILE is not actually part of ALL. You need to explicitly grant this permission to the user:

> GRANT FILE ON *.* TO 'myapp'@'*';

Alternative way

If you can’t change your permissions, there is an alternative way to export a query result into a file. You can simply use Linux’s pipes and redirection to do it. You can achieve a similar output like this:

$ echo "SELECT * FROM mytable;" | mysql --user=myapp -p mydb > output.txt

Hopefully between these various methods, you can easily provide the data that was asked of you by running these simple commands. You don’t have to be a DBA to interact with databases once you know how they work.