MySQL General Query Log

MySQL General Query Log


==参考文档==
The General Query Log: http://dev.mysql.com/doc/refman/5.1/en/query-log.html
Selecting General Query and Slow Query Log Output Destinations: http://dev.mysql.com/doc/refman/5.1/en/log-destinations.html
mysql-5.1.52.tar.gz mysql-5.1.52.tar.gz


==系统变量==
MySQL 5.1.29+
* general_log 普通查询日志开关
* general_log_file 普通查询日志文件名
* log_output 日志输出方式
* sql_log_off 会话级普通查询日志开关


==代码分析==

* 日志相关操作的代码文件是：sql/log.cc和sql/log.h

* 记录日志函数只有两个：general_log_write和general_log_print
** 两者区别主要是输入的参数不一样，general_log_print是可变参数的，general_log_write参数数量是固定的

* 函数内判断操作是否需要记录日志。判断依据是：

  if (*general_log_handler_list && (what_to_log & (1L << (uint) command)))

what_to_log变量在sql/mysqld.cc初始化为二进制的15个1（COM_TIME等于15）

what_to_log= ~ (1L << (uint) COM_TIME);

因此判断条件(what_to_log & (1L << (uint) command))总是真的，默认所有操作都被记录。好像没有手工设置what_to_log的地方？

* MySQL其它程序调general_log_write或general_log_print函数写日志，比如当建立连接、退出、查询等操作时。经查看代码，有以下地方记录日志：
** sql/sql_connect.cc
*** check_user(connect和change user都调用此函数)
**** ER_NOT_SUPPORTED_AUTH_MODE和ER_SERVER_IS_IN_SECURE_AUTH_MODE,ER_ACCESS_DENIED_ERROR记录日志：general_log_print COM_CONNECT
**** 认证通过后也记录日志：general_log_print command(COM_CONNECT或COM_CHANGE_USER)
** sql/sql_parse.cc
*** COM_INIT_DB
**** use命令执行成功后记录日志 general_log_write
*** COM_QUERY general_log_write
*** COM_FIELD_LIST general_log_print
*** COM_QUIT general_log_print
*** COM_CREATE_DB general_log_print
*** COM_DROP_DB general_log_write
*** COM_BINLOG_DUMP general_log_print
*** COM_REFRESH general_log_print
*** COM_SHUTDOWN general_log_print
*** COM_STATISTICS general_log_print
*** COM_PROCESS_INFO general_log_print
*** COM_DEBUG general_log_print
** sql/sql_prepare.cc
*** mysqld_stmt_reset general_log_print (COM_STMT_RESET)
*** mysqld_stmt_close general_log_print (COM_STMT_CLOSE)
*** mysql_stmt_get_longdata general_log_print (COM_STMT_SEND_LONG_DATA)
*** Prepared_statement::prepare
**** 存储过程内语句不记录日志 general_log_write COM_STMT_PREPARE
*** Prepared_statement::execute
**** 存储过程内语句不记录日志 general_log_write COM_STMT_EXECUTE
** sql/sp_head.cc
*** sp_instr_stmt::execute
**** general_log_write COM_QUERY
** sql/sql_show.cc
*** mysqld_show_create_db
*** 当权限不够时记录日志 general_log_print COM_INIT_DB
** sql/log_event.cc
*** Query_log_event::do_apply_event
**** If the query was not ignored, it is printed to the general log general_log_write COM_QUERY
*** Xid_log_event::do_apply_event
**** For a slave Xid_log_event is COMMIT general_log_print COM_QUERY
** sql/slave.cc
*** connect_to_master
**** slave重连接master时记录日志 general_log_print COM_CONNECT_OUT
** sql/sql_db.cc
*** mysql_change_db
**** ER_DBACCESS_DENIED_ERROR无权限访问数据库 general_log_print COM_INIT_DB

* 这两个函数内分别调用了LOGGER::general_log_write和LOGGER::general_log_print方法
* LOGGER::general_log_print又调用了LOGGER::general_log_write
* LOGGER::general_log_write根据log_output变量设置的日志输出方式，可能有多个，再调用当前日志句柄中的log_general方法

* Log_to_csv_event_handler::log_general

...


==格式==

===表===
# 当前时间
# 用户名和主机名
## 格式是：priv_user[user] @ host[ip]
# 线程标识
# 命令类型
# 查询语句

===文件===
# 当前时间
# 线程标识
# 命令类型
# 查询语句


==DML操作==
不支持DML操作

===delete===
不能delete日志表

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

避免报错，可rename表名后再操作

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
rename table general_log to general_log_tmp;
delete from general_log_tmp;
rename table general_log_tmp to general_log;
SET GLOBAL general_log = @old_log_state;

====相关代码====
sql/lock.cc

int mysql_lock_tables_check(THD *thd, TABLE **tables, uint count, uint flags)
...
      if ((t->reginfo.lock_type >= TL_READ_NO_INSERT)
          || (thd->lex->sql_command == SQLCOM_LOCK_TABLES))
      {
        my_error(ER_CANT_LOCK_LOG_TABLE, MYF(0));
        DBUG_RETURN(1);
      }
...

==DDL操作==
一些DDL操作不被支持

===drop===
不支持在线drop操作

mysql> drop table mysql.general_log;
ERROR 1580 (HY000): You cannot 'DROP' a log table if logging is enabled

====相关代码====
sql/sql_table.cc

int mysql_rm_table_part2(THD *thd, TABLE_LIST *tables, bool if_exists,
...
  /* Disable drop of enabled log tables, must be done before name locking */
  for (table= tables; table; table= table->next_local)
  {
    if (check_if_log_table(table->db_length, table->db,
                           table->table_name_length, table->table_name, 1))
    {
      my_error(ER_BAD_LOG_STATEMENT, MYF(0), "DROP");
      pthread_mutex_unlock(&LOCK_open);
      DBUG_RETURN(1);
    }
  }
...

===truncate===
支持在线truncate


==存储引擎==
默认用CSV存储引擎。只支持CSV或MyISAM

mysql> set global general_log=0;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mysql.general_log engine=innodb;
ERROR 1579 (HY000): This storage engine cannot be used for log tables"

修改为Innodb存储引擎：

SET @old_log_state = @@global.general_log;
SET GLOBAL general_log = 'OFF';
use mysql
drop table if exists general_log_new;
create table general_log_new like general_log;
alter table general_log_new engine=innodb;
rename table general_log to general_log_old, general_log_new to general_log;
show create table general_log\G
SET GLOBAL general_log = @old_log_state;

====相关代码====
sql/sql_table.cc

bool mysql_alter_table(THD *thd,char *new_db, char *new_name,
...
  /*
    Check if we attempt to alter mysql.slow_log or
    mysql.general_log table and return an error if
    it is the case.
    TODO: this design is obsolete and will be removed.
  */
  if (table_list && table_list->db && table_list->table_name)
  {
    int table_kind= 0;

    table_kind= check_if_log_table(table_list->db_length, table_list->db,
                                   table_list->table_name_length,
                                   table_list->table_name, 0);

    if (table_kind)
    {
      /* Disable alter of enabled log tables */
      if (logger.is_log_table_enabled(table_kind))
      {
        my_error(ER_BAD_LOG_STATEMENT, MYF(0), "ALTER");
        DBUG_RETURN(TRUE);
      }

      /* Disable alter of log tables to unsupported engine */
      if ((create_info->used_fields & HA_CREATE_USED_ENGINE) &&
          (!create_info->db_type || /* unknown engine */
           !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
      {
        my_error(ER_UNSUPORTED_LOG_ENGINE, MYF(0));
        DBUG_RETURN(TRUE);
      }

#ifdef WITH_PARTITION_STORAGE_ENGINE
      if (alter_info->flags & ALTER_PARTITION)
      {
        my_error(ER_WRONG_USAGE, MYF(0), "PARTITION", "log table");
        DBUG_RETURN(TRUE);
      }
#endif
    }
  }
...

HTON_SUPPORT_LOG_TABLES 表示该存储引擎是否支持日志表

$ grep -r HTON_SUPPORT_LOG_TABLES *
sql/sql_table.cc:           !(create_info->db_type->flags & HTON_SUPPORT_LOG_TABLES)))
sql/handler.h:#define HTON_SUPPORT_LOG_TABLES      (1 << 7) //Engine supports log tables
storage/myisam/ha_myisam.cc:  myisam_hton->flags= HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES;
storage/csv/ha_tina.cc:  tina_hton->flags= (HTON_CAN_RECREATE | HTON_SUPPORT_LOG_TABLES |

-fin-