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

According to the Seconds_Behind_Master found by show slave status, literally, it is the number of seconds that slave lags behind master. In general, this is true. We can see whether slave lags behind master through Seconds_Behind_Master numbers, but in some environments, it does make us hallucinate.

In http://dev.mysql.com/doc/refman/5.5/en/show-slave-status.html, there is a sentence about Seconds_Behind_Master as follows:

In essence, this field measures the time difference in seconds between the slave SQL thread and the slave I/O thread.

It is very clear that the value is SQL thread I _ thread O. The difference between.

When in the case of a fast network connection, I get O thread. Can quickly synchronize sql from master's binlog to slave's relay-log, so that this value can basically determine how many seconds slave lags behind master.

When the network environment is particularly bad, this value does make us hallucinate. Thread O synchronization is very slow. Every time the synchronization comes, SQL thread can execute it immediately. In this way, the Seconds_Behind_Master we see is 0, but really, slave has lagged far behind master. At this point, the comrades of the business department will complain that the slave and master data are wrong, and you will be very depressed when you see that the Seconds_Behind_Master is 0.

In fact, at this time, we take a look at master, and slave can well determine the reasons for this period.

Mysql > show master statusG

* * 1. Row *

File: *-bin.001291

Position: 896711460

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

Mysql > show slave statusG

* * 1. Row *

Slave_IO_State: Waiting for master to send event

Master_Host: 10.69.6.163

Master_User: replica

Master_Port: 3801

Connect_Retry: 60

Master_Log_File: *-bin.001211

Read_Master_Log_Pos: 278633662

Relay_Log_File: *-relay-bin.002323

Relay_Log_Pos: 161735853

Relay_Master_Log_File: *-bin.001211

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 278633662

Relay_Log_Space: 161735853

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: 0

1 row in set (0.00 sec)

Obviously, slave is much better than master.

Pause replication

You can stop the copy with the STOP SLAVE statement on the slave computer and start the copy with START SLAVE. Use STOP SLAVE to stop slave binary log execution:

Slave > STOP SLAVE

When execution is stopped, the slave no longer reads the binary log from the host through the IO thread and no longer handles unexecuted events in the relay log through the SQL thread.

For example: slave > STOP SLAVE IO_THREAD

If you want to perform backups or other tasks on the slave machine, only dealing with events from the host, stopping the SQL thread will be effective.

The IO thread will continue to read changes from the host, but these changes will not be applied immediately, so that the slave can easily catch up when you start the slave operation again. Stopping the IO thread causes the statements in the relay log to execute until the point where the relay log stops receiving new events.

The option to stop the IO thread is available when you want the slave to catch up with events from the host, when you want to manage the slave but make sure you have the latest updates at the specified point. This method can also be used to stop the execution of events on the slave machine, while you manage on the host to ensure that there is not a large backlog of events to execute when replication starts again.

To start the replication again, use the START SLAVE statement:

Slave > START SLAVE

If necessary, you can start the IO thread and SQL thread separately.

[@ more@]

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.