Mysql master-slave replication principle, mysql master-slave Principle

Mysql master-slave replication principle, mysql master-slave Principle
Master-slave mode

Flexible mysql master-slave Replication

• One master, one slave
• Master-master Replication
• One master multi-slave --- extended system read performance, because the read is read from the slave database;
• Support for multiple masters starting from --- 5.7
• Cascade replication ---

Purpose and conditions Usage of mysql master-slave Replication

• Real-time disaster recovery for failover
• Read/write splitting to provide query services
• Backup to avoid service impact

Prerequisites for master-slave deployment:

• Enable binlog for the master database (set the log-bin parameter)
• Different Master/Slave server-IDs
• Slave database server can connect to master database

  Master-slave Principle Mysql master-slave replication Principle

 

Two threads are generated from the database, one I/O thread and one SQL thread. the I/o thread requests the binlog of the master database and writes the obtained binlog to the relay log (relay log) file; the master database generates a log dump thread to pass binlog to the slave database I/o thread; the SQL thread reads the logs in the relay log file and parses the logs into specific operations, to achieve master-slave operation consistency, and ultimately data consistency; Problems and Solutions Problems with mysql master-slave replication:

• Data may be lost when the master database goes down.
• There is only one SQL Thread in the slave database. The master database has a high write pressure and the replication may be delayed.

Solution:

• Semi-synchronous replication-solves the problem of data loss
• Parallel replication-solves the problem of slave Database Replication latency

  Semi-synchronous Replication Mysql semi-sync (semi-synchronous replication) semi-synchronous replication:

• 5.5 integration into mysql, which exists as a plug-in and needs to be installed separately
• Make sure that the binlog is transferred to at least one slave database after the transaction is committed.
• The binlog of this transaction is not guaranteed to be applied from the slave database.
• Performance has been reduced to a certain extent, and the response time will be longer
• Network exception or slave database downtime,The primary database of the card is used until it times out or is recovered from the database.

Master-slave replication-asynchronous replication Principle

 

Principles of semi-synchronous replication:

 

Parallel Replication Mysql parallel replication

• Added in Community version 5.6
• Parallel Processing refers to the multi-thread apply binlog of the slave database.
• Binlog is applied concurrently at the database level, and data changes in the same database are still serialized (version 5.7 of parallel replication is based on transaction groups)

Set

set global slave_parallel_workers=10;

Set the number of SQL threads to 10. Others Add parameters to the master database for some data replication:

binlog_do_db=db1binlog_ignore_db=db1binlog_ignore_db=db2

Or add parameters from the database

replicate_do_db=db1replicate_ignore_db=db1replicate_do_table=db1.t1replicate_wild_do_table=db%.%replicate_wild_ignore_table=db1.%

Join-level replication (commonly used) A-> B-> CB to add parameters:

Log_slave_updatesB records the binlog of a to its own binlog.

 

Replication monitoring:

show  slave status \G

 

Common replication error handling: 1062 (primary key conflict), 1032 (record does not exist:

• Manual processing
• Replication skipping error: set global SQL _slave_skip_counter = 1

Summary  

• Master-slave mode
• • One master, one slave
  • One master, multiple slaves-extended system read performance
  • Multi-master and One-slave support from -- 5.7
  • Master-master Replication
  • Cascade Replication
• Purpose: failover, read/write splitting, and backup for real-time Disaster Recovery
• Principle
• • MASTER: the log dump thread transmits the binlog;
  • Slave
  • • The I/o thread reads binlog and writes the relay log file.
    • The SQL thread reads the binlog from the relay log file and persists it.
• Problems and Solutions
• • Data loss after the master database goes down
  • • Semi-synchronous Replication
  • There is a high write pressure on the master database. Because the slave database has only one SQL thread for persistence, replication may be delayed.
  • • Parallel Replication
• Semi-synchronous replication:
• • Principle
  • • After the binlog is written to the master database, the transaction must return an accepted record from the slave database before returning it to the client;
  • 5.5 integration into mysql, which exists as a plug-in and needs to be installed separately
  • Make sure that the binlog is transferred to at least one slave database after the transaction is committed.
  • The binlog of the transaction is not guaranteed to be completed by the slave database application.
  • Performance has been reduced.
  • The network is abnormal or the slave database is down, and the master database is stuck until it times out or recovers from the database.
• Parallel Replication
• • Principle: multi-thread apply binlog from the slave Database
  • Added in Community 5.6
  • Binlog is applied concurrently at the database level. Data changes in the same database are still serialized.
  • Version 5.7 parallel replication is based on transaction groups
• Partial data replication
• Cascade replication (commonly used)
• • A-> B-> C
  • Add the log_slave_updates parameter to B.
  • B records the binlog OF A to its binlog.
• Replication monitoring
• • Show slave status
• Copy error handling
• • Common: 1062 (primary key conflict), 1032 (record does not exist)
  • Solution:
  • • Manual processing
    • Replication skipping error: set global SQL _slave_skip_counter = 1
• Mysql master-slave replication is the foundation of mysql high availability and high performance (load balancing)
• Simple, flexible, and diverse deployment methods. You can deploy different replication structures based on different business scenarios.
• The replication status should be monitored at all times during the replication process. The replication error or delay may affect the system.
• Mysql master-slave replication also has some problems. You can deploy the replication enhancement function as needed.