当系统发生sql 死锁的时候把相关信息记录到某个日志里

我用的是crontab每隔一段时间执行一次观察复杂脚本但无法完全避免数据丢失如果  db 能否把当天所有死锁的死锁能完全纪录就好了(换句话说一有死锁马上给我发消息) 对了可以用慢查询的   (问题解决了)
Mysql管理必备工具Maatkit详解之五（mk-deadlock-logger）
2009年05月20日　作者：大头刚　
mk-deadlock-logger - 查看mysql的死锁信息。安装方法可以参考
这里
。
有2种方式可以查看死锁信息。第一种方法是直接打印出来：
$ mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --print
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2007-03-08T20:34:22 81 21309 29 baron localhost  test c GEN_CLUST_INDEX RECORD X w 1 select * from c for update
localhost 2007-03-08T20:34:22 83 21310 19 baron localhost  test a GEN_CLUST_INDEX RECORD X w 0 select * from a for update
OK，很简单的命令，查看最后出现的死锁的信息。当然，你也可以指定想看的信息：
$ mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --print -C ts,user,hostname,db,tbl,idx
ts user hostname db tbl idx
2007-03-08T20:34:22 baron localhost test c GEN_CLUST_INDEX
2007-03-08T20:34:22 baron localhost test a GEN_CLUST_INDEX
第二种方法是把信息储存到指定的表内。首先创建这个表：
mysql> use test;
Database changedmysql> CREATE TABLE deadlocks (
    ->    server char(20) NOT NULL,
    ->    ts datetime NOT NULL,
    ->    thread int unsigned NOT NULL,
    ->    txn_id bigint unsigned NOT NULL,
    ->    txn_time smallint unsigned NOT NULL,
    ->    user char(16) NOT NULL,
    ->    hostname char(20) NOT NULL,
    ->    ip char(15) NOT NULL, -- alternatively, ip int unsigned NOT NULL
    ->    db char(64) NOT NULL,
    ->    tbl char(64) NOT NULL,
    ->    idx char(64) NOT NULL,
    ->    lock_type char(16) NOT NULL,
    ->    lock_mode char(1) NOT NULL,
    ->    wait_hold char(1) NOT NULL,
    ->    victim tinyint unsigned NOT NULL,
    ->    query text NOT NULL,
    ->    PRIMARY KEY  (server,ts,thread)
    ->  ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.00 sec)mk-deadlock-logger --source u=sg,p=xxxx,h=localhost --dest D=test,\
t=deadlocks --dest D=test,t=deadlocks --daemonize -m 4h  -i 30s
OK，这条语句就是，把死锁信息存入deadlocks表，并在4小时内每30秒检查1次。
mysql5.0以后，出现死锁的情况是越来越少了，如果不是innodb引擎。出现死锁的情况，特别的少见。
OK,还有其他的一些参数，可以看下帮助文件。
mk-deadlock-logger --help
mk-deadlock-logger extracts and saves information about the most recent deadlock
in a MySQL server.  You need to specify whether to print the output or save it
in a database.  For more details, please use the --help option, or try 'perldoc
mk-deadlock-logger' for complete documentation.
Usage: mk-deadlock-logger --source DSN
Options:
  --askpass          Prompt for a password when connecting to MySQL
  --[no]collapse -c  Collapse whitespace in queries to a single space
  --columns      -C  Output only this comma-separated list of columns
  --daemonize        Fork and run in the background; POSIX OSes only
  --dest         -d  DSN for where to store deadlocks
  --help             Show this help message
  --interval     -i  How often to check for deadlocks (default 0s).  Optional
                     suffix s=seconds, m=minutes, h=hours, d=days; if no suffix,
                     s is used.
  --numip        -n  Express IP addresses as integers
  --print        -p  Print results on standard output
  --setvars          Set these MySQL variables (default wait_timeout=10000)
  --source       -s  DSN to check for deadlocks; required
  --tab          -t  Print tab-separated columns, instead of aligned
  --time         -m  How long to run before exiting.  Optional suffix s=seconds,
                     m=minutes, h=hours, d=days; if no suffix, s is used.
  --version          Output version information and exit
  Specify at least one of --print or --dest.
  DSN values in --dest default to values from --source.
DSN syntax is key=value[,key=value...]  Allowable DSN keys:
  KEY  COPY  MEANING
  ===  ====  =============================================
  A    yes   Default character set
  D    yes   Database to use
  F    yes   Only read default options from the given file
  P    yes   Port number to use for connection
  S    yes   Socket file to use for connection
  h    yes   Connect to host
  p    yes   Password to use when connecting
  t    yes   Table in which to store deadlock information
  u    yes   User for login if not current user
  If the DSN is a bareword, the word is treated as the 'h' key.
Options and values after processing arguments:
  --askpass          FALSE
  --collapse         FALSE
  --columns          (No value)
  --daemonize        FALSE
  --dest             (No value)
  --help             TRUE
  --interval         0
  --numip            FALSE
  --print            FALSE
  --setvars          wait_timeout=10000
  --source           (No value)
  --tab              FALSE
  --time             (No value)
  --version          FALSE
本文来自ChinaUnix博客，如果查看原文请点：http://blog.chinaunix.net/u3/111930/showart_2184839.html
XaprbStay curious!
Introducing MySQL Deadlock Loggerwith one commentMySQL Deadlock LoggerI’m continuing to add new tools to the MySQL Toolkit. MySQL Deadlock Logger is for extracting and storing information about the latest recorded InnoDB deadlock. It’s not only easy to view the information from the command line, it’s dead simple to store it back into a MySQL table for analysis. I think most users will find it handy to create a cron job to record the deadlocks automatically for later analysis.Like the rest of the tools in the toolkit, tried to to make this tool follow the UNIX philosophy of doing one thing and doing it well. My emphasis is on command-line scriptability combined with ease of human use. To that end, I designed the default output to be easy to pipe into awk or other tools, but I also made the tool smartly look for your .my.cnf file for database connection parameters.
How to view the latest deadlockBecause I have my database connection parameters in my .my.cnf file, I can run the deadlock logger with just the –print option and see a compact summary of the latest deadlock:$ mysql-deadlock-logger --print
server ts thread txn_id txn_time user hostname ip db tbl idx lock_type lock_mode wait_hold victim query
localhost 2007-03-08T20:34:22 81 21309 29 baron localhost  test c GEN_CLUST_INDEX RECORD X w 1 select * from c for update
localhost 2007-03-08T20:34:22 83 21310 19 baron localhost  test a GEN_CLUST_INDEX RECORD X w 0 select * from a for updateIt’s easy to specify which of those bits of data you want to see, too:$ mysql-deadlock-logger --print -C ts,user,hostname,db,tbl,idx
ts user hostname db tbl idx
2007-03-08T20:34:22 baron localhost test c GEN_CLUST_INDEX
2007-03-08T20:34:22 baron localhost test a GEN_CLUST_INDEXIf you’ve spent much time poring over the output of SHOW INNODB STATUS, you no doubt see what an improvement this is. You also might see some resemblance to innotop‘s Deadlock mode. It’s no coincidence, of course; I copied some of the parsing code.
How to save the data in a tableIt’s easy to save the data into a table for analysis. Once you’ve created the table with the query included in the documentation, you just specify the –dest option, like this: mysql-deadlock-logger --dest deadlocks.If it weren’t reading from my .my.cnf file, I’d have to be more specific: mysql-deadlock-logger --dest baron:password@localhost/test.deadlocks. That’s really easy to run from a cron job.Here’s what ends up in the table:mysql> select ts, user, hostname, db, tbl, idx from deadlocks;
+---------------------+-------+-----------+------+-----+-----------------+
| ts                  | user  | hostname  | db   | tbl | idx             |
+---------------------+-------+-----------+------+-----+-----------------+
| 2007-03-08 20:34:22 | baron | localhost | test | c   | GEN_CLUST_INDEX |
| 2007-03-08 20:34:22 | baron | localhost | test | a   | GEN_CLUST_INDEX |
+---------------------+-------+-----------+------+-----+-----------------+I’m only including some of the columns so the output isn’t too big, but you get the idea.
SummaryMySQL Deadlock Logger does the heavy lifting of parsing relevant information from SHOW INNODB STATUS so you can view or save it for analysis. If you have any feedback, I’d be glad to hear it; please use the MySQL Toolkit Forums for support or bug reports.

调试易

当系统发生sql 死锁的时候把相关信息记录到某个日志里

解决方案 »