MySQL 用 MySQLDump 備份 InnoDB 注意事項

大家在備份

MySQL 資料庫時一定是使 mysqldump 指令，不管是 MyISAM 或 InnoDB 都一樣，在處理 InnoDB 格式備份時使用 mysqldump -single-transaction，但是你會發現在大多的備份狀況都是 OK 的，只是有時候會發現有的資料表只有備份到 structure 而無備份到 Data？在 MySQL Performance Blog 看到這篇講解 Best kept MySQLDump Secret，此問題出在 how MySQL’s Transactions work with DDL，ALTER TABLE 會建立一個 temporary table，並且將該資料表資料複製過去，接著刪除原有資料表，最後將 temporary table 命名為原來資料表。底下是原作者提到的原因

How does data visibility works in this case ? DDLs are not transactional and as such the running transaction will not see the contents of old table once it is dropped, transaction also will see the new table which was created after transaction was started, including table created by ALTER TABLE statement. Transactions however apply to DATA which is stored in this table and so data which was inserted after start of transaction (by ALTER TABLE statement) will not be visible. In the end we will get new structure in the dump but no data. 最好的解法就是改用 mysqldump -lock-all-tables，但是備份的時候，資料庫是無法寫入，僅能讀取，也或者可以透過 Percona Xtrabackup 工具來備份 InnoDB。

See also