Mysql学习总结_20190327_Mysql复制原理和流程

Mysql复制原理和流程：

MySQL replication capabilities are implemented using three threads, one on the master server and two on the slave:

相关线程：
    Master：logdump thread(对应Slave的I/O thread)
    Slave：I/O thread、SQL thread
 

Binlog dump thread: 
The master creates a thread to send the binary log contents to a slave when the slave connects. This thread can be identified in the output of SHOW PROCESSLIST on the master as the Binlog Dump thread.
The binary log dump thread acquires a lock on the master's binary log for reading each event that is to be sent to the slave. As soon as the event has been read, the lock is released, even before the event is sent to the slave.

Slave I/O thread:
When a START SLAVE statement is issued on a slave server, the slave creates an I/O thread, which connects to the master and asks it to send the updates recorded in its binary logs.
The slave I/O thread reads the updates that the master's Binlog Dump thread sends (see previous item) and copies them to local files that comprise the slave's relay log.
The state of this thread is shown as Slave_IO_running in the output of SHOW SLAVE STATUS or as Slave_running in the output of SHOW STATUS.

Slave SQL thread:
The slave creates an SQL thread to read the relay log that is written by the slave I/O thread and execute the events contained therein.

Replication enables data from one MySQL database server (the master) to be copied to one or more MySQL database servers (the slaves). Replication is asynchronous by default; slaves do not need to be connected permanently to receive updates from the master. Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Each slave that connects to the master requests a copy of the binary log. That is, it pulls the data from the master, rather than the master pushing the data to the slave

Slave_IO_State: Waiting for master to send event(所以到底是push 还是pull、Master send event and Slave pull the data）

初始化同步(Slave发起)：
1)Slave上执行sart slave开启主从复制
2)Slave上的I/O thread通过已授权的复制用户权限连接到Master，并发起请求读取在之前指定的binlog+position(通过change master to ..配置)之后的binlog日志信息
3)Master在接收到Slave的I/O thread的请求后，logdump thread会根据Slave的I/O thread请求的信息分批读取指定的binlog+position之后的binlog日志信息，然后返回给Slave的I/O thread。返回的信息中除了binlog日志内容外，还有master的新的binlog文件名，以及在新的binlog中的下一个指定更新位置。
4)Slave在接收到Master的logdump thread发送的日志内容、日志文件及位置点后，会将master的binlog日志内容依次写到自身的Relay Log(中继日志)文件的最末端，并将新的binlog文件名和位置记录到master-info文件中，以便下一次读取master的binlog日志时能告诉Master服务器从新binlog日志的指定文件及位置开始读取新的binlog日志内容
5)Slave的SQL thread会实时检测Relay Log 中I/O thread新增的日志内容，然后及时把Relay LOG 文件中的内容解析成sql语句，并按顺序应用这些sql语句，并在relay-log.info中记录当前应用Relay Log的文件名和位置点

后续持续同步(Slave主动)：
3)I/O thread发起请求，Master的logdump thread读取binlog中的操作时，此线程会对Master的binlog加锁，读取完成后锁会被释放。logdump thread再把日志推给slave

In the Time column in the output of SHOW PROCESSLIST, the number of seconds displayed for the slave SQL thread is the number of seconds between the timestamp of the last replicated event and the real time of the slave machine.

Master：
binlog_format：指定binlog的格式(STATEMENT, ROW, MIXED)

net_write_timeout(default:60):
The number of seconds to wait for a block to be written to a connection before aborting the write
The number of seconds to wait for more data from a connection before aborting the read. 
When the server is reading from the client, net_read_timeout is the timeout value controlling when to abort. 
When the server is writing to the client, net_write_timeout is the timeout value controlling when to abort. 

net_retry_count(default:10):
If a read or write on a communication port is interrupted, retry this many times before giving up. 
This value should be set quite high on FreeBSD because internal interrupts are sent to all threads.
 

Slave：
Seconds_Behind_Master：
      The number of seconds that the slave SQL thread is behind processing the master binary log. A high number (or an increasing one) can indicate that the slave is unable to handle events from the master in a timely fashion.
      A value of 0 for Seconds_Behind_Master can usually be interpreted as meaning that the slave has caught up with the master, but there are some cases where this is not strictly true. For example, this can occur if the network connection between master and slave is broken but the slave I/O thread has not yet noticed this—that is, slave_net_timeout has not yet elapsed.
      It is also possible that transient values for Seconds_Behind_Master may not reflect the situation accurately. When the slave SQL thread has caught up on I/O, Seconds_Behind_Master displays 0; but when the slave I/O thread is still queuing up a new event, Seconds_Behind_Master may show a large value until the SQL thread finishes executing the new event. This is especially likely when the events have old timestamps; in such cases, if you execute SHOW SLAVE STATUS several times in a relatively short period, you may see this value change back and forth repeatedly between 0 and a relatively large value.
 

Mysql复制进程监控(Slave)：

SHOW SLAVE STATUS\Performance Schema Tables

The Performance Schema provides tables that expose replication information. This is similar to the information available from the SHOW SLAVE STATUS statement, but representation in table form is more accessible and has usability benefits:
1)SHOW SLAVE STATUS output is useful for visual inspection, but not so much for programmatic use. By contrast, using the Performance Schema tables, information about slave status can be searched using general SELECT queries, including complex WHERE conditions, joins, and so forth.
2)Query results can be saved in tables for further analysis, or assigned to variables and thus used in stored procedures.
3)The replication tables provide better diagnostic information. For multithreaded slave operation, SHOW SLAVE STATUS reports all coordinator and worker thread errors using the Last_SQL_Errno and Last_SQL_Error fields, so only the most recent of those errors is visible and information can be lost. The replication tables store errors on a per-thread basis without loss of information.
4)The last seen transaction is visible in the replication tables on a per-worker basis. This is information not avilable from SHOW SLAVE STATUS.
5)Developers familiar with the Performance Schema interface can extend the replication tables to provide additional information by adding rows to the tables.

https://www.cnblogs.com/huixuexidezhu/p/7059202.html
https://dev.mysql.com/doc/refman/5.7/en/replication-implementation.html