[MySQL] introduction to mysqlbinlog

Introduction to binlog:

A binary log that records SQL statements that occur or potentially change data and are stored on disk in binary form.

Function:

MySQL acts like Oracle's archive log and can be used to view database change history (all SQL operations at a specific point in time), database incremental backup and recovery (incremental backup and point-in-time recovery), and Mysql replication.

Enable:

Show variables like'% log_bin%'; can see if it is turned on.

Method 1. Modify the my.cnf parameter file, which needs to be restarted.

Log-bin=mysql-bin # opens the log (the host needs to open it). This mysql-bin can also be customized. You can also add a path here, such as / home/www/mysql_bin_log/mysql-bin

How to turn off the binary log: log-bin = mysql-bin comment out

Method 2. Do not restart and modify the binary log configuration. The mysql version of this method requires more than 5.6.

SET @ @ global.log_bin=1 | 0 (1: on, 0: off)

View:

# you can check the parameters to help mysqlbinlog-- no-defaults-- help # View the last 100 lines mysqlbinlog-- no-defaults-- base64-output=decode-rows-vv mysql-bin.000001 | tail-100# find mysqlbinlog-- no-defaults-- base64-output=decode-rows-vv mysql-bin.000001 based on position | grep-A 20 '4939002' # you can also recover mysqlbinlog-no-defaults-start-position=204136360 based on position recovery part of the data. -- stop-position=204136499 mysql-bin.000006 | mysql- uroot-pyourpassword test