PHP Classes

PHP MySQL Replication: Client to get MySQL replication events in pure PHP

Recommend this page to a friend!
  Info   View files Example   View files View files (81)   DownloadInstall with Composer Download .zip   Reputation   Support forum (2)   Blog    
Ratings Unique User Downloads Download Rankings
StarStarStarStarStar 87%Total: 766 All time: 4,405 This week: 95Up
Version License PHP version Categories
mysql-replication 2.0.28MIT/X Consortium ...5.6Networking, PHP 5, Databases
Description 

Author

This package implements a client to get MySQL replication events in pure PHP.

It establishes a socket connection to a MySQL binary log server to so it can listen to MySQL database changes events like insert, update, delete queries with their data as well raw SQL queries.

The events can be dumped for debugging purposes or used to replicate the database on a new server.

Innovation Award
PHP Programming Innovation award nominee
March 2016
Number 7


Prize: One subscription to the PDF edition of the PHP Architect magazine
MySQL supports replication of data, so multiple slave servers can store the same data as the master.

The replication is done using a networking protocol that can be used by slaves to retrieve data from the servers.

This package can act as a MySQL slave, so it can replicate data from a MySQL server in pure PHP.

Manuel Lemos
Picture of Kacper Rowinski
  Performance   Level  
Name: Kacper Rowinski <contact>
Classes: 14 packages by
Country: Poland Poland
Age: 40
All time rank: 91826 in Poland Poland
Week rank: 312 Up5 in Poland Poland Up
Innovation award
Innovation award
Nominee: 8x

Recommendations

extract updated data
I want a class that can sync localdatabase with online hosting

clone mysql BIG database
Clone mysql BIG databases without export/import, but copying

Example

<?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\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('root')
        ->
withHost('127.0.0.1')
        ->
withPassword('root')
        ->
withPort(3306)
        ->
withSlaveId(100)
        ->
withHeartbeatPeriod(2)
        ->
build()
);

/**
 * Register your events handler
 * @see EventSubscribers
 */
$binLogStream->registerSubscriber(
    new class() extends
EventSubscribers
   
{
        public function
allEvents(EventDTO $event): void
       
{
           
// all events got __toString() implementation
           
echo $event;

           
// all events got JsonSerializable implementation
            //echo json_encode($event, JSON_PRETTY_PRINT);

           
echo 'Memory usage ' . round(memory_get_usage() / 1048576, 2) . ' MB' . PHP_EOL;
        }
    }
);

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


Details

php-mysql-replication

Build Status Latest Stable Version Total Downloads Latest Unstable Version SensioLabsInsight License Scrutinizer Code Quality Code Coverage

Pure PHP Implementation of MySQL replication protocol. This allow you to receive event like insert, update, delete with their data and raw SQL queries.

Based on a great work of creators?https://github.com/noplay/python-mysql-replication and https://github.com/fengxiangyun/mysql-replication

Installation

In you project

composer require krowinski/php-mysql-replication

or standalone

git clone https://github.com/krowinski/php-mysql-replication.git

composer install -o

Compatibility (based on integration tests)

- mysql 5.5 - mysql 5.6 - mysql 5.7 - mysql 8.0 (ONLY with mysql_native_password) - mariadb 5.5 - mariadb 10.0 - mariadb 10.1 - probably percona versions as is based on native mysql

MySQL server settings

In your MySQL server configuration file you need to enable replication:

[mysqld]
server-id        = 1
log_bin          = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
max_binlog_size  = 100M
binlog-format    = row #Very important if you want to receive write, update and delete row events

Mysql replication events explained

https://dev.mysql.com/doc/internals/en/event-meanings.html

Mysql user privileges:

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO 'user'@'host';

GRANT SELECT ON `dbName`.* TO 'user'@'host';

Configuration

Use ConfigBuilder or ConfigFactory to create configuration. Available options:

'user' - your mysql user (mandatory)

'ip' or 'host' - your mysql host/ip (mandatory)

'password' - your mysql password (mandatory)

'port' - your mysql host port (default 3306)

'charset' - db connection charset (default utf8)

'gtid' - GTID marker(s) to start from (format 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:1-177592)

'mariaDbGtid' - MariaDB GTID marker(s) to start from (format 1-1-3,0-1-88)

'slaveId' - script slave id for identification (SHOW SLAVE HOSTS)

'binLogFileName' - bin log file name to start from

'binLogPosition' - bin log position to start from

'eventsOnly' - array to listen on events (full list in ConstEventType.php file)

'eventsIgnore' - array to ignore events (full list in ConstEventType.php file)

'tablesOnly' - array to only listen on given tables (default all tables)

'databasesOnly' - array to only listen on given databases (default all databases)

'tableCacheSize' - some data are collected from information schema, this data is cached.

'custom' - if some params must be set in extended/implemented own classes

'heartbeatPeriod' - sets the interval in seconds between replication heartbeats. Whenever the master's binary log is updated with an event, the waiting period for the next heartbeat is reset. interval is a decimal value having the range 0 to 4294967 seconds and a resolution in milliseconds; the smallest nonzero value is 0.001. Heartbeats are sent by the master only if there are no unsent events in the binary log file for a period longer than interval.

Similar projects

Ruby: https://github.com/y310/kodama

Java: https://github.com/shyiko/mysql-binlog-connector-java

GO: https://github.com/siddontang/go-mysql

Python: https://github.com/noplay/python-mysql-replication

.NET: https://github.com/rusuly/MySqlCdc

Examples

All examples are available in the examples directory

This example will dump all replication events to the console:

Remember to change config for your user, host and password.

User should have replication privileges [ REPLICATION CLIENT, SELECT]

php example/dump_events.php

For test SQL events:

CREATE DATABASE php_mysql_replication;
use php_mysql_replication;
CREATE TABLE test4 (id int NOT NULL AUTO_INCREMENT, data VARCHAR(255), data2 VARCHAR(255), PRIMARY KEY(id));
INSERT INTO test4 (data,data2) VALUES ("Hello", "World");
UPDATE test4 SET data = "World", data2="Hello" WHERE id = 1;
DELETE FROM test4 WHERE id = 1;

Output will be similar to this (depends on configuration for example GTID off/on):

=== Event format description ===
Date: 2017-07-06T13:31:11+00:00
Log position: 0
Event size: 116
Memory usage 2.4 MB

=== Event gtid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803092
Event size: 48
Commit: true
GTID NEXT: 3403c535-624f-11e7-9940-0800275713ee:13675
Memory usage 2.42 MB

=== Event query ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803237
Event size: 145
Database: php_mysql_replication
Execution time: 0
Query: CREATE DATABASE php_mysql_replication
Memory usage 2.45 MB

=== Event gtid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803285
Event size: 48
Commit: true
GTID NEXT: 3403c535-624f-11e7-9940-0800275713ee:13676
Memory usage 2.45 MB

=== Event query ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803500
Event size: 215
Database: php_mysql_replication
Execution time: 0
Query: CREATE TABLE test4 (id int NOT NULL AUTO_INCREMENT, data VARCHAR(255), data2 VARCHAR(255), PRIMARY KEY(id))
Memory usage 2.45 MB

=== Event gtid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803548
Event size: 48
Commit: true
GTID NEXT: 3403c535-624f-11e7-9940-0800275713ee:13677
Memory usage 2.45 MB

=== Event query ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803637
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.45 MB

=== Event tableMap ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803708
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 866
Columns amount: 3
Memory usage 2.71 MB

=== Event write ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803762
Event size: 54
Table: test4
Affected columns: 3
Changed rows: 1
Values: Array
(
    [0] => Array
        (
            [id] => 1
            [data] => Hello
            [data2] => World
        )

)

Memory usage 2.74 MB

=== Event xid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803793
Event size: 31
Transaction ID: 662802
Memory usage 2.75 MB

=== Event gtid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803841
Event size: 48
Commit: true
GTID NEXT: 3403c535-624f-11e7-9940-0800275713ee:13678
Memory usage 2.75 MB

=== Event query ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57803930
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.76 MB

=== Event tableMap ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804001
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 866
Columns amount: 3
Memory usage 2.75 MB

=== Event update ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804075
Event size: 74
Table: test4
Affected columns: 3
Changed rows: 1
Values: Array
(
    [0] => Array
        (
            [before] => Array
                (
                    [id] => 1
                    [data] => Hello
                    [data2] => World
                )

            [after] => Array
                (
                    [id] => 1
                    [data] => World
                    [data2] => Hello
                )

        )

)

Memory usage 2.76 MB

=== Event xid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804106
Event size: 31
Transaction ID: 662803
Memory usage 2.76 MB

=== Event gtid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804154
Event size: 48
Commit: true
GTID NEXT: 3403c535-624f-11e7-9940-0800275713ee:13679
Memory usage 2.76 MB

=== Event query ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804243
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.76 MB

=== Event tableMap ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804314
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 866
Columns amount: 3
Memory usage 2.76 MB

=== Event delete ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804368
Event size: 54
Table: test4
Affected columns: 3
Changed rows: 1
Values: Array
(
    [0] => Array
        (
            [id] => 1
            [data] => World
            [data2] => Hello
        )

)

Memory usage 2.77 MB

=== Event xid ===
Date: 2017-07-06T15:23:44+00:00
Log position: 57804399
Event size: 31
Transaction ID: 662804
Memory usage 2.77 MB

Benchmarks

Tested on VM

Debian 8.7
PHP 5.6.30
Percona 5.6.35

inxi

CPU(s)~4 Single core Intel Core i5-2500Ks (-SMP-) clocked at 5901 Mhz Kernel~3.16.0-4-amd64 x86_64 Up~1 day Mem~1340.3/1996.9MB HDD~41.9GB(27.7% used) Procs~122 Client~Shell inxi~2.1.28

php example/benchmark.php
Start insert data
7442 event by seconds (1000 total)
7679 event by seconds (2000 total)
7914 event by seconds (3000 total)
7904 event by seconds (4000 total)
7965 event by seconds (5000 total)
8006 event by seconds (6000 total)
8048 event by seconds (7000 total)
8038 event by seconds (8000 total)
8040 event by seconds (9000 total)
8055 event by seconds (10000 total)
8058 event by seconds (11000 total)
8071 event by seconds (12000 total)

FAQ

  1. ### Why and when need php-mysql-replication ? Well first of all mysql don't give you async calls. You usually need to program this in your application (by event dispaching and adding to some queue system and if your db have many point of entry like web, backend other microservices its not always cheap to add processing to all of them. But using mysql replication protocol you can lisen on write events and process then asynchronously (best combo it's to add item to some queue system like rabbitmq, redis or kafka). Also in invalidate cache, search engine replication, real time analytics and audits.
  2. ### It's awsome ! but what is the catch ? Well first of all you need to know that a lot of events may come through, like if you update 1 000 000 records in table "bar" and you need this one insert from your table "foo" Then all must be processed by script and you need to wait for your data. This is normal and this how it's work. You can speed up using config options. Also if script crashes you need to save from time to time position form binlog (or gtid) to start from this position when you run this script again to avoid duplicates.
  3. ### I need to process 1 000 000 records and its taking forever!! Like I mention in 1 point use queue system like rabbitmq, redis or kafka, they will give you ability to process data in multiple scripts.
  4. ### I have a problem ? you script is missing something ! I have found a bug ! Create an issue I will try to work on it in my free time :)
  5. ### How much its give overhead to mysql server ? It work like any other mysql in slave mode and its giving same overhead.
  6. ### Socket timeouts error To fix this best is to increase db configurations "net_read_timeout" and "net_write_timeout" to 3600. (tx Bijimon)
  7. ### Partial updates fix Set in my.conf ```binlog_row_image=full``` to fix reciving only partial updates.
  8. ### No replication events when connected to replica server Set in my.conf ```log_slave_updates=on``` to fix this (#71)(#66)

  Files folder image Files  
File Role Description
Files folder image.github (1 directory)
Files folder image.travis (1 file)
Files folder imageexample (3 files)
Files folder imagesrc (1 directory)
Files folder imagetests (1 file, 2 directories)
Accessible without login Plain text file .travis.yml Data Auxiliary data
Accessible without login Plain text file CHANGELOG.md Data Auxiliary data
Accessible without login Plain text file ISSUE_TEMPLATE.md Data Auxiliary data
Accessible without login Plain text file LICENSE Lic. LICENSE
Accessible without login Plain text file phpunit.xml Data Auxiliary data
Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  .github  
File Role Description
Files folder imageworkflows (1 file)

  Files folder image Files  /  .github  /  workflows  
File Role Description
  Accessible without login Plain text file tests.yml Data Auxiliary data

  Files folder image Files  /  .travis  
File Role Description
  Accessible without login Plain text file initializedb.sh Data Auxiliary data

  Files folder image Files  /  example  
File Role Description
  Accessible without login Plain text file benchmark.php Example Example script
  Accessible without login Plain text file dump_events.php Example Example script
  Plain text file resuming.php Class Class source

  Files folder image Files  /  src  
File Role Description
Files folder imageMySQLReplication (1 file, 11 directories)

  Files folder image Files  /  src  /  MySQLReplication  
File Role Description
Files folder imageBinaryDataReader (1 file)
Files folder imageBinLog (4 files)
Files folder imageCache (1 file)
Files folder imageConfig (3 files)
Files folder imageDefinitions (3 files)
Files folder imageEvent (9 files, 2 directories)
Files folder imageException (1 file)
Files folder imageGtid (3 files)
Files folder imageJsonBinaryDecoder (4 files)
Files folder imageRepository (6 files)
Files folder imageSocket (3 files)
  Plain text file MySQLReplicationFactory.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  BinaryDataReader  
File Role Description
  Plain text file BinaryDataReader.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  BinLog  
File Role Description
  Plain text file BinLogCurrent.php Class Class source
  Plain text file BinLogException.php Class Class source
  Plain text file BinLogServerInfo.php Class Class source
  Plain text file BinLogSocketConnect.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Cache  
File Role Description
  Plain text file ArrayCache.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Config  
File Role Description
  Accessible without login Plain text file Config.php Conf. Configuration script
  Accessible without login Plain text file ConfigBuilder.php Conf. Configuration script
  Plain text file ConfigException.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Definitions  
File Role Description
  Plain text file ConstEventsNames.php Class Class source
  Plain text file ConstEventType.php Class Class source
  Plain text file ConstFieldType.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Event  
File Role Description
Files folder imageDTO (13 files)
Files folder imageRowEvent (6 files)
  Plain text file Event.php Class Class source
  Plain text file EventCommon.php Class Class source
  Plain text file EventInfo.php Class Class source
  Plain text file EventSubscribers.php Class Class source
  Plain text file GtidEvent.php Class Class source
  Plain text file MariaDbGtidEvent.php Class Class source
  Plain text file QueryEvent.php Class Class source
  Plain text file RotateEvent.php Class Class source
  Plain text file XidEvent.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Event  /  DTO  
File Role Description
  Plain text file DeleteRowsDTO.php Class Class source
  Plain text file EventDTO.php Class Class source
  Plain text file FormatDescriptionEventDTO.php Class Class source
  Plain text file GTIDLogDTO.php Class Class source
  Plain text file HeartbeatDTO.php Class Class source
  Plain text file MariaDbGtidLogDTO.php Class Class source
  Plain text file QueryDTO.php Class Class source
  Accessible without login Plain text file RotateDTO.php Conf. Configuration script
  Plain text file RowsDTO.php Class Class source
  Plain text file TableMapDTO.php Class Class source
  Plain text file UpdateRowsDTO.php Class Class source
  Plain text file WriteRowsDTO.php Class Class source
  Plain text file XidDTO.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Event  /  RowEvent  
File Role Description
  Plain text file ColumnDTO.php Class Class source
  Plain text file ColumnDTOCollection.php Class Class source
  Plain text file RowEvent.php Class Class source
  Plain text file RowEventBuilder.php Class Class source
  Plain text file RowEventFactory.php Class Class source
  Plain text file TableMap.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Exception  
File Role Description
  Plain text file MySQLReplicationException.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Gtid  
File Role Description
  Accessible without login Plain text file Gtid.php Conf. Configuration script
  Plain text file GtidCollection.php Class Class source
  Plain text file GtidException.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  JsonBinaryDecoder  
File Role Description
  Plain text file JsonBinaryDecoderException.php Class Class source
  Plain text file JsonBinaryDecoderFormatter.php Class Class source
  Plain text file JsonBinaryDecoderService.php Class Class source
  Plain text file JsonBinaryDecoderValue.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Repository  
File Role Description
  Plain text file FieldDTO.php Class Class source
  Plain text file FieldDTOCollection.php Class Class source
  Plain text file MasterStatusDTO.php Class Class source
  Plain text file MySQLRepository.php Class Class source
  Plain text file PingableConnection.php Class Class source
  Plain text file RepositoryInterface.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Socket  
File Role Description
  Plain text file Socket.php Class Class source
  Plain text file SocketException.php Class Class source
  Plain text file SocketInterface.php Class Class source

  Files folder image Files  /  tests  
File Role Description
Files folder imageIntegration (3 files)
Files folder imageUnit (1 file, 6 directories)
  Accessible without login Plain text file bootstrap.php Test Unit test script

  Files folder image Files  /  tests  /  Integration  
File Role Description
  Plain text file BaseTest.php Class Class source
  Plain text file BasicTest.php Class Class source
  Plain text file TestEventSubscribers.php Class Class source

  Files folder image Files  /  tests  /  Unit  
File Role Description
Files folder imageBinaryDataReader (1 file)
Files folder imageCache (1 file)
Files folder imageConfig (1 file)
Files folder imageEvent (1 directory)
Files folder imageGtid (2 files)
Files folder imageRepository (1 file)
  Accessible without login Plain text file BaseTest.php Test Unit test script

  Files folder image Files  /  tests  /  Unit  /  BinaryDataReader  
File Role Description
  Accessible without login Plain text file BinaryDataReaderTest.php Test Unit test script

  Files folder image Files  /  tests  /  Unit  /  Cache  
File Role Description
  Plain text file ArrayCacheTest.php Class Class source

  Files folder image Files  /  tests  /  Unit  /  Config  
File Role Description
  Plain text file ConfigTest.php Class Class source

  Files folder image Files  /  tests  /  Unit  /  Event  
File Role Description
Files folder imageRowEvent (1 file)

  Files folder image Files  /  tests  /  Unit  /  Event  /  RowEvent  
File Role Description
  Plain text file TableMapTest.php Class Class source

  Files folder image Files  /  tests  /  Unit  /  Gtid  
File Role Description
  Accessible without login Plain text file GtidCollectionTest.php Test Unit test script
  Accessible without login Plain text file GtidTest.php Test Unit test script

  Files folder image Files  /  tests  /  Unit  /  Repository  
File Role Description
  Plain text file MySQLRepositoryTest.php Class Class source

 Version Control Unique User Downloads Download Rankings  
 100%
Total:766
This week:0
All time:4,405
This week:95Up
 User Ratings  
 
 All time
Utility:100%StarStarStarStarStarStar
Consistency:95%StarStarStarStarStar
Documentation:91%StarStarStarStarStar
Examples:100%StarStarStarStarStarStar
Tests:91%StarStarStarStarStar
Videos:-
Overall:87%StarStarStarStarStar
Rank:2