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.

sudo gem install remote_syslog

2. Configure

Save log_files.yml.example as /etc/log_files.yml (or another location). 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 which remote_syslog should watch.
  • destination hostname and port provided by Papertrail's Web interface. If no destination port was provided, remove that config line to use the default (514).

3. Start

Start the daemon:
remote_syslog

remote_syslog can be automated to start at boot using init.d (example init.d) or your preferred daemon invocation method. See remote_syslog --help or 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