The question:
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.
The Solutions:
Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.
Method 1
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.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0