Skip to main content

How does mysql replication works? io thread, sql thread and more


MySQL replication is a process that enables data from one MySQL database server (the master) to be copied automatically to one or more MySQL database servers (the slaves). It is usually used to spread read access on multiple servers for scalability, although it can also be used for other purposes such as for failover, or analyzing data on the slave in order not to overload the master.
  • At first, replication was based on propagation of SQL statements from the master server to the slave server. This replication format is called statement-based replication (SBR) and is default in older versions of MySQL (<= 5.1.4). Just as a note that in later versions of MySQL, especially with the Innodb Plugin, you NEED to run your transaction_isolation with REPEATABLE-READ.
  • The newer replication type is row-based replication (RBR), which logs changes in individual table rows to the binary log. Basically, logging the actual change and not the SQL statement itself.
  • MySQL also has the ability to change its binary logging format in real time depending on the type of event using the mixed format binary logging. When the mixed format is turned on, statement-based replication is on by default but will change to row-based replication is particular cases. For more information on Mixed Binary Logging please see Mixed Binary Logging Format in the MySQL Documentation.

1. IO thread
This process called IO thread connects to a master, reads binary log events from the master as they come in and just copies them over to a local log file called relay log. That’s all.
Even though there’s only one thread reading binary log from the master and one writing relay log on the slave, very rarely copying of replication events is a slower element of the replication. There could be a network delay, causing a steady delay of few hundred milliseconds, but that’s about it.
If you want to see where IO thread currently is, check the following in “show slave statusG”:
  • Master_Log_File – last file copied from the master (most of the time it would be the same as last binary log written by a master)
  • Read_Master_Log_Pos – binary log from master is copied over to the relay log on the slave up until this position.
And then you can compare it to the output of “show master statusG” from the master.
2. SQL thread
The second process – SQL thread – reads events from a relay log stored locally on the replication slave (the file that was written by IO thread) and then applies them as fast as possible.
This thread is what people often blame for being single-threaded. Going back to “show slave statusG”, you can get the current status of SQL thread from the following variables:
  • Relay_Master_Log_File – binary log from master, that SQL thread is “working on” (in reality it is working on relay log, so it’s just a convenient way to display information)
  • Exec_Master_Log_Pos – which position from master binary log is being executed by SQL thread.


Labels:

Comments

Post a Comment

Popular posts from this blog

What is QinQ(IEEE 802.1ad)

What is QinQ In this section, we will see about Switching concept QinQ. In service provider networks, This is very important. Service provider use this Switching function to pass customer data from one end to other end with two vlan id’s in own switching network.  Explanation: The QinQ technology is called VLAN dot1q tunnel, 802.1Q tunnel, VLAN Stacking technology. The standard comes from IEEE 802.1ad and it is the expansion of the 802.1Q protocol. QinQ adds one layer of 802.1Q tag (VLAN tag) based on the original 802.1Q packet head. With the double layers of tags, the VLAN quantity is increased to 802.1Q. QinQ encapsulates the private network VLAN tag of the user in the public(service provider) network VLAN Tag to make the packet with double layers of VLAN Tags cross the backbone network (public network) of the operator. In the public network, the packet is passed according to the out layer of VLAN tag (that is the public network VLAN Tag) and the private netw...
Post a Comment
Read more

Beacon Frames, Probe request and response

Beacon frame  is one of the management frames in  IEEE 802.11  based WLANs. It contains all the information about the network. Beacon frames are transmitted periodically, they serve to announce the presence of a wireless LAN and to synchronise the members of the service set. Beacon frames are transmitted by the  access point  (AP) in an infrastructure  basic service set  (BSS). In IBSS network beacon generation is distributed among the stations. Beacons are sent periodically at a time called Target Beacon Transmission Time(TBTT) 1 TU = 1024 microseconds Beacon interval =100 TU (100x 1024 microseconds or 102.4 milliseconds) 1. Timestamp (8 byte) 2. Beacon Interval (2 byte) 3. Capability info (2 byte) 4. SSID (variable size) 5. Supported Rates (variable size) Probe Request:  A station or client becomes active or on a PC when the wlan card it enabled it becomes acti...
Post a Comment
Read more

Difference between Polling and Trap in Network Management – Which one is better?

A Network Manager’s job is to get data from Network Elements and present it to the administrators or operators. There are two ways of doing this activity:  1) Polling and 2) Trap . Here is a quick difference between the two: Polling  : A traditional way of providing operators with the network elements information. It’s characteristics are as follows: ·        Pull Mechanism – Requests and get information from network elements at periodic intervals. The periodic interval is most often configurable. ·        Provides non-real time information. It may happen that some changes happen in network element but polling happens an hour after that. Thus, operator gets to know about the changes after an hour. ·        Higher bandwidth needed. Traps  : When an alarm situation exists a trap can be generated, or if some changes happen at network element, an attribute value chang...
Post a Comment
Read more