Shulou(Shulou.com)05/31 Report--

Today, I would like to talk to you about how to understand mysql binlog. Many people may not know much about it. In order to make you understand better, the editor has summarized the following contents for you. I hope you can get something from this article.

Mysql binlog

1. Type of mysql binlog:

According to the generation method, the binlog of mysql can be divided into three types, which are:

1) record-based replication RBR (Row Based Replication) or Row:

Pros: binlog does not record context-sensitive information about executed sql statements, but only needs to record what that record has been modified to. So the log content of rowlevel will clearly record the details of each line of data modification. And there is no problem that the calls and triggers of stored procedures, or function, and trigger can not be copied correctly in certain cases.

Disadvantages: when all executed statements are recorded in the log, they will be recorded as changes in each row, which may result in a large amount of log content, such as a update statement. If multiple records are modified, each change in binlog will be recorded, resulting in a large number of binlog logs, especially when executing statements such as alter table, each record will be changed due to table structure changes. Then each record in the table is recorded in the log.

2) statement-based replication, referred to as SBR (Statement Based Replication) or Statement:

Compared with row, it can improve performance and reduce log volume. However, this depends on the SQL of the application. Normally, the log volume generated by modifying or inserting the row format of the same record will be less than that generated by Statement, but considering that the ROW format will generate a large number of logs if there are conditional update operations, as well as whole table deletion, alter table and other operations, so when considering whether to use ROW format logs, we should consider how much the log volume will increase according to the actual situation of the application. And the IO performance problems.

Advantages: can work correctly for any statement, do not need to record the changes of each line, reduce the number of binlog logs, save IO, improve performance, and some statements are more efficient. For example, a query that updates GB data requires only a few dozen bytes of binary log.

Cons: binary logs can be large and unintuitive, so you can't use mysqlbinlog to view binary logs. And because only the execution statements are recorded, in order for these statements to run correctly on the slave, it is also necessary to record some information about the execution of each statement to ensure that all statements get the same results in the slave as they are executed on the masterside. In addition, the replication of mysql, like some specific function functions, slave can be consistent with the master will have a lot of related problems (such as sleep () function, last_insert_id (), and user-defined functions (udf) will have problems). In addition, stored procedures and triggers are a problem. Another problem is that statement-based replication must be serialized. This requires a lot of special code and configuration, such as InnoDB's next-key lock, etc. Not all storage engines support statement-based replication.

Statements that use the following functions cannot be copied either:

* LOAD_FILE ()

* UUID ()

* USER ()

* FOUND_ROWS ()

* SYSDATE () (unless the-- sysdate-is-now option is enabled at startup)

At the same time, INSERT. SELECT produces more row-level locks than RBR.

3) mixed MBR (Mixed Based Replication):

Since both methods cannot handle all cases well, MySQL 5.1 supports dynamic exchange before statement-based replication and record-based replication. You can control it by setting the session variable binlog_format.

2. Choose the format of Binlog log

Mysql uses Statement log format by default, and MIXED is recommended.

Due to some special uses, you can consider using ROW, such as synchronizing data changes through binlog logs, which will save a lot of related operations. Binlog data processing will be very easy, and parsing will be easy compared to mixed (of course, provided that the IO overhead caused by the increased log volume is tolerated).

Mysql's principle for selecting the log format: if the log format is directly manipulated by INSERT,UPDATE,DELETE, the log format is recorded according to the setting of binlog_format, and if it is done with management statements such as GRANT,REVOKE,SET PASSWORD, then the Statement mode is used anyway.

3. Binlog related parameters

As follows:

Binlog_format = MIXED / / binlog log format, which can be selected as mixed,statement,row

Log_bin = directory / mysql-bin.log / / binlog log name

Expire_logs_days = 7 / / binlog expiration cleanup time

Max_binlog_size = 100m / / binlog each log file size

Binlog-do-db = the name of the database to be backed up. If you back up multiple databases, you can set this option repeatedly.

Binlog-ignore-db = databases that do not need to be backed up are laborious. If you back up multiple databases, you can set this option repeatedly.

4. Binlog related documents

Mysql-bin.index:

Used to track which binary log files exist on disk. MySQL uses it to locate binary log files.

Mysql-relay-bin.index:

The function of this file is similar to that of mysql-bin.index, but it is for relay logs, not binary logs.

Master.info:

Save information about master. Do not delete it, otherwise, you will not be able to connect to master after slave restarts.

Relay-log.info:

Contains information about the current binary log and relay log in slave.

5. Parsing the contents of binlog logs

1) when viewing in the mysql command interface:

If it is statement mode:

Mysql > show binlog events in 'mysql-bin.000021'\ G

Intercept some query results:

* * 20. Row *

Log_name: mysql-bin.000021-> the binlog log file name of the query

Pos: 11197-- > pos starting point:

Event_type: Query-> event type: Query

Server_id: 1-- > identify which server is executed by

End_log_pos: 11308-> pos end point: 11308 (that is, the pos starting point of the downlink)

Info: use `zyyshop`; INSERT INTO `team2` VALUES (0meme 345 hands asdf8er5')

-> executed sql statement

* * 21. Row * *

Log_name: mysql-bin.000021

Pos: 11308-- > pos starting point: 11308 (that is, upstream pos end point)

Event_type: Query

Server_id: 1

End_log_pos: 11417

Info: use `zyyshop`; / *! 40000 ALTER TABLE `team2` ENABLE KEYS * /

* * 22. Row * *

Log_name: mysql-bin.000021

If it is row mode:

Mysql > show binlog events in 'mysql-bin.000005'\ G

Intercept some query results:

* 2. Row * *

Log_name: mysql-bin.000005

Pos: 120

Event_type: Query

Server_id: 3

End_log_pos: 191

Info: BEGIN

* 3. Row * *

Log_name: mysql-bin.000005

Pos: 191

Event_type: Table_map

Server_id: 3

End_log_pos: 234

Info: table_id: 87 (lxm.t)-> you can't see the executed sql statement here, only the table name

* * 4. Row *

Log_name: mysql-bin.000005

Pos: 234

Event_type: Update_rows

Server_id: 3

End_log_pos: 280

Info: table_id: 87 flags: STMT_END_F

* * 5. Row * *

Log_name: mysql-bin.000005

Pos: 280

Event_type: Xid

Server_id: 3

End_log_pos: 311

Info: COMMIT / * xid=249 * /

* 6. Row * *

Log_name: mysql-bin.000005

2) when viewing with the mysqlbinlog tool:

If it is statement mode:

# / usr/local/mysql/bin/mysqlbinlog / usr/local/mysql/data/mysql-bin.000013

Here is a snippet for analysis:

.

# at 552

# 131128 17:50:46 server id 1 end_log_pos 665POS-> execution time: 17 Query thread_id=11 exec_time=0 error_code=0 50 purl 46 position pos: 665

SET timestamp 1385632246

Update zyyshop.stu set name=' Li Si 'where id=4-> SQL executed

/ *! * /

# at 665

# 131128 17:50:46 server id 1 end_log_pos 692POS = 1454-> execution time: 17VOV 50RNAL 46th POS point: 692

.

Note: service number of server id 1 database host

End_log_pos 665 pos point

Thread_id=11 thread number

If it is row mode:

/ *! 50530 SET @ @ SESSION.PSEUDO_SLAVE_MODE=1*/

/ *! 40019 SET @ @ session.max_insert_delayed_threads=0*/

/ *! 50003 SET @ OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/

DELIMITER / *! * /

# at 4

# 161022 15:59:31 server id 3 end_log_pos 120 CRC32 0x45d9e7a2 Start: binlog v 4, server v 5.6.24-log created 161022 15:59:31

BINLOG'

YxwLWA8DAAAAdAAAAHgAAAAAAAQANS42LjI0LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaLn

2UU =

'/ *! * /

# at 120

# 161022 16:00:57 server id 3 end_log_pos 191 CRC32 0x37e11f27 Query thread_id=1 exec_time=0 error_code=0

SET timestamp 1477123257

SET @ @ session.pseudoplastics thread readreadability idling / session.pseudoplastics

SET @ @ session.foreign_key_checks=1, @ @ session.sql_auto_is_null=0, @ @ session.unique_checks=1, @ @ session.

SET @ @ session.sqlcards modewords 1073741824Universe

SET @ @ session.auto_increment_increment=1, @ @ session.

/ *!\ C utf8 * /! * /

SET @ @ session. Session. Session setting setting clientmakers 33 minutes. Session. Collationalization connections 33 minutes.

SET @ @ session. Session. LCC timetables namespace.

SET @ @ session.collationalization databases

BEGIN

/ *! * /

# at 191

# 161022 16:00:57 server id 3 end_log_pos 234 CRC32 0xe2ba303b Table_map: `lxm`.`t` mapped to number 87

# at 234

# 161022 16:00:57 server id 3 end_log_pos 280 CRC32 0xdae765d4 Update_rows: table id 87 flags: STMT_END_F

.

There is no clear text of the DML statement.

6. Common binlog log operation commands

@ View a list of all binlog logs

Mysql > show master logs; or show binary logs

@ View master status, that is, the numbered name of the last (latest) binlog log and its last action event pos end point (Position) value

Mysql > show master status

Refresh the log log and generate a newly numbered binlog log file from now on

Mysql > flush logs

Note: whenever the mysqld service is restarted, this command is automatically executed to refresh the binlog log; adding the-F option to mysqldump backup data will also refresh the binlog log.

@ reset (empty) all binlog logs

Mysql > reset master

7. How to view the contents of binlog log

1) View in the mysql command interface:

Mysql > show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

This statement can return the specified binlog log file into valid event lines, and can use limit to specify the starting offset of the pos point and query the number of entries.

Option resolution:

IN 'log_name' specifies the name of the binlog file to query (if not specified is the first binlog file)

FROM pos specifies the starting point of pos (if not specified, it is calculated from the first pos point of the whole file)

LIMIT [offset,] offset (0 if not specified)

Total number of row_count queries (all rows are not specified)

Example:

a. Query the first (earliest) binlog log:

Mysql > show binlog events\ G

b. Specify to query the mysql-bin.000021 file:

Mysql > show binlog events in 'mysql-bin.000021'\ G

c. Specify to query the mysql-bin.000021 file, starting with pos point: 8224:

Mysql > show binlog events in 'mysql-bin.000021' from 8224\ G

d. Specify to query mysql-bin.000021 this file, starting from pos point: 8224, query 10 items

Mysql > show binlog events in 'mysql-bin.000021' from 8224 limit 10\ G

e. Specify to query the mysql-bin.000021 file, starting from pos point: 8224, offset by 2 lines, query 10 items

Mysql > show binlog events in 'mysql-bin.000021' from 8224 limit 2jol 10\ G

2) use mysqlbinlog:

Binlog is a binary file. Ordinary file viewers such as cat, more, vi, etc., cannot be opened. You must use your own mysqlbinlog command to view it. The binlog log is in the same directory as the database file.

If you report an error when using the mysqlbinlog command in versions below MySQL5.5, add the "--no-defaults" option.

A) if it is a binary log file in ROW mode, in order to see what kind of sql statement mysql has executed, you need to use the-v (--verbose) option, which reconstructs the line event into a pseudo-SQL statement that is commented out. If you want to see more detailed information, you can give this option twice, such as-vv, so you can include some data types and meta-information of the comment content. For example:

Mysqlbinlog-v mysql-bin.000001

Mysqlbinlog-vv mysql-bin.000001

B) mysqlbinlog and can read binary log files from the remote server through the-- read-from-remote-server option, which requires some external connection parameters, such as-- host,--password,-- port,--user,--socket,--protocol, etc., which are valid only if-- read-from-remote-server is specified.

C) whether it is a local binary log file or a binary log file on a remote server, whether it is a binary log file in line mode, statement mode or mixed mode, it can be directly applied to MySQL Server for point-in-time, location or database-based recovery after being parsed by the mysqlbinlog tool.

Common parameters are:

1)-- database=db_name,-d db_name

This parameter causes mysqlbinlog to output only log events that occur when the specified db_name is selected as the default database by the use command from the local binary log. The behavior is similar to mysqld's-- binlog-do-db command. If this parameter is specified multiple times, only the last specified content is valid. The specific impact of the parameter depends on the binary log format, and consistency can only be guaranteed when using the log format of line mode. In statement-based or mixed-mode binary log formats, there may be cross-library updates that lead to different behaviors of database parameters, which cannot guarantee data consistency. For example:

Mysqlbinlog mysql-bin.000001-d testDB | mysql- uusername-p

2)-force-read,-f

After using this parameter, the mysqlbinlog tool will print out warning when reading unrecognized log events, ignore the event and continue execution, and mysqlbinlog will stop without this parameter.

Mysqlbinlog mysql-bin.000001-d testDB-f | mysql- uusername-p

3)-- no-defaults

Prevent the mysqlbinlog tool from reading parameters from any configuration file, except .mylogin.cnf (to save passwords securely)

Mysqlbinlog mysql-bin.000001-d testDB-f-- no-defaults | mysql- uusername-p

4)-- start-datetime=datetime and-- stop-datetime=datetime

These two parameters are used to specify the start and end time points of the recovery, either together or separately, or mixed with-- start-position,--stop-position.

Mysqlbinlog mysql-bin.000001-d testDB-f-- no-defaults-- start-datetime=datetime-- stop-position=NNNNNN | mysql- uusername-p

5)-- start-position=N,-JN and-- stop-position=N

The above set of parameters are used to specify the start and end positions of the recovery, which can be given together or alone or mixed with-- start-datetime,--stop-datetime

Mysqlbinlog mysql-bin.000001-d testDB-f-- no-defaults-- start-position=NNNNNN-- stop-datetime=datetime | mysql- uusername-p

D) if more than one binary log file needs to be restored, the safe way is to execute multiple binaries at the same time.

Mysqlbinlog mysql-bin.000001 mysql-bin.000002 mysql-bin.000003-- start-position=NNNNNN-- stop-datetime=datetime | mysql- uusername-p

Or

Mysqlbinlog mysql-bin.00000 [1-3]-- start-position=NNNNNN-- stop-datetime=datetime | mysql- uusername-p

When multiple binary log files are executed simultaneously,-- start-position and-- stop-position apply only to the first listed binary log file and the last listed binary log file, respectively.

Of course, you can also first import the output of multiple binary log files to the same .sql file and finally execute the .sql file (for cases with a small number of logs).

8. Application of binlog:

You can use binlog to recover misoperated data.

Case study:

1) full backup

Mysqldump-uroot-p123456-lF-- log-error=/root/myDump.err-B zyyshop > / root/BAK.zyyshop.sql

Using the-F option when backing up means that the log log is refreshed at the beginning of the backup, and a new binlog log is generated to record the "add, delete and modify" operation of the database after the backup.

2) after the backup, the business has made a large number of additions, deletions, modifications and queries to the database. Then a table in the database was deleted by mistake. Check the last binlog log immediately and record the key pos point, that is, where the operation caused the corruption of the database. Then flush logs, ask mysql to restart the new binlog logging file. In theory, the old binlog log will not be written again at this time. At this point, back up the old binlog log.

3) read the old binlog log and analyze the problem.

First, use the mysqlbinlog command to read the binlog log:

Mysqlbinlog / usr/local/mysql/data/mysql-bin.000023

Method 2, view in the mysql server:

Mysql > show binlog events in 'mysql-bin.000023'

Find the exact pos point where the table was mistakenly deleted in the output.

4) restore with full backup first:

Mysql-uroot-p123456-v < / root/BAK.zyyshop.sql

5) recover data from the binlog log:

Mysqlbinlog mysql-bin.0000xx | mysql- u username-p password database name

The so-called recovery is to ask mysql to re-execute the sql statements saved in the specified paragraph in the binlog log one by one.

After reading the above, do you have any further understanding of how to understand mysql binlog? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.