Menu
I'm looking to clear up a bit of confusion. I have a master + slaves setup. I have one master, and 3 slaves. All writes (INSERT|UPDATE|DELETE) go the master. All reads (SELECT) go to one of the slaves, which is chosen at random. All of my tables are using the InnoDB storage engine.
I'm curious how MySQL/InnoDB handles transactions in this setup. If MySQL writes each change within the transaction to the binlog, then all should be fine. However I can see there being some big problems if the binlog isn't written to until the transaction is commited.
Can anyone explain what's going on within MySQL during transaction with replication in place?
948
And that's where master-slave architecture comes in handy. Basically, master-slave databases involve caching data from the master database to the slave databases. This replication process helps database administrators to replicate copies of the parent database to multiple servers simultaneously.
1 Answer. Master-Master: This is similar to Master-Slave architecture, the only difference is that both the nodes are masters and replica at the same time i.e. there will be circular replication between the nodes.
GTIDs bring transaction-based replication to the MySQL databases. With GTID replication, every transaction can be identified and tracked as it is committed on the originating source server and applied by replicas. You don't have to refer to any log files when starting the replica servers.
According to this, the slaves can only see changes after the transaction is committed.
Transactions that fail on the master do not affect replication at all. MySQL replication is based on the binary log where MySQL writes SQL statements that modify data. A transaction that fails (for example, because of a foreign key violation, or because it is rolled back) is not written to the binary log, so it is not sent to slaves.
And this confirms:
The binary log is crash-safe. Only complete events or transactions are logged or read back.
129
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
