MySQL Small white series _10 MySQL master-slave copy principle

Source: Internet
Author: User
Tags log log

1. How to solve the problem of master-slave replication delay? (1) Principle of master-slave copying

Http://www.cnblogs.com/jenvid/p/8410922.html

    • 1.salve execute slave start,salve service IO thread will connect to master via authorized user and request master to send Bin-log log content after the specified file and location
    • After the 2.master server receives an IO thread request from the slave server, the IO thread on the master server sends the contents of the specified Bin-log log according to the slave server and returns the IO thread to slave. The returned information is out of the Bin-log log content wait, and this time the return log content on the master server side of the new Binlog file name is already in Binlog the next specified update bit
    • After the 3.slave IO thread receives the information, the received log content is added to the end of the Relay-log file on the salve side, and the file name and location of the Bin-log read to the master side are recorded in the Master-info file. So that the next time you read it, you can clearly tell master "I need to start from somewhere in the Bin-log log content, please send Me",
    • The 4.slave SQL thread detects that new content in Relay-log will immediately parse the contents of Relay-log into the executable content of the real execution of the master side and execute it on its own.
(2) Causes of delay
    • 1. Master-slave replication is a single-threaded operation, asynchronous
    • The Binlog of 2.master is sequential read-write, high efficiency, high throughput, DDL/DML is concurrent operation
    • When the 3.slave Relay-log is implemented, DML and DLL are randomly operated and the slave_sql_running is single-threaded, and if there is lock contention, the cost is higher.
    • 4.master TPS When concurrency peaks, the number of DDL generated exceeds the range that slave a SQL thread can withstand, and delays produce
    • 5. max_allowed_packet Master-Slave inconsistency
    • 6. Self-increment ID starting position is not the same
    • 7. The main height is inconsistent from the low version, the new feature is incompatible
(3) Resolve latency issues
    • 1. The main library DDL performs DDL operations quickly
    • 2. Main Library Settings 双1 ,sync_binlog=1 innodb_flush_log_at_trx_commit=1
    • 3. From the library can be set to双0
    • 4. slave_net_timeout slave How long it will take to reestablish the connection and get the data after it fails to read the log data
    • 5. master-connect-retry when a master-slave connection is re-established, the retry wait time if the connection fails
    • 6. Use better hardware from the library
    • 7. Dedicated slave server, dedicated read server, dedicated backup server
    • 8. Using the Semi_sync plugin for semi-synchronization, loss of performance
2. How to determine whether master-slave replication is synchronized? (1) Show slave status\g
             Slave_IO_Running: Yes            Slave_SQL_Running: Yes        Seconds_Behind_Master: 0

Seconds_behind_master is the difference between the timestamp of the event performed by the comparison Sql_thread and the timestamp (abbreviated TS) of the Io_thread copy good event.

(2) if Relay_master_Log_File== Master_Log_File
    1. Read_Master_Log_Pos= = Exec_Master_Log_Pos , it is considered to be synchronous

    2. The main library is show master status\G Position equal to the slave library and Read_Master_Log_Pos is also synchronized

(3) Use of third-party tools

Mk-heartbeat,maatkit

3. The following parameter is set to 1 how to understand?

Set global sql_slave_skip_counter=1;

Https://dev.mysql.com/doc/refman/5.6/en/set-global-sql-slave-skip-counter.html

    • (1) From the library need to skip an unenforceable command, set in the state of slave stop global sql_slave_skip_counter=N , skip the next n events

An event group contains a number of ordered events
An insert consists of three Event,begin/insert/commit
For transactional tables, an event group represents a transaction
For non-transactional tables, an event group represents a separate SQL statement

    • (2) N=1, a number of event jumps are skipped until the current transaction ends
    • (3) N>1, you have to n--every event you skip.

http://dinglin.iteye.com/blog/1236330

MySQL Small white series _10 MySQL master-slave copy 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.