4

As we know mysql do replication asynchronously. I heard that I need some extra plugins to do
synchronous replication.

So let us Consider the situation of asynchronous replication: The master writes events to its binary log but does not know whether or when a master2 has retrieved and processed them. With asynchronous replication, if the master1 crashes, transactions that it has committed might not have been transmitted to any master2.

My question is whether these transactions will finally be replicated to master2 later when master1 starts up again? If it is not, then it is a big inconsistency problem.

My question is same for master-slave replication and master is down with same situation.

Do I need some special configuration parameter to make it happen automatically?

Or Do I have to manually dump out the data from master1 and import to master2 etc?

======

Update: I probably mis-used the word "crashes" above, I just want to refer the situation that master1 fails to sync the data to others for some time period. This replies (thanks) below cover two cases: real un-recoverable crash due to disk failure for example, or temporarily offline problem due to network problem etc.

huican
  • 505
  • 1
  • 9
  • 21
  • Who says master1 ever comes back up? Maybe the hardware failed. If consistency is important to you, you need something that verifies consistency. Percona's XtraDB Cluster is a common choice. – ceejayoz Jan 17 '14 at 21:43
  • I just assume master1 coming back later. The failure is assumed not due to harware failure, but network issue etc. – huican Jan 17 '14 at 21:55
  • Ceejayoz: Appreciate your input for Percona XtraDB cluster. Also I noticed something similar: Galera Cluster. On market, who are the leaders for the mysql data replication solution? – huican Jan 17 '14 at 22:11
  • Percona uses Galera under the hood. I think most would consider Percona's solution to be one of the leaders for synchronous MySQL replication. – ceejayoz Jan 17 '14 at 22:27

2 Answers2

4

If the master1 comes back online after an interruption, and the binlogs haven't been lost, then the replica can download any binlogs it missed out on. For this discussion, master2 is a replica.

Of course if master1's crash was severe enough to corrupt or lose its binlogs, you're out of luck.

There are also cases where master1 wrote changes to its database but the binlog entry was lost in the crash. To solve this risk you can set sync_binlog=1 so that transactions on master1 require that the respective binlog entry is safe on disk or else the transaction can't complete.

But syncing binlogs to disk on every COMMIT has some overhead, and many sites decide they'd rather have performance than data safety. It's true that some workloads simply have too high a rate of transactions per second to allow this. I.e. there's an physical upper bound to the number of disk syncs per second. The solution would then need to be to get faster disks (or SSD), or spread the workload over multiple servers.

I've also seen a reverse situation: the binlog entry is written to the filesystem cache on the master1, and the replica downloads that binlog entry and the replica applies it, but that entry never makes it to disk on the master1 because of the disk was having intermittent failures. So ironically, the replica had processed changes that were never committed to disk on the master!

You mentioned the possibility of synchronous replication. MySQL doesn't really have this option. The closest they have is Semisynchronous Replication which means that a transaction on master1 isn't allowed to commit until at least one semi-sync replica confirms that it has received the transaction's binlog entry. This means you'll never have a discrepancy like those described above. But this adds some latency to every COMMIT.

Why is it "semi"-synchronous? Because the COMMIT on master1 doesn't have to wait for the transaction to be executed on the replica, it only has to wait for the replica to acknowledge receiving the binlog event. The replica can still lag behind.

Comment from @ceejayoz mentions Percona XtraDB Cluster. This works pretty similarly to semisynchronous replication, in that during COMMIT, the writer node must transmit the log for that transaction to all other nodes in the cluster. So the latency is the speed at which the slowest node acknowledges receiving the event.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Thanks a lot. I am looking at semi-synchrounous, that needs extra plugins. Also appreciate your input for Percona XtraDB cluster. Also I noticed something similar: Galera Cluster. On market, who are the leaders for the mysql data replication solution? – huican Jan 17 '14 at 22:07
  • Percona XtraDB Cluster uses Galera. – Bill Karwin Jan 17 '14 at 22:12
1

If master1 crashes, the committed transactions will be available at the logfile, and as soon as it is up again, they'll be delivered to the slave(s), in this case, master2. If, by your configuration, there's a chance that while master1 was down master2 committed an identical primary key value, you'll have an inconsistency problem.

You may prevent this by assigning different primary keys by server — for example, one only writes odd numbers while the other even numbers. Or even a composed primary key that uses the server id.

Capilé
  • 2,038
  • 17
  • 14
  • I configured the auto-increment-increment and set auto-increment-offset different value for different nodes, so I think it can prevent the primary key problem. – huican Jan 17 '14 at 22:00
  • You means that as long as the transaction log is inside the logfile, when master1 is up again, it will write to slave. So essentially your answer for my question is the data will be transfer to master2? – huican Jan 17 '14 at 22:03
  • The slave should have a different auto-increment-offset and the same auto-increment-increment. Then you should be logically safe, but not, of course, safe from a disk crash that corrupts the logfile... – Capilé Jan 17 '14 at 22:04
  • Yes, the master records all of its transactions to the logfile. The slave gets the updates and stores the logfile position (where it did stop), when slave can reach master again, it queries from the last position it got, and asks for updates. – Capilé Jan 17 '14 at 22:05
  • This is true not only for server crashes, but for connectivity interruptions as well (if servers are on different networks). – Capilé Jan 17 '14 at 22:07
  • Yes, I did configure the auto-increment-increment same for all nodes. Thanks a lot for your great information. – huican Jan 17 '14 at 22:09
  • The auto-increment-offset solution works for avoiding conflicting auto-increment values, but it doesn't help to avoid other race conditions, like violating UNIQUE constraints or FOREIGN KEY constraints. – Bill Karwin Jan 17 '14 at 22:13