I'm running the following command to backup a mysql database from a remote machine:

ssh root@example.com 'mysqldump mainserver' | gzip -c > mainserver.sql.gz

I've timed the command and it seems to take about 2.5 to 3 minutes. It seems to work with no issues.

The issue, though, is all other network traffic on the remote server seems to be interrupted.

The remote server has "comet"-like http connections (connections that remain open for an extended period) and those connections seem to stop. There are also other TCP connections that are maintained open that seem to disconnect at exactly the point where the above ssh command finishes. (at least the log messages appear when the ssh command stops, but I suspect that no data is being transferred earlier than that).

  • Probably because the dump is locking the db, and the "comet-like" http connections (and anything else that's observed to be delayed/hung) have to wait for the lock to be released. "Hangs all other network activity" is a bit of a bizarre way to look at/describe what's happening – AD7six Feb 23 '15 at 20:51
  • @AD7six - please make that an answer! I'm using InnoDB so I didn't think that was an issue, but apparently I needed to add --single-transaction to mysqldump to stop it from locking everything up. – Tim Tisdall Feb 23 '15 at 21:22
  • @AD7six I started off looking for why I was having connection issues and so that's how the issue presented itself. I'm using asynchronous code that would hang at the SQL statements but the side effect was nothing was attending to the network connections due to that hanging. – Tim Tisdall Feb 23 '15 at 21:24

The database is locked

While it's described in the question as "all other network traffic on the remote server seems to be interrupted" it is much more likely that the database dump locks (all) innodb tables.

In this scenario any process reading/writing to locked tables will wait for the dump to finish, before it can then obtain a lock itself, read/write to the database and finish the request. If all http traffic needs to read/write to the database this would give the perception of all http traffic being blocked.

Preventing locks

Mysqldump provides the --single-transaction flag, which permits mysqldump to not lock innodb tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.