Shulou(Shulou.com)06/01 Report--

Mariadb log classification:

Query log: query log slow query log: slow query log query operation time exceeds the specified time error log: error log also contains startup and shutdown service information binary log (important): binary log relay log: reley log transaction log: transaction log

1. Query log (generally not open): record query operations

General_log=ON | OFFgeneral_log_file=HOSTNAME.log is saved in the equivalent path (if the hostname cannot be resolved, it is saved as localhost.log) log_output=TABLE | FILE | in what form does NONE record # set @ @ global.general_log=1 # command line opening mode, restart is invalid

two。 Slow query log (off by default, should be enabled in production): the query operation time exceeds the specified time

Show global variables like 'long_query_time'; View the default duration select @ @ GLOBAL.long_query_time;set global long_query_time=slow_query_log=ON | slow_query_log_file=HOSTNAME-slow.log is invalidated after OFF#log_slow_queries=xx.log version 5.6

3. Error log (generally open)

Event information output during ① mysqld startup and shutdown

Error message generated during the operation of ② mysqld

Log information generated when ③ event scheduler event Scheduler runs an event

Information generated when ④ launches slave server threads on the slave server in the master-slave replication architecture

Log_error=/var/log/mariadb/mariadb.log | OFFlog_warnings=1 | 0 whether to record a warning message

4. Binary log (important)

Function: used to generate a copy of data by replaying events in the log file

Record sql statements that cause or potentially cause data changes

Show {BINARY | MASTER} LOGS; View the list of binary log files in use managed by mariadb show {BINERY | MASTER} status; View the log file show binlog events in 'mysql-bin.000001' [from num] [limit rows] in use; view recorded events

Binary logging format: binlog_format

Record based on sql statement (SBR): statement

Line-based record (RBR): row

Mixed mode (MBR): mixed, leaving the system to decide which mode to base on (default)

# modify SET SESSION binlog_format at run time =

The binary log file is composed of:

Log file: mysql-bin. File name suffix binary format

Index files: mysql-bin.index text format

Server variables:

Log_bin=/PATH/TO/BIN_LOG_FILE | OFF#sql_log_bin= {ON | OFF} whether to record # session.sql_log_bin= {ON | OFF} controls whether "write" statements in a session are recorded in the log file (temporarily shut down during playback) the maximum size of a single binary log file in max_binlog_size=100M. Default is 1Gsync_binlog=0 or an integer greater than 0. Default 0 is better for asynchronous recording. It is up to the operating system to decide when to refresh the cache to the persistence setting. If this value is greater than 0, it specifies the number of binary log writes between flushing to disk. Setting 1 is the safest but most memory-intensive, and many MySQL DBA settings have a sync_binlog that is not the safest 1, but 100 or 0. At the expense of a certain amount of consistency, higher concurrency and performance can be achieved.

Mysqlbinlog: client command tool (view binary log directly)

-- start-datetime= "YYYY-MM-DD hh:mm:ss" start event-- stop-datetime= "YYYY-MM-DD hh:mm:ss" end time-j,-- where start-position=# starts-- where stop-position=# ends-- user,-- host,-- password

Binary log event format:

# at 553 # 160831 9:56:08 server id 1 end_log_pos 624 Query thread_id=2 exec_time=0 error_code=0 SET timestamp 1472608568, BEGIN / *! * /

The start of the event: # at 553

Date and time of the event: # 160831 9:56:08

Server id:server id 1 on which the event occurred

Where the event ends: end_log_pos 624

Type of event: Query

The ID: thread_id=2 of the thread in which the event was executed on the server where the event occurred

The time stamp of the statement and the time difference between writing it to the binary log file: exec_time=0

Error code: error_code=0

Set the timestamp of the event: SET timestamp 1472608568

Event content: BEGIN

5. Relay log:

Events synchronized from the binary log files of the primary server are recorded from the server

6. Transaction log:

Log files used by the transactional storage engine innodb to guarantee transaction characteristics:

Redo log

Undo log

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.