delete from t引發(fā)的血案

1、環(huán)境描述
一主兩備，讀寫分離，主庫(kù)一張大表，數(shù)據(jù)量3千萬(wàn)，執(zhí)行delete from t;

2、備庫(kù)狀態(tài)
延遲幾個(gè)小時(shí)，寫入數(shù)據(jù)無(wú)法同步到備庫(kù)，數(shù)據(jù)不一致，最終影響業(yè)務(wù)。
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、處理方法
臨時(shí)關(guān)閉讀寫分離，恢復(fù)業(yè)務(wù)，主庫(kù)壓力增加，暫時(shí)可支撐。

4、注意
類似這樣的問(wèn)題，完全可以避免，可以truncate table ?；蛘吲R時(shí)設(shè)置 set session binlog_format='statement'; 再執(zhí)行?；蛘呓Y(jié)合業(yè)務(wù)創(chuàng)建分區(qū)表?？傊@樣的問(wèn)題不是數(shù)據(jù)庫(kù)的問(wèn)題，是使用者沒(méi)有用好而已。