CentOSで稼働するMySQLサーバーにて、

クエリログ(ファイル、テーブル)の出力とローテートを行う

ファイルだけに出力するよりもテーブルに出力したほうが何かと便利かな？と思ってテーブルにもログを出力したいと思ったのが始まり。

1. ログ出力をテーブルとファイルの両方にする
2. ログファイルを定期的にローテートする
3. ログテーブルを定期的にローテートする

1. ログ出力をテーブルとファイルの両方にする

/etc/my.cnfに以下の記述を行います。

[mysqld]
～省略～
log_output = TABLE,FILE
general_log_file = /var/log/mysql/query.log
general_log = 1
～省略～

ファイルの保存先ディレクトリがない場合は、以下のコマンドで作成します。

# mkdir /var/log/mysql
# chown -R mysql /var/log/mysql
# chmod -R 644 /var/log/mysql

MySQLを再起動すればテーブルとファイルの両方にログが出力される様になります。

# service mysqld restart

テーブルとファイルに出力されるログは、ログの出力設定を解除するまでずーっと行われるのでとんでもないファイルサイズになってしまいます。なので、これらをローテートさせるための設定を追加します。

2. ログファイルを定期的にローテートする

1.「/etc/logrotate.d/mysql」として以下の記述を書いたファイルをおきます。

/var/log/mysql/*.log {
 create 644 mysql mysql
 notifempty
 daily
 rotate 14
 missingok
 compress
 sharedscripts
 postrotate
 # just if mysqld is really running
 if test -x /usr/bin/mysqladmin && /usr/bin/mysqladmin ping &>/dev/null
 then
 /usr/bin/mysqladmin --defaults-extra-file=/root/.my.cnf flush-logs
 fi
 endscript
}

上記設定を行う上で、以下のサイトを参考にさせて頂きました。

MySQL ログのローテーション設定（logrotate）（flush-logs が cron で動かないときの対処を含む）

上記ファイル内で実行する「/usr/bin/mysqldadmin」が必要とするMySQLのパスワードを/root/ディレクトリ直下のファイル「.my.cnf」として行っておかないと、cronが自動実行される際にMySQLサーバーへのアクセスが自動で行えませんので、以下のとおり参照されるファイルを設定しパスワードを記載しておきます。

2. 「/root/.my.cnf」として以下の記述を書いたファイルをおきます。

[mysqladmin] #mysqladminコマンド用
user = root
password ="パスワード"

[mysql] #mysqlコマンド用
user = root
password ="パスワード"

私の場合、上記ファイルの設定で結構悩みました。パスワードに「#」(シャープ)を含むのですが、この「#」がコメントアウトとして扱われてしまいうまく設定できませんでした。結論としてMySQLのオフィシャルのページにて以下の記述を確認し、パスワードに「#」を含む場合は「’」もしくは「”」で囲えば問題ないことが分かりましたので、上記「”」でパスワードを囲っています。

• opt_name=valueThis is equivalent to --opt_name=value on the command line. In an option file, you can have spaces around the“=” character, something that is not true on the command line. You can optionally enclose the value within single quotation marks or double quotation marks, which is useful if the value contains a “#” comment character.
  
  MySQL :: MySQL 8.0 Reference Manual :: 6.2.2.2 Using Option Files

  dev.mysql.com

参考までに、どのようにして/etc/logrotate.d/配下のファイルが実行されるかを記載しておきます。

CentOS6.4においてlogrotateは/etc/anacrontab の記述を起因として日毎に実行されています。

1 5 cron.daily nice run-parts /etc/cron.daily

毎日(1)、5分の遅れの後(5)、/etc/cron.daily配下のファイルを順に実行する。
という記述で、対象となっているディレクトリ配下に「logrotate」があります。

# ls /etc/cron.daily
logrotate makewhatis.cron mlocate.cron

logrotate は、その中で「/usr/sbin/logrotate /etc/logrotate.conf 」を実行しており、ここで利用されている/etc/logrotate.conf の中で「include /etc/logrotate.d」しています。include(～を含むという意味)されているディレクトリ配下に今回は「mysql」というファイルを置いたため、logrotate実行時にファイルが読み込まれ処理されることになります。

さて、次はログを補完するログテーブル「general_log」のローテトです。

3. ログテーブル「general_log」を定期的にローテートする

これが結構曲者でした。調査にちょっと時間をかけすぎてしまった感があります。

ログテーブルをローテートさせるには以下の方法が使える事を確認しました。

fw_error_www

fw_error_www

dev.mysql.com

USE mysql;①
CREATE TABLE IF NOT EXISTS general_log2 LIKE general_log;②
RENAME TABLE general_log TO general_log_backup, general_log2 TO general_log;③

ここでやっていることは、
①DB「mysql」を選択し、
②「general_log」と同じ形をしたテーブル「general_log2」を作成し、
③「genera_log」を「general_log_backup」に名前変更すると同時に②で作成した「general_log2」を「general_log」に名前変更
しています。

悩みに悩んでSQLファイルを作成することにしました。作成したSQLファイルをroot権限で定期的に実行します。

1. 以下の記述を/root/logrotate.sql として保存します

-- DB選択
USE mysql;

--
-- SQLで用いる変数の指定
--
-- @clearDateに本日より14日前の日付を設定
SET @clearDate = DATE_ADD(CURDATE(), INTERVAL -14 DAY);

-- @tablename に「general_log」を指定
SET @tablename = 'general_log';

-- @tablename_new に「general_log_new」を指定
SET @tablename_new = 'general_log_new';

-- @tablename_delete に「@tablename + _ + @clearDate」を指定(例：general_log_2013-11-12」
SET @tablename_delete = CONCAT(@tablename, '_' , @clearDate);

--
-- 既存の古いログテーブルの削除
--
-- @drop_old に14日前のバックアップテーブルを削除するSQLを指定
SET @drop_old = CONCAT('DROP TABLE IF EXISTS `' , @tablename_delete , '`');

-- @drop_old のSQLを準備し実行
PREPARE stmt1 FROM @drop_old;
EXECUTE stmt1;

--
-- 新しいテーブルの作成
--
-- @create_new に新しいテーブルの作成を行うSQLを指定
SET @create_new = CONCAT('CREATE TABLE IF NOT EXISTS `' , @tablename_new , '` LIKE `' , @tablename , '`');

-- @create_new のSQLを準備し実行
PREPARE stmt2 FROM @create_new;
EXECUTE stmt2;

--
-- ログテーブルの名前変更(ローテート実施)
--
-- @rename にログテーブルのリネームを行うSQLを指定
SET @rename = CONCAT('RENAME TABLE `', @tablename, '` TO `', @tablename, '_' , CURDATE() , '`, `', @tablename_new , '` TO `' , @tablename , '`');

-- @rename のSQLを準備し実行
PREPARE stmt3 FROM @rename;
EXECUTE stmt3;

2. /etc/cron.daily/logrotate.mysql を作成します。

「–defaults-extra-file=/root/.my.cnf」なしで設定した場合、以下の内容のメールが届いたので、設定を変更しました。

/etc/cron.daily/logrotate.mysql:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

# echo "#!/bin/bash" > /etc/cron.daily/logrotate.mysql　←echoでファイルの作成
# echo "mysql --defaults-extra-file=/root/.my.cnf < /root/logrotate.sql" >> /etc/cron.daily/logrotate.mysql　←さらに追記
# cat /etc/cron.daily/logrotate.mysql ←記述の確認
#!/bin/bash
mysql  --defaults-extra-file=/root/.my.cnf < /root/logrotate.sql
# chmod 755 /etc/cron.daily/logrotate.mysql　←実行権限の付与

以上で設定が完了しました。

一日経って、ログファイルとログテーブルがローテートされている事を確認してください。

SQLの作成には以下のサイトを参考にしました。

■CURDATE()を用いた過去の日付の取得方法(英語)
http://stackoverflow.com/questions/13572083/grab-where-curdate-and-the-day-before-with-mysql

■テーブル名変更方法(英語)
http://stackoverflow.com/questions/1741008/rename-table-to-now-old-table-name?answertab=oldest#tab-top

■renegal_logローテーション方法(日本語)
http://dev.mysql.com/doc/refman/5.1/ja/log-tables.html

みなさんの参考になれば幸いです！

以上！(*´∀｀*)