mysql如何查看存储过程 mysql查看存储过程执行日志

查看MySQL数据库的SQL语句的执行记录日志

方法1：可以使用processlist查看SQL执行语句，但是有个弊端，就是只能查看正在执行的sql语句，无法查看历史执行的语句。

> use information_schema;
> show processlist;

或者

> select * from information_schema.`PROCESSLIST` where info is not null;
 +--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
 | ID     | USER | HOST      | DB                 | COMMAND | TIME | STATE                | INFO                                                                  | TIME_MS | STAGE | MAX_STAGE | PROGRESS |
 +--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
 | 347182 | root | localhost | information_schema | Query   |    0 | Filling schema table | select * from information_schema.`PROCESSLIST` where info is not null |   0.502 |     0 |         0 |    0.000 |
 +--------+------+-----------+--------------------+---------+------+----------------------+-----------------------------------------------------------------------+---------+-------+-----------+----------+
 1 row in set (0.00 sec)

方法2：开启数据库的日志模式，通过日志查看历史执行记录

* 查看当前配置
 > show variables like '%log_output%'; 
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | log_output    | FILE  |
 +---------------+-------+> show variables like '%general_log%';
 +------------------+------------------------------+
 | Variable_name    | Value                        |
 +------------------+------------------------------+
 | general_log      | OFF                          |
 | general_log_file | /var/log/mariadb/mariadb.log |
 +------------------+------------------------------+

* 开启&关闭日志模式（可选择输出到表或文件中）：

> SET GLOBAL log_output = 'FILE';  SET GLOBAL general_log = 'ON';   //日志开启（日志输出到文件）
 > SET GLOBAL log_output = 'FILE';  SET GLOBAL general_log = 'OFF';  //日志关闭

或者

> SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'ON';   //日志开启（日志输出到表：mysql.general_log）
 > SET GLOBAL log_output = 'TABLE'; SET GLOBAL general_log = 'OFF';  //日志关闭> show variables like '%log_output%'; 
 +---------------+-------+
 | Variable_name | Value |
 +---------------+-------+
 | log_output    | FILE  |
 +---------------+-------+> show variables like '%general_log%';
 +------------------+------------------------------+
 | Variable_name    | Value                        |
 +------------------+------------------------------+
 | general_log      | ON                           |
 | general_log_file | /var/log/mariadb/mariadb.log |
 +------------------+------------------------------+ * 查看日志文件中的执行记录：
 #tail -f /var/log/mariadb/mariadb.log
 ......
 347189 Query INSERT INTO `table_copy1` (`id`, `col02_str`, `col03_str`, `col04_int`, `col05_integer`, `col06_bigint`) 
  VALUES ( 1,  'aaaa',  'bbbbb',  20,  30,  655360),
         ( 2,  'xxxx',  'yyyy',  21,  333,  65536000),
         ( 3,  'asasdf',  'sdkfjdkf',  55,  900,  2222),
         ( 4,  'test',  'testaaa',  11,  22,  33),
         ( 5,  'test05',  'testssssss',  222,  222,  222),
         ( 6,  'test06',  'aaaa',  111,  111,  222),
         ( 7,  'aaaa',  'bbbb',  22,  22,  22),
         ( 8,  '你好',  'hello',  25,  25,  25),
         ( 9,  'aaa',  'aaa',  11,  11,  11),
         ( 10,  '',  'bbbbddddrrrrssss4444',  null,  null,  null),
 347189 Query    commit
 ......* 表查询执行记录：
 > SELECT * from mysql.general_log ORDER BY event_time DESC;* 日志查询执行记录
 > SELECT * from mysql.general_log ORDER BY event_time DESC;* 清空表
 > truncate table mysql.general_log;  //该表仅支持truncate不支持delete