3

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.

1
  • Did you read the rest of the paragraph after "Certain data recovery operations require use of the binary log"? It actually describes the data recovery operation, and then links to another page that goes into more detail. What's not clear about that? Mar 23, 2022 at 17:20

1 Answer 1

6

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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