MySQL主从延迟复制的方法总结
方法介绍
1.percona公司pt-slave-delay工具
主库:
[mysql@localhost ~]$ login
Logging to file '/tmp/master.log'
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18500
Server version: 5.6.28-log MySQL Community Server (GPL)
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
root@localhost [(none)] 04: 16: 08 >
root@localhost [(none)] 04: 16: 08 >use test;
Database changed
root@localhost [test] 04: 16: 16 >insert into tb values(1,'chuck');
Query OK, 1 row affected (0.03 sec)
root@localhost [test] 04: 16: 27 >commit;
Query OK, 0 rows affected (0.00 sec)
从库查询:
root@localhost [test] 04: 16: 52 >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 1006
Relay_Log_File: localhost-relay-bin.000063
Relay_Log_Pos: 1176
Relay_Master_Log_File: mysql-bin.000033
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: 1006
Relay_Log_Space: 1353
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:2052039-2052041
Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052041,
c7a64be9-61e6-11e7-9697-b083fee71372:1-3
Auto_Position: 1
1 row in set (0.00 sec)
root@localhost [test] 04: 17: 05 >select * from tb;
+------+-------+
| id | val |
+------+-------+
| 1 | chuck |
+------+-------+
1 row in set (0.00 sec)
可以看到主从现在状态是正常的.
设置延迟
[mysql@localhost ~]$ pt-slave-delay --delay=1m --interval=15s --run-time=10m u=root,p=mysql,h=localhost,P=3307 --socket=/usr/local/mysql/mysql1.sock
从库状态改变
设置延迟后从库停止了sql_thread线程:Slave_SQL_Running: No
root@localhost [test] 04: 19: 05 >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 1812
Relay_Log_File: localhost-relay-bin.000063
Relay_Log_Pos: 1739
Relay_Master_Log_File: mysql-bin.000033
Slave_IO_Running: Yes
Slave_SQL_Running: No
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: 1569
Relay_Log_Space: 2159
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: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:2052039-2052044
Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052043,
c7a64be9-61e6-11e7-9697-b083fee71372:1-3
Auto_Position: 1
1 row in set (0.00 sec)
主库再插入一条记录
root@localhost [test] 04: 17: 29 >insert into tb values(2,'chuck');
Query OK, 1 row affected (0.02 sec)
root@localhost [test] 04: 22: 10 >commit;
Query OK, 0 rows affected (0.00 sec)
延迟日志
[mysql@localhost ~]$ pt-slave-delay --delay=1m --interval=15s --run-time=10m u=root,p=mysql,h=localhost,P=3307 --socket=/usr/local/mysql/mysql1.sock
2017-07-21T16:22:04 slave running 0 seconds behind
2017-07-21T16:22:04 STOP SLAVE until 2017-07-21T16:23:04 at master position mysql-bin.000033/1569
2017-07-21T16:22:19 slave stopped at master position mysql-bin.000033/1569
2017-07-21T16:22:34 slave stopped at master position mysql-bin.000033/1812
2017-07-21T16:22:49 slave stopped at master position mysql-bin.000033/1812
2017-07-21T16:23:04 no new binlog events
2017-07-21T16:23:19 slave stopped at master position mysql-bin.000033/1812
2017-07-21T16:23:34 START SLAVE until master 2017-07-21T16:22:34 mysql-bin.000033/1812
可以看到大概一分钟后,从库开启sql_thread线程.
从库记录
root@localhost [test] 04: 24: 24 >select * from tb;
+------+-------+
| id | val |
+------+-------+
| 1 | chuck |
| 2 | chuck |
+------+-------+
2 rows in set (0.00 sec)
2.使用CHANGE MASTER TO MASTER_DELAY 单位为秒
root@localhost [(none)] 04: 50: 02 >stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
root@localhost [(none)] 04: 50: 04 >CHANGE MASTER TO MASTER_DELAY = 60;
Query OK, 0 rows affected (0.04 sec)
root@localhost [(none)] 04: 50: 10 >start slave;
Query OK, 0 rows affected (0.01 sec)
root@localhost [(none)] 04: 50: 14 >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 1812
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000033
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: 1812
Relay_Log_Space: 616
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
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372
Master_Info_File: mysql.slave_master_info
SQL_Delay: 60
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052044,
c7a64be9-61e6-11e7-9697-b083fee71372:1-3
Auto_Position: 1
1 row in set (0.00 sec)
主库插入记录
root@localhost [test] 04: 55: 44 >insert into tb values (3,'chuck');
Query OK, 1 row affected (0.02 sec)
root@localhost [test] 04: 55: 55 >commit;
Query OK, 0 rows affected (0.00 sec)
从库查询主从状态
root@localhost [(none)] 04: 56: 06 >show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.0.168
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000033
Read_Master_Log_Pos: 2055
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 408
Relay_Master_Log_File: mysql-bin.000033
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: 1812
Relay_Log_Space: 859
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: 14
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 7618d547-5d81-11e7-b9ec-b083fee71372
Master_Info_File: mysql.slave_master_info
SQL_Delay: 60
SQL_Remaining_Delay: 46 //预计还有多长时间延迟
Slave_SQL_Running_State: Waiting until MASTER_DELAY seconds after master executed event
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:2052045
Executed_Gtid_Set: 7618d547-5d81-11e7-b9ec-b083fee71372:1-2052044,
c7a64be9-61e6-11e7-9697-b083fee71372:1-3
Auto_Position: 1
1 row in set (0.00 sec)
大概一分钟后数据在从库应用.
root@localhost [(none)] 04: 56: 38 >select * from test.tb;
+------+-------+
| id | val |
+------+-------+
| 1 | chuck |
| 2 | chuck |
| 3 | chuck |
+------+-------+
3 rows in set (0.00 sec)