MySQL “master-master” is two reciprocal replication channels: node A sends its binary-log events to node B, and node B sends its events to node A. It is asynchronous circular replication, not automatic conflict resolution. The safest production design keeps one node writable at a time, uses GTIDs for positioning, assigns unique server IDs, and prevents generated-key collisions before any application writes are enabled.
Antihost offers Linux VPS plans with full root access, unlimited traffic on a 1 Gbps port and a 14-day free trial.
What this topology does—and what it does not do
MySQL calls the writing endpoint a source and the receiving endpoint a replica. In this guide, node A is a source for node B while node B is simultaneously a source for node A. Each server therefore has one replication channel pointed at its peer.
As an Amazon Associate I earn from qualifying purchases.
Recommended Free Tools
Replication is asynchronous by default: a commit on one server does not wait for the other server to apply the transaction. A network break can leave both servers accepting different changes. When the connection returns, MySQL does not merge conflicting row changes or choose a winner. Circular replication is consequently a transport topology, not a distributed conflict-resolution system.
Use one active writer and keep the second node ready for failover unless your application has a tested multi-writer design. A true multi-writer deployment needs explicit table ownership, deterministic transaction rules, conflict handling, fencing during network partitions, and tests that exercise simultaneous writes and failover.
#1 Best Overall
Plan the two-node layout
| Node | Replication identity | AUTO_INCREMENT offset | Outbound channel |
|---|---|---|---|
| Node A | server_id=101 |
1 | To node B |
| Node B | server_id=102 |
2 | To node A |
The IDs only need to be unique positive integers within the replication topology. Reusing an ID can make events appear to originate from the local server and produce confusing skips or loops.
Give each VPS a stable private address or internal DNS name, such as node-a.example.internal and node-b.example.internal. Permit MySQL traffic (normally TCP 3306) only between those addresses through a private network or a firewall. Synchronize clocks, provide enough disk for binary and relay logs, and verify that both installations use compatible MySQL versions, schemas, and storage engines.
Prerequisites and safety checks
- Two supported MySQL installations with reliable connectivity between the VPS instances.
- Matching schemas and compatible storage engines. Existing data must begin from a consistent snapshot or logical backup; a cold installation should create the same schema on both nodes.
- Binary logging enabled on both servers, with durable locations for binary and relay logs and a retention policy that matches your recovery window.
- A dedicated replication account on each server. Grant only the replication privileges required by the MySQL version in use, and restrict the account’s source host to the peer’s private address.
- An independent backup and a rollback plan. Replication copies mistakes and deletions, so it cannot replace backups.
- A written failover and rejoin procedure. Do not expose both nodes to unrestricted application writes until collision behavior has been demonstrated safely.
Configure identity and binary logging
On node A, set server_id=101; on node B, set server_id=102. Enable log_bin on both. Configure binary-log and relay-log locations on durable storage, monitor their growth, and rotate or expire logs according to the amount of history needed for recovery. Every transaction that the peer may need must remain available in the source’s binary logs until the peer has retrieved it.
Apply these settings in each server’s MySQL configuration, restart when required by the installation, and verify the result:
SHOW VARIABLES WHERE Variable_name IN ('server_id','log_bin');A source that is not writing a binary log cannot provide its local changes to the other node. Likewise, an incorrectly reused server_id can invalidate the topology even when both channels report that they are connected.
Enable GTIDs on both nodes
GTID-based replication gives each transaction a global identifier. With gtid_mode=ON and enforce_gtid_consistency=ON on both servers, SOURCE_AUTO_POSITION=1 lets a rejoining node request the transactions it is missing instead of requiring hand-maintained binary-log file and position values.
For a new installation, set the two GTID variables consistently on both nodes before creating the channels:
SET PERSIST gtid_mode = ON;
SET PERSIST enforce_gtid_consistency = ON;Follow your MySQL release’s documented transition procedure if these statements cannot be applied directly in the current state. An existing non-GTID topology must be synchronized first: make both servers read-only, allow replication to catch up, stop the servers, and restart them with GTID mode enabled. All non-GTID transactions must be cleared from the topology before the conversion is complete. Do not switch one side to GTIDs while the other side can still generate non-GTID transactions.
After enabling GTIDs, take a fresh backup. MySQL warns that backups and binary logs containing transactions without GTIDs cannot be used on GTID-enabled servers in the same way as GTID-complete material.
Rank #2
Create reciprocal replication accounts
Create an account on node A that node B will use to connect, and an account on node B that node A will use. Use separate credentials, store them in a secret manager, and protect the connection with TLS where your deployment supports it. The exact privilege name can vary by MySQL release, so follow the replication-account step in the manual for that release rather than granting broad administrative rights.
Do these 3 things before closing this tab:
1Clear out junk files and repair common Windows errors2Fix the driver behind crashes, sound loss and screen glitches3Repair Windows errors before they cause bigger problemsAt the network layer, allow the account to connect only from the peer’s private address. A replication user should not be reachable from the public internet. Record the account name, permitted host, port, and certificate requirements in the failover runbook.
Start with identical data
Replication cannot make two initially different databases identical. For existing data, stop or quiesce writes, take a consistent snapshot or logical backup, restore it on the other VPS, and verify schema, row counts, and storage engines. For a cold start, create the same databases and tables on both nodes before starting either channel.
Capture the backup or snapshot time and the GTID set represented by it. This gives you a known starting point when checking that the peer has retrieved and executed everything in the seed.
Configure the channel from node B to node A
On node A, stop any stale channel before replacing its source definition. Current MySQL 8.0 releases (8.0.23 and later) use CHANGE REPLICATION SOURCE TO and START REPLICA:
Free tools Windows power users keep installed
One-click scans. No signup required.
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='node-b.example.internal',
SOURCE_PORT=3306,
SOURCE_USER='repl_from_b',
SOURCE_PASSWORD='use-a-secret-manager',
SOURCE_AUTO_POSITION=1;
START REPLICA;The channel now retrieves node B’s binary-log transactions and applies them on node A. Use the reciprocal account created on node B.
Configure the channel from node A to node B
On node B, define node A as the source:
STOP REPLICA;
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='node-a.example.internal',
SOURCE_PORT=3306,
SOURCE_USER='repl_from_a',
SOURCE_PASSWORD='use-a-secret-manager',
SOURCE_AUTO_POSITION=1;
START REPLICA;On releases older than MySQL 8.0.23, use the older names CHANGE MASTER TO and START SLAVE instead. MySQL 8.0.26 also changed the startup option spelling to skip_replica_start; older installations use the former slave spelling. Do not mix statement names from different release documentation without checking the server version.
Do not configure both channels with the same source account or host by accident. Confirm that each channel points to the opposite node and that the source has binary logging enabled.
Prevent AUTO_INCREMENT collisions
If only one node ever writes a given table, ordinary generated keys will not collide. If both nodes may insert into the same AUTO_INCREMENT tables, reserve alternating sequences:
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Scan for outdated or missing drivers - takes under a minuteDriver Scan →Clear out junk files and repair common Windows errorsFree Scan →- Set
auto_increment_increment=2on both servers. - Set
auto_increment_offset=1on node A. - Set
auto_increment_offset=2on node B.
Persist these values in the MySQL configuration or through the supported persistent-variable mechanism, then verify them with:
SHOW VARIABLES WHERE Variable_name IN
('auto_increment_increment','auto_increment_offset');The increment and offset are server-wide settings. They affect every AUTO_INCREMENT column on that server, not just one table, and session-level overrides can change the behavior for a particular connection. Audit all tables and application connection settings before relying on this scheme. It prevents generated-number overlap; it does not resolve updates to the same logical row, deletes on one node versus updates on the other, or application-level uniqueness conflicts.
The documented default for auto_increment_increment is 1. The alternating setup changes that default and therefore must be included in configuration management and disaster-recovery procedures.
Choose a write policy before production traffic
Recommended: one active writer
Route application writes to one node and keep the peer replicating and ready for promotion. During failover, fence or otherwise make the old writer unable to accept writes, promote the peer according to the runbook, and redirect clients. This avoids most conflict classes while retaining a second server for continuity.
Multi-writer only with application controls
Concurrent writes require ownership rules (for example, each node owns a defined set of tables or key ranges), deterministic conflict behavior, and an external decision about which node remains writable during a partition. Test duplicate keys, deletes, retries, long transactions, and simultaneous updates. MySQL’s circular channels will not reconcile divergent row histories for you.
Semisynchronous versus synchronous behavior
Asynchronous replication allows a source commit to complete before the peer has received the event. MySQL semisynchronous replication can make a commit wait for at least one replica to acknowledge receipt and logging; the documented rpl_semi_sync_master_timeout default is 10,000 milliseconds. This still is not synchronous, conflict-free multi-primary replication. MySQL documentation associates synchronous replication with NDB Cluster, a different architecture that should be evaluated when the requirement is synchronous or multi-primary behavior.
Validate both channels
Run the status check on each node:
SHOW REPLICA STATUSGOn older releases, use the equivalent status statement for that release. Confirm all of the following on both servers:
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minute- The receiver and applier threads are running.
- No last-error field contains an error.
- Retrieved and executed GTID sets continue to advance.
- The source host, port, and replication user are the intended peer.
- Relay-log disk usage is stable and the channel’s lag is understood.
Use a disposable test schema for a directional check. Insert a uniquely identifiable row on node A, record its transaction identifier and timestamp, and verify that it appears on node B. Repeat in the opposite direction. This is an operator procedure to run in your environment, not a guarantee that a channel is safe merely because its threads are running.
Next, exercise the failover path: stop the active writer, fence it, promote the peer, and verify that application connections reconnect to the promoted node. Rejoin the old node by provisioning it from a known-good snapshot or by using GTID auto-positioning after its state is verified; do not blindly start a stale channel against divergent data.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.Backups, monitoring, and routine operations
Maintain backups independently of replication and take the fresh backup required after GTID activation. Test restoration, not just backup creation. A replicated deletion, corrupt transaction, or operator error is otherwise copied to both VPS instances.
Alert on stopped receiver or applier threads, increasing replication lag, relay-log or binary-log disk growth, expired TLS certificates, and divergent row counts or checksums. Keep enough binary-log history for a disconnected peer to catch up, but rotate logs before storage exhaustion. If a peer has been offline beyond retained history, rebuild it from a consistent backup rather than guessing a starting position.
The Tool Desk
Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →Outbyte Driver Updater FREEFix the driver behind crashes, sound loss and screen glitchesFind Drivers →Failure modes and recovery decisions
Duplicate-key or row conflicts
Stop one writer and preserve the relevant binary and relay logs. Common causes are concurrent writes to the same logical rows, reused server IDs, or unsafe generated-key settings. Compare the rows and transaction histories, repair the data deliberately, and only then resume replication.
Errant transactions
An errant transaction is a transaction present in a server’s GTID set that should not have been applied there. Do not skip an error casually just to make the status output turn green. Identify the transaction, compare data on both nodes, and repair from a known-good backup or controlled SQL. Apply a GTID correction procedure only after the data decision is documented.
Network partition
Both servers can continue accepting writes while isolated, creating divergent histories. Use fencing or an external leader decision so only one node remains writable. When connectivity returns, reconcile or rebuild the losing side according to the runbook; simply reconnecting channels does not merge conflicting changes.
Version mismatch
Use MySQL’s supported replication-compatibility paths and test upgrades before changing either VPS. Statement names, startup options, and supported features differ by release, especially around the 8.0.23 and 8.0.26 terminology changes.
Storage exhaustion
When binary or relay logs fill a VPS, replication and application writes can stop. Monitor free space, set retention based on recovery objectives, and remove logs only through the supported expiration process after confirming that the peer no longer needs them.
When circular replication is the wrong architecture
| Architecture | Write concurrency | Consistency behavior | Conflict and recovery burden | Operational profile |
|---|---|---|---|---|
| Circular asynchronous replication with one writer | One active writer | Peer applies changes asynchronously | Low conflict risk; failover and rejoin still require a runbook | Simplest two-VPS arrangement |
| Circular asynchronous replication with two writers | Both nodes can write | Independent commits can diverge during delays or partitions | High; application-level ownership and reconciliation are required | Use only after failure testing |
| Semisynchronous replication | Usually one active writer | Commit waits for at least one replica acknowledgement of receipt and logging | Lower acknowledgement loss window, but conflicts and failover planning remain | Additional configuration and timeout behavior |
| Synchronous clustering | Depends on the cluster product and topology | Synchronous behavior rather than ordinary asynchronous source-to-replica flow | Different membership, quorum, and recovery procedures | Consider when synchronous or multi-primary requirements outweigh two-node simplicity |
The two-VPS circular design is appropriate when you need a second reachable MySQL server, can operate with asynchronous replication, and can enforce a single-writer policy. Requirements for synchronous commits, automatic conflict handling, or unrestricted multi-primary writes call for evaluating a clustering architecture instead.
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
