Explain how to use mysql binlog

This article introduces the use of mysql binlog, including opening, closing, viewing status, refreshing, clearing, viewing executed sql statements and other operations. The settings of 5.7 and older versions are also explained to facilitate everyone's learning.

Mysql binlog introduction

Binlog is binary log, a binary log file that records all mysql dml operations.

According to the mysql binlog file, we can check what SQL statements were executed, perform data recovery, master-slave synchronous replication and other operations.

The binlog file plays an important role in the processing and recovery of a database.

1.mysql binlog opening and closing

View mysql binlog configuration

show global variables like '%log_bin%';
+---------------------------------+-------+| Variable_name                   | Value |
+---------------------------------+-------+| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |+---------------------------------+-------+

binlog is currently closed.

Open binlog

Open my.cnf or my.ini and add the following statement, Restart mysql

log_bin=ONlog_bin_basename=/usr/local/var/mysql/mysql-binlog_bin_index=/usr/local/var/mysql/mysql-bin.index

log_bin
ON means to open the binlog log, and change it to OFF.

log_bin_basename
represents the basic file name of the binlog log, and an identifier will be appended to distinguish each file.

log_bin_index
Specify the index file of the binlog file. This file manages the directories of all binlog files.


If it is below mysql5.7, this setting is enough. If it is above 5.7, you need to set it as follows

log_bin=mysql-binserver_id=123456

log_bin Indicates the custom binlog file name.

server_id means randomly specifying a string that does not have the same name as other cluster machines. It needs to be defined when configuring mysql replication. It cannot be repeated with the slaveId of canal.

Check the mysql binlog configuration again after restarting

show global variables like '%log_bin%';
+---------------------------------+--------------------------------------+| Variable_name                   | Value                                |
+---------------------------------+--------------------------------------+| log_bin                         | ON                                   |
| log_bin_basename                | /usr/local/var/mysql/mysql-bin       |
| log_bin_index                   | /usr/local/var/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                  |
| log_bin_use_v1_row_events       | OFF                                  |+---------------------------------+--------------------------------------+

You can see that binlog is enabled.

2. View the binlog log file list

show master logs;
+------------------+-----------+| Log_name         | File_size |
+------------------+-----------+| mysql-bin.000001 |       177 |
| mysql-bin.000002 |       177 |
| mysql-bin.000003 |       177 |
| mysql-bin.000004 |       177 |
| mysql-bin.000005 |       177 |
| mysql-bin.000006 |       177 |
| mysql-bin.000007 |       201 |
| mysql-bin.000008 |       201 |
| mysql-bin.000009 |       201 || mysql-bin.000010 |       154 |
+------------------+-----------+

3. View the binlog log currently being written

show master status;
+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000010 |      154 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

4. Refresh the binlog log file

flush logs;

5. Clear the log file

reset master;

6. View the contents of the binglog log file

View the binlog log file to see which sql statements have been executed. We can use mysqlbinlog Tools for processing.

First, according to log_bin_basename, find the directory where the binlog file is stored, and then use the mysqlbinlog tool to view the corresponding binlog file.

For example:

mysqlbinlog -v mysql-bin.000001 > mysql-bin-1.log

Then check mysql-bin-1.log to view the executed sql statements.

BINLOG '
Xq1HWhNA4gEAPAAAAGQBAAAAAPEEAAAAAAEACXRlc3RfdXNlcgAGY3NfdGFnAAUDDwEDAwL9AgBa
WZlG
Xq1HWh5A4gEANwAAAJsBAAAAAPEEAAAAAAEAAgAF/+ACAAAABABjc2RuAf2LG1r9ixtaIS88ZA==
'/*!*/;### INSERT INTO `test_user`.`cs_tag`### SET###   @1=2###   @2='csdn'###   @3=1###   @4=1511754749###   @5=1511754749# at 411

You need to pay attention to a few points when using mysqlbinlog

1. Do not check the binlog file currently being written. You can copy the file to another directory first and then execute it. Check.

2. Do not add the force parameter to force access.

3. If the binlog format is row mode, please add the -vv parameter.

This article explains how to use mysql binlog. For more related knowledge, please pay attention to the php Chinese website.

Related recommendations:

Explain the relevant content of PHP using the token bucket algorithm to implement flow control based on redis

How to pass PHP creates a QR code class with logo

Detailed explanation of the related methods of mysql to rebuild table partitions and retain data

The above is the detailed content of Explain how to use mysql binlog. For more information, please follow other related articles on the PHP Chinese website!