kakiro-webカキローウェブ

システム開発情報とコンピューター書籍の紹介サイト

MySQL 実行されたSQLをログに出力

MySQLで実行されたSQLをログに出力する方法を以下に示します。

※ここで説明に使用しているMySQLは、当サイトのCentOS7 64bitのyumリポジトリにMySQLを追加し、最新バージョンをインストールのページを元にインストールしたバージョン5.6になります。

※データベースの作成については、当サイトのMySQL データベースの作成のページをご覧ください。

※テーブルの作成については、当サイトのMySQL テーブルの作成のページをご覧ください。

※データベース接続ユーザーの作成については、当サイトのMySQL データベース接続ユーザーの作成のページをご覧ください。

MySQLの設定ファイルで、実行されたSQLをログに出力するよう設定

実行されたSQLのログへの出力は、デフォルトでは無効になっています。MySQLのサービスを起動時にSQLのログ出力を有効にするのは、MySQLの設定ファイル(ここでの説明に使用しているCentOSでは「/etc/my.cnf」)で指定可能になっています。

実行されたSQLのログをファイルに出力

実行されたSQLのログ出力を有効にするには、以下のような設定を行います。

[mysqld]
:(略)
general_log=1
#以下のように設定してもよい
#general_log=ON
  • 「general_log」に「1」または「ON」を指定すると、実行されたSQLのログ出力が有効になります。

    ※「0」または「OFF」を指定、または「general_log」の指定を行っていない場合は、無効になります。

ログの出力先には、ファイルとテーブルが指定可能になっており、指定を行っていない場合は、ファイルに出力されるようになっています。

ファイルに出力する場合は、ファイルの保存先が指定可能になっており、指定を行っていない場合は、データディレクトリにファイル名が「ホスト名.log」で保存されるようになっています。

ログの出力先とファイルの保存先を、デフォルトの値で指定を行った場合の設定ファイルの内容は、以下のようになります。

[mysqld]
:(略)
general_log=1
log_output=FILE
general_log_file=/var/lib/mysql/localhost.log
  • 「log_output」に「FILE」を指定すると、実行されたSQLのログがファイルに出力されるようになります。

  • 「general_log_file」に、実行されたSQLのログをファイルに出力する場合の、ファイルの保存先を指定します。

MySQLのサービスを起動後に、「USE db01;」、「SELECT * FROM tbl01;」を実行した場合のログファイルの内容は、以下のようになります。

/usr/sbin/mysqld, Version: 5.6.25-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
151101 23:09:21     1 Connect   UNKNOWN_MYSQL_USER@localhost on
                    1 Connect   Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO)
151101 23:09:28     2 Connect   root@localhost on
                    2 Query     select @@version_comment limit 1
151101 23:09:34     2 Query     SELECT DATABASE()
                    2 Init DB   db01
                    2 Query     show databases
                    2 Query     show tables
                    2 Field List        tbl01
151101 23:09:41     2 Query     SELECT * FROM tbl01

「UNKNOWN_MYSQL_USER」による接続失敗のログが出力されていますが、これはMySQLのサービスを起動時に内部で接続のテストを行うのに使用されているもののようです。

実行されたSQLのログをテーブルに出力

実行されたSQLのログをテーブルに出力するには、以下のような設定を行います。

[mysqld]
:(略)
general_log=1
log_output=TABLE
  • 「log_output」に「TABLE」を指定すると、実行されたSQLのログがテーブルに出力されるようになります。

出力されるテーブルは「mysql.general_log」になります。

テーブルの構造は、以下のようになります。

mysql> SHOW CREATE TABLE mysql.general_log;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table       | Create Table                                                                                                                                                                                                                                                                                                                                                                  |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| general_log | CREATE TABLE `general_log` (
  `event_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_host` mediumtext NOT NULL,
  `thread_id` bigint(21) unsigned NOT NULL,
  `server_id` int(10) unsigned NOT NULL,
  `command_type` varchar(64) NOT NULL,
  `argument` mediumtext NOT NULL
) ENGINE=CSV DEFAULT CHARSET=utf8 COMMENT='General log' |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

MySQLのサービスを起動後に、「USE db01;」、「SELECT * FROM tbl01;」を実行した場合のテーブル「mysql.general_log」の内容は、以下のようになります。

mysql> SELECT * FROM mysql.general_log;
+---------------------+-------------------------------------+-----------+-----------+--------------+------------------------------------------------------------------------------+
| event_time          | user_host                           | thread_id | server_id | command_type | argument                                                                     |
+---------------------+-------------------------------------+-----------+-----------+--------------+------------------------------------------------------------------------------+
| 2015-11-01 23:09:21 | [UNKNOWN_MYSQL_USER] @ localhost [] |         1 |         0 | Connect      | UNKNOWN_MYSQL_USER@localhost on                                              |
| 2015-11-01 23:09:21 | [UNKNOWN_MYSQL_USER] @ localhost [] |         1 |         0 | Connect      | Access denied for user 'UNKNOWN_MYSQL_USER'@'localhost' (using password: NO) |
| 2015-11-01 23:09:28 | [root] @ localhost []               |         2 |         0 | Connect      | root@localhost on                                                            |
| 2015-11-01 23:09:28 | root[root] @ localhost []           |         2 |         0 | Query        | select @@version_comment limit 1                                             |
| 2015-11-01 23:09:34 | root[root] @ localhost []           |         2 |         0 | Query        | SELECT DATABASE()                                                            |
| 2015-11-01 23:09:34 | root[root] @ localhost []           |         2 |         0 | Init DB      | db01                                                                         |
| 2015-11-01 23:09:34 | root[root] @ localhost []           |         2 |         0 | Query        | show databases                                                               |
| 2015-11-01 23:09:34 | root[root] @ localhost []           |         2 |         0 | Query        | show tables                                                                  |
| 2015-11-01 23:09:34 | root[root] @ localhost []           |         2 |         0 | Field List   | tbl01                                                                        |
| 2015-11-01 23:09:41 | root[root] @ localhost []           |         2 |         0 | Query        | SELECT * FROM tbl01                                                          |
| 2015-11-01 23:11:18 | root[root] @ localhost []           |         2 |         0 | Query        | SELECT * FROM mysql.general_log                                              |
+---------------------+-------------------------------------+-----------+-----------+--------------+------------------------------------------------------------------------------+

「UNKNOWN_MYSQL_USER」による接続失敗のログが出力されていますが、これはMySQLのサービスを起動時に内部で接続のテストを行うのに使用されているもののようです。

実行されたSQLのログをファイルとテーブルに出力

実行されたSQLのログをファイルとテーブルに出力するには、以下のような設定を行います。

[mysqld]
:(略)
general_log=1
log_output=FILE,TABLE
  • 「log_output」に「FILE,TABLE」を指定すると、実行されたSQLのログがファイルとテーブルに出力されるようになります。

SQLで現在のログ出力の設定を確認

ログ出力が有効になっているかを確認

実行されたSQLのログ出力が有効になっているかを確認するには、以下のようなSQLを実行します。

mysql> SELECT @@GLOBAL.general_log;
+----------------------+
| @@GLOBAL.general_log |
+----------------------+
|                    0 |
+----------------------+
  • 「@@GLOBAL」は、グローバル変数を示し、MySQLのサービスを起動中の各接続のセッションで、デフォルトで使用される設定になります。

  • 「general_log」は、セッション変数としては扱われませんので、グローバル変数での設定が全ての接続に適用されます。

  • 「0」はログ出力が無効、「1」はログ出力が有効になっていることを示しています。

ログをファイルに出力する場合のファイルの保存先を確認

実行されたSQLのログをファイルに出力する場合の、ファイルの保存先を確認するには、以下のようなSQLを実行します。

mysql> SELECT @@GLOBAL.general_log_file;
+------------------------------+
| @@GLOBAL.general_log_file    |
+------------------------------+
| /var/lib/mysql/localhost.log |
+------------------------------+
  • 「general_log_file」は、セッション変数としては扱われませんので、グローバル変数での設定が全ての接続に適用されます。

ログの出力先を確認

実行されたSQLのログの出力先が、ファイルになっているか、テーブルになっているかを確認するには、以下のようなSQLを実行します。

mysql> SELECT @@GLOBAL.log_output;
+---------------------+
| @@GLOBAL.log_output |
+---------------------+
| FILE                |
+---------------------+
  • 「log_output」は、セッション変数としては扱われませんので、グローバル変数での設定が全ての接続に適用されます。

SQLでログ出力を設定

実行されたSQLのログへの出力は、設定ファイルで有効化されていなくても、SQLでグローバル変数の設定を行うことで、MySQLのサービスを起動中に一時的に有効、無効を切り替えることができます。

以下に示す各種のグローバル変数の設定は、MySQLのサービスを再起動すると、設定ファイルで指定されているものに戻ります。

ログ出力を有効に設定

実行されたSQLのログ出力を有効にするには、以下のようなSQLを実行します。

mysql> SET GLOBAL general_log = 'ON';
  • 「SET GLOBAL」文を使用して、グローバル変数の設定を行います。

  • 「general_log」に「ON」を指定すると、実行されたSQLのログ出力が有効になります。

    ※設定ファイルのように「1」を指定することはできません。

    ※「OFF」を指定すると、実行されたSQLのログ出力が無効になります。

ログをファイルに出力する場合のファイルの保存先を設定

実行されたSQLのログをファイルに出力する場合の、ファイルの保存先を設定するには、以下のようなSQLを実行します。

mysql> SET GLOBAL general_log_file = '/var/lib/mysql/localhost.log';
  • 「SET GLOBAL」文を使用して、グローバル変数の設定を行います。

  • 「general_log_file」にファイルの保存先を指定します。

    ※ファイルの保存先は、MySQLのプロセスが書き込み可能である必要があります。

ログの出力先を設定

実行されたSQLのログの出力先を設定するには、以下のようなSQLを実行します。

mysql> SET GLOBAL log_output = 'FILE';
  • 「SET GLOBAL」文を使用して、グローバル変数の設定を行います。

  • 「log_output」に「FILE」を指定すると、ログの出力先がファイルになります。

    ※「TABLE」を指定すると、ログの出力先がテーブルになります。

    ※「FILE,TABLE」を指定すると、ログの出力先がファイルとテーブルになります。

出力されるログファイルの切り替え

実行されたSQLのログをファイルに出力する場合、ログファイルを自動的に切り替える機能はないようです。

以下の手順で、手動でログファイルの切り替えを行うことができます。

※CentOSでの環境を例にしています。

以下のようなコマンドを実行して、現在出力中のログファイルの名前を変更します。

# mv /var/lib/mysql/localhost.log /var/lib/mysql/localhost.log-1

ファイル名を変更した段階では、変更したファイル(ここでは「localhost.log-1」)に継続してログが出力されます。

以下のSQLを実行すると、ログファイルの切り替えが行われます。

mysql> FLUSH GENERAL LOGS;
  • 「FLUSH LOGS」文を実行すると、ログファイルが再オープンされます。

  • 「FLUSH」と「LOGS」の間に何も指定しないと、バイナリーログやスロークエリーログ等、全てのログファイルが再オープンされます。

    「GENERAL」を指定すると、実行されたSQLのログファイルのみが再オープンされます。

FLUSH LOGS文の実行後は、ログが出力されるファイルは本来のもの(ここでは「localhost.log」)に戻ります。

テーブル「mysql.general_log」のデータを削除

実行されたSQLのログをテーブルに出力する場合の出力先のテーブル「mysql.general_log」は、DELETE文でデータの削除を行おうとしても、以下のようなエラーとなり、削除を行うことができません。

mysql> DELETE FROM mysql.general_log;
ERROR 1556 (HY000): You can't use locks with log tables.

テーブル「mysql.general_log」からデータの削除を行うには、TRUNCATE TABLE文を使用します。

mysql> TRUNCATE TABLE mysql.general_log;

MySQL 実行されたSQLをログに出力については、以上です。

SQLのテクニックに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのテクニックに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

SQLのパフォーマンスに関するおすすめの書籍

当サイトの書籍紹介ページで、SQLのパフォーマンスに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。

DB設計、SQLに関するおすすめの書籍

当サイトの書籍紹介ページで、DB設計、SQLに関するおすすめの書籍の紹介も行っておりますので、ご覧ください。