萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 實現遠程 MySQL 數據同步:CSV 導入導出方案詳解

前言

在分佈式系統架構中,跨地域的數據同步是常見需求。本文介紹一種基於 CSV 文件的 PHP 實現方案,適用於兩側資料庫均無法直接遠程訪問的場景,同步週期為一天以內。

核心思路

由於安全策略限制,雙方 MySQL 均關閉遠程訪問權限。採用「導出 → 傳輸 → 導入」三步走策略:

  1. 源端(國內服務器)將數據導出為 CSV 格式
  2. 通過 HTTP POST 將 CSV 內容推送至目標端
  3. 目標端(美國服務器)解析 CSV 並寫入本地資料庫

關鍵代碼實現

1. CSV 導出函數 (csv.php)

部署於源端服務器,接收資料庫連接參數與 SQL 查詢,返回 CSV 格式字符串:

<?php
/**
 * 將資料表數據導出為 CSV 格式
 * @param string $host     資料庫主機
 * @param string $db       資料庫名稱
 * @param string $user     用戶名
 * @param string $pass     密碼
 * @param string $table    表名
 * @param string $crlf     換行符
 * @param string $error_url 錯誤跳轉頁面
 * @param string $sql_query 查詢語句
 * @return string CSV 格式內容
 */
function PMA_exportData($host, $db, $user, $pass, $table, $crlf, $error_url, $sql_query) {
    $what = 'csv';
    $csv_terminated = "\n";
    $csv_separator = ',';
    $csv_enclosed = '"';
    $csv_escaped = '\\';

    $conn = mysql_connect($host, $user, $pass) or die('無法連接資料庫:' . mysql_error());
    mysql_select_db($db, $conn);

    $result = mysql_query($sql_query, $conn);
    $fields_cnt = mysql_num_fields($result);
    $csv_content = '';

    while ($row = mysql_fetch_row($result)) {
        $schema_insert = '';
        for ($j = 0; $j < $fields_cnt; $j++) {
            if (!isset($row[$j]) || is_null($row[$j])) {
                $schema_insert .= 'NULL';
            } elseif ($row[$j] == '0' || $row[$j] != '') {
                if ($csv_enclosed == '') {
                    $schema_insert .= $row[$j];
                } else {
                    $schema_insert .= $csv_enclosed
                        . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j])
                        . $csv_enclosed;
                }
            } else {
                $schema_insert .= '';
            }
            if ($j < $fields_cnt - 1) {
                $schema_insert .= $csv_separator;
            }
        }
        $csv_content .= $schema_insert . $csv_terminated;
    }
    mysql_free_result($result);
    return $csv_content;
}
?>

2. CSV 導入腳本 (import.php)

部署於目標端服務器,接收 POST 過來的 CSV 內容,寫入臨時文件後逐行解析入庫:

<?php
/**
 * 從 CSV 內容導入數據到指定表
 * @param string $host 資料庫主機
 * @param string $db   資料庫名
 * @param string $user 用戶名
 * @param string $pass 密碼
 * @param string $table 表名
 * @param string $content CSV 文本內容
 * @return string 執行結果
 */
function uploadFileOfCsv($host, $db, $user, $pass, $table, $content) {
    $conn = mysql_connect($host, $user, $pass) or die('無法連接資料庫:' . mysql_error());
    mysql_select_db($db, $conn);

    // 獲取目標表結構以確定字段數
    $result = mysql_query("SELECT * FROM `$table` LIMIT 1", $conn);
    $fields_cnt = mysql_num_fields($result);

    // 寫入臨時文件
    $tmp_file = sys_get_temp_dir() . '/import_' . uniqid() . '.csv';
    file_put_contents($tmp_file, $content);

    $fp = fopen($tmp_file, 'r');
    $line_num = 0;

    while (($buffer = fgets($fp, 4096)) !== false) {
        $line_num++;
        $tmp_arr = explode(',', $buffer);

        // 跳過空 ID 行
        if (trim($tmp_arr[0]) === '') {
            continue;
        }

        // 構建 INSERT 語句
        $query = "INSERT INTO `$table` VALUES (";
        for ($q = 0; $q < $fields_cnt; $q++) {
            $val = isset($tmp_arr[$q]) ? trim($tmp_arr[$q]) : '';
            $val = mysql_real_escape_string($val, $conn);
            $query .= "'$val'";
            if ($q < $fields_cnt - 1) {
                $query .= ',';
            }
        }
        $query .= ')';

        mysql_query($query, $conn);
    }
    fclose($fp);
    unlink($tmp_file);

    return 'OK';
}
?>

3. 協調調用腳本 (test_export.php)

部署於源端,串聯導出與推送流程:

<?php
require_once('csv.php');

$host = 'localhost';
$db = 'project';
$user = 'root';
$pass = '';
$table = 'tb_contact';
$sql = "SELECT * FROM `$table`";

// 導出 CSV
$csv_data = PMA_exportData($host, $db, $user, $pass, $table, "\n", '', $sql);

// 通過 cURL POST 推送至遠端 import.php
$ch = curl_init('https://us-server.com/import.php');
curl_setopt($ch, CURLOPT_POST, true);
curl_setopt($ch, CURLOPT_POSTFIELDS, ['content' => $csv_data, 'table' => $table]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
curl_close($ch);

echo $response === 'OK' ? '同步成功' : '同步失敗:' . $response;
?>

優化建議

  • 編碼統一:確保雙端資料庫、連接、CSV 文件均使用 UTF-8,避免中文亂碼
  • 大數據分批:單表超過 10 萬行時,建議分批導出、分批導入,防止內存溢出或超時
  • 安全加固:導入端驗證簽名或 IP 白名單,防止惡意數據注入
  • 異常重試:引入隊列機制(如 Redis + Worker),失敗自動重試並記錄日誌
  • 棄用 mysql_*:生產環境請改用 PDO 或 mysqli,並使用預處理語句防注入

總結

本方案通過 CSV 中間格式繞過遠程訪問限制,結合 HTTP 傳輸實現跨地域 MySQL 同步。代碼結構清晰,易於維護擴展,適合中小規模數據的定時同步場景。對於高頻、大體量同步需求,建議採用 Binlog 解析(如 Canal)或 CDC 工具實現增量實時同步。

copyright © 萬盛學電腦網 all rights reserved