All Projects → rusuly → MySqlCdc

rusuly / MySqlCdc

Licence: other
MySQL/MariaDB binlog replication client for .NET

Programming Languages

C#
18002 projects

Projects that are alternatives of or similar to MySqlCdc

pgcapture
A scalable Netflix DBLog implementation for PostgreSQL
Stars: ✭ 94 (+32.39%)
Mutual labels:  replication, cdc, change-data-capture
mute-structs
MUTE-structs is a Typescript library that provides an implementation of the LogootSplit CRDT algorithm.
Stars: ✭ 14 (-80.28%)
Mutual labels:  real-time, replication
Replication Manager
Signal 18 repman - Replication Manager for MySQL / MariaDB / Percona Server
Stars: ✭ 461 (+549.3%)
Mutual labels:  replication, mariadb
azure-sql-db-change-stream-debezium
SQL Server Change Stream sample using Debezium
Stars: ✭ 74 (+4.23%)
Mutual labels:  cdc, change-data-capture
horgh-replicator
Golang binlog replication from MySQL to MySQL, PostgreSQL, Vertica, Clickhouse
Stars: ✭ 46 (-35.21%)
Mutual labels:  replication, binlog
pg2k4j
Postgresql To Kinesis For Java
Stars: ✭ 69 (-2.82%)
Mutual labels:  replication, cdc
kunlun-storage
Kunlun-storage is the storage component for KunlunBase. It's developed based on percona-mysql-8.0.x and contains exclusive features used by KunlunBase, performance enhancements and XA transaction crash safety enhancements without which MySQL would not be able to execute XA transactions reliably under error conditions such as power outage, proces…
Stars: ✭ 2 (-97.18%)
Mutual labels:  replication, binlog
debezium-incubator
Previously used repository for new Debezium modules and connectors in incubation phase (archived)
Stars: ✭ 89 (+25.35%)
Mutual labels:  cdc, change-data-capture
kafka-connect-http
Kafka Connect connector that enables Change Data Capture from JSON/HTTP APIs into Kafka.
Stars: ✭ 81 (+14.08%)
Mutual labels:  cdc, change-data-capture
southpaw
⚾ Streaming left joins in Kafka for change data capture
Stars: ✭ 48 (-32.39%)
Mutual labels:  cdc, change-data-capture
Realtime
Listen to your to PostgreSQL database in realtime via websockets. Built with Elixir.
Stars: ✭ 4,278 (+5925.35%)
Mutual labels:  cdc, change-data-capture
redis-connect-dist
Real-Time Event Streaming & Change Data Capture
Stars: ✭ 21 (-70.42%)
Mutual labels:  replication, cdc
Debezium
Change data capture for a variety of databases. Please log issues at https://issues.redhat.com/browse/DBZ.
Stars: ✭ 5,937 (+8261.97%)
Mutual labels:  cdc, change-data-capture
walrus
Applying RLS to PostgreSQL WAL
Stars: ✭ 59 (-16.9%)
Mutual labels:  replication, cdc
oracdc
Oracle database CDC (Change Data Capture)
Stars: ✭ 51 (-28.17%)
Mutual labels:  cdc, change-data-capture
replicator
MySQL Replicator. Replicates MySQL tables to Kafka and HBase, keeping the data changes history in HBase.
Stars: ✭ 41 (-42.25%)
Mutual labels:  cdc, binlog
OpenLogReplicator
Open Source Oracle database CDC written purely in C++. Reads transactions directly from database redo log files and streams in JSON or Protobuf format to: Kafka, RocketMQ, flat file, network stream (plain TCP/IP or ZeroMQ)
Stars: ✭ 112 (+57.75%)
Mutual labels:  cdc, change-data-capture
asyncmy
A fast asyncio MySQL/MariaDB driver with replication protocol support
Stars: ✭ 126 (+77.46%)
Mutual labels:  replication, binlog
Real-time-Data-Warehouse
Real-time Data Warehouse with Apache Flink & Apache Kafka & Apache Hudi
Stars: ✭ 52 (-26.76%)
Mutual labels:  cdc, change-data-capture
scylla-cdc-source-connector
A Kafka source connector capturing Scylla CDC changes
Stars: ✭ 19 (-73.24%)
Mutual labels:  cdc, change-data-capture

MySqlCdc

nuget build

MySQL/MariaDB binlog change data capture (CDC) connector for .NET

Implements MySQL replication protocol to stream binary log events in real-time.

Use cases

Transaction log events are immutable and appended in strictly sequential order. This simplifies your concurrency model and allows you to avoid distributed locks that handle race conditions from parallel database requests.

  • Event sourcing.
  • Cache invalidation.
  • OLAP. Analytics. Reporting. Data Warehouse.
  • Replicating MySQL database to Memcached/Redis cache.
  • Replicating MySQL database to NoSQL/Elasticsearch. Derived data systems.

Warnings

Be careful when working with binary log event streaming.

  • Binlog stream includes changes made to all databases on the master server including sql queries with sensitive information and you may leak data from the databases. Consider deploying your database to an isolated instance.
  • Transaction log represents a sequence of append-only files. It includes changes for databases/tables that you deleted and then recreated. Make sure you don't replay the phantom events in your application.
  • In transactional storage engines(InnoDB, MyRocks) binlog will only include committed transactions in their commit order to guarantee consistency. Aborted or rolled-back transactions will not appear in the binary log. On the other hand in non-transactional storage engines(MyISAM) binlog will also include changes from rolled-back transactions.

Limitations

Please note the lib currently has the following limitations:

  • Supports only standard auth plugins mysql_native_password and caching_sha2_password.
  • Currently, the library doesn't fully support SSL encryption.

Prerequisites

Please make sure the following requirements are met:

  1. The user is granted REPLICATION SLAVE, REPLICATION CLIENT privileges.

  2. Binary logging is enabled(it's done by default in MySQL 8). To enable binary logging configure the following settings on the master server and restart the service:

    binlog_format = row
    binlog_row_image = full
    

    MySQL 5.6/5.7 and MariaDB 10.1 also require the following line:

    server-id = 1
    
  3. Optionally in MySQL 5.6+ / MariaDB 10.5+ you can enable logging table metadata(column names, types, see TableMetadata class).

    binlog_row_metadata = full
    
  4. Optionally you can enable logging SQL queries that precede row based events and listen to RowsQueryEvent.

    MySQL

    binlog_rows_query_log_events = on
    

    MariaDB

    binlog_annotate_row_events = on
    
  5. Also note that there are expire_logs_days, binlog_expire_logs_seconds settings that control how long binlog files live. By default MySQL/MariaDB have expiration time set and delete expired binlog files. You can disable automatic purging of binlog files this way:

    expire_logs_days = 0
    

Example

You have to obtain columns ordinal position of the table that you are interested in. The library is not responsible for this so you have to do it using another tool.

select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME='AspNetUsers' and TABLE_SCHEMA='Identity'
order by ORDINAL_POSITION;

Alternatively, in MySQL 5.6 / MariaDB 10.5 and newer you can obtain column names by logging full metadata (see TableMetadataEvent.Metadata). This way the metadata is logged with each TableMapEvent which impacts bandwidth.

binlog_row_metadata = full

Binlog event stream replication

Data is stored in Cells property of row events in the same order. See the C# sample project.

var client = new BinlogClient(options =>
{
    options.Port = 3306;
    options.Username = "root";
    options.Password = "Qwertyu1";
    options.SslMode = SslMode.Disabled;
    options.HeartbeatInterval = TimeSpan.FromSeconds(30);
    options.Blocking = true;

    // Start replication from MariaDB GTID. Recommended.
    options.Binlog = BinlogOptions.FromGtid(GtidList.Parse("0-1-270"));

    // Start replication from MySQL GTID. Recommended.
    var gtidSet = "d4c17f0c-4f11-11ea-93e3-325d3e1cd1c8:1-107, f442510a-2881-11ea-b1dd-27916133dbb2:1-7";
    options.Binlog = BinlogOptions.FromGtid(GtidSet.Parse(gtidSet));

    // Start replication from the master binlog filename and position
    options.Binlog = BinlogOptions.FromPosition("mysql-bin.000008", 195);

    // Start replication from the master last binlog filename and position.
    options.Binlog = BinlogOptions.FromEnd();

    // Start replication from the master first available(not purged) binlog filename and position.
    options.Binlog = BinlogOptions.FromStart();
});

await foreach (var binlogEvent in client.Replicate())
{
    var state = client.State;

    if (binlogEvent is TableMapEvent tableMap)
    {
        await HandleTableMapEvent(tableMap);
    }
    else if (binlogEvent is WriteRowsEvent writeRows)
    {
        await HandleWriteRowsEvent(writeRows);
    }
    else if (binlogEvent is UpdateRowsEvent updateRows)
    {
        await HandleUpdateRowsEvent(updateRows);
    }
    else if (binlogEvent is DeleteRowsEvent deleteRows)
    {
        await HandleDeleteRowsEvent(deleteRows);
    }
    else await PrintEventAsync(binlogEvent);
}

A typical transaction has the following structure.

  1. GtidEvent if gtid mode is enabled.
  2. One or many TableMapEvent events.
    • One or many WriteRowsEvent events.
    • One or many UpdateRowsEvent events.
    • One or many DeleteRowsEvent events.
  3. XidEvent indicating commit of the transaction.

It's best practice to use GTID replication with the FromGtid method. Using the approach you can correctly perform replication failover. Note that in GTID mode FromGtid has the following behavior:

  • FromGtid(@@gtid_purged) acts like FromStart()
  • FromGtid(@@gtid_executed) acts like FromEnd()

Reading binlog files offline

In some cases you will need to read binlog files offline from the file system. This can be done using BinlogReader class.

using (Stream stream = File.OpenRead("mysql-bin.000001"))
{
    EventDeserializer deserializer = mariadb
    ? new MariaDbEventDeserializer()
    : new MySqlEventDeserializer();

    var reader = new BinlogReader(deserializer, stream);

    await foreach (var binlogEvent in reader.ReadEvents())
    {
        await PrintEventAsync(binlogEvent);
    }
}

Type mapping notes

MySQL Type .NET type
BLOB types byte[]
GEOMETRY byte[]
JSON (MySQL) byte[], see below
JSON (MariaDB) byte[], see below
BIT bool[]
TINYINT byte
SMALLINT short
MEDIUMINT int(3), see below
INT int
BIGING long
FLOAT float
DOUBLE double
DECIMAL string
VARCHAR, VARBINARY string
CHAR string
ENUM int
SET long
YEAR int
DATE Nullable<DateOnly>
DATETIME Nullable<DateTime>
TIME TimeSpan
TIMESTAMP DateTimeOffset
  • Invalid DATE, DATETIME values(0000-00-00) are parsed as DateOnly and DateTime null respectively.

  • TIME, DATETIME, TIMESTAMP (MySQL 5.6.4+) will lose microseconds when converted to .NET types as MySQL types have bigger fractional part than corresponding .NET types can store.

  • Signedness of numeric columns cannot be determined in MariaDB/MySQL 5.5. The library stores all numeric columns as CLS-compliant types from the table above. The client has the information and should manually cast to sbyte, ushort, uint or ulong if necessary. The only exception is 3-byte unsigned mediumint which must be casted this way:

    // casting unsigned mediumint columns
    uint cellValue = (uint)(int)row.Cells[0];
    uint mediumint = (cellValue << 8) >> 8;  
  • JSON columns have different storage format in MariaDB and MySQL:

    // MariaDB stores JSON as strings
    byte[] data = (byte[])row.Cells[0];
    string json = Encoding.UTF8.GetString(data);
    
    // MySQL stores JSON in binary format that needs to be parsed
    byte[] data = (byte[])row.Cells[0];
    string json = MySqlCdc.Providers.MySql.JsonParser.Parse(data);    
  • GEOMETRY type is read as byte[] but there is no parser that constructs .NET objects.

  • DECIMAL type is parsed to string as MySql decimal has bigger range(65 digits) than .NET decimal.

Similar projects

The project is based on mysql-binlog-connector-java library, MariaDB and MySQL documentation.

Supported versions

MySqlCdc supports both MariaDB & MySQL server.

MariaDB Status
10.1 Supported
10.2 Supported
10.3 Supported
10.4 Supported
10.5 Supported
10.6 Supported
MySQL Status
5.6 Supported
5.7 Supported
8.0 Supported

License

The library is provided under the MIT License.

Note that the project description data, including the texts, logos, images, and/or trademarks, for each open source project belongs to its rightful owner. If you wish to add or remove any projects, please contact us at [email protected].