PHP 實現跨伺服器 MySQL 資料同步（CSV 中轉方案）

情境

美國主機與大陸主機的 MySQL 均不開放遠端連線，需在一天內完成資料同步。採用 CSV 匯出/匯入 + HTTP POST 傳輸 + 排程自動化方案。

架構流程

1. 大陸端：csv.php 提供匯出函式 → test_export.php 呼叫產生 CSV → 置入隱藏表單 → 自動 POST 至美國端
2. 美國端：test_import.php 接收 POST 資料 → import.php 解析 CSV 匯入資料庫
3. 排程：Windows 工作排程器定時觸發 test_export.php

核心程式碼

1. csv.php - 匯出函式

function PMA_exportData($host, $db, $user, $pass, $filename, $table, $crlf, $error_url, $sql_query) {
    mysql_connect($host, $user, $pass) or die("DB連線失敗: " . mysql_error());
    mysql_select_db($db);
    $result = mysql_query($sql_query);
    $fields = mysql_num_fields($result);

    $csv = "";
    while ($row = mysql_fetch_row($result)) {
        $line = "";
        for ($j = 0; $j < $fields; $j++) {
            $val = $row[$j];
            if (!isset($val) || $val === null) {
                $val = "NULL";
            } elseif ($val === '0' || $val !== '') {
                $val = '"' . str_replace('"', '""', $val) . '"';
            } else {
                $val = '""';
            }
            $line .= $val . ($j < $fields-1 ? ',' : '');
        }
        $csv .= $line . "\r\n";
    }
    mysql_free_result($result);
    return $csv;
}

2. import.php - 匯入函式

function uploadFileOfCsv($host, $db, $user, $pass, $table, $content) {
    mysql_connect($host, $user, $pass) or die("DB連線失敗: " . mysql_error());
    mysql_select_db($db);

    // 取得欄位數
    $res = mysql_query("SELECT * FROM $table LIMIT 1");
    $fields = mysql_num_fields($res);

    // 暫存 CSV 到檔案
    $tmpFile = "C:/temp/import_" . uniqid() . ".csv";
    file_put_contents($tmpFile, $content);

    $fp = fopen($tmpFile, 'r');
    while ($buffer = fgets($fp, 4096)) {
        $cols = explode(',', $buffer);
        if (trim($cols[0]) == '') continue; // 跳過空 ID

        $sql = "INSERT INTO $table VALUES (";
        for ($q = 0; $q < $fields; $q++) {
            $val = isset($cols[$q]) ? trim($cols[$q]) : '';
            $sql .= "'" . mysql_real_escape_string($val) . "'" . ($q < $fields-1 ? ',' : '');
        }
        $sql .= ")";
        mysql_query($sql);
    }
    fclose($fp);
    unlink($tmpFile);
    return "OK";
}

3. test_export.php (大陸端觸發)

require_once('csv.php');
$csv = PMA_exportData('localhost', 'project', 'root', '', 'file.csv', 'tb_project_dvp', '', 'err.php', 'SELECT * FROM tb_project_dvp');
?>
<form id="f" method="post" action="https://us-server/test_import.php">
<textarea name="csv_data" style="display:none"><?php echo htmlspecialchars($csv); ?></textarea>
<input type="hidden" name="action" value="1"/>
</form>
<script>document.getElementById('f').submit();</script>

4. test_import.php (美國端接收)

require_once('import.php');
if ($_POST['action'] == '1') {
    echo uploadFileOfCsv('localhost', 'wintopweb', 'root', '', 'tb_project_dvp', $_POST['csv_data']);
}

改良建議

• 改用 mysqli/PDO + Prepared Statements
• CSV 產生使用 fputcsv()，解析用 fgetcsv()，正確處理逗號、引號、換行
• 傳輸加密：HTTPS + API Token 驗證
• 大量資料分批處理，避免記憶體耗盡
• 加入同步狀態記錄表、失敗重試機制
• 考慮使用 MySQL 內建 Replication 或 pt-table-sync 工具