snippetsqlMinor
How to backup a MySQL database without slowing the application?
Viewed 0 times
withouttheapplicationdatabasemysqlslowinghowbackup
Problem
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?
Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?
Solution
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.
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.
Context
StackExchange Database Administrators Q#14335, answer score: 5
Revisions (0)
No revisions yet.