Log files first recognized by mysql

Log files first recognized by mysql 1. the err log error log records all serious 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 ). in my. in cnf, configure the log-error system environment variable. when mysql enters and runs the flush logs command, mysql attaches the current error log file to the-old file for saving, 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) binlog records the mysql update content, 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 the binlog to the [mysqld] node in my. cnf to check whether the 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 /*! */;

 

Parsing the binlog format is located in the file. "at 196" indicates that the start point of "Event" is 196th bytes; "end_log_pos 294" indicates the timestamp of the end time stamp with 294th Bytes: "120330 17:54:46" event execution time: "exec_time = 28" error code: "error_code = 0" server id: "server id 1" other parameter descriptions: 1 ). binlog_do_db: indicates to record the binary log of the specified database 2 ). binlog_ignore_db: indicates to ignore the binary log of the specified database 3 ). max_binlog_cache_size: indicates the maximum memory value 4 when binlog is used ). 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: the number of transactions that use binary log cache but exceed the binlog_cache_size value, and use a temporary file to save the statements in the transaction. 5 ). max_binlog_sizeBinlog maximum 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 directly affects mysql performance and integrity. sync_binlog = 0: after a transaction is committed, Mysql only writes data in binlog_cache to the Binlog file, however, if you do not execute Disk Synchronization commands such as fsync, the file system will be notified to refresh the cache to the disk, and let Filesystem decide when to perform synchronization on its own. This is the best performance.. 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 log query logs record the logs of all query statements. It is generally not recommended to enable this function. 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 logs the slow query logs 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, which 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.