MariaDB Binary Logging

ERPNext
1

Hi,

A B2C client of mine does a bunch of Sales Invoice imports. All the sales they make on various channels get imported on ERPNext. I see that the MariaDB/MySQL Binary Logging files at /var/lib/mysql that start with mysql-bin.XXXXXX are getting created at the rate of 1 GB for every 2 hours during working hours, which means that 4GB of file space is getting consumed per day. The expire-log-days is set at 14.

The client’s operating procedure for the foreseeable future will be to import Sales Invoices using GoogleSheets template. When I review the binlog files, I see that it is mostly the Sales Invoice related information.

What’s the optimal way of managing this?

  1. Leave things as they are. Just plan for a large enough storage space. What should be the space I need to plan for? Is there any significance to the The expire-log-days is set at 14? Does that mean that on the 15th day, the first days logs will be purged or overwritted or deleted? Assuming this is true shuld I plan for 5GB per day * 14 = 70GB just fo Binary Logs? Or will the Binary Logs keep growing even beyond the 15th day?
  2. Turn off binary logging? What are the downsides to turning off binary logging?
  3. Is there a way to reduce the size or frequency of these Binary Log files that are getting generated?

Any other pieces of advice?

Thanks

Jay

2

Hi Jay,

Here’s a great summary I found here: https://mariadb.com/docs/reference/cs10.3/logging/binary/

The binary logs are intended for:

  • Replicating statements and transactions to replica nodes using MariaDB Replication
  • Performing point-in-time recovery (PITR)
  • Analyzing data changes with mariadb-binlog

So unless your client is doing one of those 3 things? I would recommend disabling Binary Logging.

I personally haven’t done anything with this in MariaDB, so don’t have any more details for you.
(I have some outdated experience with MS SQL replication and transaction logs, that seems equivalent to what MariaDB is capable of)

2 Likes
3

I did stumble into those links and figured out that it’s possible to turn off binary logging. Has anybody turned off binary logging on ERPNext? Did you have any trouble after you turned off Binary Logging? Any loss of functionality or errors?

The thing I found is that if you have periodic backups you can use Binaly Logging to restore the system after you restore from the last available backup.

So if you have a daily backup, you don’t really need the Binary Logs to be kept for 14 days. 2 Days at most is good enough.

Just checking here before I turn off binary logging entirely, or reduce the logging days value from 14 (days) to 2.

I will post my experiences here as I do it.

Thanks

Jay

1 Like