MySQL master-Slave synchronization principle

Source: Internet
Author: User
Tags error handling failover

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
      • Parallel replication
  • 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
    • Show slave status
  • 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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.