All we need is an easy explanation of the problem, so here it is.
Is the MySQL binlog similar to Oracle’s redo log or PostgreSQL’s Write-Ahead Log?
If yes, how come it be possible to disable bin logging? If the machine crashes while the data files are being updated, how will the RDBMS be able to rollback the changes (or otherwise restore the data files to a consistent state) without the binlog?
The manual says that "Certain data recovery operations require use of the binary log," but it’s not entirely clear which. If I don’t have replication and incremental backups, is it safe to disable the binlog?
The reason I’m asking these questions is that in a Nextcloud deployment I have trouble with disk performance, and I’ve found out that during heavy usage MySQL is writing to the disk (to the binlog) at a rate equal to the size of the database per minute. So I’m trying to understand what’s going on in order to address the problem.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
Unlike many other DBMSes, MySQL has two levels of logs to perform the "redo" function. Logs used to perform crash recovery are implemented (or not) by the storage engines, e.g. the InnoDB redo and undo logs. These logs allow the storage engine to replay committed and undo uncommitted transactions when the server recovers from an abend. They may also be used to allow online backups.
The binary log is maintained by the MySQL server process itself, it does not belong to any particular storage engine. As you have mentioned, it allows, among other things, point-in-time recovery (PITR) by replaying transactions on top of a restored backup. It is not used for crash recovery.
In other words, if you do not require PITR or replication you can safely disable the binary log; recovery of the database to a consistent state after a crash will be enabled by the storage engine redo/undo logs — just make sure you use a storage engine that implements them.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂