Question :
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?
Answer :
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.
Easiest option, if you aren’t relying on features specific to MyISAM or other storage engines: change your tables to the InnoDB storage engine, and use the -single-transaction option with mysqldump.
This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.
When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.
While a –single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.
The –single-transaction option and the –lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.
Also this option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.
To dump large tables, you should combine the –single-transaction option with –quick.