Monitor changes in MySQL table content and enable MySQL binlog

Preface

binlog is a binary log file, which records all the add, delete, and modify statements of MySQL. Through binlog logs, we can perform data recovery, master-slave replication, etc. As you can see, as long as we have this binlog, we have a complete backup of mysql.

We often encounter such a requirement, that is, we need to monitor the changes in a certain table and then perform some operations.

If the data in the table is only added and not deleted or modified, it is relatively simple to monitor. You can query the latest ID regularly. However, if deletion or modification operations are required, the entire table must be scanned, which is extremely inefficient. It would be best if when the table changes, an event can be triggered for monitoring.

Now we can complete it through binlog. Just monitor the changes in binlog, so that every time a statement is executed, it will be recorded in binlog and we can monitor it.

Binlog is disabled by default. To enable it, we need to modify the MySQL configuration file.

The default installation directory of MySQL installed by brew on Mac is /usr/local/Cellar, and the version is 5.7.21.

As you can see, there is no configuration file in the directory. This may be different from some other versions. Other versions may have a my.ini or my.cnf file in the root directory, or a my-default.cnf file in support-files, but this version does not have that.

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

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

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

mysql-bin is just a name, you can use any name you want. The log file names saved in the future will be mysql-bin.000001, mysql-bin.000002.

Note that binlog_format must be set to ROW, because in STATEMENT or MIXED mode, Binlog will only record and transmit SQL statements (to reduce log size) without 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 arrow above is when my.cnf is not set, and the arrow below is after my.cnf is set and restarted.

You can use the show master status command to view the status of the binlog currently being written.

There are also commands like:

Refresh binlog log file flush logs #After refreshing, a new binlog log will be created.

Clear the log file and reset the 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' .

Get the list of binlog files show binary logs .

Having binlog alone is not enough, we also need a tool to monitor binlog. Next article: Canal

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. Thank you for your support of 123WORDPRESS.COM.