The log files are always important part of any database,whether it is RDBMS database or NoSQL database.
log files make troubleshooting job easy and indicates lo of ways to analyze performance issues.
Below is the list of logs available in MySQL
By default,logfile location is always data directory. We will discuss these logs in details
The Error Log
The General Query Log
Enable - set GLOBAL general_log=ON;
log files make troubleshooting job easy and indicates lo of ways to analyze performance issues.
Below is the list of logs available in MySQL
Log type
|
Information written to log
|
Error log
|
Problem encountered during starting, running and stopping daemon
process (mysqld)
|
General Query Log
|
Established connections and statements received from client
|
Binary log
|
Statements that changes data (also used for replication)
|
Relay log
|
Data changes received from replication master server
|
Slow query log
|
Query that took more than log_query_time
seconds to executes
|
DDL log
|
Metadata operations performed by DDL statements
|
The Error Log
- As name suggest, error log contains diagnostic message such as errors, warning and notes that generated during startup and shutdown of daemon process i.e. mysqld and while server is running.
- File name always ends with .err
- If used to start mysqld, mysqld_safe nay write message to error log e.g. when mysqld_safe notices abnormal mysqld exits,it restarts and write mysql restarted message to this error log
- The log_error_services system variable control which log component to enable for error logging
+---------------------------------------------+
| @@log_error_services
|
+---------------------------------------------+
| log_filter_internal,
log_sink_internal |
+---------------------------------------------+
- Output indicates, log events generated first go through built-in filter component log_filter_internal then through the built-in log writer component log_sink_internal
- A filter modifies log events seen by components named later in log_error_internal value
- A sink is destination for log events
- The location of error log can be defined using --log-error while starting daemon process or can be set using log_error in configuration file.
Error log in JSON format
- MySQL error log can be set in JSON format by modifying log_error_services parameter value.
- To make use of JSON format error log file, we need to install components "file://component_log_sink_json";
- and set value to this component using
Format of logfile message
timestamp thread_id [severity] [error_code] [subsystem] message
The General Query Log
- The server writes information to this log when client connects or disconnects and it also log each statement received from client
- If you want to track or monitor activity or the purpose of troubleshooting one should activate this general query log by using --log option
- This can be disabled or enabled while runtime using below command
Enable - set GLOBAL general_log=ON;
- This does not require restart restart of the mysql services
- Log file location can be set using general_log_file variable in configuration file
The Binary Log
- The binary log contains "events" that describe database changes such as table creation operations or changes to the table data
- The binary log has below two important purpose
- For replication, the binary log on master replication server provides a record of data changes to be sent to slave servers
- Data recover operations requires use of binary log
- Binary logging is enabled by default i.e. log_bin system variable is set to ON
- The log_file_basename is used to specify base name for binary log files
- The mysqld appends the numeric extension to binary log base name to generate binary log file names. Number increases each time server creates new log file
- The maximum size is controlled by max_binlog_size
- To keep track of binary log index file that contains name of the binary log files
- You can delete all binary log files with RESET MASTER or a subset of this with PURGE BINARY LOGS
- To display content of binary log file , you can use mysqlbinlog utility as below
mysqlbinlog
The Slow Query Log
- This particular log is related to slow query, it lists all the queries that exceed a predefined amount of time long_query_time (mentioned seconds).
- Any query that takes more than time than this parameter value, it will be recorded in slow query log
- This slow query log is enabled by below parameters
slow_query_log=ON
slow_query_log_file=
- Slow query log contains:
- query_time : duration (in seconds)
- lock_time : duration (in seconds)
- row_sent : N
- rows_examined : N
- Thread_id : ID
- errno : error number
- There are log of parameters available in slow query log
The DDL Log
- The DDL log records metadata operations generated by data definition statements such as DROP TABLE or ALTER TABLE
- MySQL uses this log to recover from crashes occurring during metadata operations
- A record of metadata operations such as those just described are written to the file ddl_log.log in mysql data directory
- This is binary file (human unreadable)
- The ddl_log.log can hold upto 1048573 entries equivalent to 4GB in size
Relay Log
- The relay log is like binary log, consists of set of numbered files containing events that describe database changes, and and index file that contains names of all used relay log files.
- Relay log file have same format as binary log files and can be read using mysqlbinlog utility.
Sands Casino | Casino in Biloxi, MS
ReplyDeleteFind your perfect destination. The 온라인 카지노 합법 국가 largest casino, entertainment, 온라인 카지노 불법 dining, shopping and 온카지노 more at the best price 더킹 카지노 가입 on the 샌즈 카지노 가입 쿠폰 market!