Network Security Internet Technology Development Database Servers Mobile Phone Android Software Apple Software Computer Software News IT Information

In addition to Weibo, there is also WeChat

Please pay attention

WeChat public account

Shulou

Problems and Solutions of mysql Master-Slave replication

2025-01-19 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Share

Shulou(Shulou.com)06/01 Report--

Master-slave form

Mysql master-slave replication is flexible

One master and one slave

Master master replication

One master, multiple slaves-expands the performance of system reads because reads are read from the library

Multi-master, one-slave-- 5.7 support

Cascade replication-

Uses and conditions

Mysql master-slave replication purpose

Real-time disaster recovery for failover

Separation of reading and writing, providing query service

Backup to avoid affecting the busin

Necessary conditions for master-slave deployment:

Binlog log is enabled in the main library (set log-bin parameter)

Master-slave server-id is different.

The slave server can connect to the master library.

Master-slave principle

Principle of mysql master-slave replication

Two threads are generated from the library, one is an Icano thread and the other is a SQL thread

The iCompo thread requests the binlog of the main library and writes the resulting binlog log to the relay log (Relay Log) file.

The master library generates a log dump thread that is used to pass binlog to the slave library iUnix thread.

The SQL thread reads the log in the relay log file and parses it into specific operations to achieve the consistency of the master-slave operation and the final data consistency.

Problems and solutions

Problems with mysql master-slave replication:

Data may be lost after the main library goes down

There is only one sql Thread in the slave library, so the master database is under great pressure to write, and the replication is likely to be delayed.

Solution:

Semi-synchronous replication-solving the problem of data loss

Parallel replication-to solve the problem of replication delay from the library

Semi-synchronous replication

Mysql semi-sync (semi-synchronous replication)

Semi-synchronous replication:

5.5 Integration into mysql, which exists as a plug-in and requires a separate installation

Ensure that the binlog is transferred to at least one slave library after the transaction is committed

There is no guarantee that the slave library will run out of binlog for this transaction.

There is a certain decrease in performance, and the response time will be longer.

Network exception or slave library downtime, card master library, until timeout or slave library recovery

Master-slave replication-- the principle of asynchronous replication

Principle of semi-synchronous replication:

Parallel replication

Mysql parallel replication

Added in Community version 5.6

Parallelism refers to multithreaded apply binlog from the library.

Binlog is applied in parallel at the library level, and the data changes in the same library are serial (version 5.7 parallel replication is based on transaction groups)

Set up

Set global slave_parallel_workers=10

Set the number of SQL threads to 10

Other

Partial data replication

Add parameters to the main library:

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

Cascaded replication (commonly used)

A-> B-> C

Add parameters to B:

Log_slave_updatesB will record A's binlog in its binlog log.

Monitoring of replication:

Show slave status\ G

Handling of replication errors

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

Resolve:

Manual processing

Skip replication error: set global sql_slave_skip_counter=1

Summary

Master-slave form

One master and one slave

One master and multiple slaves-- extend the read performance of the system

Multi-master and one-from-5.7 support

Master master replication

Cascade replication

Purpose: real-time disaster recovery failover, read-write separation, backup

Principle

The iCompo thread accepts reading the binlog and writes to the relay log file

The sql thread reads binlog from the relay log file and persists the

Master: log dump thread passes binlog

From

Problems and solutions

Parallel replication

Semi-synchronous replication

After the main library is down, the data is lost

The master library is under great pressure to write, and replication may be delayed because there is only one sql thread to persist in the slave library.

Semi-synchronous replication:

After the binlog is written in the main library, the transaction needs to return an accepted from the library before it is returned to the client.

Principle

5.5 Integration into mysql, which exists as a plug-in and requires a separate installation

Ensure that the binlog is transferred to at least one slave library after the transaction is committed

The binlog that completes this transaction from the library application is not guaranteed.

The performance is reduced to a certain extent.

Network exception or slave downtime, card master library, until timeout or recovery from the slave library

Parallel replication

Principle: multithreaded apply binlog from the library

Added in Community 5.6

Binlog is applied in parallel at the library level, and the data changes of the same library are serial.

Version 5.7 parallel replication is based on transaction groups

Partial data replication

Cascaded replication (commonly used)

A-> B-> C

Add parameter log_slave_updates to B

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

Monitoring of replication

Show slave status

Handling of replication errors

Manual processing

Skip replication error: set global sql_slave_skip_counter=1

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

Resolve:

Mysql master-slave replication is the foundation of mysql high availability and high performance (load balancing).

It is simple, flexible and can be deployed in various ways. Different replication structures can be deployed according to different business scenarios.

The replication status should be monitored at all times during the replication process. Replication errors or delays may affect the system.

There are also some problems with mysql master-slave replication. Replication enhancements can be deployed as needed.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.

Views: 212

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.

Share To

Database

Wechat

© 2024 shulou.com SLNews company. All rights reserved.

12
Report