您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 主從資料同步實作：PHP 腳本 + Crontab 定時同步方案

編輯：MySQL 資料庫相關知識

針對雙主機 (A 動態 IP、B 固定 IP) MySQL 資料單向同步需求 (A→B)，採用 PHP 腳本比對主鍵最大值增量同步，部署於 Crontab 定時執行，零成本實現基礎資料同步。

架構設計

來源端 (A)： 動態 IP、PHP + MySQL、執行同步腳本
目標端 (B)： 固定 IP (5.6.7.8)、MySQL、建立同步專用帳號
同步策略： 比對雙端表主鍵 MAX(id)，僅同步增量區間，避免全量覆蓋
觸發方式： Crontab 定時 (如每晚 00:00) 執行 PHP CLI 腳本

目標端 (B) 建立同步帳號

GRANT ALL ON `test`.* TO 'sync_user'@'%' IDENTIFIED BY 'StrongPass123!';
FLUSH PRIVILEGES;
-- 若報錯先用具體 IP，再用 phpMyAdmin 改為 %

PHP 同步腳本核心邏輯 (sync.php)

'localhost', 'user'=>'user', 'pass'=>'pass', 'db'=>'test'];
$dst = ['host'=>'5.6.7.8', 'user'=>'sync_user', 'pass'=>'StrongPass123!', 'db'=>'test'];
$table = 'sync_table';
$pk = 'id';

// 連線函數
function connect($cfg) {
    $link = mysqli_connect($cfg['host'], $cfg['user'], $cfg['pass'], $cfg['db']);
    if (!$link) die("Connect failed: ".mysqli_connect_error());
    mysqli_set_charset($link, 'utf8mb4');
    return $link;
}

// 獲取最大主鍵
function getMaxId($link, $table, $pk) {
    $res = mysqli_query($link, "SELECT MAX(`$pk`) AS max_id FROM `$table`");
    $row = mysqli_fetch_assoc($res);
    return $row['max_id'] ?? 0;
}

// 主流程
$srcLink = connect($src);
$srcMax = getMaxId($srcLink, $table, $pk);
mysqli_close($srcLink);

$dstLink = connect($dst);
$dstMax = getMaxId($dstLink, $table, $pk);
mysqli_close($dstLink);

if ($srcMax > $dstMax) {
    $offset = $dstMax;
    $limit = $srcMax - $dstMax;
    
    $srcLink = connect($src);
    $res = mysqli_query($srcLink, "SELECT * FROM `$table` WHERE `$pk` > $offset ORDER BY `$pk` LIMIT $limit");
    $rows = [];
    while ($row = mysqli_fetch_assoc($res)) $rows[] = $row;
    mysqli_close($srcLink);
    
    if ($rows) {
        $dstLink = connect($dst);
        // 批次 INSERT IGNORE (主鍵衝突忽略) 或 REPLACE INTO
        $cols = array_keys($rows[0]);
        $colList = '`'.implode('`,`', $cols).'`';
        $placeholders = '('.implode(',', array_fill(0, count($cols), '?')).')';
        
        $stmt = mysqli_prepare($dstLink, "INSERT IGNORE INTO `$table` ($colList) VALUES $placeholders");
        // 綁定參數動態構建 (略，實際需 call_user_func_array)
        foreach ($rows as $row) {
            $vals = array_values($row);
            // bind_param + execute ...
        }
        mysqli_stmt_close($stmt);
        mysqli_close($dstLink);
        echo "Synced ".count($rows)." rows.\n";
    }
} else {
    echo "No new data to sync.\n";
}
?>

關鍵優化點

改用 mysqli 預處理批次綁定： 減少解析開銷、防注入。
主鍵必須單調遞增 (AUTO_INCREMENT)： 否則 MAX(id) 策略失效，需改用 UPDATE_TIME 或 BINLOG 同步。
大批次分塊寫入： 每 500-1000 行提交一次，避免長事務鎖表、日誌暴漲。
網路重試與斷點續傳： 記錄同步位點 (offset) 至本地檔案/資料表，失敗續傳。
雙向同步衝突解決： 本方案僅單向，雙向需引入版本向量/時間戳/業務主鍵去重。

部署至 Crontab

# 安裝 PHP CLI (通常已含)
which php  # /usr/bin/php

# 測試腳本
php -q /home/httpd/html/sync.php
# -q 抑制 HTTP Header 輸出

# 編輯 crontab
crontab -e
# 每天 00:05 執行
5 0 * * * /usr/bin/php -q /home/httpd/html/sync.php >> /var/log/mysql_sync.log 2>&1

局限性與演進方向

局限	演進方案
僅支援 INSERT 同步，不處理 UPDATE/DELETE	讀取 Binlog (Canal/Maxwell/Debezium) 解析完整 DML 事件
主鍵非遞增或有刪除導致 MAX(id) 不準	基於 `updated_at` 時間戳增量、或 GTID 複制
大表全量初始化慢	`mysqldump --single-transaction --master-data=2` + 導入
無監控告警、無冪等保證	接入 Prometheus 監控同步延遲/行數、冪等寫入 (PK/UK)
單點故障 (A 掛掉同步停)	雙主互備 (MGR) 或從庫提升主庫

生產級替代方案對比

MySQL 原生複制 (GTID)： 成熟、免費、支援多從、半同步、延遲監控。
Canal (阿里開源)： 偽裝 Slave 解析 Binlog，輸出至 MQ/Kafka/RocketMQ，下游消費同步異構目標 (MySQL/ES/Redis/HBase)。
Debezium (CDC)： Kafka Connect 連接器，捕獲變更事件，精確一次語義，支援 Schema 演進。
ShardingSphere-Proxy / MyCat： 中間件層實現讀寫分離、分片、影子表壓測、資料遷移。

PHP + Crontab 方案適合「小數據量、單向、非核心、無運維資源」的臨時同步場景。核心業務務必上生產級 CDC/複制架構，確保資料一致性、可觀測性與故障自癒。