We sometimes hear from customers that they want to export their database - say, to have a development copy on their desktop - but they can't get mysqldump to complete. Sometimes the connection to their database is dropped before the dump is complete. Sometimes they get an error message that they have no free connections. Here are a few guidelines to optimize your use of mysqldump.

First, you should understand the topology of our ClearDB multitenant databases. When you connect to ClearDB, your application is actually talking to a ClearDB Router (CDBR). The CDBR is a very smart, specialized high-availability management layer. It talks to multiple master nodes that actually contain your database; each node is constantly replicating to stay in sync with its partners. If one node should fail, the CDBR knows not to send any queries to it until it reports itself healthy and synchronized again.

One of the ways the our clusters keep performance high is by watching for "slow queries", defined as any query that takes more than 60 seconds to complete. Under normal circumstances that should never happen, so the CDBR will close the connection. But entire db dumps can easily take that long. So unless your db is very small, to do a mysqldump you should bypass the CDBR and connect directly to the node designated as the backup head; this will prevent your connection from timing out if the dump takes longer than 60 seconds to complete. If you don't know the hostname of the designated backup head, contact Support.

Second, you should use the --single-transaction switch in mysqldump, which will ensure the consistency of your backup and prevent your db from becoming unavailable due to table locks during the process.

Our Support team is always available to help you with this or any other issue.