In addition to Weibo, there is also WeChat
Please pay attention
WeChat public account
Shulou
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.
Continue with the installation of the previous hadoop.First, install zookooper1. Decompress zookoope
"Every 5-10 years, there's a rare product, a really special, very unusual product that's the most un
© 2024 shulou.com SLNews company. All rights reserved.