my sql 有类似Sql Server Profiler的工具吗?

jvhmr 2016-04-25 02:24:15
我用.net 项目调用my sql 实现事务操作,但回滚无效,

我想看看真实执行了些什么sql语句。
...全文
235 6 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
6 条回复
切换为时间正序
请发表友善的回复…
发表回复
LongRui888 2016-04-26
  • 打赏
  • 举报
回复
引用 5 楼 gikod 的回复:
[quote=引用 4 楼 yupeigu 的回复:] [quote=引用 2 楼 jvhmr 的回复:] [quote=引用 1 楼 gikod 的回复:] 如果server是开发自己用的,可以考虑general_log记录所有sql。

set global general_log = on;
执行你的程序
set global general_log = off;
相应的sql会记录在 select @@global.general_log_file 指定的文件里
这个是记录所有连接到这个my sql 的所有程序的sql命令, 还是只记录规定范围的sql 命令?[/quote] 所有的 不过你可以把这些数据插入到表里,然后直接从表里查询[/quote] 如果你的server同时会有别人用,这样筛选出你自己的sql。

MariaDB [mysql]> set log_output = 'table';
ERROR 1229 (HY000): Variable 'log_output' is a GLOBAL variable and should be set with SET GLOBAL
MariaDB [mysql]> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select 'a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)

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

MariaDB [mysql]> select 'a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.01 sec)

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

MariaDB [mysql]> set global log_output='file';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select * from mysql.general_log;
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| event_time          | user_host                    | thread_id | server_id | command_type | argument                   |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| 2016-04-25 19:02:15 | root[root] @ localhost [::1] |         3 |         1 | Query        | select 'a'                 |
| 2016-04-25 19:02:24 | root[root] @ localhost [::1] |         3 |         1 | Query        | set global general_log=off |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> show columns from mysql.general_log;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                | Null | Key | Default           | Extra                       |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| event_time   | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host    | mediumtext          | NO   |     | NULL              |                             |
| thread_id    | bigint(21) unsigned | NO   |     | NULL              |                             |
| server_id    | int(10) unsigned    | NO   |     | NULL              |                             |
| command_type | varchar(64)         | NO   |     | NULL              |                             |
| argument     | mediumtext          | NO   |     | NULL              |                             |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> select * from mysql.general_log where user_host = 'root[root] @ localhost [::1]' and thread_id = 3;
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| event_time          | user_host                    | thread_id | server_id | command_type | argument                   |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| 2016-04-25 19:02:15 | root[root] @ localhost [::1] |         3 |         1 | Query        | select 'a'                 |
| 2016-04-25 19:02:24 | root[root] @ localhost [::1] |         3 |         1 | Query        | set global general_log=off |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
2 rows in set (0.04 sec)
注意不要在生产环境上用,会有附加的开销。[/quote]
gikod 2016-04-25
  • 打赏
  • 举报
回复
引用 4 楼 yupeigu 的回复:
[quote=引用 2 楼 jvhmr 的回复:] [quote=引用 1 楼 gikod 的回复:] 如果server是开发自己用的,可以考虑general_log记录所有sql。

set global general_log = on;
执行你的程序
set global general_log = off;
相应的sql会记录在 select @@global.general_log_file 指定的文件里
这个是记录所有连接到这个my sql 的所有程序的sql命令, 还是只记录规定范围的sql 命令?[/quote] 所有的 不过你可以把这些数据插入到表里,然后直接从表里查询[/quote] 如果你的server同时会有别人用,这样筛选出你自己的sql。

MariaDB [mysql]> set log_output = 'table';
ERROR 1229 (HY000): Variable 'log_output' is a GLOBAL variable and should be set with SET GLOBAL
MariaDB [mysql]> set global log_output = 'table';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select 'a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.00 sec)

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

MariaDB [mysql]> select 'a';
+---+
| a |
+---+
| a |
+---+
1 row in set (0.01 sec)

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

MariaDB [mysql]> set global log_output='file';
Query OK, 0 rows affected (0.00 sec)

MariaDB [mysql]> select * from mysql.general_log;
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| event_time          | user_host                    | thread_id | server_id | command_type | argument                   |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| 2016-04-25 19:02:15 | root[root] @ localhost [::1] |         3 |         1 | Query        | select 'a'                 |
| 2016-04-25 19:02:24 | root[root] @ localhost [::1] |         3 |         1 | Query        | set global general_log=off |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
2 rows in set (0.00 sec)

MariaDB [mysql]> show columns from mysql.general_log;
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| Field        | Type                | Null | Key | Default           | Extra                       |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
| event_time   | timestamp           | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| user_host    | mediumtext          | NO   |     | NULL              |                             |
| thread_id    | bigint(21) unsigned | NO   |     | NULL              |                             |
| server_id    | int(10) unsigned    | NO   |     | NULL              |                             |
| command_type | varchar(64)         | NO   |     | NULL              |                             |
| argument     | mediumtext          | NO   |     | NULL              |                             |
+--------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)

MariaDB [mysql]> select * from mysql.general_log where user_host = 'root[root] @ localhost [::1]' and thread_id = 3;
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| event_time          | user_host                    | thread_id | server_id | command_type | argument                   |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
| 2016-04-25 19:02:15 | root[root] @ localhost [::1] |         3 |         1 | Query        | select 'a'                 |
| 2016-04-25 19:02:24 | root[root] @ localhost [::1] |         3 |         1 | Query        | set global general_log=off |
+---------------------+------------------------------+-----------+-----------+--------------+----------------------------+
2 rows in set (0.04 sec)
注意不要在生产环境上用,会有附加的开销。
LongRui888 2016-04-25
  • 打赏
  • 举报
回复
引用 2 楼 jvhmr 的回复:
[quote=引用 1 楼 gikod 的回复:] 如果server是开发自己用的,可以考虑general_log记录所有sql。

set global general_log = on;
执行你的程序
set global general_log = off;
相应的sql会记录在 select @@global.general_log_file 指定的文件里
这个是记录所有连接到这个my sql 的所有程序的sql命令, 还是只记录规定范围的sql 命令?[/quote] 所有的 不过你可以把这些数据插入到表里,然后直接从表里查询
ACMAIN_CHM 2016-04-25
  • 打赏
  • 举报
回复
记录所有操作。
jvhmr 2016-04-25
  • 打赏
  • 举报
回复
引用 1 楼 gikod 的回复:
如果server是开发自己用的,可以考虑general_log记录所有sql。

set global general_log = on;
执行你的程序
set global general_log = off;
相应的sql会记录在 select @@global.general_log_file 指定的文件里
这个是记录所有连接到这个my sql 的所有程序的sql命令, 还是只记录规定范围的sql 命令?
gikod 2016-04-25
  • 打赏
  • 举报
回复
如果server是开发自己用的,可以考虑general_log记录所有sql。

set global general_log = on;
执行你的程序
set global general_log = off;
相应的sql会记录在 select @@global.general_log_file 指定的文件里
Twitter Digg Facebook Del.icio.us Reddit Stumbleupon Newsvine Technorati Mr. Wong Yahoo! Google Windows Live Send as Email Add to your CodeProject bookmarks Discuss this article 85 Print Article Database » Database » Other databasesLicence CPOL First Posted 19 Jan 2012 Views 24,219 Downloads 992 Bookmarked 74 times RaptorDB - The Key Value Store V2 By Mehdi Gholam | 8 Mar 2012 | Unedited contribution C#.NETDBABeginnerIntermediateAdvanceddatabase Even faster Key/Value store nosql embedded database engine utilizing the new MGIndex data structure with MurMur2 Hashing and WAH Bitmap indexes for duplicates. See Also More like this More by this author Article Browse Code Stats Revisions (8) Alternatives 4.95 (56 votes) 1 2 3 4 5 4.95/5 - 56 votes μ 4.95, σa 1.05 [?] Is your email address OK? You are signed up for our newsletters but your email address is either unconfirmed, or has not been reconfirmed in a long time. Please click here to have a confirmation email sent so we can confirm your email address and start sending you newsletters again. Alternatively, you can update your subscriptions. Add your own alternative version Introduction What is RaptorDB? Features Why another data structure? The problem with a b+tree Requirements of a good index structure The MGIndex Page Splits Interesting side effects of MGIndex The road not taken / the road taken and doubled back! Performance Tests Comparing B+tree and MGIndex Really big data sets! Index parameter tuning Performance Tests - v2.3 Using the Code Differences to v1 Using RaptorDBString and RaptorDBGuid Global parameters RaptorDB interface Non-clean shutdowns Removing Keys Unit tests File Formats File Format : *.mgdat File Format : *.mgbmp File Format : *.mgidx File Format : *.mgbmr , *.mgrec History Download RaptorDB_v2.0.zip - 38.7 KB Download RaptorDB_v2.1.zip - 39 KB Download RaptorDB_v2.2.zip - 39 KB Download RaptorDB_v2.3.zip - 39.6 KB D

56,940

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧