Check

SELECT @@SERVER_ID;

SELECT @@INNODB_FLUSH_LOG_AT_TRX_COMMIT;

SELECT @@SYNC_BINLOG;

SELECT @@SKIP_NETWORKING;

Lost Master

Binary Log File Position Based Replication

TODO

Replication with Global Transaction Identifiers

TODO

MySQL Master-Master Replication

Master-master replication (with load balancer) introduces the possibility that two or more servers can replicate concurrent changes to each other. To prevent key conflicts by using the auto_increment_offset and auto_increment_increment configuration variables, to ensure AUTO_INCREMENT values will be unique to each server. 

auto-increment-increment = 2

auto-increment-offset = 2 or 1 

Appropriate load balancers would be:

• keepalived

• haproxy

• heartbeat + mon

• mysql-proxy

• F5 Big-IP

• java conection pools

Limitations

• the safest option is to use one server as active for both writes and reads, second will be treated as standby

• no synchronous replication - if application can handle replication lag between servers it is possible to scale out reads

• difficult to extend - this is only 2 server setup

Bibliography