Log files first recognized by mysql _ MySQL

Mysql first-recognized log file article bitsCN.com

Log files first recognized by mysql

Log files

1. err log

The error log records all severe warnings and error messages during mysql running, as well as detailed information about each mysql server startup and shutdown. By default, the error log function is disabled. The error log is stored in the data directory by default. the default file name is host name. err. Error log is enabled in two ways:

1) when mysqld is started, append the -- log-error parameter

2) configure the log-error system environment variable in my. cnf.

After you enter mysql and run the flush logs command, mysql will append the current error log file to the old file for storage and create a new empty error log file, only when -- log-error is specified.

Note: The error log does not fully Store warning and error information, but also stores information about each startup and shutdown.

Ps startup information: 130420 16:19:29 mysqld_safe mysqld from pid file/data0/mysql_data_7706/FZTEST-24178.pid ended130420 16:19:30 mysqld_safe Starting mysqld daemon with databases from/data0/keys: found option without preceding group in config file:/data0/mysql_data_7706/my. cnf at line: 1 Fatal error in defaults handling. program aborted130420 16:19:30 [Note] Plugin 'Federated 'is disabled.130420 16:19:30 [Note] Plugin 'ndbcluster' is disabled.130420 16:19:30 InnoDB: Initializing buffer pool, size = 8.0M130420 16:19:30 InnoDB: completed initialization of buffer pool (omitted below) to check whether the error log is started: mysql> show variables like 'log _ error '; + --------------- + ------------------- + | Variable_name | Value | + ----------------- + --------------------- + | log_error |/var/log/mysqld. log | + --------------- + ------------------- + 1 row in set (0.00 sec) close mysql: mysqladmin-uroot-p shutdown

2. binary log (binlog)

The binlog log mainly records the update content of mysql, and records the execution time, consumed resources, and related transaction information of each update statement. It can be used for real-time backup and master/slave replication.

How to Enable:

Add on [mysqld] Node in my. cnf

Check whether binlog is enabled

Mysql> show variables like 'log _ % '; + records + ----------------- + | Variable_name | Value | + --------------------------------- + --------------- + | log_bin | ON | view binlog content: /usr/local/mysql3306/bin/mysqlbinlog mysql-bin.00002log-bin-index file role: record all binlog Files directory [root @ FZTEST-24178 mysql_data_7706] # cat mysql_7706-relay-bin.index. /mysql_7706-relay-bin.000032binlog format description: [root @ localhost ~] # Mysqlbinlog/home/mysql/binlog/binlog.000003 | more /*! 40019 SET @ session. max_insert_delayed_threads = 0 */;/*! 50003 SET @ OLD_COMPLETION_TYPE = @ COMPLETION_TYPE, COMPLETION_TYPE = 0 */; DELIMITER /*! */; # At 4 #120330 16:51:46 server id 1 end_log_pos 98 Start: binlog v 4, server v 5.0.45-log created 120330 16:51:46 # Warning: this binlog was not closed properly. most probably mysqld crashed writing it. # at 196 #120330 17:54:15 server id 1 end_log_pos 294 Query thread_id = 3 exec_time = 2 error_code = 0 set timestamp = 1333101255 /*! */; Insert into tt7 select * from tt7 /*! */; # At 294 #120330 17:54:46 server id 1 end_log_pos 388 Query thread_id = 3 exec_time = 28 error_code = 0 set timestamp = 1333101286 /*! */; Alter table tt7 engine = innodb /*! */;

Parse binlog format

Location

The location in the file. "at 196" indicates the start point of the "event", starting from 196th bytes. "end_log_pos 294" indicates that it ends in 294th bytes.

Timestamp

Time stamp of the event: "120330 17:54:46"

Event execution time

Time spent on event execution: "exec_time = 28"

Error code

Error code: "error_code = 0"

Server ID

Server id: "server id 1"

Other parameters:

1). binlog_do_db: indicates to record the binary log of the specified database

2). binlog_ignore_db: indicates to ignore binary logs of the specified database.

3). max_binlog_cache_size: indicates the maximum memory value when binlog is used.

4). binlog_cache_size

This parameter indicates the memory size used by binlog. the state variables binlog_cache_use and binlog_cache_disk_use can be used for testing.

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

5). max_binlog_size

Maximum Binlog value. The maximum value and default value are 1 GB. this setting does not strictly control the Binlog size, especially when Binlog is close to the maximum value and a large transaction is encountered, to ensure transaction integrity, you cannot switch logs. you can only record all SQL statements of the transaction into the current log until the transaction ends.

6). sync_binlog

This parameter directly affects mysql performance and integrity.

Sync_binlog = 0:

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, the best performance is to let Filesystem decide when to synchronize data.

Sync_binlog = n. after n transaction commits, Mysql will execute a disk synchronization command such as fsync, and the gay 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 is required. in this case, the performance is the best, but the risk is also the biggest. Once the system crashes, all Binlog information in the file system cache will be lost.

Query Logs

Query logs record the logs of all query statements. it is generally recommended that you do not enable the query statements. some query statements are relatively large, which also has a great impact on the performance. It is generally used to track a special performance problem and temporarily enable the function. the default query log file name is host name. log.

Slow query log

Slow query logs are queries that take a long time to query. you can add log-slow-queries =/tmp/slow_log under [mysqld] to open some functions, the default file name is the hostname-slow.log. the default directory is the data directory.

Tools for analyzing slow queries include msyqlslowdump and mysqlsla.

Innodb online log redo log

Innodb is a transaction-safe storage engine, its transaction security is mainly through the online redo log and the undo information recorded in the tablespace to ensure that the redo log records all the physical changes and transaction information made by innodb, through the redo log and undo information, innodb ensures transaction security under any circumstances. The redo logs of innodb are also stored in the data directory by default. you can use innodb_log_group_home_dir to change the bus for storing log settings, and set the number of logs through innodb_log_files_in_group.

BitsCN.com