[Architecture] MySQL Log File-binary log: binary log & binary log Index

Source: Internet
Author: User

First find the two files,

Rocky@localhost> show variables like 'datadir';+---------------+-------------------------+| Variable_name | Value                   |+---------------+-------------------------+| datadir       | /home/mysql/mysql/data/ |+---------------+-------------------------+1 row in set (0.00 sec)Rocky@localhost> system ls -l /home/mysql/mysql/data/total 31804-rw-rw---- 1 mysql dba 18874368 Apr 14 01:27 ibdata1-rw-rw---- 1 mysql dba  5242880 Apr 15 20:15 ib_logfile0-rw-rw---- 1 mysql dba  5242880 Apr 12 12:37 ib_logfile1-rw-rw---- 1 mysql dba        6 Apr 15 20:15 localhost.localdomain.piddrwxr-xr-x 2 mysql dba     4096 Apr 12 12:35 mysql-rw-rw---- 1 mysql dba    27828 Apr 12 12:35 mysql-bin.000001-rw-rw---- 1 mysql dba   997144 Apr 12 12:35 mysql-bin.000002-rw-rw---- 1 mysql dba      150 Apr 12 13:17 mysql-bin.000003-rw-rw---- 1 mysql dba    27025 Apr 12 12:56 mysql-bin.000004-rw-rw---- 1 mysql dba   997144 Apr 12 12:56 mysql-bin.000005-rw-rw---- 1 mysql dba    27025 Apr 12 12:59 mysql-bin.000006-rw-rw---- 1 mysql dba   997144 Apr 12 12:59 mysql-bin.000007-rw-rw---- 1 mysql dba      126 Apr 12 13:18 mysql-bin.000008-rw-rw---- 1 mysql dba      126 Apr 13 00:14 mysql-bin.000009-rw-rw---- 1 mysql dba     1346 Apr 13 20:20 mysql-bin.000010-rw-rw---- 1 mysql dba      150 Apr 13 20:23 mysql-bin.000011-rw-rw---- 1 mysql dba      107 Apr 13 20:23 mysql-bin.000012-rw-rw---- 1 mysql dba      107 Apr 13 21:56 mysql-bin.000013-rw-rw---- 1 mysql dba      126 Apr 13 22:50 mysql-bin.000014-rw-rw---- 1 mysql dba      229 Apr 14 01:27 mysql-bin.000015-rw-rw---- 1 mysql dba      107 Apr 15 20:15 mysql-bin.000016-rw-rw---- 1 mysql dba      304 Apr 15 20:15 mysql-bin.index

(I) Definition

All modifications recordedDataQuery, including: query statement, execution time, resources consumed, and related transaction information

(Ii) Role

① Recovery
② Copy

(Iii) Related Parameters

There are many parameters. Here we will focus on one:

Binlog_format

Description: Log format
Nature: Dynamic parameter, session level, or global level
Set @ session. binlog_format = 'row' or
Set global binlog_format = 'row'
Value:
● Statement

Copy the SQL apply statement based on this format, similar to the Oracle logic standby

● Row

Replication Based on this format, redo apply similar to Oracle physical standby

Enable rowAdvantages:
① If we set the transaction isolation level of InnoDB to read commited and add row, we can get better results.Concurrency
② Better recovery and ReplicationReliability
Disadvantages:
① Rapid expansion of binary logs and high disk space overhead
② High network bandwidth requirements during replication


● Mixed

This is the default format, relatively tricky, usually running with statement, sometimes running with row

Rocky@localhost> show variables like 'binlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | MIXED |+---------------+-------+1 row in set (0.00 sec)

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.