PHP中MySQL的二進制日志解析

在 PHP 中，你可以使用 mysqli 或 PDO 擴展來處理 MySQL 二進制日志（Binary Log）。二進制日志記錄了所有更改數據庫結構的操作，例如創建和刪除表、插入、更新和刪除數據等。這對于復制和數據恢復非常有用。

以下是使用 mysqli 和 PDO 擴展解析 MySQL 二進制日志的示例：

1. 使用 mysqli 擴展解析二進制日志：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// 創建連接
$conn = new mysqli($servername, $username, $password, $dbname);

// 檢查連接
if ($conn->connect_error) {
    die("連接失敗: " . $conn->connect_error);
}

// 設置為從二進制日志讀取模式
$conn->query("SET SQL_LOG_BIN=0");

// 查詢二進制日志
$result = $conn->query("SHOW BINARY LOGS");

if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $binlog_file = $row["File"];
        $binlog_position = $row["Position"];
        echo "Binary Log File: " . $binlog_file . ", Position: " . $binlog_position . "\n";

        // 讀取并解析二進制日志事件
        $content = "";
        $handle = fopen($binlog_file, "rb");
        while (!feof($handle)) {
            $event = fread($handle, 4096);
            if (strlen($event) < 4) {
                break;
            }
            $event_type = ord($event[0]);
            if ($event_type == 2) { // 行復制事件
                $events = [];
                while (strlen($event) >= 8) {
                    $length = unpack('N', substr($event, 4, 4))[1];
                    $timestamp = unpack('N', substr($event, 8, 4))[1];
                    $log_data = substr($event, 12, $length);
                    $events[] = [$timestamp, $log_data];
                    $event = substr($event, 16 + $length);
                }
                foreach ($events as $event) {
                    echo "Timestamp: " . $event[0] . ", Data: " . $event[1] . "\n";
                }
            }
        }
        fclose($handle);
    }
} else {
    echo "沒有找到二進制日志文件。\n";
}

// 重置為正常模式
$conn->query("SET SQL_LOG_BIN=1");

// 關閉連接
$conn->close();
?>

2. 使用 PDO 擴展解析二進制日志：

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // 設置為從二進制日志讀取模式
    $conn->exec("SET SQL_LOG_BIN=0");

    // 查詢二進制日志
    $result = $conn->query("SHOW BINARY LOGS");

    if ($result->rowCount() > 0) {
        while ($row = $result->fetch()) {
            $binlog_file = $row["File"];
            $binlog_position = $row["Position"];
            echo "Binary Log File: " . $binlog_file . ", Position: " . $binlog_position . "\n";

            // 讀取并解析二進制日志事件
            $content = "";
            $handle = fopen($binlog_file, "rb");
            while (!feof($handle)) {
                $event = fread($handle, 4096);
                if (strlen($event) < 4) {
                    break;
                }
                $event_type = ord($event[0]);
                if ($event_type == 2) { // 行復制事件
                    $events = [];
                    while (strlen($event) >= 8) {
                        $length = unpack('N', substr($event, 4, 4))[1];
                        $timestamp = unpack('N', substr($event, 8, 4))[1];
                        $log_data = substr($event, 12, $length);
                        $events[] = [$timestamp, $log_data];
                        $event = substr($event, 16 + $length);
                    }
                    foreach ($events as $event) {
                        echo "Timestamp: " . $event[0] . ", Data: " . $event[1] . "\n";
                    }
                }
            }
            fclose($handle);
        }
    } else {
        echo "沒有找到二進制日志文件。\n";
    }

    // 重置為正常模式
    $conn->exec("SET SQL_LOG_BIN=1");
} catch (PDOException $e) {
    echo "連接失敗: " . $e->getMessage();
}

// 關閉連接
$conn = null;
?>

請注意，這些示例僅適用于行復制事件。如果你需要處理其他類型的事件，你需要根據事件類型進行相應的解析。同時，這些示例可能需要根據你的實際需求進行調整。