下面来为各位介绍一个mysql查看sql语句执行历史记录的例子,如果你希望跟踪你程序的执行性能我们就可以使用下面方法,有需要了解的朋友可进入看看.

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

cat ~/.mysql_history

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

1. MariaDB [(none)]> SET GLOBAL log_output = 'TABLE';
2. Query OK, 0 rows affected (0.02 sec)
3. MariaDB [(none)]> SET GLOBAL general_log = 'ON';
4. Query OK, 0 rows affected (0.00 sec)
5. MariaDB [(none)]> show databases;
6. +--------------------+
7. | Database |
8. +--------------------+
9. | information_schema |
10. | gfan_log |
11. | gfan_pay |
12. | gfanpg |
13. | gfanrc |
14. | mysql |
15. | performance_schema |
16. | ucenter |
17. +--------------------+
18. 8 rows in set (0.02 sec)
19. MariaDB [(none)]> use mysql
20. Reading table information for completion of table and column names
21. You can turn off this feature to get a quicker startup with -A
22. Database changed
23. MariaDB [mysql]> show tables;
24. +---------------------------+
25. | Tables_in_mysql |
26. +---------------------------+
27. | columns_priv |
28. | db |
29. | event |
30. | func |
31. | general_log |
32. | help_category |
33. | help_keyword |
34. | help_relation |
35. | help_topic |
36. | host |
37. | ndb_binlog_index |
38. | plugin |
39. | proc |
40. | procs_priv |
41. | proxies_priv |
42. | servers |
43. | slow_log |
44. | tables_priv |
45. | time_zone |
46. | time_zone_leap_second |
47. | time_zone_name |
48. | time_zone_transition |
49. | time_zone_transition_type |
50. | user |
51. +---------------------------+
52. 24 rows in set (0.00 sec)
53. MariaDB [mysql]> select * from general_log limit 10;
54. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
55. | event_time | user_host | thread_id | server_id | command_type | argument |
56. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
57. | 2014-11-12 14:29:49.810999 | root[root] @ localhost [] | 1304 | 0 | Query | show databases |
58. | 2014-11-12 14:29:51.951747 | root[root] @ localhost [] | 1304 | 0 | Query | SELECT DATABASE() |
59. | 2014-11-12 14:29:51.973180 | root[root] @ localhost [] | 1304 | 0 | Init DB | mysql |
60. | 2014-11-12 14:29:51.975048 | root[root] @ localhost [] | 1304 | 0 | Query | show databases |
61. | 2014-11-12 14:29:51.975689 | root[root] @ localhost [] | 1304 | 0 | Query | show tables |
62. | 2014-11-12 14:29:51.976347 | root[root] @ localhost [] | 1304 | 0 | Field List | columns_priv | --phpfensi.com
63. | 2014-11-12 14:29:51.976800 | root[root] @ localhost [] | 1304 | 0 | Field List | db |
64. | 2014-11-12 14:29:51.977431 | root[root] @ localhost [] | 1304 | 0 | Field List | event |
65. | 2014-11-12 14:29:51.978052 | root[root] @ localhost [] | 1304 | 0 | Field List | func |
66. | 2014-11-12 14:29:51.978224 | root[root] @ localhost [] | 1304 | 0 | Field List | general_log |
67. +----------------------------+---------------------------+-----------+-----------+--------------+-------------------+
68. 10 rows in set (0.01 sec)
69. MariaDB [mysql]>
70. If you want to output to the log file:
71. SET GLOBAL log_output = “FILE”;
72. SET GLOBAL general_log_file = “/path/to/your/logfile.log”
73. SET GLOBAL general_log = ‘ON’;
74. Restart MySQL to apply the changes if you edit the config, e.g. /etc/mysql/my.cnf
75. Now, if you’d like you can tail -f /var/log/mysql/mysql.log