Re: Backup doesn't work for many number of tables?

[Mike Kruckenberg]

Am curious why you need so many tables, but aside from that I believe in theory MySQL dump should be able to handle this.

That being said, I've done some testing and have found that it's quite a burden to do work with this many tables. In my testing (which went up to 35,000 tables) it's not a problem to create and query against that many tables, but trying to dump them isn't easy or quick. I first ran into the --max_allowed_packet limit, so I bumped that up only to find that I was then getting a file handle error because MySQL had too many files open. I think the resolution is to use the -q option which dumps the tables directly out to STDOUT instead of putting them in the buffer. This seems to help, but when I get up around 10,000 tables dumped things get really bogged down and it's taking 7-10 seconds per table (very small tables of 50 1-column records).

Mike Kruckenberg
Co-author of Pro MySQL (Apress)
http://mike.kruckenberg.com