MYSQLDUMP appears done, but is timing out

[csdude55]

This is the command I use, via SSH (Putty):

mysqldump --single-transaction --quick --lock-tables=false database_name | gzip > /backup/database_name.sql.gz

The original /var/mysql/ files are cumulatively about 15G, and the completed backup should be around 2G.

The last several times I've run it, though, SSH never shows that it's done and eventually just times out.

But it LOOKS to be done.

The last successful backup was a few weeks ago. It took 16 minutes to complete, and the GZIP file was was 2,037,494,108 bytes.

Tonight, I began the backup at 12:54am. It's now 1:26am and mysqldump still appears to still be running in Putty, but there's a file at /backup/database_name.sql.gz with the "Last modified" time of 1:07am and it's 2,051,999,839. So slightly larger than the previous backup, which is to be expected of a completed backup.

Based on that, I THINK that the backup is done, but SSH is still running it.

I could probably break out of it with "q" (I haven't tried), but then how do I know if the backup is valid?

[csdude55]

SSH just now timed out:

Network error: Software caused connection abort

[not2easy]

Does it run repeatedly? Your description of the file being created and the process continuing sounds like it just runs again, then times out.

[csdude55]

In SSH, all I see is the blank cursor that shows that it's running. I looked at /var/log/secure (apparently the log for SSH, I just found that out) but there's nothing at all between the time that I started and ended:

Mar 8 00:53:04 example sudo: pam_unix(sudo:session): session opened for user root by (uid=0)
Mar 8 00:53:04 example sudo: pam_unix(sudo:session): session closed for user root
Mar 8 01:18:05 example sshd[26512]: Accepted password for root from 123.45.67.89 port 12345 ssh2
Mar 8 01:18:05 example sshd[26512]: pam_unix(sshd:session): session opened for user root by (uid=0)
Mar 8 01:24:26 example sshd[26719]: pam_unix(sshd:session): session closed for user root
Mar 8 01:28:27 example sshd[28911]: Accepted password for root from 123.45.67.89 port 98765 ssh2
Mar 8 01:28:27 example sshd[28911]: pam_unix(sshd:session): session opened for user root by (uid=0)

The time there is a little confusing; I initiated mysqldump at 12:54am, so the 00:53:04 makes sense (logging in). But it timed out at 1:28am, then I closed Putty, reopened it, and ran "top". So I don't know what was happening at 1:18am.

[robzilla]

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

[linux.die.net...]

Maybe try dumping to a file first, then gzip it?

mysqldump --single-transaction --quick database_name /backup/database_name.sql && gzip /backup/database_name.sql

Note that you can always open another SSH session to check what's happening while mysqldump runs.

[csdude55]

I finally got around to testing this... dumping first like you suggested created the unzipped version in 8 minutes, but then the separate gzip took 12 minutes. Which isn't a big deal or anything, just noting the times.

But Putty still didn't appear to stop running. It's now 8 minutes after gzip finished, but Putty looks like it's still running.

I opened a second Putty session, and "top" doesn't look like anything's still running. Or if it is, it's not noticeable.

[csdude55]

Just an update, I found the problem! It wasn't with mysqldump or even with SSH; the problem was with Putty!

I changed the Keepalive setting from the default 0 to 180, and now it's working properly again :-)

[robzilla]

Ah, LOL. Guess I missed your post about the timeout.

[csdude55]

I'm going to pretend that it was easy to miss, since I didn't catch it, either ;-) Nothing actually says "timeout", though, so I thought that SSH was getting hung up.

It was only after I used WHM's "Terminal" without issue that I was able to narrow it down to Putty instead of the server.

[tangor]

Happy for the result. Sad thing is we often bang heads against desk searching for the OBVIOUS and often miss the IN YOUR FACE kind of things.

Been there, done that, don't want the t-shirt!