我有點驚訝地發現 DDL 語句（alter table等create index）隱式送出 MySQL 中的目前事務。來自 MS SQL Server，在本地事務中進行數據庫更改（然後回滾）的能力是我工作流程的重要組成部分。對於持續集成，如果遷移因任何原因而中斷，則使用回滾，這樣至少我們不會使數據庫處於半遷移狀態。

當使用 MySQL 進行遷移和持續集成時，人們如何解決這兩個問題？

對於許多人來說，MySQL 的致命弱點是隱式送出。

根據本書第 418 頁第 3 段

以下命令可以並且將會中斷事務

• ALTER TABLE
• BEGIN
• CREATE INDEX
• DROP DATABASE
• DROP INDEX
• DROP TABLE
• RENAME TABLE
• TRUNCATE TABLE
• LOCK TABLES
• UNLOCK TABLES
• SET AUTOCOMMIT = 1
• START TRANSACTION

建議

對於 MySQL，您建構的任何 ContinuousIntegration (CI)/SelfService 作業都應始終使 Transactional 作業和 DDL 腳本互斥。

這使您有機會創建將

• 支持與START TRANSACTION/COMMIT塊正確隔離的事務

• 通過自己編寫 DDL 腳本來控制 DDL，將 DDL 作為建構子或解構子執行

  • 建構子：使用新設計製作表格的 DDL
  • 解構子：DDL 使表恢復到以前的設計

• 永遠不要將這些操作合併到一項工作中

**警告：**如果您將 MyISAM 用於任何此操作，您可以（不）友好地將 MyISAM 添加到可能破壞事務的事物列表中，可能不是在隱式送出方面，但在數據一致性方面絕對應該回滾需要。

為什麼不使用 LVM？

LVM 快照非常棒，無需執行繁重的 SQL 處理即可恢復整個數據庫實例是理想的選擇。但是，當涉及到 MySQL 時，您必須考慮兩個儲存引擎：InnoDB 和 MyISAM。

全 InnoDB 數據庫

查看 InnoDB 的架構（圖片由 Percona 首席技術官 Vadim Tkachenko 提供）

InnoDB 有許多活動元件

• 系統表空間
  • 數據字典
  • Double Write Buffer（支持數據一致性；用於Crash Recovery）
  • 插入緩衝區（緩衝對二級非唯一索引的更改）
  • 回滾段
  • 撤消空間**（最不受控制的增長可能發生的地方）**
• InnoDB 緩衝池
  • 臟數據頁
  • 臟索引頁
  • 對非唯一索引的更改
• 其他重要的記憶體記憶體

對所有 InnoDB 數據庫進行 LVM 快照，其中浮動在緩衝池和記憶體記憶體中的未送出更改將產生一個數據集，一旦 LUN 恢復並啟動 mysqld，就需要 InnoDB 崩潰恢復。

ALL-InnoDB 的建議

如果您可以在拍攝快照之前關閉 MySQL

• 1. 跑步SET GLOBAL innodb_fast_shutdown = 0;
• 2. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 3. 跑步SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 4. 重複步驟 3 直到Innodb_buffer_pool_pages_dirty為 0 或盡可能接近 0
• 5. service mysql stop
• 6. 拍攝 LVM 快照
• 7. service mysql stop

如果您無法關閉但使用 MySQL Live 拍攝快照

• 1. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 2. 跑步SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 3. 重複步驟 2，直到Innodb_buffer_pool_pages_dirty為 0 或盡可能接近 0
• 4. 拍攝 LVM 快照
• 5. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 75;

All-MyISAM 數據庫或 InnoDB/MyISAM 混合

MyISAM 在被訪問時會維護一個打開文件句柄的計數。如果 MySQL 崩潰，任何打開文件句柄計數 > 0 的 MyISAM 表都將被標記為崩潰並需要修復（即使數據沒有任何問題）。

在恢復快照並啟動 mysqld 時，為具有正在使用的 MyISAM 表的數據庫拍攝 LVM 快照將有一個或多個 MyISAM 表需要修復。

All-MyISAM 或 InnoDB/MyISAM 混合的建議

如果您可以在拍攝快照之前關閉 MySQL

• 1. 跑步SET GLOBAL innodb_fast_shutdown = 0;
• 2. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 3. 跑步SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 4. 重複步驟 3 直到Innodb_buffer_pool_pages_dirty為 0 或盡可能接近 0
• 5. service mysql stop
• 6. 拍攝 LVM 快照
• 7. service mysql stop

如果您無法關閉但使用 MySQL Live 拍攝快照

您可以強制刷新某些 InnoDB 表

• 1. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 0;
• 2. 跑步SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
• 3. 重複步驟 2，直到Innodb_buffer_pool_pages_dirty為 0 或盡可能接近 0
• 4. FLUSH TABLES innodb_tbl1,... FOR EXPORT;在關鍵 InnoDB 表上執行
• 5. 跑步FLUSH TABLES WITH READ LOCK;
• 6. 拍攝 LVM 快照
• 7. 跑步UNLOCK TABLES;
• 8. 跑步SET GLOBAL innodb_max_dirty_pages_pct = 75;

MySQL 複製有幫助嗎？

雖然您可以將一個 LVM 快照恢復到兩台伺服器並設置 MySQL 主/從複製，但在恢復快照時這會成為額外的清理來源。

如果您在 Master 上執行 CI 作業並且這些作業很小，那麼在某些情況下複製可能會節省時間。您可以只STOP SLAVE;在 Slave 上執行，在 Master 上啟動 CI 作業，然後START SLAVE;在 Master 的數據得到認證後在 Slave 上執行。

如果 CI 作業提醒過多數據，您可以從頭開始恢復 LVM 快照並設置複製。如果您發現自己經常這樣做，您可能可以設置 MySQL 複製。

最後的想法

• 最好使用多個數據庫伺服器（3 個或更多）來執行恢復和回歸測試。
• 如果這些表不需要保留 MyISAM，則將剩餘的 MyISAM 表轉換為 InnoDB。
• 如果您的數據內容很敏感，您應該在恢復快照後執行 CI 作業以清理數據，然後再啟動任何測試。作為替代方案，您可能希望使用已清理的數據拍攝 MySQL 的快照。

如果您談論持續集成，那麼我認為它是一個開發環境。在這種情況下，我會說進行結構更改的人必須測試它們以確保不會破壞他人的東西，就像更新公共庫的人一樣：在送出此類更改之前在您自己的沙箱中進行測試。

在生產部署過程中，您通常會通過開發、QA 甚至預生產環境來測試您的更改，就像任何程式碼更改一樣。

請注意，這不是 MySQL 特定的：Oracle 數據庫在發出“alter table”等時也會隱式執行 COMMIT。

現在，如果您想保護自己，當然可以事先進行備份，或者如果您的系統可以這樣做，則可以進行 LVM 或文件系統快照。您可能還有一個奴隸，您可以在敏感操作之前延遲/停止作為安全措施。

引用自：https://dba.stackexchange.com/questions/90794