What is binlog in mysql?

Question

I've set mysql parameter innodb_flush_log_at_trx_commit=0. It means that mysql flushes transactions to HDD 1 time per second. Is it true that if mysql will fail with this flush (because of power off) i will lose my data from these transactions. Or mysql will save them in data file (ibdata1) after each transaction regardless of binlog flush?

Thanks.

Allanson

Answer 1

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. The binary log also contains information about how long each statement took that updated data. The binary log has two important purposes:

1. For replication, the binary log on a primary replication server provides a record of the data changes to be sent to secondary servers. The primary server sends the events contained in its binary log to its secondaries, which execute those events to make the same data changes that were made on the primary.
2. Certain data recovery operations require the use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup

The binary log is not used for statements such as SELECT or SHOW that do not modify data. https://dev.mysql.com/doc/refman/8.0/en/binary-log.html

Supportive

Answer 2

Here is the entry in the MySQL reference manual for innodb_flush_log_at_trx_commit. You can lose the last second of transactions with the value set to 0.

Note that the binlog is actually something different that is independent of innodb and is used for all storage engines. Here is the chapter on the binary log in the MySQL reference manual.

Madura