Summary of various MySQL logs

Summary of various MySQL logs

Log Type:

• Error log)
• General log)
• Binary log)
• Relay log)
• Slow log)
• Redo log of InnoDB Engine

Error Log

Log-error = error. log

Save the storage path to datadir without specifying the hostname. err

Record mysql startup and shutdown, exceptions or important prompts during running

Monitor this log in the production environment

Full logs (General logs)

General_log = OFF | ON

General_log_file = general. log

The path is not specified to be stored in datadir. If the name is hostname. log

After it is enabled, all requests from the client and database are recorded.

Second, the maximum number of log files is 2 to the power of 32

Log-bin =/data/mysql/mysql3306/logs/mysql-bin

Set the storage location and prefix name of binary logs

Logs used to record database write operations can be used for backup or master/slave replication. server-id is required

If no value can be added by modifying the value in the memory, it is risky.

It cannot be generated too quickly. It should be as big as possible. A log will be generated in about 5 minutes.

Log_bin =/data/mysql/mysql3306/log/mysql-bin whether binlog is enabled

Log_bin_index =/data/mysql/mysql3306/log/mysql-bin.index

Specify an index file for binlog. The default value is to create a log_bin prefix index in datadir.

Binlog_do_db = thunder

Used to specify to record only the binary logs of that database (not recommended)

Max_binlog_size = 500 M

Specify the binlog size as mb. The default value is 1 GB.

Expire-logs-days = 5

Specify the number of days for binlog retention

Binlog_format = row

Specify the binlog log format. The statement, row, and mixed formats are supported.

Statement format records native SQL

The row format does not exist, but after 5.6, a query event is added to view the original SQL statement.

Binlog_rows_query_log_events is off by default.

Mixed format is a mixture of two forms: DDL statement record statement format, DML statement record row format

Row format is recommended.

Binlog_row_image = full

Controls the degree to which binlog details are related. full (default), minimal, and nobolb are supported.

Minimal: only records changed rows and unique ID columns.

Nobolb: records all columns except blob and text.

Binlog_error_action = abort_server

When mysql cannot write binlog, an exception is reported. The default value is ignore error and no error is reported.

Binlog_direct_non_transactional_updates = on

Non-transaction engine tables are directly submitted using logs instead of 2 pcs. This is not supported by default.

Binlog_order_commit = on

Write logs in sequence

Binlog_cache_size = 1 M is already large

This parameter indicates the memory size used by binlog. You can use the state variables binlog_cache_use and bin_cache_disk_use to help test

Binlog_cache_use: number of transactions cached using binary logs

Binlog_cache_disk_use: number of transactions in which binary logs are cached but the value of binlog_cache_size is exceeded and temporary files are used to save the statements in the transaction

Binlog_checksum (introduced in 5.6.2) modified in later versions

After mysql5.6.6, the default value is crc32, and the previous version is none.

Before enabling this parameter, the complete log verification is performed by comparing the length. After a new method is introduced, the content is verified using the new method.

Binlog_rows_query_log_events (introduced in 5.6.2)

Only applies to the RBR format. It is disabled by default.

If enabled, the original DML operations written directly by the user will be recorded in the binlog.

Log_bin_use_v1_row_events (introduced in 5.6.6)

The default value is 0. If Version1 is used, mysql5.5 can recognize it. If 0 is in version2 format after 5.6.6

Sync_binlog = 1 | 0

This parameter has a serious impact on performance. The data consistency requirements are adjusted to 1 and the performance difference is about 10 times.

After the transaction is committed, mysql only writes data in binlog_cache to the binlog file, but does not execute Disk Synchronization commands such as fsync to notify the file system to refresh the cache to the disk, this is the best performance for filesystem to decide when to synchronize.

Sync_binlog = n after n transaction commits, mysql will execute a Disk Synchronization command such as fsync, and the file system will refresh the binlog File Cache to the disk.

In mysql, the default setting is sync_binlog = 0, that is, no mandatory disk refresh command. This is the best performance, but the risk is also the biggest. Once the system crash is enabled, all binlog information in the file system cache will be lost.

Relay log

Relay-log = relay-bin

Set the name prefix of the relay log. If the path is not specified, it is under datadir by default.

Log_thread writes the logs read from the master to the relay log for SQL _thread to execute to complete replication.

Slow log

Slow-query-log-file = slow. log

Slow-query-log

Long_query_time = 1

How the timing,

Ddl statement exec time

Dml statement select starts timing from lock wait, insert only records execution time

Use the slow-query-log parameter to open slow logs. slow-query-log-file specifies the name of slow logs. If no path is specified, it is under datadir by default.

We recommend that you create a regular task for a long time.

Innodb redo log transaction processing concepts transaction processing Bible

Innodb_log_group_home_dir =/data/mysql/mysql3317/logs under datadir by default

In an SSD disk, do not place log files in an SSD disk.

Innodb_log_file_size = around 200 M

Innodb_log_files_in_group = 3 count

Specify the storage location and size of the redo log, and the number of files.

An indispensable part of Innodb Transaction operations

This article permanently updates the link address: