PHP-实现MYSQL不同数据库-数据表增量数据同步

实现php版的mysql数据表增量数据同步，话不多说直接上干货。

<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2025/10/30
 * Time: 10:58
 */
namespace mysql;

/*
 *  Mysql数据表，增量更新，新增，修改，删除
 *  赵子龙：2025-10-30
 *  qq: 805929498
 */
class MySQLIncrementalSync {
    private $sourceConn;
    private $targetConn;
    private $config;
    private $syncStateFile;

    public function __construct($sourceConfig, $targetConfig, $config = []) {
        // 默认配置
        $defaultConfig = [
            'state_file' => 'sync_state.json',
            'chunk_size' => 1000,
            'max_retries' => 3,
            'retry_delay' => 2,
            'auto_fix_structure' => true,
            'log_file' => 'sync.log',
            'soft_delete_column' => 'is_deleted', // 软删除字段名
            'deletion_log_table' => '_sync_deletions', // 删除日志表
            'conflict_resolution' => 'source_wins' // 冲突解决策略: source_wins, target_wins, newer_wins
        ];

        $this->config = array_merge($defaultConfig, $config);
        $this->syncStateFile = $this->config['state_file'];

        // 初始化数据库连接
        $this->sourceConn = new \mysqli(
            $sourceConfig['host'],
            $sourceConfig['username'],
            $sourceConfig['password'],
            $sourceConfig['database'],
            $sourceConfig['port'] ?? 3306
        );

        $this->targetConn = new \mysqli(
            $targetConfig['host'],
            $targetConfig['username'],
            $targetConfig['password'],
            $targetConfig['database'],
            $targetConfig['port'] ?? 3306
        );

        // 检查连接
        if ($this->sourceConn->connect_error) {
            throw new \Exception("源数据库连接失败: " . $this->sourceConn->connect_error);
        }

        if ($this->targetConn->connect_error) {
            throw new \Exception("目标数据库连接失败: " . $this->targetConn->connect_error);
        }

        // 设置字符集
        $this->sourceConn->set_charset($sourceConfig['charset'] ?? 'utf8mb4');
        $this->targetConn->set_charset($targetConfig['charset'] ?? 'utf8mb4');

        // 初始化日志和删除日志表
        $this->initLog();
        $this->initDeletionLog();
    }

    /**
     * 初始化日志
     */
    private function initLog() {
        $logDir = dirname($this->config['log_file']);
        if (!is_dir($logDir) && $logDir !== '.') {
            mkdir($logDir, 0755, true);
        }
    }

    /**
     * 初始化删除日志表
     */
    private function initDeletionLog() {
        $query = "CREATE TABLE IF NOT EXISTS {$this->config['deletion_log_table']} (
            id INT AUTO_INCREMENT PRIMARY KEY,
            table_name VARCHAR(100) NOT NULL,
            record_id VARCHAR(255) NOT NULL,
            deleted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            INDEX idx_table_record (table_name, record_id),
            INDEX idx_deleted_at (deleted_at)
        )";

        if (!$this->sourceConn->query($query)) {
            throw new \Exception("创建删除日志表失败: " . $this->sourceConn->error);
        }
    }

    /**
     * 记录日志
     */
    private function log($message, $level = 'INFO') {
        $timestamp = date('Y-m-d H:i:s');
        $logMessage = "[$timestamp] [$level] $message\n";
        file_put_contents($this->config['log_file'], $logMessage, FILE_APPEND);
        echo $logMessage;
        echo '<hr/>';
    }

    /**
     * 记录删除操作到日志
     */
    public function logDeletion($tableName, $recordId) {
        $query = "INSERT INTO {$this->config['deletion_log_table']} (table_name, record_id) VALUES (?, ?)";
        $stmt = $this->sourceConn->prepare($query);
        $stmt->bind_param('ss', $tableName, $recordId);

        if (!$stmt->execute()) {
            $this->log("记录删除操作失败: {$stmt->error}", 'ERROR');
        }

        $stmt->close();
    }

    /**
     * 同步单个表（包含增、删、改）
     */
    public function syncTable($tableName, $keyColumn = 'id', $timestampColumn = 'updated_at') {
        $this->log("开始同步表: $tableName");

        set_time_limit(0);


        // 获取源表结构信息
        $sourceColumns = $this->getTableColumns($tableName, $this->sourceConn);
        $targetColumns = $this->getTableColumns($tableName, $this->targetConn);

        // 验证表结构兼容性
        $validationResult = $this->validateTableStructure($sourceColumns, $targetColumns);

        if (!$validationRes