Linux log file mysql

MySQL Log File Location

Logs are valuable. Logs generated by a major backend resource that provides clients with access to crucial data are more than just valuable; knowing where they are and being able to manage and understand the information that they contain can mean the difference between smooth, secure operation and degraded performance or even catastrophic failure for your application.

MySQL Server produces a handful of basic logs. We’ll look at which ones are important (and why), where they are, and what you can do to get the most out of them.

MySQL Logs

The three most important logs in terms of day-to-day IT operations are the error log, the slow query log, and (to a lesser degree) the general query log. Their default format is text, and they are useful for detecting and diagnosing functional problems and security issues, for improving performance, and for tracing the history of server operations and client access to the server.

The binary, relay, and DDL logs are all binary in format, and they are designed for use primarily by MySQL itself, specifically for tasks such as server replication and data recovery.

The Question of How and Where with MySQL Log Files

First, let’s take a quick look at how and under what circumstances the various MySQL distributions set default log file locations.

There are three basic types of MySQL distribution: Windows, platform-specific UNIX/Linux, and generic UNIX/Linux. In general, the platform-specific distributions have default settings for placing and enabling logs, while the generic UNIX/Linux distributions assume that logs will be managed via manual settings.

Windows

Official MySQL Windows distributions use an MSI installer with user-selectable options at various stages of the installation. The Logging Options page displays the log enabling and location defaults and also allows you to make adjustments as required.

The error, slow query, and binary logs are enabled by default, but the general query log is not enabled. The default location for each of the logs is the MySQL Data directory (C:\ProgramData\MySQL\MySQL Server [version number]\Data\), and the default log names are based on the computer’s device name.

You can manually enable/disable the general query, slow query, and binary logs via the installer GUI, but not the error log. You can also manually set the names and paths for each of the logs.

After installation, the log settings are managed via the user-editable C:\ProgramData\MySQL\MySQL Server [version number]\my.ini file. These settings include log names and paths as well as enable/disable switches.

For more information on collecting Microsoft MySQL logs, see our dedicated page to collecting and understanding MySQL Logs on windows.

Platform-Specific UNIX/Linux

The official distributions for individual UNIX/Linux platforms are typically script-based, with little or no interactive configuration during installation. Some installation packages (including Yum and APT) create the error log in /var/log/ or var/log/mysql/ with a name like error.log or mysqld.log. The data directory will typically be /var/lib/mysql/ or something similar, and it will serve as the default destination for any logs that are enabled without an alternate path.

The log settings are managed via a user-editable configuration file such as /etc/mysql/mysql.conf.d/mysqld.cnf. These settings include log names and paths as well as enable/disable switches. Startup and shutdown are typically managed by mysqld_safe (or with some distributions, systemd), which should find and apply log configuration options.

Generic UNIX/Linux

Generic installation is largely manual. During the installation process, you can enable and configure logs via the command line, by running scripts, or by editing the appropriate configuration file. The MySQL online reference manual (https://dev.mysql.com/doc/refman/5.7/en/server-logs.html) covers these options in detail.

The MySQL Error Log

The error log includes error messages, warnings, and notes generated during server operations as well as during the startup and shutdown phases; it also records startup and shutdown times.

The basic error log format is:

timestamp thread ID [error type] [error code] [MySQL subsystem] Error message text Error types include System, Warning, Note, and ERROR. Typical log entries might look like this: 2020-05-24T11:55:27.611014Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.20) starting as process 36070 2020-05-24T12:14:51.002836Z 2 [Note] [MY-010051] [Server] Event Scheduler: scheduler thread started with id 2 2020-05-24T12:41:45.059924Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-05-24T12:41:45.086628Z 0 [ERROR] [MY-011825] [InnoDB] Failed to delete file ./#innodb_temp/temp_7.ibt

Error logging is always enabled, and the available options allow you to set the destination, verbosity level, and time zone.

Possible error log destinations are a file or the console. On Windows, if no destination option is specified, the error log is written to host_name.err (where host_name is the host system name) in the data directory. On UNIX/Linux systems, the default destination when no option is specified is the console.

For both UNIX/Linux and Windows-based MySQL installations, the —log-error option by itself (with no file name or path) sends the error log to host_name.err in the Data directory. If you specify the name and path (i.e., —log-error=»G:/TMP/mysql_logs/mysql_error.err» or —log-error=/var/log/mysql/error.log), the error log will be written to the specified file. In order to send the error log to the console on Windows, you must use the —console option; it overrides the —log-error option if both are present.

For more information regarding error logs and collecting them, see our page on Collecting logs in MySQL.

The General and Slow Query Logs

The general query and slow query logs both record user queries using a similar format: Time, ID, Command, and Argument (where Argument includes both the SQL commands and the data making up the query).

The general query log, however, records all client SQL statements along with connect and disconnect times, while the slow query log only records queries that take longer than the time specified by the long-query-time system variable. The slow query log also includes a set of fields containing the execution time, lock time, rows sent, and rows examined for each logged query.

A typical general query log entry might look like this:

2020-05-26T08:01:39.429740Z 17 Query INSERT INTO rental VALUES (1,'2005-05-24 22:53:30…

The slow query log entry for the same event might look like this:

# Time: 2020-05-26T08:01:50.095050Z # User@Host: root[root] @ localhost [::1] Id: 17 # Query_time: 10.699002 Lock_time: 0.037315 Rows_sent: 0 Rows_examined: 0 use sakila; SET timestamp=1590480099; INSERT INTO rental VALUES (1,'2005-05-24 22:53:30…

The slow query log allows you to identify queries that require an unusually long time to execute; it may also be of use in uncovering system or database issues that result in slow execution. The general query log allows you to track all client SQL statements, which can be useful both for tracing errors and for identifying potential security problems.

The query logs tend to accumulate data rapidly, which may impact system performance in addition to taking up disk space. The general query log in particular can grow very quickly; both it and the slow query log are disabled by default in most installation packages. (The Windows MySQL installer, however, is an exception, as described above.)

The general query and slow query logs are enabled separately, using the —general-log and —slow-query-log options. The default destination is the data directory, with (host name].log and [host name]-slow.log as the file names. To set the log names and paths, use the —general-log-file and —slow-log-file options.

The format of both logs is controlled by a single option, —log-output, which takes the following values: FILE, TABLE, or NONE. FILE is the default value. TABLE stores both logs as tables, which can be read and managed via SQL queries. Both FILE and TABLE can be used together, separated by commas. NONE disables the output of both logs; if it is present, it overrides the other values.

Binary, Relay, and DDL Logs

The binary and relay logs are necessary for server replication, and the DDL log is used by the system to manage metadata during mysqld operation. These logs are generally of limited diagnostic use, although you may need to access binary and relay logs using the mysqlbinlog utility as part of the data recovery process.

Simplifying MySQL Log File location with Sumo Logic

The truth is that even with a relatively small number of server logs, MySQL can generate a lot of log data. At the same time, elements of that data may be important in terms of error-tracking, performance, and security. What’s the best way to organize and sort through MySQL server log data in order to find the things that you need to know?

The Sumo Logic App for MYSQL automatically picks out key metrics and data items from the error and slow query logs and presents them in easy-to-read dashboards. Sumo Logic makes it easy to identify performance issues, unusual behavior and activity patterns, and critical errors. You can check system health, replication status, and server performance at a glance, drilling down to detailed real-time information on slow queries (including origin by individual host, IP, and user), failed logins (by user, host, and location), and replication and server problems.

Don’t spend hours digging through log files — let Sumo Logic do the work for you so that you have time to get down to the business of serving your customers!

Complete visibility for DevSecOps

Reduce downtime and move from reactive to proactive monitoring.

Источник

How to see mysql error logs?

Sorry if this is a wrong place to ask. I use Ubuntu 16.04, with very little knowledge of linux system, I depend on webmin to host my website. Recently there has been a problem where mysql won’t start, so I am trying to look into the error logs to get a clue about what is wrong. My first question is: What is the general way to find where the error log is? I’ve done a lot of search but it seems to me in different system, and with different setting, these error logs could be in different places, and the filename is also different. Although I’ve managed to find them under /var/log/mysql/ (which is different from every source I was able to find), I’d like to know how to find these logs’ location in general. I think there should be some configuration file in control of this, right? Besides, on some webpage a mysql.log file is mentioned, but I can’t find where it is. Secondly, under /var/log/mysql , there are files like error.log and error.log.1.gz , error.log.2.gz . Except the error.log file, in order to read other files I have to extract them first. Is there any way, like some mysql command , to read them directly?

1 Answer 1

Although I’ve managed to find them under /var/log/mysql/ (which is different from every source I was able to find),

Logfiles have been stored under /var/log/ for a long time now. It was changed to that location so they are added to «logrotate» by default. You can expect any log file in Ubuntu in /var/log/ .

Secondly, under /var/log/mysql, there are files like error.log and error.log.1.gz error.log.2.gz.

The logs with numbers are older logs. Log files get compressed at some point by «logrotate». The settings for when it gets compressed are stored in /etc/logrotate.conf and by default files are compressed «weekly».

Except the error.log file, in order to read other files I have to extract them first. Is there any way, like some mysql command , to read them directly?

No you can’t. And no that 1st part is not correct. 2 reasons:

• The files ending in gz are at least a week old why would you need to see them? A week old errors are too old to be of any use.
• You can view log files that are compressed.

zcat /var/log/mysql/error.1.gz | more 

And to add: log viewer also shows compressed log files. ALl you need to do is add the log file to the viewer (see «open» in the settings):

Источник