mysql - 解析 mysql.log 文件

Question

我想解析mysql.log文件。这是我的文件的内容。我想打印时间戳、thread_id、命令、参数。如果行中缺少某些内容，则打印该特定值。就像第一行一样，“700”是 thread_id，“Quit”是命令，但没有时间和参数。因此仅打印 thread_id 和命令。

/usr/sbin/mysqld, Version: 5.5.61-0ubuntu0.14.04.1-log ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
      700 Quit  
190207 11:11:24   701 Connect   root@localhost on db_services
      701 Query SET SESSION sql_mode =
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                @@sql_mode,
                "STRICT_ALL_TABLES,", ""),
                ",STRICT_ALL_TABLES", ""),
                "STRICT_ALL_TABLES", ""),
                "STRICT_TRANS_TABLES,", ""),
                ",STRICT_TRANS_TABLES", ""),
                "STRICT_TRANS_TABLES", "")
      701 Query SET NAMES utf8
190207 11:11:26   701 Quit  
190207 11:11:27   702 Connect   root@localhost on db_services
      702 Query SET SESSION sql_mode =
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                @@sql_mode,
                "STRICT_ALL_TABLES,", ""),
                ",STRICT_ALL_TABLES", ""),
                "STRICT_ALL_TABLES", ""),
                "STRICT_TRANS_TABLES,", ""),
                ",STRICT_TRANS_TABLES", ""),
                "STRICT_TRANS_TABLES", "")
      702 Query SET NAMES utf8
      702 Quit  
190207 11:11:29   703 Connect   root@localhost on db_services
      703 Query SET SESSION sql_mode =
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                @@sql_mode,
                "STRICT_ALL_TABLES,", ""),
                ",STRICT_ALL_TABLES", ""),
                "STRICT_ALL_TABLES", ""),
                "STRICT_TRANS_TABLES,", ""),
                ",STRICT_TRANS_TABLES", ""),
                "STRICT_TRANS_TABLES", "")
      703 Query SET NAMES utf8
      703 Quit  
      704 Connect   root@localhost on db_services
      704 Query SET SESSION sql_mode =
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                @@sql_mode,
                "STRICT_ALL_TABLES,", ""),
                ",STRICT_ALL_TABLES", ""),
                "STRICT_ALL_TABLES", ""),
                "STRICT_TRANS_TABLES,", ""),
                ",STRICT_TRANS_TABLES", ""),
                "STRICT_TRANS_TABLES", "")
      704 Query SET NAMES utf8
190207 11:11:30   704 Query SELECT `clientID`
FROM `authentication`
      704 Query SELECT `privatekey`
FROM `authentication`
WHERE `clientID` = '1'

输出就像

timestamp: '', thread_id:'700', command:'Quit', argument:''
timestamp: '190207 11:11:24', thread_id:'701', command:'Connect', argument:'root@localhost on db_services'
timestamp: '', thread_id:'701', command:'Query', argument:'SET SESSION sql_mode =
                REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
                @@sql_mode,
                "STRICT_ALL_TABLES,", ""),
                ",STRICT_ALL_TABLES", ""),
                "STRICT_ALL_TABLES", ""),
                "STRICT_TRANS_TABLES,", ""),
                ",STRICT_TRANS_TABLES", ""),
                "STRICT_TRANS_TABLES", "")'

and so on.

Answer 1

您可以将日志信息直接存储在如下表中:

SET GLOBAL log_output = 'FILE,TABLE';
SET GLOBAL general_log = 'ON';

或

SET GLOBAL log_output = 'TABLE';
SET GLOBAL general_log = 'ON';

然后MySQL(在本例中为MariaDB)在表mysql.general_log中，您可以通过“SELECT”直接读取您想要的信息。请参阅:https://mariadb.com/kb/en/library/writing-logs-into-tables/

示例

MariaDB [(none)]> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SET GLOBAL general_log = 'ON';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> SELECT * from mysql.general_log limit 10;
+----------------------------+------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
| event_time                 | user_host                          | thread_id | server_id | command_type | argument                                                        |
+----------------------------+------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
| 2019-02-08 08:48:39.476877 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | SELECT seq FROM seq_1_to_100                                    |
| 2019-02-08 08:48:43.076693 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | SHOW TABLE STATUS LIKE 'general_log'                            |
| 2019-02-08 08:48:43.077482 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | SHOW CREATE TABLE `general_log`                                 |
| 2019-02-08 08:48:43.088938 | root[root] @ localhost [127.0.0.1] |         8 |         1 | Query        | SELECT * FROM `general_log` LIMIT 0,1000                        |
| 2019-02-08 08:53:33.262970 | [root] @ localhost []              |        10 |         1 | Connect      | root@localhost as anonymous on                                  |
| 2019-02-08 08:53:33.266018 | [root] @ localhost []              |        10 |         1 | Connect      | Access denied for user 'root'@'localhost' (using password: YES) |
| 2019-02-08 08:53:35.666988 | [root] @ localhost []              |        11 |         1 | Connect      | root@localhost as anonymous on                                  |
| 2019-02-08 08:53:35.674512 | root[root] @ localhost []          |        11 |         1 | Query        | select @@version_comment limit 1                                |
| 2019-02-08 08:53:57.407500 | root[root] @ localhost []          |        11 |         1 | Query        | SET GLOBAL log_output = 'TABLE'                                 |
| 2019-02-08 08:54:06.251004 | root[root] @ localhost []          |        11 |         1 | Query        | SET GLOBAL general_log = 'ON'                                   |
+----------------------------+------------------------------------+-----------+-----------+--------------+-----------------------------------------------------------------+
10 rows in set (0.000 sec)

MariaDB [(none)]>