Use of MySQL Log and mysqlbinlog Tool

Use of MySQL Log and mysqlbinlog Tool

View log-related global variables:
Mysql> show global variables like '% log %'

Modify related variables:
1. For a function, use set global xxx = xxx.
2. For file operations, you can only modify the configuration file, add the content to the configuration file, and restart the service to take effect.

Error Log:
Record Content:
Information generated by server startup and Shutdown
Error message during server running
Information generated when an event is run on the Time Scheduler
Information generated when starting and disabling processes on the slave server

File Location:
By default, files are stored in the data directory named "SERVERHOSTNAME. err.

Related variables:
Log_error specifies the Error Log File
Whether log_warning records warning information. 1 indicates logging. The default value is 1.

Query logs:
Record Content:
Query operations

File Location:
By default, files are stored in the data directory named "SERVERHOSTNAME. log ".

Related variables:
Whether general_log enables general query logs. The default value is OFF. Avoid recording a large amount of io.
General_log_file specifies the location of the log file to be queried.
Whether to record all statements to the log. The default value is OFF. Mysql5.6 has been deprecated.

Log_output specifies the output location of general query logs and slow query logs. By default, TABLE is used. It is changed to FILE during compilation. You can use TABLE to indicate record to the TABLE or NONE to indicate no record. TABLE and FILE can be used together and separated by commas. Note that this variable has a high priority. Once set to NONE, instant general_log is set to ON, and no record is recorded.

SQL _log_off: whether to prohibit logging general query log information into the log file. The default value is OFF.

Slow query log:
Record Content:
Operations with long query time

File Location:
By default, the data directory name is SERVERHOSTNAME-slow.log.

Related variables:
Long_query_time specifies the query duration threshold. If this threshold is exceeded, it is defined as a slow query. Note that the actual operation duration is not the cpu duration. The minimum value is 0. The default value is 10. The unit is seconds. millisecond-level resolution is supported.

Whether slow_query_log enables slow query logs. The log output location also depends on the log_output settings.

Slow_query_log_file specifies the location of the slow query log file.
Max_long_data_size
Performance_schema_events_waits_history_long_size collect the length of the event wait history

Binary log:
Record Content:
Record any operations that may cause database changes, including DDL, DML, and authorization statements. Mysql binary format is used to support replication and instant point recovery.

Binary log format:
Statement-based: statment
Row-based: row
Mixed Mode: mixed

File Header + event...

Event composition:
Position: the end position of the last event and the start position of the next event.
Starttime: the start time of the event.
Action: The action of the event.

File Location:
You can use the mysqlbinlog tool to view data. Generally, text editing files cannot be viewed.

Binary log files: by default, files starting with mysql-bin or SERVERHOSTNAME and ending with. bin.00000NUM are stored in the data directory. The log is rolled every time the server is restarted. Retain old files and create new files. Use show master status to view the files currently in use. Use show binlog events in 'mysql-bin.00000num' [from position] To View Details. The log file size is greater than the data size because additional information is recorded in the log file.

Index file: records information about binary log files. By default, the name is mysql-bin.index in the data directory.

We recommend that you store binary log files and data separately on different disks. This ensures security and prevents io competition between log files.

You can also execute flush logs to manually scroll LOGS. Note that only binary and relay logs are actually rolled, and other logs are turned off and enabled.

Use show binary logs to view LOGS

You can use purge binary logs to 'binlogfile' TO delete log files before a specified file.

Related variables:
Binlog_format STATMENT | ROW | MIXED specifies the binary log file format

Log_bin ON | OFF [FILE] specifies the location of the binary log FILE. If no FILE is specified, it is written under the data FILE by default and whether the binary log FILE function is enabled. When mysql is started, use -- log-bin = mysql-bin or modify the configuration file. In [mysqld], add log-bin = mysql-bin and binlog_format = mixed.

SQL _log_bin ON | OFF controls whether to write logs to binary log files

Binlog_cache_size: the cache size, which varies with binlog_stmt_cache_size.

Binlog_stmt_cache_size statement cache size

Sync_binlog NUM is set to write the binary log file NUM times and then synchronize it to the disk. 0 indicates that the transaction is not synchronized. 1 indicates that the transaction is written into the binary log only after the transaction is committed. All other positive numbers indicate that binary logs are synchronized to the disk after the NUM write. Note that if autocommit = 1, the execution of each statement will be submitted directly. Otherwise, the transaction is written to the binary log only after the COMMIT operation is performed.

Max_binlong_cache_size Upper Limit

Max_binlog_size

Max_binlog_stmt_cache_size Upper Limit

Expire_logs_days the log expiration time is set to DAY, and the expiration time is automatically deleted. The default value is 0.

Relay log:
Record Content:
Events copied from the binary log file of the master server are essentially binary log files.
File Location:
Located on the slave server.
Related variables:

Transaction log:
Record Content:
The engine that supports transactions is dedicated to ensuring ACID properties of transactions. It converts random io into sequential io to improve efficiency and ensure that transactions are not lost.

Transaction ID number + raw data + new data
TID <OLD_VLAUE> <NEW_VALUE>

Transactions can only perform ROLLBACK on the TABLE content. operations such as drop table cannot be rolled back.

File Location:
The default value is ib_logfileNUM in the data directory. We recommend that you store log files and data files separately and mirror logs.

Related variables:
Innodb_flush_log_at_trx_commit 0 | 1 | 2 it is set to synchronize the log events in the memory to the log file after transaction commit is enabled. 1 indicates that every time a transaction commit or disk refresh is written, the default value is. 2 indicates synchronization whenever a transaction is committed. 0 indicates that the data is synchronized once every 1 second. If the data is not cached in the kernel, the data is directly written to the disk.

Innodb_log_buffer_size memory cache size
Innodb_log_size Log File Size
Number of log files in the innodb_log_files_in_group log Group
Innodb_log_group_home_dir log storage location, which is the data directory by default
Does innodb_mirrored_log_groups mirror Log File groups?

Innodb_support_xa = TURE | whether to enable distributed transactions in FLASE. It is enabled by default. If the data is modified by only one thread, the InnoDB efficiency is improved after it is disabled.

# Mysqlbinlog [OPTION] BINLOGFILE Binary-to-file viewing tool
[OPTION]
-- Start-datetime
-- Stop-datetime
-- Start-position
-- Stop-position

For example:
# Mysqlbinlog/mydata/mdata/mysql-bin.00005

# Mysqlbinlog -- start-position = 177 -- stop-position = 358/mydata/mdata/mysql-bin.00005

# Mysqlbinlog -- start-datetime = '2017-07-21 19:22:31 '/mydata/mdata/mysql-bin.00005

# Mysqlbinlog/mydata/mdata/mysql-bin.00005> a. SQL reads contents from binary files and exports them to SQL script files.

Instance:
0. Enable the binary logging function:
# Vim/etc/my. cnf
[Mysqld]
Log_bin = mysql-bin
Binlog_format = MIXED

Or specify
# Mysqld_safe -- log_bin = mysql-bin -- binlog_format = 'mixed' -- user = root &

1. view all binary log files:
Mysql> show binary logs;

2. display the binary log files currently in use:
Mysql> show master status;

3. view the specific content of the specified binary date to the file:
Mysql> show binlog events in 'mysql-bin.000005 'from position = 177;
Or
# Mysqlbinlog/mydata/mdata/mysql-bin.000005

4. Manually scroll the binary file:
Mysql> flush logs;

5. Delete the log file before the specified binary log file
Mysql> purge binary logs to 'mysql-bin.00005 ';

6. Export the binary log file as SQL:
# Mysqlbinlog/mydata/mdata/mysql-bin.000005> a. SQL

This article permanently updates the link address: