Shulou(Shulou.com)05/31 Report--

Today, I will talk to you about how to achieve delayed replication in Mysql. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something according to this article.

First of all, the replication structure of mysql is studied. Starting from more than 4.0, replication is divided into two processes, io process and sql process. The io process connects to the master to read the binlog and writes to the relaylog, while the sql process reads the relaylog and then apply to the slave.

The dump of binlog and relaylog formats looks like this:

# 090108 20:24:17 server id 1 log_pos 9466422 Query thread_id=34456 exec_time=0 error_code=0

SET TIMESTAMP=1231417457

Insert into xxxx (UDusedo,UDdirect,UDuserid,UDusername,UDgetuserid,UDgetusername,UDcoins,UDtype,UDzone1,UDtargetvalue,UDdate,UD

Ip,UDstatus) values (33,000,9376) values (116.53.1.144) 7495715)

SET TIMESTAMP=1231417457 is included in log to prevent inconsistencies in some time field values caused by different times between slave and master. In fact, it is also equivalent to the time that sql runs on master, so as long as we get it and compare it with the time on the current slave, if it is less than the delay we need, we will stop the replication, which will achieve delayed replication.

In mysql, you can get the same result as the TIMESTAMP above through the function UNIX_TIMESTAMP.

Mysql > select unix_timestamp ()

+-+

| | unix_timestamp () |

+-+

| | 1231750815 |

+-+

1 row in set (0.00 sec)

How to dig the tail of relaylog is a problem, because the log can be very large, so we need to use the following command:

[root@HB-150-189data] # mysql-e "show slave statusG"

* * 1. Row *

Master_Host: 192.168.1.104

Master_User: rep

Master_Port: 3306

Connect_retry: 60

Master_Log_File: HBDB104-bin.104

Read_Master_Log_Pos: 534427423

Relay_Log_File: HB-150-189-relay-bin.070

Relay_Log_Pos: 20200284

Relay_Master_Log_File: HBDB104-bin.104

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_do_db: aushop,auhj

Replicate_ignore_db:

Last_errno: 0

Last_error:

Skip_counter: 0

Exec_master_log_pos: 534427423

Relay_log_space: 20200284

Where you get the name of Relay_Log_File, Relay_Log_Pos: 20200284, you can get an offset through which you can mine relaylog to get the nearest TIMESTAMP, and then use:

Mysqlbinlog-j 20200284./HB-150-189-relay-bin.070 | grep "SET TIMESTAMP" | sed-n '1p'

Add the processing of shell and you can easily get the latest TIMESTAMP.

Take a look at the output of show slave status, version 4.0.26:

Mysql > show slave statusG

* * 1. Row *

Master_Host: 192.168.1.184

Master_User: rep

Master_Port: 3306

Connect_retry: 60

Master_Log_File: HBDB184-bin.072

Read_Master_Log_Pos: 358310392

Relay_Log_File: HB150-130-relay-bin.076

Relay_Log_Pos: 348847513

Relay_Master_Log_File: HBDB184-bin.072

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_do_db: money

Replicate_ignore_db:

Last_errno: 0

Last_error:

Skip_counter: 0

Exec_master_log_pos: 358310392

Relay_log_space: 348847513

1 row in set (0.00 sec)

The output information includes the status of Slave_IO_Running and Slave_SQL_Running.

After mysql 4, you can use STOP SLAVE IO_THREAD to stop the activity of the io process, and STOP SLAVE SQL_THREAD to stop the activity of the sql process. You only need to use shell to combine crontab and nohup, plus the

[root@HB-150-189 data] # mysqladmin extended-status | grep Slave_running

After reading the above, do you have any further understanding of how to achieve delayed replication in Mysql? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.