As a "courier" in the database field, MySQL replication technology assumes the important task of data transportation. Today we will talk about the "express services" in this data world - what are the differences between synchronous replication, asynchronous replication, and semi-synchronous replication? Why does the financial system have to use semi-synchronization? How should I choose during e-commerce promotions? Let us uncover the mystery of these replication technologies in the most down-to-earth way.
The competition of express service: analysis of the essence of three replication modes
1. Asynchronous Replication - Buddhist courier
#Classic configuration exampleCHANGE MASTER TO MASTER_HOST='', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_AUTO_POSITION=1;
How it works：
After writing the local Binlog, the main library returns immediately and pulls data asynchronously from the library, just like the courier does not guarantee when it will be delivered after receiving the receipt. This is the default copying method for MySQL.
Typical scenarios：
- Cross-computer room disaster recovery (Beijing main library -> Shanghai sub-reservoir)
- Data analysis from the library (allows short delays)
- Non-core business query with separate reading and writing
2. Synchronous Replication - Obsessive-compulsive couriers
How it works：
The master database must wait for all slave databases to confirm receipt of data before returning to the client successfully, which means that the express delivery must be signed in person before it is considered complete.
Hardcore cost：
- Actual tests of a certain bank system: Synchronous copying results in an increase of 30ms in write operation delay
- Any slave library failure will make the entire cluster unwritable
- Network jitter directly causes service interruption
Applicable scenarios：
- Financial core trading system (fund transfer, securities trading)
- Government confidential data storage
- A distributed system that requires strong consistency
3. Semisynchronous Replication (Semisynchronous Replication) - Eclectic Express Station
# Semi-synchronous configuration steps (MySQL 8.0+)INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; SET GLOBAL rpl_semi_sync_master_enabled=1; SET GLOBAL rpl_semi_sync_master_timeout=1000; # time out1Degraded in seconds
Operation mechanism：
When the main library submits a transaction, at least one slave library confirms that the Binlog event is received and returns successfully, which is equivalent to the express delivery outlet confirming that the item has been collected (but not delivered).
Practical data：
- Master-slave delay is controlled within 100ms
- Data loss window during failover <1 second
- Write performance loss is about 15% (compared with asynchronous copying)
Classic Applications：
- E-commerce order system
- Gamer data storage
- Report status of IoT devices
Parameter tuning practice: secrets to improve replication efficiency by 300%
1. Asynchronous replication acceleration solution
# Optimize parallel replication (MySQL 8.0 is enabled by default)slave_parallel_workers=8 slave_preserve_commit_order=1 # Adjust log refresh strategysync_binlog=1000 innodb_flush_log_at_trx_commit=2
Effect comparison：
After the optimization of a live broadcast platform, the delay from library was reduced from 5 minutes to within 10 seconds, and the data throughput increased by 3 times.
2. Semi-synchronous copying of life-saving configurations
# Prevent service interruption caused by network jitterrpl_semi_sync_master_timeout=500 # Timeout 500ms automatically downgrade to asynchronous # Enable lossless semi-synchronization (New feature of MySQL 8.0)rpl_semi_sync_master_wait_point=AFTER_SYNC
Replication selection policy in high availability architecture
1. Golden combination plan
Scene
Recommended plan
Supporting tools
Cross-city disaster preparedness
Asynchronous replication + delayed replication
Percona XtraBackup
Two lives in the same city
Semi-synchronous replication + MHA
MySQL Router
Financial core system
Synchronous replication + InnoDB Cluster
Consul service discovery
Ultimate question: How to choose?
- Pursuing performance selection asynchronous: Suitable for log collection, big data analysis and other scenarios
- Advantages: High throughput (up to 100,000 TPS)
- Risk: Minutes of data may be lost in the event of a failure
- To be safe and to choose half-synchronous in performance: The first choice for Internet services such as e-commerce and social networking
- Balance point: RPO (recovery point target) <1 second, RTO (recovery time) <30 seconds
- Cost: Need at least two slave libraries
- Strong consistency requirements are selected for synchronization: Key areas such as finance and government affairs
- Cost: Dedicated network is required (delay <2ms)
- Deployment: At least 3 node clusters
Conclusion
Choosing a copy strategy is like choosing a courier service:
- Normal parcelUse asynchronous (cheap but possibly delayed)
- Important documentsUse semi-synchronous (extended price guarantee)
- Confidential FilesMust be synchronized (armed escort)
It is recommended that all production systems be configured with at least a dual-insurance architecture of semi-synchronous replication + asynchronous slave libraries. Remember, there is no best way to replicate, only the most suitable choice for business scenarios. Next time you design a system, you might as well ask yourself: How much data loss can this business scenario bear?
Here is the article about the three modes of MySQL replication: synchronous, asynchronous, and semi-synchronous replication? That’s all for the article. For more information about MySQL synchronization, asynchronous, and semi-synchronous replication, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!