I'm working on a project that uses MySQL as the RDBMS and though MySQL has grown quite a bit more mature than when I first experimented with it back around version 3 when it was the pseudo-rdbms to pair with php for your wack web site, I still have an enjoyable learning curve replacing all the tools I know how to use with Sybase or SQL Server. Actually, a large part of the/my problem is that I'm not using Windows, which means I can't fall back on the SQL Server tools that work with any ODBC-compliant database systems. MySQL on the command-line is much different than even Oracle with SQuirreL SQL.

Anyhow, here's today's lesson. One way to get a table from MySQL into a text file for slapping into another datasource is the following:

1.) I decided to use XML for my output, so I needed to log into mysql using the -X option. This makes all output from SELECTs, etc, come in XML format.

mysql -u username -p -X

2.) To SELECT INTO a file, you use this format:

select * from TableName into OUTFILE '/path/to/file/out.txt';

Note that the file cannot already exist (so you can't use SQL to introduce a major security breech and overwrite files) and that, on *NIX, you'll need to have permissions to that path and to write in that path.

2a.) Finding a dir that I could write into was a pain in the system I'm using, so I had to make one. Apparently mysql was using a different user than the one I was logging in as (I'm not the admin on one box), so I went to an appropriate location and created a directory.

prompt>cd ~/Public
prompt>mkdir smack
prompt>chmod 777 smack
prompt>cd smack

This makes me a directory in /Users/currentUser/Public/smack that anyone can write into. In general, that's a bad idea. I really need to limit to the mysql user. Wish I knew who that was, and how I'd change things appropriately even if I did. The "777" bit is a grenade to allow most anyone in smack to do most anything they want, and makes administrators squirrelly. Rightfully, afaik.

In any event, here's the SQL to use now to write out that file with XML in it. Note that I'm using OS X here, so the Public dir in my user's home dir was already there.

select * from TableName into OUTFILE '/Users/currentUser/Public/smack/Out.txt';

3.) That's essentially it/There is no step 3. But now you do need to get that file from the new dir to somewhere you can hack on it. I'm using OS X at home for this as well, in spite of it not being my typical rdbms dev environment, and Fugu somehow knows how to use SFTP or the like to browse my dirs and grab the file with my standard ssh login info for the MySQL server. Putty or something similar would probably be your choice on Windows.

What a pain. I miss my bcp and DTS.

[Update: Of course the file is not longer in XML, though the screen output is. Go figure. No idea how to fix that yet.]