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

Building MySql From Sources on Redhat

UPDATE 7/2010:  Caveat emptor, this information is out of date.  I've learned some new things since originally writing this post as well.  I wrote this when I was first figuring out how to build from source.

Building MySql from sources has worked very well for me, at least once I got over a few humps.  First, there are several non-default options to "configure" that you probably want to use.  For example, by default, the "FEDERATED" storage engine isn't included.  Second, there is additional software that's not installed by default with Redhat that's needed.

The guidance here works for MySql 5.1.33.

Building MySql:

1. Get the source tree from mysql.com

2. cd mysql-X.XX

3. ./configure options

4. make

5. make install

Step (5) installs the mysql binaries to /usr/local/bin.

My "configure" options:

./configure --with-charset=utf8 --with-collation=utf8_general_ci --with-plugins=innobase,partition,archive,federated,heap,myisam,myisammrg --enable-local-infile --without-debug --enable-thread-safe-client --with-client-ldflags=-all-static

I'll explain why I use these particular options later on in this post.

Missing software problems:

1. "configure" fails complaining about "no curses/termcap library found"

Cure:  yum install ncurses-devel

2. "make" fails because of missing g++

Cure:  yum install gcc-c++

3. "make" (of innodb backend) fails due to missing file zlib.h

Cure:  yum install zlib-devel

Configure options:

1. --with-charset=utf8 --with-collation=utf8_general_ci

Why: we got tired of having to jump through hoops when our data happens to contain

non-latin1 characters.  These options make all db's utf8 by default.

2. --with-plugins=innobase,partition,archive,federated,heap,myisam,myisammrg

Why: don't futz with plug-ins.  Link things directly in.  Should yield a bit of performance.

3. --enable-local-infile

Why: without this, LOAD DATA LOCAL INFILE is disabled.

4. --without-debug

Why:  should gain some performance. 

5. --enable-thread-safe-client

Why:  our clients can have multiple threads.

6. --with-client-ldflags=-all-static

Why: should gain a bit of performance