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

Journal

Transaction log: transaction log

Error log: error log

Query log: query log

Slow log: slow query log

Binary log: binary log

Relay log: reley log

Command log: ~ / .mysql_history to record mysql commands that have been typed by each terminal

Transaction log

Transaction log: transaction log

Transactional storage engine manages and uses itself

Redo log

Undo log

Innodb transaction log related configuration:

Show variables like'% innodb_log%'

Innodb_log_file_size 5242880 each log file size (it is recommended to increase the size according to production conditions, such as 1G, otherwise large transactions may be committed directly and cannot be rolled back)

Number of innodb_log_files_in_group 2 log group members (it is recommended to adjust more according to production conditions, such as 3)

Innodb_log_group_home_dir. / transaction file path (it is recommended that transaction logs and data be stored in different directories)

Relay log: relay log

In the master-slave replication architecture, the slave server is used to hold events read from the binary logs of the master server.

Optimize table tbl_name organizes the table, such as making a large number of modifications | the size of the table file does not decrease after deletion, but can be reduced after executing this command.

Error log

Error log

Event information output during mysqld startup and shutdown

Error message generated during mysqld operation

Log information generated when event scheduler runs an event

Information generated when the slave server thread is started on the slave server in the master-slave replication architecture

Error log related configuration

SHOW GLOBAL VARIABLES LIKE 'log_error'

Error file path:

Log_error=/PATH/TO/LOG_ERROR_FILE

Whether to log warning messages to the error log file

Log_warnings=1 | 0 default value 1

General log

Generic logs: recording query operations

File: file, default

Table: table

General log related settings

General_log=ON | OFF (general log, which is recorded by all operations, can be used in a large amount, so it is not recommended to enable it)

General_log_file=HOSTNAME.log

Log_output=TABLE | FILE | NONE (log is recorded in table / file)

Slow query log

Slow query log: record operations that take longer than a specified length of time to execute a query

Slow_query_log=ON | OFF enables or disables slow query

Threshold for long_query_time=N slow query (in second)

Slow_query_log_file=HOSTNAME-slow.log slow query log file

Log_slow_filter = admin,filesort,filesort_on_disk,full_join

Full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk

Whether the statements that log_queries_not_using_indexes=ON does not use indexes and do not reach the slow query threshold log, default OFF, that is, do not log (can be turned on, to see which commands can be optimized if more records are recorded, such as adding indexes)

Log_slow_rate_limit = 1 how many queries to record, unique to mariadb

Log_slow_verbosity= Query_plan,explain record content

Log_slow_queries = OFF with slow_query_log the new version is obsolete

Example:

Select sleep (1), name from students; hibernates for 1 second for each item checked

Then show profile; to check the execution time of each phase of the command, you need to set profiling=1 first.

Binary log

Record SQL statements that cause or potentially cause data changes

Record submitted logs

Does not depend on storage engine type

Function: make a copy of the data by replaying the events in the log file

Note: it is recommended that binary logs and data files be stored separately.

Binary logging format

Three formats of binary logging

Based on statement record: statement, record statement, default mode

Based on "row" records: row, record data, large log volume (strongly recommended in production based on "row" records)

Mixed mode: mixed, let the system decide which method to base on.

Format configuration

Show variables like'% binlog_format%'

The composition of binary log files

There are two types of files

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

Such as: mysql-bin.000001

Index file: mysql | mariadb-bin.index, text format

Server variables related to binary logs:

Sql_log_bin=ON | OFF: whether to record binary log. Default ON (this item can be changed dynamically. For example, if you want not to log temporarily when the hard disk is full, you can turn it off temporarily)

Log_bin=/PATH/BIN_LOG_FILE: specify the file location; the default OFF means that binary logging is not enabled, and both of the above items can be enabled (dynamic changes are not supported. You need to write to the configuration file and restart the service).

Binlog_format=STATEMENT | ROW | MIXED: binary log recording format. Default is STATEMENT (it is strongly recommended to change it to ROW)

Max_binlog_size=1073741824: the maximum volume of a single binary log file. When it reaches the maximum, it scrolls automatically. The default is 1G.

Note: the size of the file when it reaches the upper limit may not be the specified exact value

| sync_binlog=1 | 0: set whether to start the binary log instant disk synchronization function. Default is 0, and the operating system is responsible for synchronizing the log to disk. (if it is inefficient, the advantage is that nothing will be lost. In general, 0 is fine). |

Expire_logs_days=N: the number of days that binary logs can be deleted automatically. The default is 0, that is, it is not deleted automatically (you can leave it for 30 days)

Binary log related configuration

View the list and size of binary log files in use managed by mariadb itself

SHOW {BINARY | MASTER} LOGS

View binary log files in use

SHOW MASTER STATUS

View the specified content in the binary file

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]

Show binlog events in 'mysql-bin.000001' from 6516 limit 2

Mysqlbinlog

Mysqlbinlog: a client command tool for binary logs

Command format:

Mysqlbinlog [OPTIONS] log_file...

-- start-position=# specifies the start position

-- stop-position=#

-- start-datetime=

-- stop-datetime=

Time format: YYYY-MM-DD hh:mm:ss

-- base64-output [= name]

Example:

Mysqlbinlog-start-position=6787-stop-position=7527 / var/lib/mysql/mariadb-bin.000003

Mysqlbinlog-start-datetime= "2018-01-30 20:30:10"-- stop-datetime= "2018-01-30 20:35:22" mariadb-bin.000003

Format of binary log events:

# at 328

# 151105 16:31:40 server id 1 end_log_pos 431 Query thread_id=1 exec_time=0 error_code=0

Use `mydb` / *! * /

SET timestamp 1446712300

CREATE TABLE tb1 (id int, name char (30))

/ *! * /

Date and time of occurrence: 151105 16:31:40

Server identity where the event occurred: server id 1

Where the event ends: end_log_pos 431

Type of event: Query

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

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

Error code: error_code=0

Event content:

GTID:Global Transaction ID,mysql5.6 is exclusive to mariadb10 and above: GTID

Clear the specified binary log:

PURGE {BINARY | MASTER} LOGS

{TO 'log_name' | BEFORE datetime_expr}

Example:

PURGE BINARY LOGS TO 'mariadb-bin.000003'; deletes pre-3 logs

PURGE BINARY LOGS BEFORE '2017-01-23'

PURGE BINARY LOGS BEFORE '2017-03-22 09Rose 25purl 30'

Delete all binary logs and re-count index files

RESET MASTER [TO #]; log files are counted from #, which starts from 1 by default, which is usually executed when master is started for the first time, and MariaDB10.1.6 supports TO #

Switch binary log files:

FLUSH LOGS

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.