Chasing down cause of crash on CDP backup

[andor]

Hi fellow JPC people,

I am looking for pointers re chasing down an issue with our dedicated server environment, and would gratefully appreciate the experience and wisdom of people with greater understanding of these things.

We have an error that happens every few months or so, and seems to be related with MySQL database running InnoDB tables, and the CDP auto backups.

What seems to happen is that those specific users' that use scripts running MySQL InnoDB tables have their data corrupted or locked. Also, any other regular MySQL MyISAM databases for these users are corrupted or cannot be accessed. Attempting to restore the databases from CDP has no effect, the data is unrecoverable/unaccessible at our level; we end up having our ticket escalated to Level 3 JPC support to get the data recovered or the issue resolved, which consistently they are able to (thank God) - and again, I *think* it happens on the scheduled backup into the CDP system, but only on a blue moon.

Weird thing is, any other user that has script running just MySQL databases with MyISAM tables, they seem to remain unscathed.

So, how does one begin to attempt to sleuth these issues?

1. With InnoDB tables, is the best procedure to stop mysqld, THEN attempt to restore the databases?

2. A mail script that we use on the server has been stated to run better using qmail for mail queue efficiency; we're using exim currently. Has anyone else got good feedback for qmail?

3. Our server regularly has mysqld running at about 30% of memory - does this sound normal?

4. How would one begin to track processes by scripts etc. calling on MySQL so one could begin to unravel any culprit/s that could be actually causing these crashes in the first place?

5. Can anyone suggest a nice script that dumps MySQL databases regularly to the server in the most efficient and rock solid way?

Any feedback would be appreciated.

David

[Ron]

You're looking for a php script or shell script to dump all of the databases or are you looking to do it by account?

[andor]

I think shell would be better, and all databases... Per account is just too fiddly

[Ron]

A simple script might be able to do that, but do you really want it all in a single file?

I use some simple scripts that dump all the databases for a single cpanel account (all done from one of the accounts) and each puts the output into its own file. They dump the sql file then compress it in separqate steps to reduce table lock (database downtime) time in the process.

There are more complex scripts out there that I've read a tiny tiny bit about such as mysqldumper.

[andor]

Hmmmm... I had quite a good script running that ran on cron, allowed me to specify databases to backup, and would also FTP or email dumps if required. This was a while back when we were on VPS. However, I think execution timeout became a problem, and when we migrated to dedicated, CDP just seemed like the better option by miles.

I think my major issue is still: what is happening to the databases in the first place? And how to diagnose what is happening. Getting to the root of the issue is probably better than treating symptoms, I guess...

[Ron]

Ahh. Yes, well that's per account (or database), and my little script would do that, but I think you're looking for something a little more advanced than my down and dirty script.

Perhaps you can quiesce the database before the backup starts?

I agree you should find the root cause, but sql dumps IMHO are really important too; I'm not sure I would stop doing them even with a full backup system like CDP.

I haven't looked into MySQL's native capabilities; do you know if it has binary logs or something similar to facilitate incremental backup strategies?

Good luck!