1

I'm trying to do this:

mysql -u root old_db -e "SELECT some_fields FROM huge_table INTO OUTFILE ..." \
 | cat header.sql - trailer.sql \
 | psql new_db

Where header.sql contains:

COPY huge_table (some_fields) FROM stdin;

And trailer.sql just contains the \. to terminate the stream.

I can't for the life of me figure out how to get MySQL to dump tab-separated values into STDOUT so that this pipe can work. I've tried specifying /dev/stdout as the path, but it claims to not have permission.

I'm trying to do this to get the data from MySQL to PostgreSQL as fast as possible. The time to write to disk and back into PostgreSQL is too slow.

I notice that if you pipe a query into MySQL it almost outputs a TSV, but not quite.

Is there any way to do this or am I flogging a dead horse?

d11wtq
  • 34,788
  • 19
  • 120
  • 195

1 Answers1

1

PostgreSQL's COPY doesn't require tab-separated; just use \copy tablename from stdin csv.

If you can't get MySQL to write to a pipe-opened command, or directly to stdout so you can pipe its output with the shell, there's a classic workaround using a unix named pipe:

$ mkfifo copy_fifo
$ psql dbname '\copy table1 from copy_fifo csv'

then in another terminal:

$ mysql -u root old_db -e "SELECT some_fields FROM huge_table INTO OUTFILE copy_fifo FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"

Alternately try:

$ mysql dbname -B -e "SELECT some_fields FROM huge_table" | psql dbname '\copy table1 from stdin'

though this is unlikely to work in the presence of NULLs.

Another option is to use mysqldump to produce CSV-like output using the -T flag and --fields-terminated-by=..., --fields-enclosed-by=..., --fields-optionally-enclosed-by=..., --fields-escaped-by=... as per the manual for mysqldump. Pipe mysqldump's output to psql.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • 1
    Hmm, I really wanted this to work, but unfortunately MySQL just complains that the file already exists (it insists on creating a new file). I think this isn't easily solvable. Damn you MySQL :P – d11wtq Oct 25 '12 at 11:00
  • @d11wtq Damn... I didn't think to check that. There's no `OVERWRITE` or `APPEND` flag to `INTO OUTFILE` ? Alternately, can you get `INTO` to write to `stdout` then use the shell to pipe output into `psql`? – Craig Ringer Oct 25 '12 at 12:08
  • Try http://lists.mysql.com/mysql/214911 or http://osdir.com/ml/mysql/2010-02/msg00252.html – Craig Ringer Oct 25 '12 at 12:32