Fun MySQL fact of the day: the binary log
The very first Fun MySQL fact of the day discussed a MySQL flag, sync_binlog, which is, indeed, a counter, and while we discussed that it can be used to tune a MySQL database's durability, we didn't really dig much deeper. Over the next little while, we'll do just that by taking a journey into the MySQL binary log. Today, we'll start simple.
By default, starting in MySQL 3.23.14 up until 5.7.7, when log_bin!=off, MySQL would record each change made to a MySQL database by recording DML (Data Manipulation Language) statements into a binary-encoded log in the order in which the DML statements were executed (let's ignore multi-statement transactions for now). In MySQL 5.7.7 and above, changes were made to the defaults, but, like transactions, we'll discuss those changes later. In the meantime, let's proceed with this somewhat-simplified, albeit admittedly potentially dangerous, mental model.
Now, you may be wondering, "why do I need a binary log if I have back-ups"? Well, there are three very good reasons that might be applicable to you
- MySQL's replication mechanism requires binary logging enabled on the master host(s).
- Back-ups do not easily allow for point-in-time recovery of data. With binary logs, you can
- rebuild a database from scratch or from a back-up statement-by-statement and
- skip over and revert destructive operations, like setting everybody's name to
Charlie.
- Binary logs facilitate in detection of a corrupt transactional engine, like InnoDB, during crash recovery.
We'll get into each of these fun topics in the coming days. In the meantime, you should determine if any of these reasons apply to you and go check if log_bin is set (it's a filename prefix) on the databases you care about.