1) Error log
The error log logs the start, run, and shutdown processes of MySQL.
Locate the file by show variables like ' Log_error '.
Mysql> Show variables like ' log_error '; +---------------+---------------------------------+| variable_name | Value |+---------------+---------------------------------+| log_error |/usr/local/mysql/data/rhel6.err | +---------------+---------------------------------+1 row in Set (0.00 sec)
2) binary log (Binlog)
The binary journal records all operations that make changes to the MySQL database, but does not include select and show operations.
The binary log can be started by configuring the parameter Log-bin [=name].
[Email protected] data]# CAT/ETC/MY.CNF | grep log-binlog-bin=mysql-bin
Related parameters:
Max_binlog_size: Specifies the maximum value of a single binary log file file, and if this value is exceeded, a new binary log file is generated, with the suffix name +1, and logged to the. index file.
binlog_cache_size: When a transactional table storage engine is used, all uncommitted binary journals are recorded in a buffer, and when the transaction commits, the binary journal in the buffer is written directly to the binary file, and the buffer size is determined by binlog_cache_size, and the default size is 32K.
Sync_binlog: parameter Sync_binlog=[n], which indicates how many times each write buffer is synchronized to disk.
BINLOG-DO-DB: The database to synchronize.
BINLOG-IGNORE-DB: Do not synchronize the database.
log-slave-update: If slave needs to write the binary log from master to its own binary log file, set log-slave-update.
Bin_format:binlog log format, there are statement, ROW, mixed three kinds of formats.
3) slow query logs (slow query log)
The slow query log helps DBAs locate SQL statements that may be problematic for SQL-level optimizations. You can log all SQL statements that run longer than this value to the slow query journal by setting a threshold of 10 seconds by default.
Mysql> Show variables like ' long_query_time '; +-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in Set ( 0.00 sec)
Check if slow query journaling is turned on:
Mysql> Show variables like ' log_slow_queries '; +------------------+-------+| Variable_name | Value |+------------------+-------+| log_slow_queries | On |+------------------+-------+1 row in Set (0.00 sec)
To view the slow query log:
[Email protected] data]# Mysqldumpslow Rhel6-slow.log
The MySQL5.6.5 version starts with a new parameter, log_throttle_queries_not_using_indexes, that is used to mark unused indexes that are allowed to log to slow log per minute. SQL statement Times number.
4) The query log (log) query journal records all the information requested by the MySQL database, regardless of whether the requests were executed correctly. The default file name is: Host name. Log
To open the query log:
[Email protected] data]# CAT/ETC/MY.CNF | grep loglog=imkh.log
To view the query log:
[email protected] data]# cat Imkh.log/usr/local/mysql/bin/mysqld, Version:5.5.29-log (Source distribution). Started with:tcp port:3306 Unix socket:/tmp/mysql.socktime Id Command Argument140506 0:14:01 1 Connect [email protected] on 1 connect Access denied for user ' imkh ' @ ' localhost ' (using Password:yes)
Reference: "MySQL technology insider: InnoDB Storage Engine"