55

I'm trying to write the results of a query to a file using mysql. I've seen some information on the outfile construct in a few places but it seems that this only writes the file to the machine that MySQL is running on (in this case a remote machine, i.e. the database is not on my local machine).

Alternatively, I've also tried to run the query and grab (copy/paste) the results from the mysql workbench results window. This worked for some of the smaller datasets, but the largest of the datasets seems to be too big and causing an out of memory exception/bug/crash.

Any help on this matter would be greatly appreciated.

Ramy
  • 20,541
  • 41
  • 103
  • 153
  • If you have access to the MySQL server from your machine, you should be able to dump the results of a query via command line - in case you have a usable one. – miku Jun 13 '11 at 16:11

5 Answers5

87

You could try executing the query from the your local cli and redirect the output to a local file destination;

mysql -user -pass -e"select cols from table where cols not null" > /tmp/output
Ztyx
  • 14,100
  • 15
  • 78
  • 114
eroomydna
  • 1,261
  • 9
  • 4
  • 3
    Is there some sort of flag that gives you the ascii structure with that? Such as +---------+ etc – Big Money Nov 20 '13 at 07:41
  • 3
    It should do it by default. Passing -s argument should remove the table formatting. – eroomydna Mar 12 '14 at 10:31
  • 9
    @BigMoney It didn't for me either on CentOS. Adding `--table` or `-t` forced it to add the +-----+ structure though – Matthew Jun 17 '14 at 23:06
  • 2
    The problem I have with this is that if the results are large, it caches all the data in memory before flushing to the file, which eats up all the machine memory – Daniel Pinyol Sep 15 '15 at 14:14
  • 4
    You may have to select a database, too, in order for this to work; for example by adding the option -Ddatabase – kingledion Jan 24 '17 at 21:07
  • I was unable to do it and then noticed ```"``` in the sql querry. Be careful with quotes :) – a_girl Oct 15 '20 at 15:06
17

You could try this, if you want to write MySQL query result in a file.

This example write the MySQL query result into a csv file with comma separated format

SELECT id,name,email FROM customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
geekdev
  • 1,192
  • 11
  • 15
  • 1
    The problem is with getting the export into local machine. This query saves the CSV in the same server as the mySQL host. –  Oct 05 '16 at 06:58
  • `show variables like 'datadir';` to see [what directory](https://stackoverflow.com/a/31558673/673991) that file will go in. User will need the [awesome responsibility](https://stackoverflow.com/a/39963216/673991) of the `GRANT FILE` privilege. – Bob Stein Oct 14 '17 at 21:34
  • No it won't be a problem - you can get the file with sftp from the remote server. – nuriselcuk May 07 '20 at 13:34
  • The `/tmp` part is actually important; MySQL can not write anywhere else due to Linux user permissions – Mordechai Aug 12 '21 at 21:23
17

This is dependent on the SQL client you're using to interact with the database. For example, you could use the mysql command line interface in conjunction with the "tee" operator to output to a local file:

http://dev.mysql.com/doc/refman/5.1/en/mysql-commands.html

tee [file_name], \T [file_name]

Execute the command above before executing the SQL and the result of the query will be output to the file.

Specifically for MySQL Workbench, here's an article on Execute Query to Text Output. Although I don't see any documentation, there are indications that there should be also be an "Export" option under Query, though that is almost certainly version dependent.

Nazar Annagurban
  • 336
  • 1
  • 8
dolphy
  • 6,218
  • 4
  • 24
  • 32
11

If you are running mysql queries on the command line. Here I suppose you have the list of queries in a text file and you want the output in another text file. Then you can use this. [ test_2 is the database name ]

COMMAND 1

mysql -vv -u root -p test_2  < query.txt >  /root/results.txt 2>&1

Where -vv is for the verbose output.

If you use the above statement as

COMMAND 2

mysql -vv -u root -p test_2  < query.txt  2>&1 >  /root/results.txt

It will redirect STDERR to normal location (i.e on the terminal) and STDOUT to the output file which in my case is results.txt

The first command executes the query.txt until is faces an error and stops there.

That's how the redirection works. You can try

#ls key.pem asdf > /tmp/output_1 2>&1 /tmp/output_2

Here key.pm file exists and asdf doesn't exists. So when you cat the files you get the following

# cat /tmp/output_1
key.pem
#cat /tmp/output_2
ls: cannot access asdf: No such file or directory

But if you modify the previous statement with this

ls key.pem asdf > /tmp/output_1 > /tmp/output_2 2>&1

Then you get the both error and output in output_2

cat /tmp/output_2

ls: cannot access asdf: No such file or directory
key.pem
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134
Himanshu Chauhan
  • 812
  • 9
  • 11
  • Additionally you can use "nohup" command if you are Linux and the queries you have in the file will take long enough time to complete execution. – learner May 04 '20 at 11:21
0

mysql -v -u -c root -p < /media/sf_Share/Solution2.sql 2>&1 > /media/sf_Share/results.txt This worked for me. Since I wanted the comments in my script also to be reflected in the report I added a flag -c

Suraj Jeswara
  • 446
  • 2
  • 10
  • 23