Mysql, binlog log query

One 、 Turn on binlog

stay mysql The configuration file my.cnf in , Turn on log-bin function ,log-bin=mysql-bin Make sure it's on .

[mysqld]
log-bin=mysql-bin

explain , value mysql-bin Is the base name or prefix name of the log .

Two 、 adopt mysql Variable configuration table of , Check whether the binary log is turned on

mysql> show variables like 'log%'; 
+----------------------------------------+--------------------------------------+
| Variable_name                          | Value                                |
+----------------------------------------+--------------------------------------+
| log_bin                                | ON                                   | ---> ON, Indicates that it has been turned on binlog journal 
| log_bin_basename                       |/usr/local/mysql/data/mysql-bin       | ---> binlog Log storage location 
| log_bin_index                          |/usr/local/mysql/data/mysql-bin.index | ---> binlog Format of log 
| log_bin_trust_function_creators        | OFF                                  |
| log_bin_use_v1_row_events              | OFF                                  |
| log_builtin_as_identified_by_password  | OFF                                  |
| log_error                              | /data1/mysql/logs/error.log          |
| log_error_verbosity                    | 3                                    |
| log_output                             | FILE                                 |
| log_queries_not_using_indexes          | OFF                                  |
| log_slave_updates                      | ON                                   |
| log_slow_admin_statements              | OFF                                  |
| log_slow_slave_statements              | OFF                                  |
| log_statements_unsafe_for_binlog       | ON                                   |
| log_syslog                             | OFF                                  |
| log_syslog_facility                    | daemon                               |
| log_syslog_include_pid                 | ON                                   |
| log_syslog_tag                         |                                      |
| log_throttle_queries_not_using_indexes | 0                                    |
| log_timestamps                         | UTC                                  |
| log_warnings                           | 2                                    |
+----------------------------------------+--------------------------------------+

3、 ... and 、binlog Log content viewing

1、mysql see binlog command

mysql> show binlog events;                          #  Just look at the first  binlog  The content of the document 
mysql> show binlog events in 'mysql-bin.000002';    #  View specified  binlog  The content of the document 
mysql> show binary logs;                            #  obtain  binlog  File list 
mysql> show master status;                         #  View what is currently being written  binlog  file 

2、 find mysqlbinlog command

mysqlbinlog It's a check mysql Binary log tool , You can put mysql All the above operation records are exported from the log , The default installation path of this tool is ：/usr/local/mysql/bin/mysqlbinlog .

You can find... Through the command mysqlbinlog Tool path for ：

find / -name "mysqlbinlog"

3、 Use mysqlbinlog command

bin log It's binary , Binary files save more space , Write faster , You can't open it directly to view .

（1） If log-bin The file is relatively small , You can view the log directly ：

/usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.0007

--no-defaults It can be omitted , Reference resources Below mysqlbinlog abnormal explain .

（2） take log-bin The parsed log is transferred to the specified file

/usr/local/mysql/bin/mysqlbinlog --no-defaults/usr/local/mysql/data/mysql-bin.0007 > /tmp/mysql_bin_07.sql

（3） Based on start and end times ：

/usr/local/mysql/bin/mysqlbinlog --start-datetime="2021-10-01 00:00:00" --stop-datetime="2021-10-21 23:59:59" /usr/local/mysql/data/mysql-bin.0007 -r test2.sql

（4） Add filtration , Query only insert、update、delete The sentence of , Use more Check it out. ：

/usr/local/mysql/bin/mysqlbinlog --no-defaults --database=raceEnroll mysql-bin.000007 |grep update | more

4、mysqlbinlog abnormal

direct perform /usr/local/mysql/bin/mysqlbinlog mysql-bin.000007 When the log , The following errors may be reported ：

mysql> /usr/local/mysql/bin/mysqlbinlog  mysql-bin.000007
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8mb4'
mysqlbinlog: [ERROR] unknown variable 'default-character-set=utf8'

reason ：

mysqlbinlog This tool doesn't recognize binlog In the configuration of default-character-set=utf8mb4 This instruction .

resolvent ：

add to --no-defaults Parameters , namely ： /usr/local/mysql/bin/mysqlbinlog --no-defaults mysql-bin.000007 .

Four 、 frequently-used Binlog Operation command

#  Is it enabled? binlog journal 
show variables like 'log_bin';

#  View detailed log configuration information 
show global variables like '%log%';

# mysql Data storage directory 
show variables like '%dir%';

#  see binlog The catalog of 
show global variables like "%log_bin%";

#  View the... Currently used by the server biglog File and size 
show binary logs;

#  Check the... Used by the main server biglog File and size 

#  Check out the latest binlog Log file name and Position
show master status;


#  Event query command 
# IN 'log_name' ： Specify the binlog file name ( No designation is the first binlog file )
# FROM pos ： Specify from which pos Start from ( Do not specify is from the entire file first pos Start at )
# LIMIT [offset,] ： Offset ( No designation is 0)
# row_count ： Total number of queries ( Not specifying is all lines )
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

#  see  binlog  Content 
show binlog events;

#  Check out a specific binlog The content of the document  （in  The back is binlog The name of the file ）
show binlog events in 'master.000003';

#  Set up binlog File save event , Expired delete , Unit day 
set global expire_log_days=3; 

#  Delete the current binlog file 
reset master; 

#  Delete slave The trunk log of 
reset slave;

#  Delete the log index before the specified date binlog Log files 
purge master logs before '2019-03-09 14:00:00';

#  Delete the specified log file 
purge master logs to 'master.000003';