SSH, mysqldump > gzip timing out

[GoWilkes]

I'm having what I think is a minor problem, but since my MySQL is held together with bubble gum and prayers I want to make sure that it's minor before I do anything else.

When I backup MySQL for my largest account, this is the command I'm running via Putty (SSH):

mysqldump --single-transaction --quick my_db_name| gzip > /backup/my_db_name.sql.gz

The database is about 15G.

When I look at it via FTP, I can see that it's finished in about 20 minutes. The problem is that Putty appears to continue running for about 40 minutes after it has completed, so I can't do anything else until Putty times out!

I've logged in to a second session during this period, and it doesn't look like anything is actually running. So it's just Putty or SSH that's getting hung up.

I tried this alternative yesterday, but it did the same thing:

mysqldump --single-transaction --quick my_db_name > /backup/my_db_name.sql && gzip /backup/my_db_name.sql

I'm only assuming that the backup file is complete and error free, I'm not sure how I could check that.

Any ideas on why it's getting hung up? My server provider said they've never seen it happen before. It only started happening in February or early March, so maybe there's a timeout setting or something?

 

[GoWilkes]

I just now ran tmpwatch, using:

tmpwatch --mtime --all 168 /tmp

It finished running about 20 minutes ago but Putty still looks like it's running, so it's definitely not an issue with mysqldump or gzip!




Update:

After that session finally timed out, I opened a new session and used "crontab -e" to add the tmpwatch cron. It went through without any delays, so I'm just guessing that it's only messing up when something takes awhile to process?

 

[cPRex]

Are we sure it's even bubble gum some days?

The best way to confirm if things are still happening would require two SSH sessions - one where you run your mysqldump command, and one where you watch the output with strace. Once you think it's done, you can run "strace -p XXXXX" where "XXXXX" is the PID of your dump command. That will show if anything is actively happening with that process.

To verify the dump, the best way is to perform an import to a test database and ensure all data is there.

 

[GoWilkes]

Thanks, I'll try that next time. I've been creating a second session and using top to see if it's running, but not strace.

It appears to be done at a normal time using top, though. The more I think about it, the more that I'm convinced that it's either an issue with Putty or with SSH getting hung up.

Are we sure it's even bubble gum some days?

Haha, I was being generous. At best, I'm pretty sure it's the cheap bubble gum, never the good stuff ;-P

 

[cPRex]

If your host has a console option you could try running it through that instead, as that isn't something that should experience typical SSH timeouts.

 

[GoWilkes]

Thanks! I see "Terminal" under WHM, so I'll give that a shot tonight and see what happens. If it works properly then would it be safe to assume that the problem is with Putty?

 

[cPRex]

That sounds right to me!

 

[GoWilkes]

Just ran a backup, it completed in 10 minutes via WHM > Terminal; faster than Putty and did not get hung up. So the problem appears to be with Putty :-/

I changed Putty's Keepalive setting from 0 to 180, I'll try again tomorrow and post back with an update.

 

[GoWilkes]

For the sake of posterity, the Keepalive setting seemed to fix the issue

 

[cPRex]

Nice - I'm glad that worked well!