4 ms·

So if these clusters are using binlog replication, do they just ignore the possibility of lost writes and inconsistent data after a failover?

by aeyes 2y ago

matthewaveryusa 2y ago
Correct, you would need to use something like galera clustering or synchronous replication to not lose writes.
bigmutant 2y ago
That all depends on the setup. The "standard" setup (not specific to MySQL) is: - Single Write Leader per partition - Backup Write Leader that is setup with synchronous replication (so WL -> WLB and waits for commit) - Read Followers all connected asynchronously using either binlog replication (not recommended anymore) or GTID-based row replication (recommended) In the above scenario, the odds of loss are pretty small since the Write Leader has a direct backup, and any of the Read Followers can be promoted to a Write Leader/Backup. DDIA calls the above semi-synchronous replication, although MySQL now supports a similar-but-slightly different version out of the box: https://dev.mysql.com/doc/refman/8.4/en/replication-semisync.html https://dev.mysql.com/doc/refman/8.4/en/replication-semisync...
- evanelias 2y ago
  Terminology-wise, that's not quite right. "Binlog replication" is the general term for all built-in MySQL logical replication, including all formats (statement, mixed, row); positioning via coordinates or via GTID; async or semi-sync or group replication. Among AWS users, "binlog replication" is often contrasted against Aurora's system, which uses physical replication instead of logical. When using binlog replication, you are correct that GTID positioning and row-based replication are strongly recommended and widely used. Regarding semi-sync replication: that's been a MySQL feature for over a decade now, and there are some indications from Oracle that it may become deprecated in the future. (which is surprising, since many large MySQL users do leverage it to ensure writes cannot be lost. But it seems Group Replication is promoted more by Oracle.) MySQL semi-sync doesn't necessarily involve the setup you've described as "standard" above. In my experience it's more common to see 2 replicas doing semi-sync ack'ing, not 1. And sometimes these are just binlog servers rather than full MySQL replicas; that's the setup Facebook adopted in 2015, although more recently they've moved to a home-grown Raft-based replication system.
roncesvalles 2y ago
Yes. This is in fact a CAP theorem tradeoff. Binlog asynchronous replication means they've elected to be AP. If you want zero risk of data loss for committed transactions (for single node failure), you'd replicate synchronously to one other replica. But then, both the primary and replica would need to be available and connected for the leader to accept write transactions, making it CP.

So if these clusters are using binlog replication, do they just ignore the possibility of lost writes and inconsistent data after a failover?

by aeyes 2y ago

So if these clusters are using binlog replication, do they just ignore the possibility of lost writes and inconsistent data after a failover?

matthewaveryusa 2y ago

Correct, you would need to use something like galera clustering or synchronous replication to not lose writes.

bigmutant 2y ago

That all depends on the setup. The "standard" setup (not specific to MySQL) is: - Single Write Leader per partition - Backup Write Leader that is setup with synchronous replication (so WL -> WLB and waits for commit) - Read Followers all connected asynchronously using either binlog replication (not recommended anymore) or GTID-based row replication (recommended) In the above scenario, the odds of loss are pretty small since the Write Leader has a direct backup, and any of the Read Followers can be promoted to a Write Leader/Backup. DDIA calls the above semi-synchronous replication, although MySQL now supports a similar-but-slightly different version out of the box: https://dev.mysql.com/doc/refman/8.4/en/replication-semisync.html https://dev.mysql.com/doc/refman/8.4/en/replication-semisync...

evanelias 2y ago
Terminology-wise, that's not quite right. "Binlog replication" is the general term for all built-in MySQL logical replication, including all formats (statement, mixed, row); positioning via coordinates or via GTID; async or semi-sync or group replication. Among AWS users, "binlog replication" is often contrasted against Aurora's system, which uses physical replication instead of logical. When using binlog replication, you are correct that GTID positioning and row-based replication are strongly recommended and widely used. Regarding semi-sync replication: that's been a MySQL feature for over a decade now, and there are some indications from Oracle that it may become deprecated in the future. (which is surprising, since many large MySQL users do leverage it to ensure writes cannot be lost. But it seems Group Replication is promoted more by Oracle.) MySQL semi-sync doesn't necessarily involve the setup you've described as "standard" above. In my experience it's more common to see 2 replicas doing semi-sync ack'ing, not 1. And sometimes these are just binlog servers rather than full MySQL replicas; that's the setup Facebook adopted in 2015, although more recently they've moved to a home-grown Raft-based replication system.

roncesvalles 2y ago

Yes. This is in fact a CAP theorem tradeoff. Binlog asynchronous replication means they've elected to be AP. If you want zero risk of data loss for committed transactions (for single node failure), you'd replicate synchronously to one other replica. But then, both the primary and replica would need to be available and connected for the leader to accept write transactions, making it CP.