MySQL Log Management (i)

A. mysql log view:QL early, need to master the use of SQL statements, the simplest way is more practice more use, but good need to have a certain purpose, what meaning?

Is that everyone's habits are not the same, it may be easy to make mistakes are different, so need to summarize their frequent mistakes, targeted to make up, so that the learning and mastery of MySQL to play a multiplier effect. Today, I'll talk about several logs that record MySQL operations:

#错误日志 Log-error

#查询日志 Log

#二进制日志 Log-bin

#慢日志 log-slow-queries

#更新日志 Log-update (official recommendation, do not open, so ignore it here)

The following one by one details:

Method/Step

2. 1

   First, after logging in to MySQL, execute the SQL statement:

   Show variables like ' Log_bin ';

   See if the log is open, detailed results

4. 2

   #错误日志 Log-error

   Open mode:

   Under My.ini's [mysqld] option:

   Add code:

   Log-error=e:\log-error.txt

   Record content:

   It is a system-level error record that is primarily a record of fatal issues that occur when you start, run, or stop mysqld

6. 3

   #查询日志 Log

   Open mode:

   Under My.ini's [mysqld] option:

   Add code:

   Log=e:/mysql_log.txt

   Record content:

   The main is to record database established client connection and execute statement

8. 4

   #二进制日志 Log-bin

   Open mode:

   Under My.ini's [mysqld] option:

   Add code:

   Log-bin=e:/mysql_log_bin

   Record content:

   It is primarily a statement that records all change data, and you can use the Mysqlbinlog command to recover the data.

10. 5

    #慢日志 log-slow-queries

    Open mode:

    Under My.ini's [mysqld] option:

    Add code:

    Long_query_time = 1 (set the standard for slow queries, in s/seconds)

    log-slow-queries= E:/mysql_log_slow.txt

    Record content:

    Primarily records all queries that have executed more than long_query_time or that do not use an index

    END

______________________________________________________________________________________

Two. MySQL Log Management

Outline

I. Classification of logs

Second, the log detailed

Note: MySQL version, Mysql-5.5.32 (different versions of MySQL variables vary)

I. Classification of logs

• Error log

• Query log

• Slow query log

• Binary log

• Relay Log

• Transaction log

• Scrolling log

Second, the log detailed

1. Error log

Description: In the corresponding data directory, the name of the host name +.err file, error logging information type:

• Logs error messages that are generated during server operation

• Records the information that is generated when the service starts and stops

• When the replication process is started on the slave server, the replication process information is also logged

• Logging the event error log

To configure the master configuration file for MySQL:

12	log_error = /mydata/data/mysql.test.com.err #指定错误日志的位置，默认是在数据目录下，这个位置mysql用户必须有写权限log_warning = {0|1} #默认开启，服务器运行中的警告日志也会记录在错误日志中

2. Query log

Note: The query information recorded in addition to the slow scan log will be recorded, which will put a lot of pressure on the server host, so the busy server should close this log

Variables associated with the query log,

1234	log = {ON|OFF} #是否启用查询日志，该指令在mysq5.6中已废弃general_log = {ON|OFF} #启动或关闭查询日志，默认是关闭的general_log_file = /mydata/data/mysql.log #指定查询日志的位置，默认在数据目录下log_output = {TABLE|FILE|NONE} #指定存放查询日志的位置，可以放在文件中，也可以放在数据库的表中，放在表中比放在文件中更容易查看

3. Slow query Log

Note: The default is off, logging the query time longer than the set length of the query, these query logs will be recorded by the slow log

To configure the master configuration file for MySQL:

12345

slow_query_log  = {ON | OFF} #是否开启慢慢查询日志，默认是关闭的 slow_query_log_file = /mydata/data/mysql-slow.log #慢查询日志的存放位置，默认在数据目录下log_query_time = 10 #定义默认的时长，默认时长为10秒log_query_not_using_indexes = {ON|OFF} #设定是否将没有使用索引的查询操作记录到慢查询日志 log_output = {TABLE|FILE|NONE} #定义一般查询日志和慢查询日志的保存方式，可以是TABLE、FILE、NONE，也可以是TABLE及FILE的组合(用逗号隔开)，默认为FILE。如果组合中出现了NONE，那么其它设定都将失效，同时，无论是否启用日志功能，也不会记录任何相关的日志信息

4. Binary Log

Description: A data object that is enabled by default to accurately record commands and actions that the user operates on data in the database.
The binary log file functions:

• Provides the ability to incrementally backup

• Provides data point-in-time recovery that can be controlled by the user at a point in time

• Provides the basis for MySQL's replication architecture, which copies the binary logs of the primary server to the slave server and performs the same operations to synchronize the data

Binary log format:

• Statement-based statement

• Based on Row row

• Mixed Mode Mixed

Binary Log events:

• Position based on location

• DateTime based on time

How binary logs are viewed and deleted:

12345

mysql>show master status; 查看当前正在使用的二进制日志 mysql>show binlog events in‘mysql-bin.000001‘; 查看二进制日志记录的事件[from position]  mysql>flush logs; 二进制日志滚动  mysql>show binary logs; 查看所有二进制日志  mysql>purge binary logs to ‘mysql-bin.000003‘; 删除二进制日志

commands for viewing binary logs in the file system:

123456

mysqlbinlog相关选项， --start-position #开始位置  --stop-position #结束位置  --start-datetime ‘yyyy-mm-dd hh:mm:ss‘; #开始时间  --stop-datetime ‘‘; #结束时间

To configure the master configuration file for MySQL:

123456789

sql_log_bin = {ON|OFF} #用于控制二进制日志信息是否记录进日志文件。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限binlog_cache_size = 32768 #默认值32768 Binlog Cache 用于在打开了二进制日志（binlog）记录功能的环境，是 MySQL 用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及 binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件（binlog_cache_disk_use）来缓存了 binlog_stmt_cache_size = 32768 #当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句  log_bin = mysql-bin #指定binlog的位置，默认在数据目录下binlog-format= {ROW|STATEMENT|MIXED} #指定二进制日志的类型，默认为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。 sync_binlog = 10 #设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步  max_binlog_cache_size = {4096 .. 18446744073709547520} #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。  max_binlog_stmt_cache_size = {4096 .. 18446744073709547520} #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存  expire_log_days = {0..99} #设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时

Note: The Binlog log and data files are generally recommended to be stored separately, not only to improve MySQL performance, but also to increase security!

5. Relay Log

Description: Mainly in the MySQL server from the schema from the server to use, when the server wants and the primary server for data synchronization, from the server to the primary server to copy the binary log files to their own host on the trunk log, Then call the SQL thread to follow the binary log file in the copy relay log file so that the data can be synchronized

Open method: (only open from server)
To configure the master configuration file for MySQL:

123456

relay-log = file_name #指定中继日志的位置和名字，默认为host_name-relay-bin。也可以使用绝对路径，以指定非数据目录来存储中继日志  relay-log-index = file_name #指定中继日志的名字的索引文件的位置和名字，默认为数据目录中的host_name-relay-bin.indexrelay-log-info-file = file_name #设定中继服务用于记录中继信息的文件，默认为数据目录中的relay-log.inforelay_log_purge = {ON|OFF} #设定对不再需要的中继日志是否自动进行清理。默认值为ONrelay_log_space_limit = 0#设定用于存储所有中继日志文件的可用空间大小。默认为0，表示不限定。最大值取决于系统平台位数max_relay_log_size = {4096..1073741824} #设定从服务器上中继日志的体积上限，到达此限度时其会自动进行中继日志滚动。此参数值为0时，mysqld将使用max_binlog_size参数同时为二进制日志和中继日志设定日志文件体积上限

6. Transaction log

Description: Detailed records of what happened at what time, at which time the data were changed, can be implemented after the event replay, generally only record the operation of the data changes, for the read operation is generally not recorded.

The things log implements the following functions for the database server:
(1). The conversion of random IO to sequential IO greatly improves the performance of the database, the stored data may exist in different locations of the disk, reducing the data read and operation performance. The principle of converting to sequential IO is that the data is stored in the log file, and then the data in the log is stored on the disk by the RDBSM background, which ensures that the stored data is continuous.
(2). Provides the basis for event replay, in which the transaction log details the time taken and the data objects that are manipulated, and the transaction process can replay the time based on that information.
The default transaction log file has two, in the data directory under the Ibdata+number end of the number, we can define the location of the transaction log, file size, growth mode, defined by the following methods:
This is an example of using a InnoDB storage engine that supports transactions.

To configure the master configuration file for MySQL:

1234567

innodb_data_home_dir = /mydata/data#InnoDB所有共享表空间数据文件的目录路径，默认在数据目录下innodb_data_file_path = ibdata1:1024M  #指定InnoDB的各个数据文件及其大小，文件多于一个时彼此间用分号隔开 innodb_data_file_path = ibdata2:50M:autoextend  #定义数据大小的增长方式innodb_log_group_home_dir = /mydata/data#设定InnoDB重要日志文件的存储目录。在缺省使用InnoDB日志相关的所有变量时，其默认会在数据目录中创建两个大小为5MB的名为ib_logfile0和ib_logfile1的日志文件innodb_log_files_in_group = {2 .. 100} #设定日志组中日志文件的个数。InnoDB以循环的方式使用这些日志文件。默认值为2innodb_log_file_size = {108576 .. 4294967295} #设定日志组中每个日志文件的大小，单位是字节，默认值是5MB。较为明智的取值范围是从1MB到缓存池体积的1/n，其中n表示日志组中日志文件的个数。日志文件越大，在缓存池中需要执行的检查点刷写操作就越少，这意味着所需的I/O操作也就越少，然而这也会导致较慢的故障恢复速度innodb_log_buffer_size = {262144 .. 4294967295} #设定InnoDB用于辅助完成日志文件写操作的日志缓冲区大小，单位是字节，默认为8MB。较大的事务可以借助于更大的日志缓冲区来避免在事务完成之前将日志缓冲区的数据写入日志文件，以减少I/O操作进而提升系统性能。因此，在有着较大事务的应用场景中，建议为此变量设定一个更大的值

7. Scrolling logs

Note: As long as it is for the binary log scrolling, a log file of a type to generate a new corresponding log file, in this way to ensure the specific size of the log file, so as to ensure that the server in the log file query with a high response ability.

To scroll through the binary log commands:

1	mysql> FLUSH LOGS;

Iii. Summary

Through the above study, we should have a better understanding of MySQL log management, ^_^ ...!

MySQL Log Management (i)