MySQL 4.0.27 to MySQL 5.0.41 Migration

[Remi Broemeling]

I am currently involved in an undertaking to migrate some live/corporate databases (the tables are mostly MyISAM, with a few InnoDB) from MySQL 4.0.27 to 5.0.41. While the migration is taking place, a number of services that require them will not be available and thus it is of the utmost importance to make the migration happen as quickly as possible.

After a bit of research on the subject, I have found that `mysqlimport` (LOAD DATA INFILE) is the most likely candidate for the fastest migration. However, one problem that I am having is that it requires an intermediary format -- `mysqldump -T` will create static files, which then need to be sourced in order for `mysqlimport` to read from them. This is a problem for me because it is slower to have that static step in the middle of the migration process than to simply pipe the data around as needed (i.e. `mysqldump -T | mysqlimport`).

What is the fastest way to migrated data between two MySQL instances (on two different hosts)? Assuming that I am correct that it is `mysqlimport`/'LOAD DATA INFILE', is there any way to simply pipe the data around without needing the intermediary step of writing it all into static files that are then referenced by `mysqlimport`?

I have tried FIFOs already, for example:

Source machine runs `mysqldump -T` into /tmp/table.txt FIFO, which sends the data to the destination machine /tmp/table.txt, which in turn is read by `mysqlimport`; however SELECT INTO OUTFILE (which I understand is how `mysqldump -T` is implemented) does not properly write to FIFOs on the source machine (although I have found references that make me believe that `mysqlimport` _does_ properly read from FIFOs). Rather than writing to the FIFOs, the source machine removes them/overwrites them with standard files of the same name.

The current idea that we have is to run a script on the destination machine that does an unbuffered SELECT * on the source machine, formats the data so that it can be properly read by LOAD DATA INFILE, and then writes it to a FIFO on the destination machine (which mysqlimport/LOAD DATA INFILE then picks up and runs with). I am OK with writing the script to do that myself, but am somewhat surprised at the seeming lack of ability within the MySQL core utilities themselves to do this sort of thing. Am I missing the functionality somewhere?

Thanks for any information or guidance that is offered.