I take you to understand how MySQL master-slave replication works

MySQL master-slave replication principle

Master-Slave form

MySQL 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

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/820365/201608/820365-20160821160605026-595389100. PNG "style=" border:0px; "/>

Use and Conditions

MySQL 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 principle

MySQL master-slave replication principle

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/820365/201608/820365-20160821160615776-1749314661. PNG "style=" border:0px; "/>

Generate two threads from the library, one I/O thread, and one SQL thread;

The I/O thread requests the binlog of the main library and writes the resulting binlog log to the relay log (trunk log) file;

The main library generates a log dump thread to path Binlog from the library I/O line;

SQL thread, will read the log in the relay log file, and parse into concrete operation, to achieve the master-slave operation consistent, and the final data consistent;

Problems and Solutions

Problems 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 replication

MySQL 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

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/820365/201608/820365-20160821160632292-210490874. PNG "style=" border:0px; "/>

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

The principle of semi-synchronous replication:

650) this.width=650; "src="/e/u261/themes/default/images/spacer.gif "style=" Background:url ("/e/u261/lang/zh-cn/ Images/localimage.png ") no-repeat center;border:1px solid #ddd;" alt= "Spacer.gif"/>

650) this.width=650; "Src=" http://images2015.cnblogs.com/blog/820365/201608/820365-20160821160639917-571481540. PNG "style=" border:0px; "/>

Parallel replication

MySQL 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=10;

Set the number of SQL threads to 10

Other

Partial 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.%

cascading replication (Common)

A->b->c

Add parameters in B:

LOG_SLAVE_UPDATESB will record A's binlog in his binlog log.

Monitoring of replication:

Show slave status \g

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

Summarize

• 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

• 
  

  • I/O thread accepts read Binlog and writes to relay log file

  • SQL thread reads Binlog from the relay log file and persists

  • Main: Log dump line path Binlog;

  • From

  • 
    

• Problems and Solutions

• 
  

  • Parallel replication

  • Semi-synchronous replication

  • Data loss after main library outage

  • 
    

  • Main Library write pressure is high, because only one SQL thread is persisted from the library, replication may be delayed

  • 
    

• Semi-synchronous replication:

• 
  

  • 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;

  • Principle

  • 
    

  • 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

• 
  

  • Manual processing

  • Skip replication error: Set global sql_slave_skip_counter=1

  • Common: 1062 (primary key conflict), 1032 (record does not exist)

  • Solve:

  • 
    

• 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

This article from "Li Shilong" blog, declined reprint!

I take you to understand how MySQL master-slave replication works