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
Don’t put space after -p and then password

Leave a Reply

Your email address will not be published. Required fields are marked *