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

1. Environment description

One master and two backups, separate read and write, a large table in the main database, 30 million data, execute delete from t

2. Status of repository preparation

After a delay of several hours, the write data cannot be synchronized to the slave database, and the data is inconsistent, which ultimately affects the business.

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-A CPU runs to death, sweat ~

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. Treatment method

Temporarily shut down the separation of read and write, resume business, and increase the pressure on the main library, which can be supported temporarily.

4. Pay attention

Problems like this can be avoided and can be truncate table. Or temporarily set set session binlog_format='statement'; before execution. Or create a partition table with the business. In short, such a problem is not the problem of the database, but that the users are not using it well.

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.