MySQL log feature detailed (2)

Binary log: (multiple threads are contention and can be written to the buffer first)

Records the current server's data modifications and the possibility of potentially affecting data modification statements

Default in Data directory, typically Mysql-bin

[[Email protected] data]# file mysql-bin.000001 mysql-bin.000001:mysql replication Log copy Log

You can use the Mysqlbinlog command to view

TIME History

Offset position position

650) this.width=650; "Src=" Http://s1.51cto.com/wyfs02/M02/8B/E0/wKiom1hbVoajfNDGAACYEEjh4nI581.png-wh_500x0-wm_3 -wmp_4-s_2945073666.png "title=" 1.png "alt=" Wkiom1hbvoajfndgaacyeejh4ni581.png-wh_50 "/>

MySQL > Show master status displays the primary server state to view the currently used binary log and the next event starting at the location based on MySQL [(none)]> Show Master status; +------------------+----------+--------------+------------------+| File | Position | binlog_do_db | binlog_ignore_db |+------------------+----------+--------------+------------------+|      mysql-bin.000003 |              107 |                  | |+------------------+----------+--------------+------------------+1 row in Set (0.00 sec)

Log scrolling:

1, can be defined by size

2, can be defined by time

3. Restart the MySQL server

4. Perform flush logs (manual scrolling)

650) this.width=650; "Src=" Http://s5.51cto.com/wyfs02/M00/8B/E0/wKiom1hbY2fCmsTiAABCmBOMV-s281.png-wh_500x0-wm_3 -wmp_4-s_2666151624.png "title=" 2.png "alt=" Wkiom1hby2fcmstiaabcmbomv-s281.png-wh_50 "/>

View binary logs currently in use

MariaDB [(none)]> Show binary logs;+------------------+-----------+| Log_name | File_size |+------------------+-----------+|      mysql-bin.000001 | 1043 | |       mysql-bin.000002 | 245 | | ...............       3 | 245 |+------------------+-----------+2 rows in Set (0.00 sec)

The function of binary log

Instant (Point-in-time) recovery:

Copy:

[[Email protected] data]# file Mysql-bin.index mysql-bin.index:ascii text[[email protected] data]# cat mysql-bin.index./ mysql-bin.000001./mysql-bin.000002./mysql-bin.000003./mysql-bin.000004

Purge binary logs

Mysql > Help Purgepurge {BINARY | MASTER} LOGS {to ' log_name ' | Before datetime_expr (before a certain time)}

View MySQL binary logs

MySQL > Help show BINLOG events;mysql > Show BINLOG EVENTS; MariaDB [(None)]> show Binlog events\g*************************** 1. Row *************************** log_name:mysql-bin.000001 pos:4 event_type:format_desc Server_id:1end_log_po s:245 info:server Ver:5.5.36-mariadb-log, Binlog ver:4

Service-id: Server Identity identification

(Avoid repeating the ' modify ' class statement in the MySQL binary log)

MySQL Record binary log format

MariaDB [(None)]> show variables like ' binlog_format '; +---------------+-------+| variable_name | Value |+---------------+-------+| Binlog_format | Row |+---------------+-------+1 row in Set (0.00 sec)

Statement based: statement

Row-based (data): row

update tb1 set salary=salary+1000; -Large data volumes

Mixed mode: Mixed

Specify to start reading from that location

MariaDB [(None)]> show Binlog events in ' mysql-bin.000001 ' from 591\g

You can also use

[Email protected] data]# Mysqlbinlog--start-position=24223 mysql-bin.000001

Mysqlbinlog command (Client command)

--start-time--stop-time--start-position--stop-position

Binary log file Content format

Date and time when the event occurred

Server ID

Event End Location

Type of Event

The thread ID number of the original server when this event was generated MySQL [(none)]> show processlist;

The time difference between the statement timestamp and the write binary file, in seconds

Error code, 0 indicates normal execution

Event Content

Event location (equivalent to where the next event starts)

  

mysql [(none)]> show global variables like  '%log% ';   +------------- ------------------------------------------------------------------+  | log_bin = {on| OFF}; can also be a file path                              |  | log_bin_ trust_function_creators   whether to block any storage functions                           |  |  sql_log_bin = {on| off}  whether the current session will enter binary files into the binary file   |  | sql_log_off  whether the general query log is logged into the query log   |   | sync_binlog  sync buffer in binary to hard disk time, 0 not based on time synchronization, only sync  |  | binlog_format at event commit  = {|statement|row|mixed}  |  | max_binlog_cache_size  mysql binary LogBuffer size, only used to cache the statement      |  | max_binlog_stmt_cache_size  statement buffer size of the transaction class, That is, transaction class and non-transactional class common size         |  | max_binlog_size  The upper limit of the binary log file, in bytes    (exceeding the upper limit will scroll)               |                |   +-------------------------------------------------------------------------------+ Recommendation: Do not put binary logs and data files on the same device

Plan ahead

[Email protected] data]# mkdir/mysql/binlog[[email protected] data]# Vim/etc/my.cnflog-bin=/mysql/binlog/master-bin [[email protected] data]# chown-r mysql:mysql/mysql/binlog/restart mysqld server [[email protected] data]# Cd/mysql/binlog/[[ema Il protected] binlog]# lsmaster-bin.000001 Master-bin.index

Can temporarily control binary writes via Sql_log_bin

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/E0/wKiom1hbY7Hx1fXmAABZitaGvjc480.png-wh_500x0-wm_3 -wmp_4-s_1494540742.png "style=" Float:none; "title=" 3.png "alt=" Wkiom1hby7hx1fxmaabzitagvjc480.png-wh_50 "/>

650) this.width=650; "Src=" Http://s3.51cto.com/wyfs02/M02/8B/DD/wKioL1hbY7GgSo_rAABdAH63bS4908.png-wh_500x0-wm_3 -wmp_4-s_945158039.png "style=" Float:none; "title=" 4.png "alt=" Wkiol1hby7ggso_raabdah63bs4908.png-wh_50 "/>

Relay log: (related to master-slave replication)

For non-slave servers, the relay log is not enabled

Relay_log indicates a file path

Relay_log_index

Relay_log_purge = {on| OFF} automatically cleans up the trunk logs that are not needed

Relay_log_space_limit Whether the trunk size is limited

This article is from the "Homecoming" blog, make sure to keep this source http://sixijie123.blog.51cto.com/11880770/1885021

MySQL log feature detailed (2)