Suddenly a massive amount of binlog files

Question

I've seen a sudden increase of binlog files, very large in number and size, in all three nodes of a Percona XtraDB cluster:

-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:11 binlog-32.000001
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:36 binlog-32.000002
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:48 binlog-32.000003
-rw-rw----  1 mysql mysql 1,1G 19 oct.  16:59 binlog-32.000004
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:08 binlog-32.000005
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:26 binlog-32.000006
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:38 binlog-32.000007
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:47 binlog-32.000008
-rw-rw----  1 mysql mysql 1,1G 19 oct.  17:59 binlog-32.000009
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:10 binlog-32.000010
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:20 binlog-32.000011
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:29 binlog-32.000012
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:37 binlog-32.000013
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:45 binlog-32.000014
-rw-rw----  1 mysql mysql 1,1G 19 oct.  18:53 binlog-32.000015
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:01 binlog-32.000016
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:11 binlog-32.000017
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:19 binlog-32.000018
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:28 binlog-32.000019
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:38 binlog-32.000020
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:44 binlog-32.000021
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:52 binlog-32.000022
-rw-rw----  1 mysql mysql 1,1G 19 oct.  19:59 binlog-32.000023
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:08 binlog-32.000024
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:17 binlog-32.000025
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:24 binlog-32.000026
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:32 binlog-32.000027
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:40 binlog-32.000028
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:47 binlog-32.000029
-rw-rw----  1 mysql mysql 1,1G 19 oct.  20:56 binlog-32.000030
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:05 binlog-32.000031
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:15 binlog-32.000032
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:25 binlog-32.000033
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:36 binlog-32.000034
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:46 binlog-32.000035
-rw-rw----  1 mysql mysql 1,1G 19 oct.  21:48 binlog-32.000036
-rw-rw----  1 mysql mysql 254M 20 oct.  09:34 binlog-32.000037
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000038
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000039
-rw-rw----  1 mysql mysql  143 20 oct.  09:34 binlog-32.000040
-rw-rw----  1 mysql mysql  120 20 oct.  09:34 binlog-32.000041
-rw-rw----  1 mysql mysql 1,2K 20 oct.  09:34 binlog-32.index

It appears that an user lanced a script that does quite a lot of SELECTs and UPDATEs over many connections. However, it surprises me how he managed to DoS the server. Is this a reasonable cause, or might be something else, perhaps some misconfiguration?

Answer 1

Your user seems to have cause quite a high workload.

SELECTs are not written in the binlog, but UPDATEs are (as well as all other data changes).

Settings that affect the binary log size are:

• binlog_format: I recommend to set it to ROW for performance reasons, but this may cause your files to take a log of space if UPDATEs and DELETEs affect many rows.
• binlog_row_image: When events are logged in ROW format, this variable is relevant for UPDATE, as well as DELETE andREPLACE: it determines whether the old values are written to the binlog (normally unnecessary, but it can be required by some tools that read the binlog).
• binlog_row_metadata: Whether columns and primary key metadata are logged.
• binlog_row_value_options: Whether modifying a part of a JSON document will cause the all document to be written in the binlog.
• Binary log transaction compression

Answer 2

Check server-id on in my.cnf on each server in your replication topology. Probably there is a duplicate. Server ids must be different.