MySQL Server Logs : Error log and General Query Log

MySQL Server has several logs that can help you find out what activity is taking place.

  • By Default , No Logs are enabled in MySQL.
  • By default, the server writes files for all enabled logs in the data directory.
  • Logs can be flush by Issuing FLUSH LOGS statement.
  • Binary log is flushed when its size reaches the value of the max_binlog_size system variable.
  • General query log and slow query log can be enable and disable during run time. We can also tell server to write general query and slow query entries to log tables, log files or both.
  • Relay log is only used on replication servers, to hold the data changes from the master server that must also be made on  slave.
mysql server log
mysql server log

 

General Query Log

  • The general query log is a general record of what mysqld is doing.
  • The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients.
  • Can be very useful when you suspect an error in a client and want to know exactly what the client
    sent to mysqld.
  • mysqld writes statements to the query log in the order that it receives them, which might differ from the
    order in which they are executed. This logging order is in contrast with that of the binary log, for which
    statements are written after they are executed but before any locks are released.
  • If the general query log file is enabled but no name is specified, the default name is host_name.log and
    the server creates the file in the same directory where it creates the PID file. If a name is given, the server
    creates the file in the data directory unless an absolute path name is given to specify a different directory.

 

  1. Enable general query log:

SET global general_log = 1;

SET global log_output = ‘table’;

2. View general query log:

Select * from mysql.general_log;

3. Disable general query log:

SET global general_log = 0;

Error Log

  1. The error log contains information indicating when mysqld was started and stopped and also any critical
    errors that occur while the server is running.
  2. If mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log.
  3. On some Operating systems, the error log contains a stack trace if mysqld dies. The trace can be used to
    determine where mysqld died.
  4. If mysqld_safe is used to start mysqld and mysqld dies unexpectedly, mysqld_safe notices this, restarts mysqld, and writes a restarted mysqld message to the error log.
  5. At runtime, log_error system variable indicates the error log file name if error output is written to a file.
  6. If you specify –log-error in an option file in a [mysqld], [server], or [mysqld_safe] section,
    mysqld_safe will find and use the option.

Related Posts

  • 53
    To enable the query log, put this in /etc/my.cnf in the [mysqld] section log = /path/to/query.log #works for mysql < 5.1.29 Also, to enable it from MySQL console SET general_log = 1; Remember that this logfile can grow very big on a busy server. update: With mysql 5.1.29+ , the log option is deprecated. To specify…
    Tags: mysql, log, query, server, file
  • 48
    90% of the time, this error is due either to the MySQL Server not running, or else firewall configuration on the Windows server blocking access on port 3306 (or whatever port your MySQL instance is configured to use). Error: 2003 (CR_CONN_HOST_ERROR) Message: Can't connect to MySQL server on '%s' (%d)
    Tags: mysql, server, error
  • 47
    I tried the following steps: Log in as super user or use sudo Open /etc/mysql/my.cnf using gedit Check the all the configuration Find bind-address, and change its value to the database server host machine's IP address. For me, it was localhost or 127.0.0.1 Save and close the file. Come back…
    Tags: mysql, server, file, log, error, mysqld
  • 41
    If you have enabled binary logging for the point-in-time recovery (or using replication in your environment) option and forgot to purge it then it may eat up memory in no time, so to “purge” binary logs from production server follow the steps(depending upon your environment) List the current binary logs…
    Tags: logs, log, mysql
  • 37
    Easiest way to do this is to add a UNIQUE index on the column. When you write the ALTER statement, include the IGNORE keyword. Like so: ALTER IGNORE TABLE jobs ADD UNIQUE INDEX idx_name (site_id, title, company); This will drop all the duplicate rows. As an added benefit, future INSERTs…
    Tags: error, mysql, query

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: