时间戳

首页 > 解决方案 > PHP 无法捕获 Percona 集群上的 MySQL 二进制日志事件

php - PHP 无法捕获 Percona 集群上的 MySQL 二进制日志事件

问题描述

我在这里使用这个包：https ://github.com/krowinski/php-mysql-replication

我已经能够在我的本地 mysql 数据库上测试一个非常简单的应用程序，它按预期工作。

但是，一旦我将测试转移到我们的生产系统，它似乎就停止了工作。

现在，我的本地系统和生产之间存在一些自然差异，我在下面概述了这些差异。

mysql> SHOW variables WHERE variable_name LIKE "%binlog%";
+--------------------------------------------+----------------------+
| Variable_name                              | Value                |
+--------------------------------------------+----------------------+
| binlog_cache_size                          | 32768                |
| binlog_checksum                            | CRC32                |
| binlog_direct_non_transactional_updates    | OFF                  |
| binlog_error_action                        | ABORT_SERVER         |
| binlog_format                              | ROW                  |
| binlog_group_commit_sync_delay             | 0                    |
| binlog_group_commit_sync_no_delay_count    | 0                    |
| binlog_gtid_simple_recovery                | ON                   |
| binlog_max_flush_queue_time                | 0                    |
| binlog_order_commits                       | ON                   |
| binlog_row_image                           | FULL                 |
| binlog_rows_query_log_events               | OFF                  |
| binlog_skip_flush_commands                 | OFF                  |
| binlog_space_limit                         | 0                    |
| binlog_stmt_cache_size                     | 32768                |
| binlog_transaction_dependency_history_size | 25000                |
| binlog_transaction_dependency_tracking     | COMMIT_ORDER         |
| encrypt_binlog                             | OFF                  |
| have_backup_safe_binlog_info               | YES                  |
| innodb_api_enable_binlog                   | OFF                  |
| innodb_locks_unsafe_for_binlog             | OFF                  |
| log_statements_unsafe_for_binlog           | ON                   |
| max_binlog_cache_size                      | 18446744073709547520 |
| max_binlog_files                           | 0                    |
| max_binlog_size                            | 1073741824           |
| max_binlog_stmt_cache_size                 | 18446744073709547520 |
| sync_binlog                                | 1                    |
| wsrep_forced_binlog_format                 | NONE                 |
+--------------------------------------------+----------------------+
28 rows in set (0.00 sec)

mysql> SHOW variables WHERE variable_name LIKE "server_%";
+----------------+--------------------------------------+
| Variable_name  | Value                                |
+----------------+--------------------------------------+
| server_id      | 3                                    |
| server_id_bits | 32                                   |
| server_uuid    | 4fe64515-be6b-11e9-8e7f-026f00293d26 |
+----------------+--------------------------------------+
3 rows in set (0.00 sec)

应用程序本身非常简单。它连接到数据库，监听所有 binlog 事件，并在运行时将它们转储到控制台：

<?php
declare(strict_types=1);

error_reporting(E_ALL);
date_default_timezone_set('UTC');
include __DIR__ . '/vendor/autoload.php';

use MySQLReplication\Config\ConfigBuilder;
use MySQLReplication\Definitions\ConstEventsNames;
use MySQLReplication\Event\DTO\EventDTO;
use MySQLReplication\Event\EventSubscribers;
use MySQLReplication\MySQLReplicationFactory;

/**
 * Your db configuration
 * @see ConfigBuilder
 * @link https://github.com/krowinski/php-mysql-replication/blob/master/README.md
 */
$binLogStream = new MySQLReplicationFactory(
    (new ConfigBuilder())
        ->withUser('<DB_USER>')
        ->withHost('127.0.0.1')
        ->withPassword('<DB_PASS>')
        ->withPort(3306)
        ->withSlaveId(100)
        ->withHeartbeatPeriod(2)
        ->build()
);

/**
 * Register your events handler
 * @see EventSubscribers
 */
$binLogStream->registerSubscriber(
    new class() extends EventSubscribers
    {
        /**
         * @param EventDTO $event
         */
        public function allEvents(EventDTO $event): void
        {
            $data = json_encode($event);

            /**
             * log the event.
             */
            echo json_encode($event);
        }
    }
);

// start consuming events
$binLogStream->run();

当应用程序实际运行时，唯一触发的事件是无限重复的心跳事件：

{
    "type": "heartbeat",
    "eventInfo": {
        "timestamp": 0,
        "type": 27,
        "id": 3,
        "size": 39,
        "pos": 11890,
        "flag": 0,
        "checkSum": true,
        "sizeNoHeader": null,
        "dateTime": null,
        "binLogCurrent": {
            "binLogPosition": 11890,
            "binFileName": "mysql-bin.000016",
            "gtid": null,
            "mariaDbGtid": null
        }
    },
    "subject": null,
    "arguments": null
}{
    "type": "heartbeat",
    "eventInfo": {
        "timestamp": 0,
        "type": 27,
        "id": 3,
        "size": 39,
        "pos": 11890,
        "flag": 0,
        "checkSum": true,
        "sizeNoHeader": null,
        "dateTime": null,
        "binLogCurrent": {
            "binLogPosition": 11890,
            "binFileName": "mysql-bin.000016",
            "gtid": null,
            "mariaDbGtid": null
        }
    },
    "subject": null,
    "arguments": null
}
...

奇怪的是，无论捕获多少心跳事件，包括几天内，binlog 文件信息都不会改变。

数据库集群节点肯定在实时更新，但没有捕获事件。

寻找有关问题可能是什么的任何想法。我怀疑这是一个配置问题，但我不确定那可能是什么。

标签： phpmysqlpercona-xtradb-cluster

解决方案

推荐阅读