MySQL: mysqldump and dropping database connections

I'm using MySQL as the database to store a lot of measurement data coming from the mobile phones of around twenty users. The server backend is written in Java and it connects to the database using a connection pool. Every day at 3AM a copy of the entire database is made using mysqldump.

As time progressed we would see an enormous amount of SQL error messages in the logs, about the connection between Java and the database being dropped, and 'too many connections' errors from the database pool. Lines like these:

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.Connection was implicitly closed by the driver.

The last packet successfully received from the server was 8,532 milliseconds ago.  The last packet sent successfully to the server was 8,532 milliseconds ago.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Too many connections

At first I couldn't figure out what the cause was (tried to fiddle with validation queries, pool sizes etc) until I finally noticed that all these errors happened just after 3AM. That's where it clicked: mysqldump by default locks the entire database you're trying to backup for the duration of the backup, to ensure that it generates a consistent dump. As the size of the data grew (the backup is about 750 Mb at the moment) it took mysqldump a lot longer to generate the backup.

This progressed up to the point where the Java connections to the database would timeout causing all kinds of problems. Furthermore the 'too many connections' errors were cause by the fact that whilst the database is locked, the SQL connections block, and are therefor not returned to the pool: thus the pool keeps on creating new connections until it maxes out and  starts throwing these errors.

The solution for me was to add the following parameters to the mysqldump command:

mysqldump --quick --single-transaction ...

The --quick options just speeds up the process of retrieving the data from the database (see here), the real improvement comes from the --single-transaction option: instead of locking the database, mysqldump then uses the transaction mechanism to isolate the backup from concurrent changes to the database. Note that this only works if you're using InnoDB tables.