[How To] Online Backup Innodb Tables

Backup the database inevitable cause MySQL server unavailable to applications because when exporting, all tables acquired a global read lock using FLUSH TABLES WITH READ LOCK at the beginning of the dump until finish. So although READ statements can proceed, all INSERT, UPDATE and DELETE statements will have to queue due to locked tables, as if MySQL is down or stalled. If you’re using InnoDB, –single-transaction is the way to minimize this locking time duration to almost non-existent as if performing an online backup. It works by reading the binary log coordinates as soon as the lock has been acquired, and lock is then immediately released.

mysqldump -u username –ppassword –all-databases –single-transaction > dump.sql

[box type=”warning”] Don’t put space after -p and then password[/box]