Questions tagged [semi-sync-replication]

Semisynchronous Replication : MySQL's Proprietary Mechanism for Replication

First introduced in MySQL 5.5, Semisynchronous Replication offers a healthy alternative to MySQL Replication, which is asynchronous by nature. Here is how they differ:

MySQL Replication

  • Master Executes SQL
  • Master Records SQL Event in its Binary Logs
  • Slave Reads SQL Event from Master Binary Logs
  • Slave Stores SQL Event in its Relay Logs via I/O Thread
  • Slave Reads Next SQL Event From Relay Log via SQL Thread
  • Slave Executes SQL
  • Slave Acknowledges Master of the Complete Execution of the SQL Event

Semisynchronous Replication

  • Master Executes SQL
  • Master Records SQL Event in its Binary Logs
  • Slave Reads SQL Event from Master Binary Logs
  • Slave Acknowledges Master of the Receipt of the SQL Event
  • Slave Stores SQL Event in its Relay Logs via I/O Thread
  • Slave Reads Next SQL Event From Relay Log via SQL Thread
  • Slave Executes SQL
  • Slave Acknowledges Master of the Complete Execution of the SQL Event

Benefits

  1. This permits a Slave to be closer sync'd to its Master
  2. If there are multiple Slaves, the Slave with the minimum ProcessID on the Master would be closer sync'd to its Master
  3. In the event of network latency, MySQL will switch back to standard asynchronous replication until network latency dissipates.
11 questions
5
votes
1 answer

Installing and enabling both master and slave semisynchronous plugins on same server

I want to run a semisynchronous replication setup, and for simplicity I'd like to not have to INSTALL and UNINSTALL plugins when a slave is promoted to a master. So I'm wondering if I'll experience any problems having both master and slave plugins…
jberryman
  • 481
  • 1
  • 5
  • 11
4
votes
1 answer

Question about MySQL Semisynchronous Replication

I was researching about MySQL Semisynchronous Replication, my understanding about the workflow is the follow: Master Receive a successful transaction from a client session. Master write it to his binaty log Master wait for…
altmannmarcelo
  • 1,207
  • 9
  • 26
3
votes
3 answers

High disk IO with MySQL master-master replication

We've been evaluating an active-passive master-master replication setup for our MySQL database, running on version 8.0. While diagnosing some general slowness issues, we were surprised to see very high disk IO ranging from 500MB/s - 1500MB/s on both…
2
votes
1 answer

Syncing updates to master in master/slave setup [semi-sync replication]

Background info I have two MySQL 5.5 servers set up in a Master/Slave configuration with Semi-synchronous replication. The database is being used for a high-profile WordPress site. Problem It seems to work great except for one thing: When the master…
2
votes
1 answer

Can PostgresSQL cluster have synchronus and asyncronus standbys at the same time for the same dbs?

In this question, Write concern in PostgreSQL Laurenz Albe explained how to control the set of standby servers when data is committed to PostgresSQL, and that gave birth to another question. What if I want to have N standbys, and want to have…
2
votes
1 answer

MySQL Semi-synchronous replication with Multi-Master

Is it possible to use semi-synchronous replication with a Multi-Master setup? I've tried to follow this guide to setup a semi-synchronous replication for a master-slave setup: https://avdeo.com/2015/02/02/semi-synchronous-replication-in-mysql/ But…
1
vote
1 answer

MySQL Database replication for high availability

configuration: 1 balancer 2 Azure machines(4 gb ram, 2 cores), with MySQL master-master replication enabled. X users connecting to the balancer to sync data. test: i've tested the MySQL replication with loader.io (for a connection spike), for 6000…
1
vote
2 answers

Breaking Semisynchronous Replication in MySQL 5.5

I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7. My application is running and updating the database of the master server and same is being updated in the slave database server. But due to some unknown…
Neeru Sharma
  • 173
  • 1
  • 1
  • 8
1
vote
1 answer

Difference between flushing and synchronisation and writing to disk (MySQL)

I get confused with the terminology such as flushing and writing to disk and synchronising to the disk. Could someone please explain me what is the difference between flushing and writing and synchronising to disk especially in context of MySQL. I…
Avinash Pawar
  • 216
  • 2
  • 11
1
vote
1 answer

Accessing Oracle DB from SQL Server DB

I am looking for the best and simplest way for accessing an Oracle db from an SQL Server db. Both are on separate physical servers. I am looking for a one-way access in read-only mode. SQL Server Database would be used by one system for now but…
0
votes
0 answers

mysql unable to start with semi sync parameter

Im using mysql 5.7 and it has 2 slaves with GTID. I tried to enable semi-sync and I have done with set global variable in mysql prompt. It was running fine. Then I did a restart, after that mysql unable to start and it says unknown variable…
TheDataGuy
  • 1,986
  • 5
  • 36
  • 67