Mysql log binlog introduction

log-bin=master-bin #Log file prefix, such as master-bin.000001log-bin-index=master-bin.index #Record the name of the log file, the content is the name of the log file defined abovebinlog-format=ROW #binlog log recording mode or format

• Restart the MySql database service, and use the following command to check whether binlog is enabled:

show variables like< /span> 'log_%';

• binlog log file generation: the log file starts with the prefix in the configuration, and then is delivered in turn Add, for example, from master-bin.000001 to master-bin.00000n.
  • When the MySql server is stopped or restarted, MySql will generate a new log file when restarting.
  • Use the flush logs command. In addition, you can use the reset master command to clear the binlog log.
  • When the binlog file size exceeds the upper limit configured by the max_binlog_size system variable.
• The binlog log format classification:
  • Statement: Based on the statement mode, every sql that will modify the data will be recorded in the binlog. Advantages: There is no need to record the changes of each line, reducing the amount of binlog, saving IO, and improving performance. Disadvantages: Since only the executed statements are recorded, in order for these statements to run correctly on the slave, it is necessary to record some relevant information when each statement is executed to ensure that all statements can be obtained on the slave and executed on the master side. The same result.
  • Row: Based on the row mode, it does not record the context-related information of the SQL statement, but only saves which record has been modified. Advantages: The context-related information of the executed sql statement can not be recorded in binlog, only what the record has been modified to. Therefore, the log content of row will record the details of each row of data modification very clearly. And there will not be the problem of stored procedures, or functions, and trigger calls and triggers that cannot be copied correctly under certain circumstances. Disadvantages: All executed statements will be recorded in each line when they are recorded in the log. The modification is recorded, which may generate a large amount of log content.
  • Mixed: Mixed mode is actually a combination of Statement and Row. In Mixed mode, the general statement modification uses the statment format to save the binlog. For some functions, the statement cannot complete the master-slave replication operation. The row format is used to save the binlog. MySql will distinguish and treat records according to each specific SQL statement executed. The log format is to choose one between Statement and Row.
• Check the binlog log format with the following commands:

SHOW VARIABLES LIKE 'binlog_format';

< /tbody>

• Use the following command to query all binlog log files

SHOW BINARY LOGS;

• Use the following command to view a specific binlog log file: (The file is Binary bytes, which can only be opened through MySql tools)

< span class="k">SHOW BINLOG EVENTS IN 'master-bin.000001';#PURGE MASTER LOGS TO 'master-bin.000001'; #This statement deletes a specific log file

This is the statement mode log, the main record is to add a piece of data to t_user

< th>Info

< td >1

< /tr>

This is the log in Row mode. It also adds a row of data to t_user

< th>Event_type

???? The above is just simple Introduce the generation of MySql’s binlog log and viewing and waiting. A more detailed introduction to the content of the binlog log will be described in detail in a future blog.

Original text: Big column MySql log binlog introduction

 log-bin=master-bin #Log file prefix, such as master-bin.000001log-bin-index=master-bin.index #Record the name of the log file, the content is defined above The name of the log filebinlog-format=ROW #binlog log record Mode or format

log-bin=master-bin #Log file prefix, such as master-bin.000001log-bin-index=master-bin.index #Record the name of the log file, the content is the name of the log file defined abovebinlog-format=ROW #binlog log recording mode or format

< /p>

show variables like 'lo g_%';

show variables like 'log_%';

SHOW VARIABLES LIKE 'binlog_format' ;

SHOW VARIABLES  LIKE 'binlog_format';

< /p>

SHOW BINARY LOGS;

SHOW BINARY LOGS;< /pre>
 
SHOW BINLOG EVENTS IN 'master-bin.000001';# span>PURGE MASTER LOGS TO  'master-bin.000001'; #< span class="err">This statement deletes a specific log file


SHOW BINLOG EVENTS IN 'master-bin.000001';#PURGE  MASTER LOGS TO 'master-bin.000001'; #This sentence is deleted A specific log file