mysql-binary Log

MySQL---binary log

Records all modifications to the MySQL database, including additions and deletions and modification events to the table structure

Binary Log on

#是否启用了日志mysql> show variables like ‘log_bin‘; +---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | OFF   |+---------------+-------+[mysqld]log-bin [=DIR \ [filename]]log_bin=mysql-bin 名字可以随便起(mysql-bin)

Format

binlog_format={STATEMENT|ROW|MIXED}

1. Statement (statement): The default record format, based on segment
2. Row: Defines not the data itself but what the data in this row is
3. Mixed mode (mixed): Alternating lines and statements, self-judging by MySQL server

Segment-based log format benefits

1. Relatively small amount of log records, saving disk and network IO
2. Modify or insert only one record, the row format generates less log volume than the segment generates

Disadvantages

1. Context information must be logged to ensure that the statement executes the same result from the server as the primary server
2. A specific function such as UUID (), user () is not able to replicate the non-deterministic function, may cause MySQL replication of the primary and standby server data inconsistent

Operation

like ‘binlog_format‘;mysql> set session binlog_format=statement;mysql> show binary logs; mysql> flush logs;

Row log Format

1. Default format for version 5.7
2. Row format avoids slave-to-master inconsistencies in MySQL replication

In case the same SQL statement modifies 10,000 data

• Segment-based log format will only record this SQL statement
• Row-based logs have 10,000 records that record data modifications for each row, respectively

Advantages

1. Make MySQL master-slave replication more secure
2. Modification of each row of data is more efficient than segment-based replication

When the data in the database is modified by mistake and no backup can be restored, we can analyze the binary log and reverse process the data modification operation recorded in the log to achieve the purpose of recovering the data.

Disadvantages

1. Record log volume is large; binlog_row_image=[full| minimal| NOBLOB]

Mixed log format

binlog_format=MIXED

Characteristics

• Depending on the SQL statement, the system determines the selection based on the segment and row-based log format
• The size of the data amount is determined by the SQL statement executed

Effect of binary log format on replication based on SQL statement (statement) replication (SBR) Benefits

1. Generate less log volume and save network transfer IO
2. Does not force the table definition of the master-slave database to be exactly the same
3. More flexibility than row-based replication

Disadvantages

1. For non-deterministic events, the consistency of master-slave replication data is not guaranteed
2. Modifications to stored procedures, triggers, and custom functions can also result in inconsistent data
3. Requires more row locks than row-based replication when executed from the top

Row-based replication (PBR) Benefits

1. Replication that can be applied to any SQL including non-deterministic functions, stored procedures, etc.
2. Can reduce the use of database locks
3. Better consistency of master-slave data

Disadvantages

1. Requires the same table structure as the master-slave database, or it may interrupt replication
2. Unable to execute the trigger separately from the top

View binary Log Database session view

#显示当前服务器使用的二进制文件及大小mysql > Show binary logs#显示主服务器使用的二进制文件及大小mysql > Show master Logs;#当前使用的二进制文件及所处位置mysql > Show Master Status#lush logs generally only scrolls the trunk log and binary log mysql> flush logs; #二进制日志的记录位置, usually mysql> showmaster status for the last event execution end time; # ... Database additions and deletions change  #观察二进制位置的变化mysql > Show Master Status; #查看所有的二进制信息mysql > Show Binlog events\g;  #查看指定日志的二进制信息mysql > Show Binlog events in  ' mysql-bin.000011 ';  #从指定的事件位置开始mysql > Show Binlog events in  ' mysql-bin.000011 ' from 190; #指定偏移量 (not statement, event) mysql> Showbinlog events in  mysql-bin.000011 ' from 190 limit 3;   

Command line Mysqlbinlog

#必须在数据目录下mysqlbinlog mysql-bin.000017#导出此数据库的信息  mysqlbinlog mysql-bin.000017 > /tmp/a.sql#导入此数据库的信息mysql < a.sqlmysqlbinlog -vv 日志名

Binary log Removal

Not cleaning for a long time can waste a lot of disk space, but it can cause a database crash to fail to recover after the deletion.

To delete a binary log first copy it and the database backup

#删除所有的二进制日志（不可效仿）mysql> reset master; #根据文件或时间点来删除二进制日志# TO ‘log_name‘ 删除文件之前的所有文件 mysql> PURGE { BINARY | MASTER } LOGS {TO ‘log_name‘ | BEFORE datetime_expr }#使用时间来删除二进制日志mysql> PURGEBINARY LOGS BEFORE ‘13-10-19 10:26:36‘; 

mysql-binary Log