MySQL Master-slave replication

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

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

Generate two threads from the library, an I/O thread, a 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 from the library I/O line Binlog 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 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

The principle of semi-synchronous replication:

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

Add parameters to the A->B->CB replication (Common):

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

Tags: mysql advanced

MySQL Master-slave replication