mysql查看sql语句执行历史记录的例子

mysql查看sql语句执行历史记录

 cat ~/.mysql_history
对于mysql版本5.1以后的版本，可以通过以下方式启动日志记录。能够记录下包括应用程序执行的sql语句。

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

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

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| gfan_log |
| gfan_pay |
| gfanpg |
| gfanrc |
| mysql |
| performance_schema |
| ucenter |
+--------------------+
8 rows in set (0.02 sec)

MariaDB [(none)]> use mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| event |
| func |
| general_log |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| servers |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
24 rows in set (0.00 sec)

MariaDB [mysql]> select * from general_log limit 10;
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
| 2014-11-12 14:29:49.810999 | root[root] @ localhost [] | 1304 | 0 | Query | show databases |
| 2014-11-12 14:29:51.951747 | root[root] @ localhost [] | 1304 | 0 | Query | SELECT DATABASE() |
| 2014-11-12 14:29:51.973180 | root[root] @ localhost [] | 1304 | 0 | Init DB | mysql |
| 2014-11-12 14:29:51.975048 | root[root] @ localhost [] | 1304 | 0 | Query | show databases |
| 2014-11-12 14:29:51.975689 | root[root] @ localhost [] | 1304 | 0 | Query | show tables |
| 2014-11-12 14:29:51.976347 | root[root] @ localhost [] | 1304 | 0 | Field List | columns_priv |
| 2014-11-12 14:29:51.976800 | root[root] @ localhost [] | 1304 | 0 | Field List | db |
| 2014-11-12 14:29:51.977431 | root[root] @ localhost [] | 1304 | 0 | Field List | event |
| 2014-11-12 14:29:51.978052 | root[root] @ localhost [] | 1304 | 0 | Field List | func |
| 2014-11-12 14:29:51.978224 | root[root] @ localhost [] | 1304 | 0 | Field List | general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
10 rows in set (0.01 sec)

MariaDB [mysql]>

If you want to output to the log file:
SET GLOBAL log_output = “FILE”;
SET GLOBAL general_log_file = “/path/to/your/logfile.log”
SET GLOBAL general_log = ‘ON’;
Restart MySQL to apply the changes if you edit the config, e.g. /etc/mysql/my.cnf
Now, if you’d like you can tail -f /var/log/mysql/mysql.log