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.