search

krowinski / php-mysql-replication

Pure PHP Implementation of MySQL replication protocol. This allow you to receive event like insert, update, delete with their data and raw SQL queries.
MIT License
323 stars 98 forks source link

Events not being captured on Percona Cluster #66

Closed brandinchiu closed 4 years ago

brandinchiu commented 4 years ago

Please provide the following details.

Steps required to reproduce the problem.

  1. Test example/dump_events.php

Expected Result.

Actual Result.

I've tested this same solution on my local system using a regular install of MySQL. When I moved to test on my production system, the events are not propagating as expected. My concern is that the version of Percona Cluster we're using is at fault, and I'm curious if there are any ideas on how to address.

We have a 3 node cluster. I'm attempting to run the php-mysql-replication application on our 3rd node.

I've added the server config (of the node that is not working) below for reference:

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)

When the application is run, I only receive heartbeat events, even though I am 100% other activity is happening across the cluster (I do occasionally get "format description" events, but I'm not terribly sure what these are for). I have yet to see any of the important events like updates, writes, or deletes.

{
    "type": "format description",
    "eventInfo": {
        "timestamp": 1598513185,
        "type": 15,
        "id": 3,
        "size": 119,
        "pos": 0,
        "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
}{
    "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
}

EDIT: additional note; it looks like that each heartbeat event is identical while the application is running. The binlog position and file never changes. Not sure if this is helpful information or not, but thought I should include it.

krowinski commented 4 years ago

Hi can you show me you php configuration did you select correct db name? or using default config ?

$binLogStream = new MySQLReplicationFactory(
    (new ConfigBuilder())
        ->withUser('some user')
        ->withHost('some host')
        ->withPassword('some pass')
        ->withPort(3306)
        ->withSlaveId(100)
        ->withHeartbeatPeriod(2)
        ->build()
);
brandinchiu commented 4 years ago

Yes, I'm just using the default one for now. I haven't had much chance to explore the rest of the configuration since I hit this wall basically right away.

krowinski commented 4 years ago

Can you send me your my.cnf ? I will try do setup docker with percona 5.7

brandinchiu commented 4 years ago

The file below contains the raw output from all of our configs for each of our nodes. Each has a cluster.cnf and a my.cnf file.

I appreciate the effort and am happy to help with anything else you need, just let me know!

nodes-config.txt

krowinski commented 4 years ago

hmm I created cluster 


docker run -p 0.0.0.0:3390:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -e CLUSTER_NAME=cluster1 --net=pxc-network --name=mysql_cluster1 -d percona/percona-xtradb-cluster:5.7 \
mysqld \
  --datadir=/var/lib/mysql \
  --user=mysql \
  --server-id=1 \
  --log-bin=/var/lib/mysql/mysql-bin.log \
  --binlog-format=row \
  --max_allowed_packet=64M

docker run -p 0.0.0.0:3391:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -e CLUSTER_NAME=cluster1 -e CLUSTER_JOIN=mysql_cluster1 --net=pxc-network --name=mysql_cluster2 -d percona/percona-xtradb-cluster:5.7 \
mysqld \
  --datadir=/var/lib/mysql \
  --user=mysql \
  --server-id=1 \
  --log-bin=/var/lib/mysql/mysql-bin.log \
  --binlog-format=row \
  --max_allowed_packet=64M

docker run -p 0.0.0.0:3392:3306 -e MYSQL_ROOT_PASSWORD=root -e MYSQL_ROOT_HOST=% -e CLUSTER_NAME=cluster1 -e CLUSTER_JOIN=mysql_cluster1 --net=pxc-network --name=mysql_cluster3 -d percona/percona-xtradb-cluster:5.7 \
mysqld \
  --datadir=/var/lib/mysql \
  --user=mysql \
  --server-id=1 \
  --log-bin=/var/lib/mysql/mysql-bin.log \
  --binlog-format=row \
  --max_allowed_packet=64M

I connected to "master" mysql_cluster1 and I receive events :/

So probably its something with configuration ? no idea will try to use you config to debug... l

brandinchiu commented 4 years ago

@krowinski You're getting all of the events? Annoying. That means the next step is for me to rebuild a production replica from scratch and see if I can reproduce the issue.

krowinski commented 4 years ago

Give me some time I will find out why. You are connecting to your master node ?

brandinchiu commented 4 years ago

No, we weren't trying to connect to the master node. We didn't expect that would make a difference.

I can try that though and get back to you.

krowinski commented 4 years ago

yep I think this is a problem nodes don't send events from what I tested only master sends. (If not someone correct me :trollface: )

brandinchiu commented 4 years ago

Omg if that's just what the issue is that would be fantastic. I'll be sure to find time to test this soon and will get back to you to confirm.

Thanks!

brandinchiu commented 4 years ago

I appreciate all the help you have given @krowinski. I've tested again using the master node and it is indeed working.

I'm sorry for wasting your time on something that seems like should have been a no-brainer.

krowinski commented 4 years ago

I think there is also feature to be added like salve to salve replication right now there is only master -> slave support I need to check this if is possible.

or try do add (#71)

[mysqld]
log_slave_updates = on