Mysql log binlog introduction

???? MySql will generate some logs during the running process, and now for the consistency of the master-slave data synchronization problem, as well as database recovery, database optimization, database exception investigation and other actions are all based on MySql The generated log to achieve.

  • MySql log type
    • Error log: Record abnormal information during MySql operation.
    • General query log: record what MySql is doing, such as client connection and disconnection, each Sql Statement record information from the client, etc.
    • Slow statement log: record some queries Slow SQL statement, when we find that the system is running slowly, we can turn it on, and then find the slow statement for database tuning, etc.
    • binlog log: The record contains some events, these events describe the database Changes, such as table creation, data changes, etc.

???? Next is the binlog log. The main application scenarios of the log are the synchronization of the master-slave database and the data recovery. .

binlog introduction

  • To enable binlog logging, you need to add configuration in the MySql configuration file my.ini:
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

< /div>

  • Restart the MySql database service, and use the following command to check whether binlog is enabled:
show variables like< /span> 'log_%';

Variable_name Value
log_s low_queries ON
  • 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';

< th>Variable_name

< /tbody>

binlog_format ROW
  • Use the following command to query all binlog log files

Log_name File_size
master-bin.000001 392
master-bin.000002 126
mas ter-bin.000003 324
  • 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>

Log_name Pos Event_type Server_id End_log_pos
master-bin.000001 4 Format_desc 107 Server ver: 5.5.41-enterprise-commercial-advanced-log, Binlog ver: 4
master-bin.000001 107 Query 1 181 BEGIN
master-bin.000001 181 Intvar 1 209 INSERT_ID=2
master-bin.000001 209 Query 1< /td>

346 use demobinlog; insert into t_user(name,age,isman,remark) values(‘hhh’,12 ,1,’kkkkk’)
master-bin.000001 346 Xid 1 373 COMMIT /* xid=16 */
master-bin.000001 373 Stop 1 392 ?

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

< th>Event_type

Log_name Pos Server_id End_log_pos Info
master-bin.000003 4 Format_desc 1 107 Server ver: 5.5.41-enterprise-commercial-advanced-log, Binlog ver: 4
master-bin.000003 107 Query 1 181 BEGIN< /td>
master-bin.000003 181 Table_map 1 240 table_id: 33 (demobinlog.t_user)
master-bin.000003 240 Write_rows 1 297 table_id: 33 flags: STMT_END_F
master-bin.000003 297 Xid 1 324 COMMIT /* xid=7 */

???? 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 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

Leave a Comment

Your email address will not be published.