I had to export some data from a Mysql database, for the purpose of creating a spreadsheet, which would be used for presentation purposes and also to import into a DB2 database.

Initially I considered using an ODBC connection from my laptop,  I do not have ODBC drivers for mysql on my laptop. The policy of the company is to not allow new drivers installed with centralised authority.

Even without the drivers it was straightforward.

Logged into mysql and created a resultset via the following command:

Mysql>select p.page_title from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%';

How can I get that resultset into an output file, which will be useful?

mysql> select p.page_title,t.old_text from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%' INTO OUTFILE '/tmp/jvtest.csv';

ERROR 1045 (28000): Access denied for user 'usertemp'"'localhost' (using password: YES)

I'd seen this error before, and related to the actual Mysql user not having access to the underlying file system. Although the Linux user account Id used to log onto the Linux server, had sufficient permissions.

Under the circumstances two methods were available:

Method 1

echo @ select p.page_title,t.old_text from page p inner join text t on p.page_latest = t.old_id and p.page_title LIKE 'db2%'@ > /tmp/test

mysql> -pmypassword -umyuser wikidb < /tmp/test > /tmp/jvtest.csv

Method 2

Use tee - this will save all output from screen

Mysql > tee /tmp/output.txt

Mysql > no teee

I used method 1 as I had more scope to format .

Republished from http://www.dba-db2.com/.