Effective MySQL之備份與恢復

五分鐘成為一名DBA

  如果我們已經有了一個MySQL生產級系統，而該產品卻沒有MySQL備份策略，那麼我們至少應該做些什麼呢？在採取任何備份策略之前，有許多有關資料庫 大小和儲存策略引擎的用法的預備知識需要了解，在執行任何備份方案期間，上述二者對於系統的可用性都會產生直接影響。

  本章將討論在確定一個最小功能性備份時所需的方法，包括：

●      確定資料庫的大小

●      確定儲存策略引擎的使用

●      鎖和當機帶來的影響

1.1 My SQL備份

  備份MySQL環境的策略不止一種，它們都取決於MySQL拓撲中伺服器的數量。有大量的開源或商業工具軟體可用於執行備份。第2章中將對這些策略進行詳細討論。

  現在我們要討論的情形是：環境中只有一臺伺服器，且需要建立一個一致的備份策略。我們有兩個適用於所有MySQL環境的選擇方案。第一個就是把MySQL 例項停下來，然後對整個檔案系統進行冷備份。這樣做會導致系統在一段不確定的時間內不可用，還要保證對所有正確的資訊都進行了複製，這些正確的資訊包 括：MySQL資料、可用的事務和二進位制日誌資料，以及當前的MySQL配置。

  第二個選擇方案是利用標準MySQL安裝中所包含的一個客戶端工具。使用mysqldump命令可不停止MySQL例項就能夠產生一個一致的備份。但在使用mysqldump命令時，需要做出幾個重要的決定，以便選定最佳方案。這些決定包括：

●      需要備份的資料庫有多大？

●      要生成一個一致性備份，什麼鎖策略是必需的？

●      備份需要佔用多長時間？

.1.1  確定資料庫的大小

  執行一次MySQL備份時，需要考慮一個重要問題，那就是將MySQL備份到本地磁碟上時，這個備份有多大。需要確保有足夠的磁碟空間來儲存備份檔案。

  透過下面的SQL語句，可以得到當前的資料和索引的總大小(以MB為單位)：

mysql>SELECT ROUND(SUM(data_length+index_length)/1024/1024)

   ->          AS total_mb,

   ->          ROUND(SUM(data_length)/1024/1024)ASdata_mb,

   ->          ROUND(SUM(index_length)/1024/1024)ASindex_mb

    ->FROM    INFORMATION_SCHEMA.tables;

+----------+---------+----------+

| total_mb | data_mb | index_mb |

+----------+---------+----------+

|      927 |      847 |       80 |

+----------+---------+----------+

  執行所 得的備份的大小大致與資料的大小相同，但為了安全起見，有10%到15%的冗餘。這種計算是不精確的，然而，這一備份會產生一個資料的基於文字的輸出。例 如，一個在資料庫中4位元組的整數，在mysqldump備份檔案中就可能長達10字元位元組。在執行備份的同時將備份壓縮或傳輸到另一個不同的網路裝置上是 可能的，在第2章和第8章中將對它們及相關的限制進行討論。

  執行上述SQL語句得到的資料庫中的資料的大小是847MB，後面我們會看到，用通常的預設選項執行mysqldump所得的備份檔案的大小是818MB。第8章中的示例資料庫的大小是4.5GB，而所產生的備份檔案的大小卻只有2.9GB。

1.1.2  選擇鎖策略

  所選擇的鎖策略將決定在執行備份期間，應用程式是否可以對資料庫執行寫操作。預設情況下，mysqldump利用LOCK TABLES命令進行表級加鎖，以便確保所有資料有一個一致的版本。這取決於--lock tables命令列選項，而這一選項在預設狀態下是disabled的，它是--opt選項的一部分，而--opt選項在預設狀態下是enabled的。 我們可以不鎖表，但這樣一來，就不能保證備份的一致性了。當使用MyISAM儲存引擎時，--lock-tables對於確保備份的一致性而言是非常必要 的。

  反過來，提 供了--single-transaction選項，它可以為一個單獨的事務中所有的表建立一個版本一致的快照。這一選項只有在使用某種支援多版本的儲存 引擎時才可用。InnoDB是MySQL預設安裝時唯一包含的儲存引擎。使用這一選項時，它自動關閉--lock-tables。

下面的SQL語句將確認當前MySQL例項所使用的儲存引擎：

mysql> SELECT table_schema, engine, COUNT(*) AS tables

    ->FROM    information_schema.tables

    -> WHERE   table_schema NOT IN

   ->          ('INFORMATION_SCHEMA','PERFORMANCE_SCHEMA')

    ->GROUP BY table_schema, engine

    ->ORDER BY 3 DESC;

+--------------------+--------+--------+

|table_schema       |engine | tables |

+--------------------+--------+--------+

|shopping_cart      |MyISAM |    109 |

|cust_db            |InnoDB |     48 |

|mysql              |MyISAM |     21 |

|analytics          |InnoDB |     20 |

|phpmyadmin         |MyISAM |      8 |

|newsletter         |MyISAM |      8 |

|cust_db            |MyISAM |      3 |

|mysql              |CSV    |      2 |

+--------------------+--------+--------+

在本例中，此MySQL例項包含數個不同的支援不同功能的模式(schedule)，包括一個購物車、時事新聞和管理工具。一個完整的InnoDB應用具有如下形式：

+--------------------+-------------+-------------+

| table_schema       |  engine     |      tables |

+--------------------+-------------+-------------+

| prod_db            |  InnoDB     |         122 |

| mysql              |  MyISAM     |          21 |

| mysql              |  CSV        |           2 |

+--------------------+-------------+-------------+

如本例所示，mysql元模式使用MyISAM，這是無法改變的。如果資料庫使用了完整的InnoDB，則將會有兩種處理MyISAM的mysql表的選擇，本章後續部分將對此進行討論。

1.1.3  執行時間

確定備份需要消耗多長時間是最重要的需求。沒有什麼計算方法可以給出精確答案。資料庫的大小、系統的RAM的容量、所使用的儲存引擎、MySQL的 配置、硬碟的速度以及當前的工作負載等都會影響到計算結果。在執行備份時收集這種型別資訊的重要意義在於將來要用得到它們。執行時間是重要的，這是因為它 是維護資料庫的有效視窗。在資料庫備份期間，可能會存在應用程式的功能限制、效能開銷等，而且備份還可能限制了其他操作，如批處理或軟體維護等。

1.1.4  組合資訊

下面是一條推薦的SQL語句，它會把所有資訊組合起來，以便對資料庫大小進行稽核：

$cat  storage_engines.sql

SELECT table_schema, engine,

         ROUND(SUM(data_length+index_length)/1024/1024) AStotal_mb,

         ROUND(SUM(data_length)/1024/1024) AS data_mb,

         ROUND(SUM(index_length)/1024/1024) AS index_mb,

        COUNT(*)  AS tables

FROM  information_schema.tables

GROUP BY  table_schema,  engine

ORDER BY  3  DESC;

 

mysql> source  storage_engines.sql

+--------------------+--------+-------+-------+-----+-----+

| table_schema |engine | total_mb | data_mb | index_mb | tables |

+--------------------+--------+-------+-------+-----+-----+

| analytics          | InnoDB | 10930 | 10525 | 378 |  20 |

| cust_db            | InnoDB |  1155 |   962 | 194 |  48 |

| newsletter         | InnoDB |   514 |   278 | 237 |   7 |

| shopping_cart      | MyISAM |    27 |    19 |   8 | 109 |

| cust_db            | MyISAM |     9 |     3 |   7 |   3 |

| mysql              | MyISAM |     1 |     0 |   0 |  21 |

| information_schema | MyISAM |     0 |     0 |   0 |   8 |

| information_schema | MEMORY |     0 |     0 |   0 |  20 |

| mysql              | CSV    |     0 |     0 |   0 |   2 |

+--------------------+--------+-------+-------+-----+-----+