"Too many connections" when running MySQLDump MySQL 5.5.x on Windows, 4GB RAM.

I have a Wordpress Multi-Site install.  The WPMU db contains over 6k tables (I know, horrendous... WPMU is not my favorite app right now)

I am running mysqldump early mornings, and from my monitoring tools, I am seeing that my other db sites on the server, along with the WPMU site is going down for about 10-15 min during the time when the WPMU db is being dumped.  The error is "Too many connections"

MySQL is currently set to 100 connections.

What is my best path forward to mitigate the downtime? Up the connections, and throw more RAM at the box?
If you convert all your tables to InnoDB, then you can perform your mysqldumps without disturbing any of DB Connections.

Here is a script to mass convert all MyISAM tables into InnoDB.

mysql -uroot -p -AN -e"SELECT CONCAT('ALTER TABLE ',table_schema,'.',table_name,' ENGINE=InnoDB;') InnoDBConversionSQL FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql','performance_schema') ORDER BY (data_length+index_length)" &gt; /root/ConvertMyISAMToInnoDB.sql


Just execute /root/ConvertMyISAMToInnoDB.sql during a full maintenance. This needs to be done only once. Once all your tables are InnoDB, you could probably mysqldump your databases in parallel. I wrote an earlier post back in April 2011 on multiple ways to perform mysqldumps. You need option 2. You will also need to add the --single-transaction option to each mysqldump so that all the mysqldumps are launched from the same point-in-time.

However, you could still experience performance problems anyway because each table that is being mysqldump'd will push old data out of the InnoDB Buffer Pool to push in table data for each table being mysqldump'd.

Operations must slow down because queries being executed must share the InnoDB Buffer Pool with the mysqldumps that are thrashing it.

As for upping the max_connections you can do this without restarting mysql.

Please add this to my.ini

[mysqld]
max_connections=1000


You do not have to restart mysql. Just run this command in the mysql client:

mysql&gt; SET GLOBAL max_connections = 1000;


You may need more RAM on the box to accommodate additional DB Connections as well as a Decent Sized InnoDB Buffer Pool.