前言

最近工作的專案是使用 Java SpringBoot 搭配 MySQL 資料庫做數據儲存與管理，部署在客戶環境時需要備份資料，為了有效地完成備份工作，使用 MySQL 常見的備份工具 - mysqldump，但過程中牽涉到很多參數的用途，所以有了這篇文章做紀錄，以供未來參考

什麼是 mysqldump？

mysqldump 是 MySQL 提供的一個命令行工具，用於備份資料庫。它可以生成 SQL 語句，這些語句可以用於重建資料庫的結構和數據。這使得 mysqldump 成為一個非常靈活且易於使用的備份解決方案

基本語法

mysqldump [options] database [tables]
  • database 是你要備份的資料庫名稱
  • tables 是可選的，表示你可以指定要備份的特定表

常用用途

  1. 備份整個資料庫

    要備份整個資料庫，可以使用以下指令：

    mysqldump -u username -p database_name > backup.sql
    • -u: 用於指定用戶名
    • -p: 會提示輸入密碼
    • backup.sql: 是你要生成的備份文件名

  2. 備份特定表

    如果只想備份特定的表，可以這樣做：

    mysqldump -u username -p database_name table1 table2 > backup.sql

    這將備份 table1 和 table2 兩個表

常用參數 (Options)

底下列出在實際備份時，個人有使用到的一些參數，如果要了解全部的參數意義，可參考 官方文件

需格外注意的是 MySQL 有區分幾種 engine，最常見的就是 MyISAM 和 InnoDB，前者是 non-transactional，後者才是 transactional，這會影響到備份時是否需要鎖表，以及帶來的效能影響與資料一致性的差異

  1. --single-transaction

    • 對於 InnoDB 引擎的資料庫，建議使用 --single-transaction 選項來獲取一致性的快照(snapshot)，而且不會鎖定表
    • 這樣可以在備份過程中允許其他操作(ex: UPDATE, INSERT)繼續進行，適合在運行環境(live environment)中備份

  2. --lock-tables

    • 預設 mysqldump 會使用 --lock-tables，可以使用 --skip-lock-tables 來關閉
    • 由於另一個 engine 叫 MyISAM 是 non-transactional，為了滿足資料的一致性，要使用 --lock-tables 來確保在備份期間不會有其他寫入操作
    • 要特別注意這會鎖定所有表進而影響性能

  3. --quick

    • 它強制 mysqldump 逐行從伺服器拿取表格的行，而不是一次性拿取整個行集並在寫出之前將其緩存在記憶體中
    • 適合備份龐大資料量的表格

  4. --no-tablespaces

    • 可防止 mysqldump 在備份檔案中包含任何 tablespace 的資訊
      • tablespace 是 MySQL 用來管理表的儲存
    • 適合轉移資料庫到別的伺服器的情境

  5. --skip-add-drop-table

    • 預設 mysqldump 會在每個 CREATE TABLE 語句之前添加一條 DROP TABLE IF EXISTS 語句，以確保在創建新表之前刪除現有的表
    • 該參數會告訴 mysqldump 不要在備份檔案中的 CREATE TABLE 語句之前包含 DROP TABLE 的語句，所以如果表已存在，則在嘗試創建時備份將會失敗

  6. --no-create-db

    • 預設 mysqldump 會 CREATE DATABASE 來創建資料庫（如果它不存在）
    • 使用此參數可避免 mysqldump 在備份檔案中包含該語句
    • 適合將備份檔還原到已存在的資料庫而不重新建立的情境

  7. --no-create-info

    • 預設 mysqldump 會 CREATE TABLE 來創建資料表（如果它不存在）
    • 使用此參數可避免 mysqldump 在備份檔案中包含該語句，意味者備份檔案中僅包含 INSERT 語句，而不包含資料表的 schema
    • 適合只想備份數據時(假設目標資料庫中已存在資料表的 schema)

  8. --replace

    • 此參數會將備份檔中的 INSERT 語句修改為 REPLACE
    • REPLACE 會在表中插入新行，如果已存在具有相同主鍵的行，將刪除現有行並插入新行
    • 適合確保還原時，資料庫的資料來自於備份檔案中的新數據

  9. --complete-insert

    • 此參數會在備份檔案中保留欄位名稱，不單單只是 INSERT INTO table VALUES (...)，而是產生 INSERT INTO table (column1, column2, ...) VALUES (...)
    • 此舉不但增加備份檔案的可讀性，也確保未來還原資料時，即使資料表的 schema 更動，也能明確的將資料寫入到正確的欄位內
    • 適合當資料表的 schema 更動時，能確保將資料正確寫入到正確欄位內

還原備份

要還原備份，可以使用以下指令：

mysql -u username -p database_name < backup.sql

或是使用

cat backup.sql | mysql -u username -p database_name

如果使用 Docker 架設 MySQL，也可以用一行指令來還原資料

cat *.sql | sudo docker exec -i <db_container_id> mysql -u username -p database_name

結論

定期備份 MySQL 資料庫是確保數據安全的重要措施。透過 mysqldump 工具，可以輕鬆地創建資料庫的備份，並根據需要選擇合適的選項來滿足不同的需求

參考資料