Configuring centralized logging from MySQL query logs

Aggregate logs from MySQL, including slow queries and error messages.

Introduction

We recommend Papertrail’s tiny standalone remote_syslog2 daemon to read the mysql-slow.log (and any other log files) in realtime (remote_syslog2). It requires no changes to MySQL, is easier to setup, and works for non-MySQL log files as well.

MySQL 5.1.20 and later can also use its own native syslog support plus the system-wide syslog daemon (Native). To see which version of MySQL is installed, run: mysql -V.

The native method is not available in MySQL 5.1.19 and earlier.

Send log file with remote_syslog2

Install remote_syslog2

Download the current release. To extract it and copy the binary into a system path, run:

$ tar xzf ./remote_syslog*.tar.gz
$ cd remote_syslog
$ sudo cp ./remote_syslog /usr/local/bin

RPM and Debian packages are also available.

Configure

Paths to log file(s) can be specified on the command-line, or save log_files.yml.example as /etc/log_files.yml. Edit it to define:

  • path to your MySQL slow query log file (such as /var/log/mysql/slow_queries.log), error log file (such as /var/log/mysqld.log), and any other log files that remote_syslog2 should watch.
  • the destination host and port provided under log destinations. If no destination port was provided, set host to logs.papertrailapp.com and remove the port config line to use the default port (514).

The remote_syslog2 README has complete documentation and more examples.

Start

Start the daemon:

$ sudo remote_syslog

Logs should appear in Papertrail within a few seconds of being written to the on-disk log file. Problem? See Troubleshooting.

remote_syslog requires read permission on the log files it is monitoring.

Auto-start

remote_syslog2 can be automated to start at boot using init scripts (examples) or your preferred daemon invocation method, such as monit or god. See remote_syslog --help or the full README on GitHub.

Troubleshooting

See remote_syslog2 troubleshooting.


Send events from MySQL native syslog

MySQL versions 5.1.20 and later support logging to syslog natively. Two arguments to mysqld_safe control logging: --syslog and --log-error="/path/to/file"

Both, either, or neither argument can be used. mysqld_safe is generally started from /etc/init.d/mysqld. Edit that file (or its equivalent for your distribution) and confirm that --syslog is included on the command line, or add it.

Here is a sample that sends MySQL logs to syslog and directly to a file:

$ /usr/bin/mysqld_safe --datadir="$datadir" --socket="$socketfile" \
    --syslog --log-error="/var/log/mysql.err" --pid-file="$mypidfile" \
    >/dev/null 2>&1 &

More

Official documentation: MySQL 5.1: The Error Log, MySQL 5.0: The Error Log