MySQL logs and the use of Mysqlbinlog tools

To view the log-related global variables:
Mysql>show GLOBAL VARIABLES like '%log% '

To modify a related variable:
1. For switching a function, use set GLOBAL xxx=xxx.
2. Related to the operation of the file, can only modify the configuration file, add content to the configuration file and restart the service after the effective.


Error log:
Record content:
Information generated by startup and shutdown of the server
Error message during server run
Information generated when an event is run on the time scheduler
Information generated from server processes when starting and shutting down from the server

File Location:
The default is the file that is stored in the data directory with the file name Serverhostname.err.

Related variables:
Log_error Specifying error log files
log_warning If a warning message is logged, 1 indicates a record. The default is 1.


General Query log:
Record content:
Operation of the query

File Location:
The default is the file that is stored in the data directory with the file name SERVERHOSTNAME.log

Related variables:
General_log whether to enable the General query log, which is off by default. Avoid recording large amounts of IO.
General_log_file specifying the location of the General query log file
Log logs all statements to the log, which is off by default. mysql5.6 is obsolete.

LOG_OUTPUT Specifies the output location of the general query log and the slow query log, using table by default. will become file at compile time. You can use table to indicate that the record is in a table or none to indicate no record. Table and file can be used together, separated by commas. Note This variable has a high precedence. Once set to None, the instant General_log is set to on and not logged.

Sql_log_off controls whether generic query log information is prohibited from logging into the log file, which is off by default.


Slow query log:
Record content:
Query for long-time operations

File Location:
The default is the data directory named Serverhostname-slow.log.

Related variables:
LONG_QUERY_TIME Specifies the query duration threshold, which is defined as a slow query over this time. Note this long is the length of time that the actual operation was executed rather than the CPU execution time. The minimum value is 0, the default is 10, the unit is seconds, and the millisecond resolution is supported.

Slow_query_log whether to enable slow query logging, the output location of the log 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 the length of the event waiting history to be collected

Binary log:
Record content:
Log any operations that might cause changes to the database, including DDL,DML, authorization statements, and so on. Support for replication and instant point recovery with MySQL's unique binary format.

Binary log format:
Statement based: statment
Row-based: row
Mixing mode: Mixed

File Header + Event ...

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

File Location:
Using the tool Mysqlbinlog to view, the general text editing file is unable to produce to see.


Binary log file: Default in the Data directory beginning with Mysql-bin or Serverhostname, and at the end of a. bin.00000x file. The log scrolls after each reboot of the server. Keep the old file and create the new file. Use the show MASTER status to view the files that are currently in use. Use show BINLOG EVENTS in ' mysql-bin.00000x ' [from POSITION] to see the details. The size of the log file is larger than the size of the data, because additional information is recorded inside.


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

It is recommended that binary log files and data be stored separately on different disks, so as to guarantee security and ensure that there is no competitive IO between log files.

You can also perform a flush logs to manually scroll through the log. Note that only binary and trunk logs are really scrolling, and the other logs are turned off after they are turned on.

viewing logs using show BINARY logs

Log file before the specified file can be deleted using purge BINARY LOGS to ' binlogfile '

Related variables:
Binlog_format statment| row| MIXED specifying the format of a binary log file

Log_bin on| OFF [file] Specifies the location of the binary log file and does not specify whether the file default is written under the data file, or if the binary log file feature is enabled. Add Log-bin=mysql-bin and binlog_format=mixed to [mysqld] by using--log-bin=mysql-bin or modifying the configuration file when MySQL starts.

Sql_log_bin on| OFF controls whether logs are written to binary log files

Binlog_cache_size cache size, follow binlog_stmt_cache_size size change

Binlog_stmt_cache_size Statement Cache Size

Sync_binlog NUM Sets the time to synchronize the binary log to disk, 0 means no synchronization, and any positive number indicates that the binary is synchronized once per many write operations. If autocommit=1, the execution of each statement causes synchronization, otherwise the log synchronization is caused when the transaction commits.

Max_binlong_cache_size Upper Value

Max_binlog_size

Max_binlog_stmt_cache_size Upper Value

Expire_logs_days Day Sets the log expiration time to days, and expiration is automatically deleted. The default is 0.



Relay LOG:
Record content:
Events that are copied from the binary log file of the primary server are essentially binary log files.
File Location:
is located on the slave server.
Related variables:

Transaction log:
Record content:
The engine that supports transactions is unique in order to guarantee the acid characteristics of transactions, convert random io to sequential io, improve efficiency, and ensure that transactions are not lost.

Transaction ID number + RAW data + new data
Tid<old_vlaue><new_value>

Transactions can only be rollback to the contents of a table and cannot be rolled back for operations such as drop table.

File Location:
The default is within the data directory, with the name Ib_logfilex. It is recommended that log files and data files be stored separately and mirror the logs.

Related variables:
Innodb_flush_log_at_trx_commit 0|1|2 The log events in memory are synchronized to the log file after transaction commit is enabled, and 1 indicates the default value whenever a transaction commit or disk flush is written. 2 indicates that synchronization occurs whenever a transaction commits. 0 means that every 1 seconds is synchronized, not cached in the kernel and written directly to disk.

Innodb_log_buffer_size Memory Cache Size
Innodb_log_size log File size
Innodb_log_files_in_group number of log files in the log group
Innodb_log_group_home_dir Log storage location, default to Data directory
Innodb_mirrored_log_groups whether to mirror the log file group

innodb_support_xa=ture| Flase whether the distributed transaction is enabled, is enabled by default. If only one thread modifies the data, turn it off to improve the efficiency of the InnoDB.




#mysqlbinlog [OPTION] binlogfile binary Day 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= ' 2015-07-21 19:22:31 '/mydata/mdata/mysql-bin.00005

#mysqlbinlog/mydata/mdata/mysql-bin.00005 > A.sql export from binary files to SQL script files.

Instance:
0. Turn on binary services:
#vim/etc/my.cnf
[Msyqld]
Log_bin=mysql-bin
Binlog_format=mixed


1. View all the binary log files:
Mysql>show BINARY LOGS;

2. Displays the binary log files that are currently in use:
Mysql>show MASTER STATUS;

3. View the specific contents 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 binaries:
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 script:
#mysqlbinlog/mydata/mdata/mysql-bin.000005 > A.sql

This article is from "Small Private blog" blog, please be sure to keep this source http://ggvylf.blog.51cto.com/784661/1681437

MySQL logs and the use of Mysqlbinlog tools