1

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?

Glorfindel
  • 2,201
  • 5
  • 17
  • 26
Ibrahim EL-Sanosi
  • 11
  • 1

3 Answers3

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.

Bill Karwin
  • 14,693
  • 2
  • 30
  • 42
  • 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”?.

     –  Ibrahim EL-Sanosi Oct 12 '21 at 10:19
  • Read https://dev.mysql.com/doc/refman/8.0/en/mysqlbinlog-row-events.html for details on dumping row events from the binlog. – Bill Karwin Oct 12 '21 at 13:08
  • To disable the binary log: https://dba.stackexchange.com/questions/72770/disable-mysql-binary-logging-with-log-bin-variable/226944 – Bill Karwin Oct 12 '21 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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
0

With MySQL8, they have turned on binary logging by default and the default purge (expiry/deletion) of binary logs is set to 30days.

Once you are in your SSH and in mysql, you can use the below commands

To show binary logs

mysql> SHOW BINARY LOGS;

To Purge binary logs manually until some point

mysql> PURGE BINARY LOGS TO 'binlog.000142';

Change automatic default purge expiry from 30days (deafault) to 3days

mysql> SET GLOBAL binlog_expire_logs_seconds = (60*60*24*3);
Query OK, 0 rows affected (0.00 sec)

mysql> SET PERSIST binlog_expire_logs_seconds = (606024*3);
Query OK, 0 rows affected (0.01 sec)

The above value is in seconds, i.e. 3 days in seconds = (60 seconds x 60 minutes x 24 hours x 3 days)

verge
  • 201
  • 2
  • 2