Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I'm an application developer and wondering about our database backup strategy. Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is running our application response time goes through the roof. So every 8 hours, the application is slow and sometimes even produces errors.

Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?

share|improve this question

1 Answer

up vote 4 down vote accepted

A) Take the backup from a replicating slave. Zero performance loss.

B) Use LVM to take a consistent snapshot. Copy the database files at your leisure.

C) Use Rsync over a remote connection to copy the data files, then do a final Rsync with the database briefly locked for a couple seconds.

As an aside, how are they taking a consistent backup with MyISAM tables without LOCKING the application for 8 hours straight? It doesn't seem possible.

share|improve this answer
1  
+1, I could use these tips. On the other hand, I think he means the backs are happening every eight hours, rather than for eight hours. – StanleyJohns Mar 4 '12 at 0:49
Ah yes, I misread. – Brian Papantonio Mar 4 '12 at 0:54
1  
Something tells me the sysadmins aren't taking consistent snapshots as it is. And if you care about your data at all, why haven't you switched to Innodb? – Brian Papantonio Mar 4 '12 at 0:57
As Stanley says, it's 3 times a day every 8 hours. The Innodb thing, well, "company standard". Any persuasive links on the MyISAM/Innodb thing I can take with me when I tell them to think about backups from slaves? – Jan P. Mar 4 '12 at 1:29
1  
I see so many links on Google about using InnoDB instead of MyISAM that I honestly can't pick any one. In my professional experience, there's rarely a good reason to use MyISAM. Even if you don't use ANY of the features of InnoDB, it's still a better choice than MyISAM because InnoDB uses a doublewrite buffer to write your data. This makes recovery possible in the event of a crash during a write operation. – Brian Papantonio Mar 4 '12 at 2:26
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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