PHP Not able to capture MySQL binlog events on Percona Cluster

Question

I'm working with this package here: https://github.com/krowinski/php-mysql-replication

I've been able to test a very simple application on my local mysql database, with it working as expected.

However, once I moved my testing to our production systems, it appears to have stopped working.

Now, there are some natural differences between my local system and production, which I've outlined below.

PHP Version: 7.4.9
mysql version: 5.7.30-33-57-log Percona XtraDB Cluster (GPL), Release rel33, Revision 5dd6d59, WSREP version 31.43, wsrep_31.43

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)

The application itself is very simple. It connects to the database, listens to any and all binlog events, and just dumps them to the console while running:

<?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();

When the application is actually run, the only events that fire are a heartbeat event that repeats indefinitely:

{
    "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
}
...

Curiously, the binlog file information never changes, no matter how many heartbeat events are captured, including over several days.

The database cluster node is definitely updating in real time, but the events are not being captured.

Looking for any ideas on what the issue may be. I suspect it's a configuration issue, but I'm not sure what that could be.

PHP Not able to capture MySQL binlog events on Percona Cluster

0 Answers0