MySQL/MariaDB binlog change data capture (CDC) connector for .NET
Implements MySQL replication protocol to stream binary log events in real-time.
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.
Be careful when working with binary log event streaming.
Please note the lib currently has the following limitations:
mysql_native_password
and caching_sha2_password
.Please make sure the following requirements are met:
The user is granted REPLICATION SLAVE
, REPLICATION CLIENT
privileges.
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
Optionally in MySQL 5.6+ / MariaDB 10.5+ you can enable logging table metadata(column names, types, see TableMetadata
class).
binlog_row_metadata = full
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
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
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
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.
GtidEvent
if gtid mode is enabled.TableMapEvent
events.
WriteRowsEvent
events.UpdateRowsEvent
events.DeleteRowsEvent
events.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()
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);
}
}
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.
The project is based on mysql-binlog-connector-java library, MariaDB and MySQL documentation.
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 |
The library is provided under the MIT License.
此处可能存在不合适展示的内容,页面不予展示。您可通过相关编辑功能自查并修改。
如您确认内容无涉及 不当用语 / 纯广告导流 / 暴力 / 低俗色情 / 侵权 / 盗版 / 虚假 / 无价值内容或违法国家有关法律法规的内容,可点击提交进行申诉,我们将尽快为您处理。