A, conversations,

A while ago, I had a chat with a classmate about these MySQL logs. That classmate feels these a few log quite abstruse quite difficult of. There’s no need to scare yourself.

For example, have you ever wondered why MySQL writes logs? Write log must have disk IO, do not write not sweet? Speed and performance will increase. MySQL writes logs because logging gives MySQL some power.

For example, undo log gives mysql the ability to roll back things, redo log gives mysql the ability to crash and recover, and bin log gives mysql the ability to cluster, back up, and restore data.

I don’t want MySQL to record those logs. Sure! MySQL doesn’t even voluntarily log bin logs for you by default, but then again, if you don’t let it write those logs, it doesn’t have the ability to do that for you. Isn’t it! So all in all, journaling smells good.

Knowing these, is it very purposeful to learn the relevant knowledge points? The undo log and redo log daydreams have already been shared with you. If you feel any more, please follow me and check them out.

Most of you have heard of bin Log. And you don’t necessarily know where the binlog is, right? Who wrote it? How to configure binlog? And what the binlog does. So in the next few articles, let’s take a look at two or three things about binlogs to help you understand them better.

What is bin log?

Bin log is a binary log file for MySQL. So I’m just going to call it binlog.

We all know that MySQL is divided into two main sections. The upper layer is mysqL-Server and the lower layer is pluggable storage engine.

The binlog is generated by the Server layer of MySQL.

Three, where is it?

The location of the binlog is controlled by the datadir parameter.

You can view it in the following way

So if you know where the binlog is, you can take a look at it and say, okay

There are two types of files in this directory: mysql-bin.0000xx and mysql-bin.index

The former holds the logic for changes to MySQL

And the latter long below such, estimate you will understand ~

4. Configure bin log

The general configuration of binlog is written in the MySQL configuration file: my.cnf, so that when MySQL is started, these configurations can be directly implemented

For an idea, you can take a quick peek at the binlog configuration items

[mysqld] [mysqld] [mysqld] [mysqld] [mysqld] [mysqld] [mysqld] [mysqld] Log-bin = mysql-bin # index log-bin-index = mysql-bin.index # Max_binlog_size = 1G # Sync-binlog = 1 # Binlog-format = ROW # Binlog expire_logs_days = 7Copy the code

What is the use of binlog?

If redolog records are biased towards the physical plane, for example, what changes were made to that record on which data page.

The binlog is a logical record: for example, what changes were made to the row id=yyy in XXX, and what values were changed.

Binlog does not record your select, show, etc.

You can find query-only SQL such as select and show executed in the Query log.

Common binlogs have the following functions.

  1. Delete without where condition? Don’t panic! Binlog can help you recover your data

  2. Set up a MySQL cluster with one master and two slave, binlog for you to complete the master and slave data synchronization.

  3. Check whether SQL injection attacks exist by analyzing the binlog.

But you know what?

Binlog is disabled by default. Because enabling binlog reduces mysql performance slightly (1%).

With binlog enabled, you can set up a MySQL cluster, check for SQL injection, and recover deleted data by mistake. MySQL cluster is enabled with binlog enabled. Very sweet?

Sql_log_bin

You can see how this parameter works by sharing an online scenario.

For example, if you are using a MySQL cluster with one master and two slaves online, then an activity comes up, and you need to add a column to a table in a library online, and the number of columns in that table is very large.

Adding a column is required to acquire a table lock, and the large amount of data makes your ALTER table extremely slow. Normal DML will block during the process of acquiring a table lock. This is where you have to consider lossless DDL, such as Golang’s Ghost.

Ghost’s feature is that it requires some pre-executed SQL to verify that your cluster character meets its requirements. In order not to have an impact online, you will want to put the pre-executed SQL on the slave library. Executing from a library is fine, but the SQL you’re executing will generate a bin log. There are new GtiDs (which we will share with you later on in MySQL cluster, but you just need to understand as a unique identifier of a thing), and what’s worse, there are no gtiDs in the main library.

When both the master and slave are running normally, it does not matter if the master and slave are inconsistent in their bin logs. However, when the slave library is down, the slave library will send its GTID collection to the master library after being restarted. As a result, the slave library cannot be added to the cluster again due to the extra GTID that the master library does not have.

In fact, this problem is easy to solve. Use this parameter SQL_log_bin

This SQL_log_bin variable controls whether logging to binary logs is enabled for the current session (assuming binary logging itself is enabled). The default value is ON. To disable or enable binary logging for the current session, set the session SQL_LOG_bin variable to OFF or ON.

The global SQL_LOG_bin variable is read-only and cannot be modified.