MySQL master-slave synchronization principle
Transfer from Blog Park-aiapple:http://www.cnblogs.com/aiapple/p/5792939.html
master-Slave formMySQL master-slave replication flexibility
- One Master one from
- Primary master replication
- One master multiple reads from the---extended system, because reads are read from the library;
- Multi-master One support from---5.7
- Replication---at the same level
use and ConditionsMySQL master-slave replication uses
- Real-time disaster preparedness for failover
- Read and write separation, provide query service
- Backup to avoid impacting your business
Prerequisites for Master-Slave deployment:
- Main Library Open Binlog log (set log-bin parameter)
- Master-Slave Server-id different
- Connect to the main library from the library server
Master PrincipleMySQL master-slave replication principle
1) When Binlog is turned on, the main library will generate a log dump thread to enable slave from the library I/O line path binlog;2), generate two threads from the library, an I/O thread, a SQL thread, 3) I/O thread will request the binlog of the main library, and writes the resulting binlog log to the relay log (trunk log) file; 4) The SQL thread will read the log in the relay log file and parse it into concrete operation to achieve the same master-slave operation, and the final data is consistent;
Problems and SolutionsProblems with MySQL master-slave replication:
- Data may be lost after a main library outage
- There is only one SQL Thread from the library, the main library write pressure is high, replication is likely to delay
Workaround:
- Semi-synchronous Replication---resolve data loss issues
- Parallel replication----Solve problems with replication delays from libraries
semi-synchronous replicationMySQL Semi-sync (semi-synchronous replication) semi-synchronous replication:
- 5.5 integrated into MySQL, in the form of plugins, need to be installed separately
- Ensure that Binlog is transferred to at least one slave library after the transaction commits
- There is no guarantee that the Binlog of this transaction is done from the library
- Performance is reduced and response time is longer
- Network exceptions or downtime from the library, card Master Main Library, until timeout or recovery from library
Master-Slave replication-the principle of asynchronous replication
The principle of semi-synchronous replication:
Parallel ReplicationMySQL parallel replication
- New in Community edition 5.6
- Parallel refers to the multi-threaded from library apply Binlog
- Library level parallel application Binlog, same library data change or serial (version 5.7 parallel replication based on transaction group)
Set up
Set global slave_parallel_workers=5;
Set the number of SQL threads to 5
otherPartial data replication Main Library add parameters:
binlog_do_db=db1binlog_ignore_db=db1binlog_ignore_db=db2
Or add parameters from the library
replicate_do_db=db1replicate_ignore_db=db1replicate_do_table=db1.t1replicate_wild_do_table=db%.% replicate_wild_ignore_table=db1.%
Add parameters to the Cascade Replication (Common) A->B->CB:
LOG_SLAVE_UPDATESB will record A's binlog in his binlog log.
Monitoring of replication:
Show slave Status \g
Replication error Handling common: 1062 (primary key conflict), 1032 (record not present) resolved:
- Manual processing
- Skip replication error: Set global sql_slave_skip_counter=1
Summary
- Master-Slave form
-
- One Master one from
- One master multiple slave-extended system read performance
- Multi-master starts from--5.7 support
- Primary master replication
- Replication at the Cascade level
- Use: Real-time disaster recovery failover, read/write separation, backup
- Principle
-
- Main: Log dump line path Binlog;
- From
-
- I/O thread accepts read Binlog and writes to relay log file
- SQL thread reads Binlog from the relay log file and persists
- Problems and Solutions
-
- Data loss after main library outage
-
- Semi-synchronous replication
- Main Library write pressure is high, because only one SQL thread is persisted from the library, replication may be delayed
-
- Semi-synchronous replication:
-
- Principle
-
- A transaction needs to return an accepted one from the library after the main library has finished writing Binlog, before it is put back to the client;
- 5.5 integrated into MySQL, in the form of plugins, need to be installed separately
- Ensure that Binlog is transferred to at least one slave library after the transaction commits
- There is no guarantee that the Binlog of this transaction is done from the library application
- A certain reduction in performance
- Network exception or outage from library, CARTICU, until timeout or recovery from library
- Parallel replication
-
- Principle: Multi-threaded from library apply Binlog
- Added in Community 5.6
- Library level parallel application Binlog, same library data change or serial
- 5.7 Version parallel replication based on transaction group
- Partial data replication
- cascading replication (Common)
-
- A->b->c
- b Add the parameter log_slave_updates
- B will record A's binlog into its own binlog log.
- Monitoring of replication
-
- Copy error handling
-
- Common: 1062 (primary key conflict), 1032 (record does not exist)
- Solve:
-
- Manual processing
- Skip replication error: Set global sql_slave_skip_counter=1
- MySQL master-slave replication is the basis of MySQL high availability, high performance (load balancing)
- Simple, flexible, and versatile deployment options to deploy different replication architectures based on different business scenarios
- Replication status should be monitored at all times during replication, and replication errors or delays can affect the system
- MySQL master-slave replication currently has some problems, you can deploy replication enhancements as needed
MySQL master-Slave synchronization principle