Follow Techotopia on Twitter

On-line Guides
All Guides
eBook Store
iOS / Android
Linux for Beginners
Office Productivity
Linux Installation
Linux Security
Linux Utilities
Linux Virtualization
Linux Kernel
System/Network Admin
Programming
Scripting Languages
Development Tools
Web Development
GUI Toolkits/Desktop
Databases
Mail Systems
openSolaris
Eclipse Documentation
Techotopia.com
Virtuatopia.com

How To Guides
Virtualization
General System Admin
Linux Security
Linux Filesystems
Web Servers
Graphics & Desktop
PC Hardware
Windows
Problem Solutions

  




 

 

5.11.1. Server Log Tables

Before MySQL 5.1.6, the server writes general query log and slow query log entries to log files. As of MySQL 5.1.6, the server's logging capabilities for these logs are more flexible. Log entries can be written to log files (as before) or to the general_log and slow_log tables in the mysql database. If logging is enabled, either or both destinations can be selected.

The --log-output option specifies where log output should be written, if logging is enabled. It does not itself enable the logs. The syntax for this option is --log-output[=value,...]:

  • If --log-output is given with a value, the value can be a comma-separated list of one or more of the words TABLE (log to tables), FILE (log to files), or NONE (do not log to tables or files). NONE, if present, takes precedence over any other specifiers.

  • If --log-output is omitted or given without a value, the effect is the same as --log-output=TABLE. That is, the table destination is selected.

The --log[=file_name] option, if given, enables logging to the general query log for the selected log destinations. Similarly, the --log-slow-queries[=file_name] option, if given, enables logging to the slow query log for the selected destinations. For both options, the filename is ignored unless the FILE destination is selected.

If you specify the --log or --log-slow-queries option, the server opens the corresponding log file and writes startup messages to it. However, query logging to the file does not occur unless the FILE log destination is selected

Examples:

  • If you had the server configured for logging to log files formerly, use --log-output=FILE to preserve this behavior after an upgrade to MySQL 5.1.6 or higher.

  • To write general query log entries to the log table and the log file, use --log-output=TABLE,FILE and --log.

  • To write general and slow query log entries only to the log tables, use --log-output=TABLE, --log and --log-slow-queries. (In this case, because the default log destination is TABLE, you could omit the --log-output option.)

Note: For new installations of MySQL 5.1.6 or higher, the log tables are created during the installation procedure along with the other system tables. If you upgrade MySQL from a release older than 5.1.6 to MySQL 5.1.6 or higher, you must upgrade the system tables after upgrading to make sure that the log tables exist.

The use of tables for log output offers the following benefits:

  • Log entries have a standard format. To display the current structure of the log tables, use these statements:

    SHOW CREATE TABLE mysql.general_log;
    SHOW CREATE TABLE mysql.slow_log;
    
  • Log contents are accessible via SQL statements. This enables the use of queries that select only those log entries that satisfy specific criteria. For example, this can make it easier to identify problem queries from a particular client.

  • Logs are accessible remotely through any client that can connect to the server and issue queries (if the client has the appropriate log table privileges). It's not necessary to log in to the server host and directly access the filesystem.

  • Log entries can be expired by using TRUNCATE TABLE.

The log tables use the CSV storage engine that writes data in comma-separated values format. For those users who have access to the .CSV files that contain log table data, the files are easy to import into other programs such as spreadsheets that can process CSV input.


 
 
  Published under the terms of the GNU General Public License Design by Interspire