0

My WordPress website uses Nginx. Recently I have noticed that server volume has increased from 8GB to 40GB. I have found that the WordPress MYSQL Binary Log is taking more space. Each second, database transactions are written to the Binlog files.

I am wondering because we do not have massive traffic on our website/WordPress. And having so many and large MySQL binlog files, as well as a lot of database events that are logged in the binlog files per second, could cause by a bot attack or any plugins are heavily using the database for temporal storage.

What I have done so far, I ran mysql> PURGE BINARY LOGS BEFORE '2021-08-30 22:46:26'. This reduces disk size from 40GB to 10GB which is good.

My questions:

  1. Since I use a single server and I am not using a replica, can I disable BINARY LOG (mysql> SET SQL_LOG_BIN =0), if I disable BINARY LOG, will this cause an issue?

  2. What causes binary log files to increase rapidly, could this be malicious traffic? if so how can I stop it?

Any thought?

2
  • What is "server volume"? Network traffic? Disk used? Something else?
    – Rick James
    Oct 11 at 18:24
  • server volume=80GB, Network Traffic = How can I check this? Disk used 14GB Oct 12 at 10:29
1

The binary log contains changes to data or metadata. I.e., INSERT/UPDATE/DELETE/REPLACE, and also CREATE/ALTER/DROP/TRUNCATE statements.

It isn't necessarily due to malicious traffic. It could simply be creating records of users visiting your site, or making copies of versions of wordpress posts as you edit them.

You can use mysqlbinlog to inspect the binary log so you can see exactly the statements in it.

You can disable the binary log if you want. But the command you show, SET SQL_LOG_BIN=0 only works for the current session. To disable the binary log globally, you must change the my.cnf configuration file and restart the MySQL Server. You can't change this without a restart. See the documentation on log_bin for details.

You may also want to set automatic expiration for the binary log. In MySQL 5.* you can use expire_logs_days. In MySQL 8.0, you should use the new option binlog_expire_logs_seconds.

The binary log may be disabled, but it is useful if you want to audit changes, or if you want to do point-in-time recovery after restoring a backup, or as you mentioned if you want to use replication.

3
  • I inspect binlog using mysqlbinlog as you recommend. I found that each day we have 9 binlog files and each file has 100MB size. Also, I inspect the content and all content are mainly has long string like JG1hdGNoZXN I cannot understand why we have this long string and so much transaction written to binlog and this affecting our disk? To edit my.cnf file. I have only found these two lines, inside the file: !includedir /etc/mysql/conf.d/ !includedir /etc/mysql/mysql.conf.d/. And each above folder has got mysql.cnf So which folder I should use to add “log_bin = OFF”?. Oct 12 at 10:19
  • Read dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-row-events.html for details on dumping row events from the binlog. Oct 12 at 13:08
  • To disable the binary log: dba.stackexchange.com/questions/72770/… Oct 12 at 13:11
0

An UPDATE of all rows of a million-row table will create a million entries in the binlog. This is bulky.

Large TEXT or BLOB columns can be bulky.

"JG1hdGNoZXN" may be a BASE64 encoding of binary data that would be confusing to read if it were not encoded that way.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.