189 8069 5689
1、环境描述
一主两备，读写分离，主库一张大表，数据量3千万，执行delete from t;
十载建站经验， 网站制作、做网站客户的见证与正确选择。创新互联提供完善的营销型网页建站明细报价表。后期开发更加便捷高效，我们致力于追求更美、更快、更规范。
2、备库状态
延迟几个小时，写入数据无法同步到备库，数据不一致，最终影响业务。
MySQL(root@localhost:(none))>show slave status\G;
mysql(root@localhost:(none))>show slave status\G;
1. row
Slave_IO_State: Waiting for master to send event
Master_Host: 172.17.230.52
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 194
Relay_Log_File: relay-bin.000005
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
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: 404
Relay_Log_Space: 168492815
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: 24049
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: 1002
Master_UUID: 3f69546d-c6f3-11e8-97a0-00163e0cb7f6
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Reading event from the relay log
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 3f69546d-c6f3-11e8-97a0-00163e0cb7f6:2-9
Executed_Gtid_Set: 3f69546d-c6f3-11e8-97a0-00163e0cb7f6:1,
f571e3f1-c6f1-11e8-9a2d-00163e0efca3:1-31
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
ERROR:
No query specified
#top--一颗CPU跑死了，汗~~
Tasks: 91 total, 1 running, 90 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.0 us, 0.0 sy, 0.0 ni, 99.7 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 :100.0 us, 0.0 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu2 : 0.0 us, 0.0 sy, 0.0 ni, 99.7 id, 0.3 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu3 : 0.0 us, 0.3 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
KiB Mem : 8010424 total, 4899140 free, 1894900 used, 1216384 buff/cache
KiB Swap: 0 total, 0 free, 0 used. 5861632 avail Mem
3、处理方法
临时关闭读写分离，恢复业务，主库压力增加，暂时可支撑。
4、注意
类似这样的问题，完全可以避免，可以truncate table 。或者临时设置 set session binlog_format='statement'; 再执行。或者结合业务创建分区表。总之，这样的问题不是数据库的问题，是使用者没有用好而已。