Beware: myqldump and FEDERATED tables

mysqldump in 5.1 is broken in my opinion with respect to FEDERATED tables.

mysqldump insists on dumping all the rows of a FEDERATED table.  That's bad when you go to restore from the dump.  The normal case is that the remote table our FEDERATED table points to is fine and needs nothing done to it.  The restore will try to re-INSERT all the rows into the remote table.  But they are already there.

A solution to this problem is to isolate all FEDERATED tables within a single database, say "my_federated" for example.  Don't put anything but FEDERATED tables in "my_federated".

Then dump the database as follows:

mysqldump --opt --no-data --result-file=federated.dmp --verbose --databases my_federated

mysqldump --opt --result-file=data.dmp --verbose --databases list of all databases except my_federated and information_schema

Restore the db by loading both dump files:

mysql

mysql> source federated.dmp

mysql> source data.dmp