Hi all,
This probably isn't related to SMF at all but I'm wondering if someone here can help because I'm a bit clueless with this stuff! :P
Every other day or so I SSH into my dedicated server and dump the SMF database into a file using this commend:
mysqldump -u XXXX -p XXXX > dump.sql
Then I gzip the file and download, archive it, etc. This system has been working great for many many months.
But all of a sudden mysqldump just hangs forever when I run it! Well, I guess I can't say forever ;D but I've waited upwards of 30 minutes for it to finish. The dump file gets to be a certain size and never grows no matter how long I wait. So I think it's hung up somewhere.
Any ideas at all what could be causing this and how to fix it?
Thanks!
Leah
It could be that the error log or some other table is unexpectedly large?
What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying
Do you see any queries that are holding things up?
Also, I'd suggest the use of:
mysqldump -CceKq --single-transaction -uusername -p > dump.sql
Or:
mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz
-[Unknown]
Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
It could be that the error log or some other table is unexpectedly large?
What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying
Do you see any queries that are holding things up?
It doesn't catch anything with this command.
Is there a safe way to clear out the error log?
Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz
This also hangs. (But it's handy to know I gzip it at the same time as dumping it!)
What does the -CceKq do by the way?
Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
It could be that the error log or some other table is unexpectedly large?
What happens if you run, in another shell:
mysqladmin proc | grep -e Sending -e Copying
Do you see any queries that are holding things up?
It doesn't catch anything with this command.
Is there a safe way to clear out the error log?
Quote from: [Unknown] on August 04, 2005, 09:07:51 AM
mysqldump -CceKq --single-transaction -uusername -p | gzip -7 > dump.sql.gz
This also hangs. (But it's handy to know how to gzip it at the same time as dumping it!)
What does the -CceKq do by the way?
Nothing? What about:
mysqladmin proc
Alone?
The CceKq options do, respectively:
C: use compression between server and client (to reduce memory usage, or bandwidth if across servers.)
c: use complete inserts, meaning with column names not just values.
e: insert multiple rows with one statement, which is a lot faster and generates a much smaller backup.
K: put in the backup commands to turn off and back on keys, to improve restoration speed.
q: dump more quickly for larger tables, more directly without a middle buffer between data and stdout.
And --single-transaction makes your backup more reliable if the forum is being used at the same time and you have any InnoDB tables.
-[Unknown]
Here's potentially another clue? I opened up the dump file and see how far it gets each time. It successfully dumps these tables:
-- Dumping data for table `dbyabbse_attachments`
-- Dumping data for table `dbyabbse_ban_groups`
-- Dumping data for table `dbyabbse_ban_items`
-- Dumping data for table `dbyabbse_board_permissions`
-- Dumping data for table `dbyabbse_boards`
-- Dumping data for table `dbyabbse_calendar`
-- Dumping data for table `dbyabbse_calendar_holidays`
-- Dumping data for table `dbyabbse_categories`
-- Dumping data for table `dbyabbse_collapsed_categories`
-- Dumping data for table `dbyabbse_fc_bans`
-- Dumping data for table `dbyabbse_fc_connections`
-- Dumping data for table `dbyabbse_fc_ignors`
-- Dumping data for table `dbyabbse_fc_messages`
-- Dumping data for table `dbyabbse_fc_rooms`
According to older backups I have, the next table should be: dbyabbse_log_actions
What's also odd is that when I show the mySql Process List, this is at the top of the list:
| 30400366 | root | localhost | craftster_smf | Query | 212527 | Waiting to get writelock | OPTIMIZE TABLE `dbyabbse_log_actions`
I have no idea what any of this means. But thought it might be helpful.
Hmm... that could be causing problems. Do:
mysqladmin kill 30400366
The log_actions table is just a log, and can be cleared either from the administration panel or by TRUNCATEing it.
-[Unknown]
WOO HOO! I have a backup! Phew. I can sleep again now. :)
[Unknown] - can I hire you as my personal admin? ha ha... I wish! Man -- I really need to take a class or buy a book on mySQL.
Can I ask you one more question...
Do you recommend, generally speaking, that I use these arguments to mysqldump:
Quote
mysqldump -CceKq --single-transaction -uusername -p
I want to be sure that my backups will have everything they need in case I need to restore from one of them.
Thank you!!
With or without those arguments, you should be fine. The most important one is --single-tansaction, which will make things better upon restoration.
-[Unknown]