Shulou(Shulou.com)05/31 Report--

This article mainly shows you "how to monitor changes in the content of the MySQL table". The content is easy to understand and clear. I hope it can help you solve your doubts. Let the editor lead you to study and learn the article "how to monitor changes in the content of the MySQL table".

Preface

Binlog is binary log, the binary log file, which records all the add, delete and change statements of mysql. Through the binlog log, we can do data recovery, master-slave replication and so on. As you can see, as long as we have this binlog, we have a full backup of mysql.

We often encounter the need to listen for changes in a table and then do something.

If the table data is only added but not deleted, it is relatively easy to monitor. You can query the latest id on a regular basis. However, if there is a delete, modify operation, it is inevitable that the full table scan, the efficiency is very low. It would be best if something like an event can be triggered for monitoring when the table changes.

Now we can do it through binlog. Just listen for changes in binlog, so that every statement executed will be withdrawn in binlog, and we can listen to it.

Binlog is off by default, and if we want to open it, we need to modify the configuration file of mysql.

For mysql installed by brew on mac, the default installed directory is / usr/local/Cellar, and the version is 5.7.21.

As you can see, there is no configuration file in the directory, which may be different from some other versions, which may have a my.ini or my.cnf file in the root directory, or a my-default.cnf file in support-files, which is not available.

To modify the configuration file, we need to create it ourselves.

Create a my.cnf file in the / etc/ directory with the following contents

[mysqld] server_id = 1log-bin = mysql-binbinlog-format = ROW

Mysql-bin is just a name, and you can choose it as you like. The log file name that will be saved in the future is like mysql-bin.000001,mysql-bin.000002.

Note that binlog_format must be set to ROW, because in STATEMENT or MIXED mode, Binlog only records and transmits SQL statements (to reduce the log size) and does not contain specific data, so we cannot save it.

Then restart mysql via brew restart mysql. Then enter the mysql console through the mysql-uroot-p command, and execute

Show variables like'% log_bin%'

The top arrow is when my.cnf is not set, and the bottom is after my.cnf is set and restarted.

You can view the status of the binlog log that is currently being written through the show master status command.

There is also the following command:

Refresh the binlog log file flush logs # refresh creates a new binlog log.

Clear the log file reset master.

View the contents of the first binlog file, show binlog events.

View the contents of the specified binlog file, show binlog events in 'mysql-bin.000004'.

Gets the list of binlog files, show binary logs.

Just binlog, that's not enough, we also need a tool to listen to binlog. Next, let's take a look at canal.

The above is all the contents of the article "how to monitor changes in the content of the MySQL table". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.