Papertrail Knowledge Base

Configuring centralized logging from MySQL query logs

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

Intro

We recommend Papertrail's tiny standalone remote_syslog daemon to read the mysql-slow.log (and any other log files) in realtime. Setup remote_syslog.


Advanced: Other methods

Using remote_syslog is usually the way to go, since 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 (Method B). To see which version of MySQL is installed, run: mysql -V.

That's not available in MySQL 5.1.19 and earlier and it's more work to setup, so we recommend remote_syslog (A).


Method A: Use Papertrail's tiny remote_syslog daemon

1. Install remote_syslog.

Download a release and then:

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

Problem? See Troubleshooting.

2. 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:

The README has complete documentation and more examples.

3. 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.

4. Auto-start

remote_syslog 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.


Method B: Use MySQL Native Syslog

MySQL 5.1.20+ only

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 which makes MySQL log 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