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

This article mainly explains "what are the knowledge points related to mysql binlog". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what are the knowledge points related to mysql binlog"?

1.

-logical log, which records all changes within the database (changes in sql statement lines)

-server layer log, binlog records not only changes in innodb, but also changes in myisam storage engine.

-innodb redo is the storage engine layer, not the same layer as binlog. Redo only records the changes of innodb.

-binlog does not record queries such as select show, but records database changes such as dml ddl.

The role of 2.binlog

-you can view mysql changes

-replication architecture of mysql (master-slave)

-mysql backup and recovery (data can be completed or rolled back through binlog)

Location of 3.binlog

Log_bin = / home/mysql3306/mysql3306/mysql-bin

# binlog path + binlog prefix name such as: mysql-bin.000001

4. Brief introduction of related parameters

(1) log-bin

Determines the name of msyql's binlog, and the generated binlog name is mysql-bin.000001

(2) binlog_format

Specify the format of binlog. Binlog has three formats: statement,row and mixed. Statement is used by default, and row format is recommended.

(3) expire_logs_days

It is recommended that you set 7D-30D according to your business during the expiration time.

(4) binlog_do_db

This parameter indicates that only the binary log of the specified database is recorded.

(5) binlog_ignore_db

This parameter indicates that the binary log of the specified database is not recorded.

(6) .sync_binlog (binlog listing strategy)

0Murray-swipe the disk every second

1Murray-flush the disk every time a transaction is committed

NMMI-(100 200 500) the order is closed every n times.

Innodb_flush_log_at_trx_commit = 1

Sync_binlog=1

Double 1 configuration, the database security is the highest, will not lose transactions.

(7) .binlog_checksum is {CRC32 | NONE}

When you write a binlog, the content is generated into check bits and then stored in the binlog.

By default, the server records the length of the event, as well as the event itself, and uses it to verify that the event is written correctly. You can also set the binlog_checksum system variable to have the server write a checksum for the event.

(8). Max_binlog_size

The maximum value of binlog file, default and maximum is 1GB, and the size of binary file cannot be strictly limited.

(9). Max_binlog_cache_size

Represents the maximum cache memory size that binlog can use

When we execute a multi-statement transaction, the memory used by all session exceeds the value of max_binlog_cache_size

There will be an error: "Multi-statement transaction required more than 'max_binlog_cache_size' bytes ofstorage"

(10). Binlog_cache_size = 4m

Thread-level parameters, cannot be set too large

5.binlog format

(1) statement

Mysql > show variables like'% binlog_format%'

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | STATEMENT |

+-+ +

Execute the statement:

Insert into wwj.t1 values (2meme mxt2')

Insert into wwj.t1 values (3meme mxt3')

Insert into wwj.t1 values (4 ~ mxt4')

View binlog

Click (here) to collapse or open

Mysql > show binlog events

+-- +

| | Log_name | Pos | Event_type | Server_id | End_log_pos | Info | |

+-- +

| | mysql-bin.000001 | 4 | Format_desc | 12023307 | 12023307 | Server ver: 5.7.21-log, Binlog ver: 4 |

| | mysql-bin.000001 | 123 | Previous_gtids | 12023307 | 154th |

| | mysql-bin.000001 | 154 | Anonymous_Gtid | 12023307 | 219 | SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS' |

| | mysql-bin.000001 | 219 | Query | 12023307 | 293 | BEGIN |

| | mysql-bin.000001 | 293 | Query | 12023307 | 398 | insert into wwj.t1 values (13meme mxt3') |

| | mysql-bin.000001 | 398 | Xid | 12023307 | 429 | COMMIT / * xid=275 * / | |

| | mysql-bin.000001 | 429 | Anonymous_Gtid | 12023307 | 494 | SET @ @ SESSION.GTID_NEXT= 'ANONYMOUS' |

| | mysql-bin.000001 | 494 | Query | 12023307 | 568 | BEGIN |

| | mysql-bin.000001 | 568 | Query | 12023307 | 673 | insert into wwj.t1 values (14recoverymxt4') |

| | mysql-bin.000001 | 673 | Xid | 12023307 | 704 | COMMIT / * xid=276 * / | |

+-- +

Log_name: binlog file name

The position point of the Pos:binlog, the number of bytes written to the binlog log, is the size of the pos. Each time a new binlog is generated, the pos point is reset, but within a binlog, the pos is monotonously incremented.

Xid: distributed transactions ID,Innodb supports distributed transactions, Innodb supports distributed transactions within a single instance, server tier and storage engine layer, and multi-node distributed transactions (ndb)

Server_id: instance server_id

Parsing binlog tools

/ usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > / tmp/all.bin

Click (here) to collapse or open

# at 293

# 180325 4:14:20 server id 12023307 end_log_pos 398 CRC32 0xb9d2f949 Query thread_id=14 exec_time=0 error_code=0

SET timestamp 152192460

Insert into wwj.t1 values (13meme mxt3')

/ *! * /

# at 398

View the location of the current binlog pos point

Mysql > show master status

ERROR 2006 (HY000): MySQL server has gone away

No connection. Trying to reconnect...

Connection id: 15

Current database: * * NONE * *

+-+

| | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | |

+-+

| | mysql-bin.000001 | 704 |

+-+

Apply binlog

/ usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 > / tmp/all.bin

Mysql-uroot-proot-S / tmp/mysql3306.sock

< /tmp/all.bin mysql >

Source / tmp/all.bin

Matters needing attention in applying binlog

If you apply multiple binlog

Wrong practice

Mysqlbinlog mysql-bin.000001 | mysql- S-u-p

Mysqlbinlog mysql-bin.000002 | mysql- S-u-p

Correct approach

Mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql- S-u-p

Or

Mysqlbinlog mysql-bin.000001 mysql-bin.000002 > / tmp/all.bin

Mysql-S-u-p

< /tmp/all.bin (2).row mysql>

Show variables like "binlog_format"

+-+ +

| | Variable_name | Value |

+-+ +

| | binlog_format | ROW |

+-+ +

Row format binlog:row format binlog records the data before and after the row of mysql is changed

For example, in stament format, update data records update statements.

And in row format, before and after the change of the recorded row, if the update1000 data, record a thousand rows of data change

Mysqlbinlog-v mysql-bin.000001 > / tmp/all.bin add pseudo sql

Mysqlbinlog-vv mysql-bin.000001 > / tmp/all.bin add data types

Click (here) to collapse or open

# at 334

# 180325 5:51:04 server id 12023307 end_log_pos 458 CRC32 0x1be59376 Delete_rows: table id 108 flags: STMT_END_F

BINLOG'

SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM=

SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt

EHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA

AARteHQ0dpPlGw==

'/ *! * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room1 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2 / VARSTRING / * meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room2 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt2' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room3 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt3' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room4 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt4' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room5 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt2' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room6 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt3' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room7 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt4' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1mm 13 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt3' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# DELETE FROM `wwj`.`t1`

# WHERE

# @ 1room14 / * INT meta=0 nullable=0 is_null=0 * /

# @ 2roommxt4' / * VARSTRING (180) meta=180 nullable=1 is_null=0 * /

# at 458

Other analytical methods

Parsing data without rows

/ usr/local/mysql/bin/mysqlbinlog-- base64-output=DECODE-ROWS-vv mysql-bin.000001

If you need to restore to the database after binlog parsing, you need to be careful not to add-- base64-output=DECODE-ROWS.

/ usr/local/mysql/bin/mysqlbinlog

Start-datetime start time

Stop-datetime stop time

/ usr/local/mysql/bin/mysqlbinlog-- start-datetime= "2017-04-20 9:00:00"-- stop-datetime= "2017-07-20 18:00:00" / home/mysql3306/mysql3306/mysql-bin.000001 > / tmp/mysql_binlog.sql

Position of the start-position start time

Position of stop-position stop time

Advantages of row format:

-record row changes, rather than SQL, to maximize data security

-row format can be used for flashback (data recovery)

In an online environment, binlog in row format is recommended

-row format update delete will cause a great pressure on the disk network

(3) mixed

In general, the statment format is used, and in special cases (resulting in principal inconsistency), the record is changed to use the row format.

There are many mixed formats in the old version of bug.

Not recommended

6. Clean up binlog

Reset master; / / Delete the binlog of master, do not use it online

Purge master logs before '2012-03-30 17 20 00; / / Delete binlog log files in the log index before the specified date

Purge master logs to 'binlog.000002'; / / Deletes the binlog log file in the log index of the specified log file

If you want to delete binlog directly

Rm binlog.000002

(do not delete the binlog currently in use, that is, the binlog with the highest number)

[root@namenode mysql3306] # cat mysql-bin.index will not update automatically

/ home/mysql3306/mysql3306/mysql-bin.000001

/ home/mysql3306/mysql3306/mysql-bin.000002

If the master-slave replication is deleted, it is easy to report an error from the library (ensure that the slave database will no longer synchronize the data of the binlog)

The difference between 7.binlog and redo

-redo physical logic log physics: data page logic: data page changes

Binlog is the SQL or line change of logical logging

-redo is the innodb storage engine layer

Binlog is the server layer

-redo writes files all the time

Binlog is the write of commit transaction (specific writing strategy is related to specific parameters)

At this point, I believe you have a deeper understanding of "what are the relevant knowledge points of mysql binlog?" you might as well come to the actual operation! Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.