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

I believe that MySQL master-slave has always been an inseparable topic as a DBA, and we can see it in many highly available architectures. There are more or less questions in our minds, such as:

Why is the master-slave delay high? Why does the master-slave delay never move? Why does the master-slave delay jump instantaneously? Does a delay of 0 necessarily mean there is no delay? Why does the abnormal restart from the library report an error? Can a slave library use indexes just like a master library? How does MTS improve the efficiency of slave library applications? Why are there so many sync parameters associated with slave libraries, and how do I configure them? What is the mysql.gtid_executed table for?

……

This series is intended to unravel these questions by describing the master-slave principle. I think that if you want to learn the master-slave principle in depth, you need to learn it in a certain order. If you don't know GTID, Event and how the main library generates Event, you definitely can't understand the master-slave principle in depth. Therefore, this series explains it in this order. The series is divided into five parts as follows:

Part I GTID-related Section 1: Basic concepts of GTID

Section 2: mysql.gtid_executed table/gtid_executed variable/gtid_purged variable change timing

Section 3: Introduction to GTID module initialization and parameters binlog_gtid_simple_recovery

Section 4: Operations in GTID Section 2 Event-related Section 5: General Format of Binary Log Events

Section 6: Highlights Event FORMAT_DESCRIPTION_EVENT/PREVIOUS_GTIDS_LOG_EVENT

Section 7: Key Event GTID_LOG_EVENT

Section 8: Focus Event QUERY_EVENT/MAP_EVENT

Section 9: Focus Event WRITE_ROWS_EVENT/Delete_ROWS_EVENT

Section 10: Focus Event UPDATE_ROWS_EVENT/XID_EVENT

Section 11: Impact of parameter binlog_row_image

Section 12: How to use Event to discover problems Section 13: Binlog cache

Section 14: Transaction Event Generation and Write Flow

Section 15: MySQL Layer Transaction Submission Process Brief

Section 16: WriteSet-based Parallel Replication

Section 17: DUMP Threads of the Main Library

Section 18: DUMP Thread Finding and Filtering Basic Algorithms for GTIDs Section 4: MTS Multithreaded Parallel Playback from Libraries

Section 20: Multithreaded Parallel Playback from Library MTS (II)

Section 21: GAP test and parameter slave_preserve_commit_order in MTS

Section 22: IO threads from libraries

Section 23: SQL threads from libraries (MTS coordination threads) and parameters sql_slave_skip_counter

Section 24: Searching from Library Data and Parameters slave_rows_search_algorithms

Section 25: Shutdown and Recovery Process from Libraries

Section 26: Reasonable Slave Settings

Section 27: Calculation of Seconds_Behind_Master from Library

Section 28: Seconds_Behind_Master Latency Summary Part 5 Others Section 29: Introduction to Threads and MySQL Debugging Environment Setup

Section 30: Delete vs. Insert Deadlock Case Study

Section 31: Three Pages and Linked Lists of Innodb Buffer Pool

Section 32: Using performance_schema to get statements that cause deadlocks

Of which:

The first 29 verses were written by me alone. Sections 30 and 31 were written by my friends Yang Qilong and Wang Hangwei respectively. They are both excellent DBAs. In ordinary communication, I feel that they cover a wide range that I can't reach. Section 32 was written by my colleague Tian Xingchun. In his usual work and study, he always found some cases that surprised me, and then studied together. Scope of discussion and engagement

This series uses Percona version 5.7.22, so the scope of discussion is definitely version 5.7.

Although this series contains most of the knowledge points in master-slave, there are still some contents that are not covered, as follows:

No coverage semi-synchronous. Consider only the case where master_info_repository and relay_log_info_repository are set to 'table'. Consider only cases where binlog_format is set to 'row'.

This series has the following conventions:

Row format: binlog_format is set to 'row'. Statement format: binlog_format is set to 'statement'. binary log: This represents what we often call a binary log physical file. order commit: stands for the 'MYSQL_BIN_LOG::ordered_commit' function, which is simplified because it is used frequently in this series. The process will be detailed in Section 15. GTID AUTO_POSITION MODE: indicates that GTID is used by master and master_auto_position=1. POSITION MODE: This means that the master-slave mode is the traditional position mode. Single SQL thread: used to distinguish from MTS, representing only one SQL thread for Event application. MTS: Short for 'multi-threaded slaves', consists of a coordinator thread and multiple worker threads, with events applied by the worker threads. presentation

At the beginning of writing some friends suggested that I do not write source code and some friends suggested that I post more code, so I did a comprehensive, the whole series I will give some source code interface and the necessary source code proof, but will not post a lot of source code and stack frames. Some chapters will contain a note to my Jane book, this note is when I study the source code call sequence and some stack frames for their own review and for friends who want to understand the source code to view, but readability is not so good. If friends who are not interested in source code can skip these source code interfaces and code, I don't think this will affect the continuity of reading.

Since 2016, eight monsters (original name Gao Peng) often ask me some source code questions, which shows that eight monsters are a very persistent person to the source code. Looking at the entire catalogue covering all aspects of master-slave, I hope his series will give you a deeper understanding of the master-slave principle.

---Zhai Weixiang (net name Yinfeng) Senior MySQL kernel expert of Alibaba Cloud Intelligent Database Product Division

He got to know Eight Eccentrics (his real name was Gao Peng) because one of his colleagues took part in the MySQL course of Zhishu Hall. After learning, he had some interaction with Eight Eccentrics, discussed some opinions, and then got to know them logically. Eight strange was originally ORACLE DBA, but after he came into contact with MySQL, he began to explore MySQL in depth. This attitude and method of learning impressed me, and in turn promoted my deeper understanding of MySQL.

In order to better understand the InnoDB engine, Baogui has also developed several tools such as bctool, bcview and innblock (some of which I don't know). Among them, I also helped to make some suggestions as a "product manager" for the development of the innblock tool. After the release of this tool, I also helped push a wave of: innblock on my public account.| InnoDB page observation tool.

Ever since I heard that the Eight Eccentrics were going to write a column, I have been following the progress and occasionally giving advice. The main content of this column is MySQL master-slave replication related content, from the source level in-depth analysis of MySQL master-slave replication aspects, a little regret is that this book does not involve semi-synchronous replication, group replication of these two hot content, look forward to eight strange in the future can have energy to increase these two pieces of knowledge.

---Ye Jinrong MySQL ACE, co-founder of Zhishutang

Gao Peng was a person who was very focused and had a strong hands-on ability. One of the things I was particularly impressed with was that less than two weeks after talking to me about MGR high availability node selection, he told me that he implemented a Python script for MGR vip drift. He realized Page analysis of InnoDB, MySQL InnoDB locking analysis, and then began to work hard to write this series. Through the catalogue of this series, you can see that Gao Peng basically made a thorough analysis of replication related principles, which is a rare learning material for friends who want to deeply understand MySQL replication and Binlog related content.

---Wu Bingxi, co-founder of Zhishutang

As an old man who used to hang out on itpub, he had read quite a few of Gao Peng's Oracle articles. Accidentally found that it moved to MySQL field, and wrote a large number of principle analysis articles, more impressive is a few articles on the source code level analysis of MDL lock, after reading it, I feel suddenly enlightened. Open source databases are getting more and more popular, especially MySQL performance is the most eye-catching, if you want to learn MySQL master-slave principle in depth, this series is really rare information, strongly recommended!

----Li Zhenxu General Manager of Oracle ACE Cloud and Enmo Service Product Group

Knowing Gao Peng originated from ITPUB blog experts recommendation, first met Oracle technology, then MySQL technology rise, we turned to MySQL, Gao Peng brother focused on source code analysis, analysis of various difficult miscellaneous diseases into three points, enthusiastic to help many netizens solve various problems. Now he will be part of the MySQL master-slave copy of the interpretation of the summary into a series of articles to share with you. Read carefully and you will benefit greatly.

---Yang Qilong Hangzhou Youzan Technology DBA, public number yangyidba author

Buy can contact me wx:gaopp_22389860

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.