Mysqldump causes “Too many connections”

I've had good success with MySQL replication and nightly tarballs. For smaller db, the mysql database, and schema I use a combination of scripts designed to use mysqlhotcopy and mysqldump.

InnoDB hot backup is a great commercial product but I'm not sure how it handles mixed tables in the same database. pQd's recommendation for XtraBackup may be good to compare against this.

Others like LVM snapshots and I'd say that's definitely something to consider. Ultimately, a combination of solutions would probably be best.

It's also notable this is an old topic. Between the High Performance MySQL book, the MySQL manual, and previous ServerFault questions-- this has been exhausted on a general basis. See:

• Is there any equivalent of mysqlhotcopy for InnoDB?
• Backup MySQL Server
• How do you usually back up your database?

That is a nice number of tables, but the thing to remember here is that not all of them are going to be open at once - most times you'll set the MySQL parameter for open files to around 300-500, and MySQL itself will keep the ones most active on your system in the memory table pool (cache, if you will) much like how the kernel keeps memory pages for applications around.

I personally am a firm believer in the file per table as a systems management tool; while it does have it's tradeoffs as you rightly bring up, the ability to keep each client's tables distinct (you could even spread them out to different disks - RAID, SAN, DAS, etc. - by customer database!), back the raw ones up by hand if needed, optimize them if needed and so forth goes a lot longer at the end of the day. The main price you pay is more fsync() calls during writes which can degrade performance if you have a lot of tables being written too, but in general it's not too bad.