Hi folks,
I've recently tried to do a database backup on a database server that has a fairly high concurrency rate (1000+ queries/sec) and have noticed that the backup process seemed to deadlock the machine and I had to resort to extreme measures to get the database back up (killed the process and had to restart it in recovery mode). The command: mysqldump --all-databases --opt --single-transaction --master-data=1 >dump.txt All my tables use InnoDB, and the database is about 100 gigabytes in size. Does anyone have any suggestions for getting consistent database snapshots? I tried the InnoDB binary backup tool in the past, but that lead to a corrupted database, and I'm not sure that it'll lead to a different outcome as both single-transaction and the binary backup tool use the same mechanism (versionnning). The documentation describes the single-transaction as taking a short global lock, which is the root cause of the deadlock I saw I believe. When the server was deadlocked, all the connections were 'waiting on table', and the backup process was apparently stuck on 'flushing tables'. Cheers, Mark Steele Information Systems Manager Zango E: [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> P: 514.787.4681 | F: 514.787.4707 www.zango.com <BLOCKED::http://www.zango.com> Read our blog at http://blog.zango.com <BLOCKED::http://blog.zango.com>
