PHP Classes
elePHPant
Icontem

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 (62)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Last Updated Ratings Unique User Downloads Download Rankings
2016-05-09 (1 month ago) RSS 2.0 feedNot enough user ratingsTotal: 285 This week: 3All time: 7,062 This week: 360Up
Version License PHP version Categories
mysql-replication 1.4MIT/X Consortium ...5.4Networking, 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: 7 packages by
Country: Poland Poland
Age: 32
All time rank: 126033 in Poland Poland
Week rank: 147 Up3 in Poland Poland Up
Innovation award
Innovation award
Nominee: 3x

Details

php-mysql-replication

Build Status Latest Stable Version Total Downloads Latest Unstable Version License

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

composer require krowinski/php-mysql-replication

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

Configuration

You can pass this params to ConfigService->makeConfigFromArray([])

'user' - your mysql user (mandatory)

'ip' - your mysql host ip (mandatory)

'password' - your mysql password (mandatory)

'port' - your mysql host port

'dbName' - db name you want to listen

'charset' - db connection charset

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

'slaveId' - script slave id for identification

'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 yo ignore events (full list in ConstEventType.php file)

'tablesOnly' - array to only listen on given tables

'databasesOnly' - array to only listen on given databases

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:

=== Event gtid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021014
Event size: 48
Commit: true
GTID NEXT: 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:160837
Memory usage 2.36 MB

=== Event query ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021159
Event size: 145
Database: php_mysql_replication
Execution time: 0
Query: CREATE DATABASE php_mysql_replication
Memory usage 2.36 MB

=== Event gtid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021207
Event size: 48
Commit: true
GTID NEXT: 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:160838
Memory usage 2.36 MB

=== Event query ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021422
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.36 MB

=== Event gtid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021470
Event size: 48
Commit: true
GTID NEXT: 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:160839
Memory usage 2.36 MB

=== Event query ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021559
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.36 MB

=== Event tableMap ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021630
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 1135
Columns amount: 3
Memory usage 2.36 MB

=== Event write ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021682
Event size: 52
Table: test4
Affected columns: 3
Changed rows: 1
Values: Array
(
    [0] => Array
        (
            [id] => 1
            [data] => Hello
            [data2] => World
        )

)

Memory usage 2.37 MB

=== Event xid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021713
Event size: 31
Transaction ID: 252191
Memory usage 2.37 MB

=== Event gtid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021761
Event size: 48
Commit: true
GTID NEXT: 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:160840
Memory usage 2.37 MB

=== Event query ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021850
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.37 MB

=== Event tableMap ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021921
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 1135
Columns amount: 3
Memory usage 2.37 MB

=== Event update ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4021991
Event size: 70
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.37 MB

=== Event xid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022022
Event size: 31
Transaction ID: 252196
Memory usage 2.37 MB

=== Event gtid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022070
Event size: 48
Commit: true
GTID NEXT: 9b1c8d18-2a76-11e5-a26b-000c2976f3f3:160841
Memory usage 2.37 MB

=== Event query ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022159
Event size: 89
Database: php_mysql_replication
Execution time: 0
Query: BEGIN
Memory usage 2.37 MB

=== Event tableMap ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022230
Event size: 71
Table: test4
Database: php_mysql_replication
Table Id: 1135
Columns amount: 3
Memory usage 2.37 MB

=== Event delete ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022282
Event size: 52
Table: test4
Affected columns: 3
Changed rows: 1
Values: Array
(
    [0] => Array
        (
            [id] => 1
            [data] => World
            [data2] => Hello
        )

)

Memory usage 2.38 MB

=== Event xid ===
Date: 2016-03-13T21:46:31+00:00
Log position: 4022313
Event size: 31
Transaction ID: 252199
Memory usage 2.38 MB

Benchmarks

Tested on VM

Debian 8.3 PHP 5.6.17 MySQL 5.6.29-76.2-log Percona Server

inxi
CPU~Dual core Intel Core i5-2500K (-MCP-) clocked at 3701 Mhz Kernel~3.16.0-4-amd64 x86_64 Up~2 days Mem~1170.9/3952.4MB HDD~41.9GB(15.2% used) Procs~119 Client~Shell inxi~2.1.28

php example/benchmark.php
Start insert data
6531 event by seconds (1000 total)
6665 event by seconds (2000 total)
6674 event by seconds (3000 total)
6535 event by seconds (4000 total)
6555 event by seconds (5000 total)
6615 event by seconds (6000 total)
6619 event by seconds (7000 total)
6660 event by seconds (8000 total)
6666 event by seconds (9000 total)
6701 event by seconds (10000 total)
6696 event by seconds (11000 total)
6704 event by seconds (12000 total)

  Files folder image Files  
File Role Description
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 composer.json 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  /  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
  Accessible without login Plain text file dump_events_callback.php Example Example script

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

  Files folder image Files  /  src  /  MySQLReplication  
File Role Description
Files folder imageBinaryDataReader (3 files, 1 directory)
Files folder imageBinLog (3 files, 1 directory)
Files folder imageConfig (3 files, 1 directory)
Files folder imageDefinitions (5 files)
Files folder imageEvent (7 files, 3 directories)
Files folder imageException (1 file)
Files folder imageGtid (4 files)
Files folder imageRepository (1 file)
  Plain text file MySQLReplicationFactory.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  BinaryDataReader  
File Role Description
Files folder imageException (1 file)
  Plain text file BinaryDataReader.php Class Class source
  Plain text file BinaryDataReaderBuilder.php Class Class source
  Plain text file BinaryDataReaderService.php Class Class source

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

  Files folder image Files  /  src  /  MySQLReplication  /  BinLog  
File Role Description
Files folder imageException (1 file)
  Plain text file BinLogAuth.php Class Class source
  Plain text file BinLogConnect.php Class Class source
  Plain text file BinLogServerInfo.php Class Class source

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

  Files folder image Files  /  src  /  MySQLReplication  /  Config  
File Role Description
Files folder imageException (1 file)
  Accessible without login Plain text file Config.php Conf. Configuration script
  Accessible without login Plain text file ConfigBuilder.php Conf. Configuration script
  Accessible without login Plain text file ConfigService.php Conf. Configuration script

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

  Files folder image Files  /  src  /  MySQLReplication  /  Definitions  
File Role Description
  Plain text file ConstCapabilityFlags.php Class Class source
  Plain text file ConstCommand.php Class Class source
  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 (10 files)
Files folder imageException (1 file)
Files folder imageRowEvent (5 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 GtidEvent.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 GTIDLogDTO.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  /  Exception  
File Role Description
  Plain text file EventException.php Class Class source

  Files folder image Files  /  src  /  MySQLReplication  /  Event  /  RowEvent  
File Role Description
  Plain text file Columns.php Class Class source
  Plain text file RowEvent.php Class Class source
  Plain text file RowEventBuilder.php Class Class source
  Plain text file RowEventService.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
  Plain text file GtidService.php Class Class source

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

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

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

  Files folder image Files  /  tests  /  Unit  
File Role Description
Files folder imageBinaryDataReader (1 file)
Files folder imageGtid (3 files)
  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  /  Gtid  
File Role Description
  Accessible without login Plain text file GtidCollectionTest.php Test Unit test script
  Accessible without login Plain text file GtidServiceTest.php Test Unit test script
  Accessible without login Plain text file GtidTest.php Test Unit test script

 Version Control Unique User Downloads Download Rankings  
 100%
Total:285
This week:3
All time:7,062
This week:360Up