The Will Will Web

我們有幾個 MySQL 資料庫非常大，資料庫都將近 1GB 之多，所以每次在做完整備份時都非常耗時也非常耗費硬碟空間，因此研究出 MySQL 進行增量備份的方法，以下為設定過程的心得筆記。

首先必須設定 my.cnf 設定檔

Linux 平台

/etc/mysql/my.cnf

Windows 平台

C:\Program Files\MySQL\MySQL Server 5.0\my.ini

開啟後要找到 [mysqld] 區段，並加上 log_bin 參數指定 Binary Log 的儲存位置 ( 類似 "交易記錄檔" )

Linux 平台

[mysqld]
 
log_bin = /var/log/mysql/mysql-bin.log

Windows 平台

[mysqld]

log_bin="E:/MySQL/log/mysql-bin.log"

設定完成後將 MySQL 重新啟動，接著在指定的目錄下就會開始紀錄所有資料庫的交易紀錄，檔名如下：

mysql-bin.000001

接著建議立即做一次完整備份，需注意以下指令的參數跟一般做完整備份時不太一樣：

mysqldump --single-transaction --flush-logs --master-data=2 --all-databases -r MySQL.Full.sql

備份完成後，你會在 Binary Log 目錄下發現多出一個 Binary Log 檔，如下：

mysql-bin.000001
mysql-bin.000002

這時的 mysql-bin.000002 檔案就是你完整備份過後的「增量備份」記錄檔。

若日後資料庫需要還原時，就可以透過以下指令依序還原資料庫：

mysql < MySQL.Full.sql
mysqlbinlog mysql-bin.000002 | mysql
mysqlbinlog mysql-bin.000003 | mysql
mysqlbinlog mysql-bin.000004 | mysql
mysqlbinlog mysql-bin.000005 | mysql

如果想要一天產生一個「增量備份」檔，可以透過以下指令。執行以下指令後，會讓現有的交易記錄停止紀錄，並產生新的交易記錄檔(Binary Log)：

mysql -e "FLUSH LOGS"

若要刪除舊的交易記錄檔，可透過以下指令指定特定時間前的交易記錄檔自動刪除：

mysql -e "PURGE BINARY LOGS BEFORE '2009-07-24 00:00:00';"

注意：建議不要手動刪除交易記錄檔，透過指令刪除是比較保險的方式。

熟悉這些概念後就可以寫程式自動化所有備份作業，建議可以透過批次檔或 Powershell 進行自動化。

相關的原理與說明請參考 [相關連結] 的參考文件。

相關連結

• MySQL 5.1 Reference Manuel - 5.2. MySQL Server Logs
• MySQL 5.1 Reference Manuel - 5.2.4. The Binary Log
• MySQL 5.1 Reference Manuel - 4.6.7. mysqlbinlog — Utility for Processing Binary Log Files
• MySQL 5.1 Reference Manuel - Chapter 6. Backup and Recovery
• MySQL 5.1 Reference Manuel - 6.2. Example Backup and Recovery Strategy
• MySQL 5.1 Reference Manuel - 6.2.1. Backup Policy
• MySQL 5.1 Reference Manuel - 6.2.2. Using Backups for Recovery
• MySQL 5.1 Reference Manuel - 16.1.3.4. Binary Log Options and Variables
• MySQL 5.1 Reference Manuel - 12.6.1.1. PURGE BINARY LOGS Syntax
• MySQL 5.1 Reference Manuel - 12.5.5.2. SHOW BINARY LOGS Syntax
• 如何設定 MySQL 監控所有 AP 對資料庫的查詢

我今天發現 PowerShell 有個非常需要小心運用的地方，就是在執行非 .NET 應用程式時，當利用 Out-File 或 pipe 運算子 ( > ) 輸出至文字檔時很容易會有編碼錯亂的問題，如果一不小心設定，你輸出的所有文字全部都會變成亂碼，而且是無法復原的亂碼！

我今天利用 PowerShell 執行 mysqldump 指令將 MySQL 資料庫匯出成檔案，執行的指令如下：

&mysqldump -u $strUserName "-p$strUserPassword" $strDBName > "$strDBName-$Today.sql"

看起來是多麼的理所當然阿，但魔鬼總在細節裡！

首先，當你執行 mysqldump 指令時，所預設輸出字集是 UTF-8 編碼，你自然也會很理所當然的認為輸出的 "$strDBName-$Today.sql" 檔案也是 UTF-8 編碼，而且結果也不例外，輸出的檔案用 Notepad++ 開啟後的確也是 UTF-8 編碼。

在 DOS 模式下執行 mysqldump 指令匯出資料的時候中文是正常的，而且是 UTF-8 編碼

但是在 PowerShell 模式下執行 mysqldump 指令匯出資料的時候中文卻是亂碼，但文件還是 UTF-8 編碼!

如果開啟 mysqldump 匯出的資料檔，在編輯器第一頁可能看不到任何中文字，所以你也許會誤以為檔案匯出成功，而放心的度假去了。

由於 PowerShell 核心就是 .NET 執行環境，當你執行非 .NET 應用程式時，PowerShell 卻會以系統預設字集當成所有文字派送管道(pipeline)，所以就算 mysqldump 輸出的文字編碼是 UTF-8，PowerShell 依然會將他認為是 Big5 (繁體作業系統下的預設編碼)，但是 PowerShell 雖然是以 Big5 作為 mysqldump 的輸入編碼，但透過大於符號 ( > ) 輸出的檔案卻是以 UTF-16 編碼 ( 因為 PowerShell 就等於 .NET，所有 .NET 的文字處理預設用 UTF-16 進行編碼 )。

為了驗證上述觀念，我利用 Jeffery Lee 所開發的 中文編碼解析 Ver 1.31 版 分析被弄成亂碼的文字：

你可以看到，原本的 [平裝] 透過 mysqldump 輸出時的確是 UTF-8 編碼，但是 PowerShell 卻以 Big5 碼進行處理，我試著用內碼輸入法依序輸入 E5 B9 B3 E8 A3 9D 等十六進制位元，所得到的文字正好就是變亂碼的 [撟唾?]。其中 E5 B9 正是 Big5 編碼的 [撟]，B3 E8 正是 Big5 編碼的 [唾]，而最後的 A3 9D 並不是有效的 Big5 文字，所以才會出現問號 ( ? )。

這就是典型的文字編碼錯亂事件，而且由於這種文字錯亂的現象是先從 UTF-8 轉成 Big5 編碼，此時已經有些編碼轉不過去了，所以變成問號，也就是說原本的 A3 9D 這兩個位元(byte)只會變成一個問號位元 ( 3F )，文字已經不完整了。之後再從一個不完整的 Big5 字元再轉成 UTF-16 編碼，這時的文字已經殘破不堪，而且幾乎無法逆轉。

這種問題我遇到不止一次了，老外寫軟體總是不仔細考慮非英語系使用者的困難，而且像這種問題微軟根本不應該再允許發生，透過程式比對非 .NET 應用程式的輸出是不是 UTF-8 應該很容易阿，為什麼不多做一點檢查呢？！

若要解決 mysqldump 輸出文字混亂的情況，可以指定輸出的字集為 Big5 就能輸出正常的編碼，例如：

&mysqldump -u $strUserName "-p$strUserPassword" --default-character-set=big5 $strDBName > 
    "$strDBName-$Today.sql"

不過，也只有 DML (資料操作語言) 的部分才會正常 [資料部分]，原本在 DDL (資料定義語言) 的部分 [結構部分] 依然會輸出 UTF-8 編碼，所以輸出後的結果依然會出問題。在此建議的作法是套用 mysqldump 的 -r 選項，直接在 mysqldump 執行時直接指定輸出檔名與路徑，例如：

&mysqldump -u $strUserName "-p$strUserPassword" -r "$strDBName-$Today.sql" $strDBName

最後給PowerShell使用者的由衷建議

當執行非 .NET 應用程式時，不要用 PowerShell 執行文字資料匯流排(pipe)功能，否則你會欲哭無淚。

給微軟開發 PowerShell 團隊的建議

別再害我的資料毀損了，PowerShell 又不是什麼需要超高效能的運算能力，不就是一些腳本嘛，多花點 CPU 時間判斷我原本程式輸出的文字編碼吧，總比把客戶的資料搞亂掉的好些。(希望有人可以幫我回報)

我很久以前曾經寫過一篇【MySQL 4.1/5 如何將現有 latin1 中文資料匯入成 UTF-8 資料】文章，而我最近為了協助客戶將他們舊有的網站從 PHP 4.3 + MySQL 4.0 全面升級到 PHP 5 + MySQL 5.1 也是費了一番功夫，本次的心得比上次更精簡、實用。

MySQL 4.0 升級到 MySQL 5.1 最大的困難點在於資料庫本身對文字編碼 (Text Encoding) 的支援，舊版的 MySQL 4.0 所匯出的資料對 MySQL 5.1 來說，所有的文字都是 latin1 字集，所以你在 MySQL 5.1 中建立資料庫時選的若是 latin1 的話，要匯入這些資料是完全沒問題的，只是你就再也無法透過任何 MySQL 管理工具看到正常的中文字了。

所以要正確的從 MySQL 4.0 匯入資料到 MySQL 5.0 的話，一定要讓匯入的資料變成具有文字編碼特性的資料，要成功匯入資料我認為有以下步驟：

1. 從 MySQL 4.0 匯出資料 ( 透過 mysqldump 指令 )
2. 修正匯出的 SQL 指令檔
3. 將資料修正成符合 big5 字集的格式
4. 匯入檔時要設定正確的字集
5. 將修正過的 SQL 指令執行匯入到 MySQL 5.1
   • 匯入過程若還有錯誤可手動再修正過再重新匯入
   • 有時後會因為某筆資料的某欄位的最後一個字元當 ASCII 字碼大於 127 的字元 ( High bit ) 時，而導致將欄位結尾的單引號 ( ' ) 給吃掉，這在後來匯入時會導致錯誤發生！

首先，我先準備一個檔案 ( prepend-big5.txt )，用來修正舊版匯出檔案的字集 ( Big5 )，之後要加在 SQL 指令檔最前面的：

SET NAMES 'big5';

SET CHARACTER SET 'big5';

然後，我也寫了一支 PHP 程式用來修正 MySQL 4.0 匯出檔用的，將 SQL 指令全部轉成符合 Big5 字集的指令，其實最主要也是修正「許功蓋」的問題而已，大部分的文字都是 Big5 編碼應該是沒錯的。

檔名：Fix_MySQL_Script_For_Big5.php

<?php

set_time_limit(0);

$fp = fopen('php://stdin', "r");

while($line = fgets($fp))
{
    $hasB5 = false;
    $len = strlen($line);

    for($i=0 ; $i < $len ; $i++)
    {
        $highbit = ord($line[$i]);
        if($highbit >= 0x81 && $highbit <= 0xFE)
        {
            $hasB5 = true;
            break;
        }
    }

    if($hasB5) for($high=0x81; $high <= 0xFE; $high++)
    {
        $line = str_replace(
                   chr($high) . '\\' . '\\', 
                   chr($high) . '\\', 
                   $line);
    }
    echo $line;
}

fclose($fp);

?>

這支程式會讀入 stdin 的串流資料，並且修正語法後在輸出到 stdout 串流中。

準備好資料與程式後，就是重頭戲了。

從 MySQL 4.0 匯出資料並修正字集問題

1. 先將之前準備的 prepend-big5.txt 加到 dbname.sql 檔案中

cat prepend-big5.txt > dbname.sql

2. 從 MySQL 4.0 匯出指令檔，並附加到 dbname.sql 中

mysqldump -Q --all --add-drop-table -u root -pYourPassword dbname >> dbname.sql

3. 透過 Fix_MySQL_Script_For_Big5.php 指令修正 SQL 指令檔將 dbname.sql 轉換成 dbname-big5.sql

cat dbname.sql | php -q Fix_MySQL_Script_For_Big5.php > dbname-big5.sql

此時，這個 dbname-big5.sql 已經是符合 big5 字集的 SQL 指令檔了，這時就可以準備將資料正式匯入 MySQL 5.1 資料庫。

匯入資料至 MySQL 5.1

1. 建立資料庫 ( 預設為 utf-8 字集 )

mysqladmin -u root -pYourPassword create dbname

2. 匯入資料 ( 需指明匯入的指令檔字集為 big5 )

mysql --default-character-set=big5 -u root -pYourPassword dbname < dbname-big5.sql

幾個月前曾經做一個系統整合的案子，要整合客戶內部的系統並提供新服務，進一步瞭解後才發現他們用的資料庫竟然是多年前的 MySQL 4.0 版，這個版本在官方早就不支援了，而且也無法下載的到任何檔案，所以我光是要設立開發環境都還有點困難，還好我有在義守大學的檔案伺服器找到舊版的 MySQL 伺服器。

但重點是我的 .NET 應用程式必須要能連接他們的資料庫才能完成新服務的開發，因此也找了幾套可以連接 MySQL 的類別庫套件，我首先先嘗試使用官方的 MySQL Connector/Net 的各版本來測試，不過因為 MySQL 4.0 是不支援 Unicode 特性的，而 .NET 卻是一個完全以 Unicode 為基礎的開發環境，測試的過程中遇到許多問題，最後終於放棄。

我另外找到一套開放原始碼的 MySQL Drivers for .NET 可以有效的處理中文問題，他會將從 MySQL 4.0 資料庫中的資料適當的轉成 .NET 可以讀取的字串資料，進而讓我們開發的系統能夠正常運作，算是一個蠻不錯的解決方案。只是，不使用 Unicode 而衍生的老問題在這裡一樣還是會發生，也就是「許功蓋」的問題，不過這也是難以避免的，所以也是必須勉強接受。

以下是我使用 MySQLDriverCS 所寫出來的程式碼範例：

string host = System.Web.Configuration.WebConfigurationManager.AppSettings["MySQL_Host"];
string user = System.Web.Configuration.WebConfigurationManager.AppSettings["MySQL_User"];
string pass = System.Web.Configuration.WebConfigurationManager.AppSettings["MySQL_Pass"];
string dbname = System.Web.Configuration.WebConfigurationManager.AppSettings["MySQL_DBName"];

using (MySQLConnection conn = new MySQLConnection(
                new MySQLConnectionString(host, dbname, user, pass).AsString))
{
    string strSQL = "SELECT * FROM products WHERE id=@ID LIMIT 1";

    // 準備 MySQLCommand
    MySQLCommand cmd = new MySQLCommand(strSQL, conn);

    // 產品編號
    MySQLParameter p1 = new MySQLParameter("@ID", DbType.String);
    p1.Size = 12;
    p1.Value = "ESC0023";
    cmd.Parameters.Add(p1);

    // MySQLDataAdapter ( 使用 MySQLDataAdapter 會自動開啟資料庫連線 )
    MySQLDataAdapter ta = new MySQLDataAdapter(cmd);
    ta.Fill(dt);

    cmd.Dispose();
    conn.Close();
}

我並沒有測試過使用 MySQLDriverCS 連接 MySQL 4.1/5.0 資料庫，如果有人有時間測試的話，可以留言給我，謝謝。

相關連結

• MySQLDriverCS
• MySQL Downloads
• 下載舊版的 MySQL 4.0 伺服器 (義守大學FTP伺服器)

我們一直以來就很少使用 Oracle 資料庫，一年下來也頂多 1 ~ 2 個案子採用 Oracle 的資料庫，所以一直都對 Oracle 資料庫的操作不太熟悉，尤其是用 Oracle 內建的那些超難用工具，更是不想親近，太不人性了。不過今年 Oracle 終於推出了一套 SQL Developer 資料庫管理工具，雖然還是比 Management Studio 遜色很多，不過終究像是個人用的管理工具了。

這套 SQL Developer 資料庫管理工具也是有些與 Management Studio 不同的特色，其中我覺得最大的不同在於 SQL Developer 資料庫管理工具可以不止管理 Oracle 資料庫，甚至還可以管理 SQL Server 或 MySQL 以及所有支援 JDBC Driver 的資料庫。

另外像是跨平台執行能力(Windows, Linux, Mac OS)、支援 CVS 與 Subversion 版本管理、資料庫結構比對(Schema Diff)、匯入匯出資料、...等等都是不錯個功能。

當你下載 SQL Developer 資料庫管理工具後，免安裝、直接點選 sqldeveloper.exe 就可以立即執行，軟體的使用我就不多說了，各位可以自行研究，比較特別要講的是如何安裝 JDBC driver 以支援各種不同的資料庫管理。

若要讓 SQL Developer 資料庫管理工具支援 Microsoft SQL Server 管理，可以先到 jTDS JDBC Driver 網站下載 JDBC driver，下載後解壓縮 jtds-1.2.2.jar 檔案並置於 [SQL Developer 安裝目錄]\jdbc\lib 目錄下。

若要讓 SQL Developer 資料庫管理工具支援 MySQL 管理，可以到 MySQL Connector/J 5.1 下載 JDBC driver，下載後解壓縮 mysql-connector-java-5.1.7-bin.jar 檔案並置於 [SQL Developer 安裝目錄]\jdbc\lib 目錄下。

之後就要進 SQL Developer 資料庫管理工具的 Tools 選單中選取 Preferences 設定 Third Party JDBC Drivers 項目，並點選 Add Entry 新增 JDBC driver 的 *.jar 檔。

之後，選取你的 jar 檔（一次選一個）

然後再新增連線

此時就可以看到各種資料庫的頁籤了，不過缺點是在設定 Microsoft SQL Server 時只能使用 TCP/IP 連線，無法使用 Named Pipe 設定連線。

相關連結

• Oracle SQL Developer
• Oracle SQL Developer Collaborative Blog
• Oracle SQL Developer Documentation
• JDBC drivers
• jTDS JDBC Driver
• MySQL Connector/J 5.1