MySQL死锁与日志相关经验分享

(c) binlog 日记：二进制格局，记录所有更改数据的语句，重要用于 slave 复制和数据恢复。
(d) slow 日记：记录所有履行时光跨越 long_query_time 秒的萌芽或不应用索引的萌芽，默认封闭。
(e) Innodb日记：innodb redo log、undo log，用于恢复数据和撤销操作。

大年夜膳绫擎的介绍可以看到，今朝这个问题的日记可能在 d 和 b 中，看了下 d 中没有，那就只能开启 b 了，但 b 对数据库的机能有必定损耗，因为是全量日记，量异常巨大年夜，所以开启必定要谨慎：

-- general_log 日记默认封闭，开启会影响数据库 5% 阁下机能：show variables like 'general%';+------------------+---------------------------------+| Variable_name    | Value                           ||------------------+---------------------------------|| general_log      | OFF                             || general_log_file | /opt/data/mysql/tjtx-103-26.log |+------------------+---------------------------------+-- 全局 session 级别开启：set global general_log=1-- 如不雅须要对当前 session 生效须要：set general_log=1-- set 指令设置的动态参数在 MySQL 重启后掉效，如不雅须要永远生效须要在 /etc/my.cnf 中设备静态变量/参数。-- 如不雅不知道 my.cnf 地位，可以根据 mysql -? | grep ".cnf" 萌芽                      order of preference, my.cnf, $MYSQL_TCP_PORT,/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf

这是因为咱们的 innodb 默认是主动提交的：

我这里只是天天在出问题的前后半小时开启下全量日记，结不雅没有发明任何 MySQL-client 请求到我们的营业数据库！该日记格局如下，记录了所有的连接与敕令：

因为凌晨是数据仓库的营业岑岭，很多问题都是在这个时刻爆发，一些诡异的问题往往是过了这个村就没这个店了，日间无法复现。若何能捕获我们关怀的日记，便于快速的定位问题，这个是重中之重，这里我写了个小脚本，crontab 安排，可以选择时光范围开启，每分钟采样一次日记，须要解释的是 general log 没事别随便马虎开启，不然对数据库机能损耗较大年夜。

# crontab 安排方法：# */1 0-10 * * * cd /opt/ooxx/script && bash mysql_perf.sh >> logs/mysql_perf.log.`date -I` 2>&1date -Isecondsecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> top -bn1|head'top -bn1|head  echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SHOW ENGINE INNODB STATUS\G 'mysql -uroot -pooxx -h127.0.0.1 -e 'SHOW ENGINE INNODB STATUS\G'echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show open tables where in_use>0;'mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;'echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show full processlist;'mysql -uroot -pooxx -h127.0.0.1 -e 'show full processlist;'echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; 'mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;' echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_locks`;'mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_locks`;'echoecho ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show status like '%lock%';"mysql -uroot -pooxx -h127.0.0.1 -e "show status like '%lock%';"echoecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show global status like "table_locks%";'mysql -uroot -pooxx -h127.0.0.1 -e 'show global status like "table_locks%";'echoecho ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..."mysql -uroot -pooxx -h127.0.0.1 -e "select r.trx_isolation_level, r.trx_id waiting_trx_id, r.trx_mysql_thread_id  waiting_trx_thread, r.trx_state  waiting_trx_state, lr.lock_mode waiting_trx_lock_mode, lr.lock_type  waiting_trx_lock_type, lr.lock_table  waiting_trx_lock_table, lr.lock_index  waiting_trx_lock_index, r.trx_query  waiting_trx_query, b.trx_id  blocking_trx_id, b.trx_mysql_thread_id  blocking_trx_thread, b.trx_state  blocking_trx_state, lb.lock_mode blocking_trx_lock_mode, lb.lock_type  blocking_trx_lock_type, lb.lock_table  blocking_trx_lock_table, lb.lock_index  blocking_trx_lock_index, b.trx_query  blocking_query from  information_schema.innodb_lock_waits  w  inner  join  information_schema.innodb_trx b  on  b.trx_id=w.blocking_trx_id  inner  join  information_schema.innodb_trx  r on  r.trx_id=w.requesting_trx_id  inner  join   information_schema.innodb_locks  lb on  lb.lock_trx_id=w.blocking_trx_id inner  join   information_schema.innodb_locks  lr on  lr.lock_trx_id=w.requesting_trx_id\G"echoecho ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> too long omit..."mysql -uroot -pooxx -h127.0.0.1 -e "SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id,  b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON  b.trx_id = w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON  r.trx_id = w.requesting_trx_id\G"echodate -Isecondsecho '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>=================================================================================='echofile_name=mysql_perf.log.`date -I`if [[ `date +%-H%-M` == 1059 ]]then    cd /opt/ooxx/script/logs    chmod 777 /home/work/ooxx/$file_name    find /home/work/ooxx -name 'mysql_perf.log.*' -mtime +7 -delete        cd /opt/data/mysql    cp tjtx-ooxx-slow.log /home/work/ooxx/tjtx-ooxx-slow.log.`date -I`    chmod 777 /home/work/ooxx/tjtx-ooxx-slow.log.`date -I`    find /home/work/ooxx -name 'tjtx-ooxx-slow.log.*' -mtime +7 -delete        cp mysqld.log /home/work/ooxx/mysqld.log.`date -I`    chmod 777 /home/work/ooxx/mysqld.log.`date -I`    find /home/work/ooxx -name 'mysqld.log.*' -mtime +7 -deletefi################# 开启 general_log 全量明细日记会降低数据库 5% 机能#if [[ "`date +%H%M`" == "0545" ]]#then#   echo "`date +%H%M` ------- set global general_log=1;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;'#elif [[ "`date +%H%M`" == "0630" ]]#then#   echo "`date +%H%M` ------- set global general_log=0;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;'#elif [[ "`date +%H%M`" == "0745" ]]#then#   echo "`date +%H%M` ------- set global general_log=1;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;'#elif [[ "`date +%H%M`" == "0830" ]]#then#   echo "`date +%H%M` ------- set global general_log=0;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;'#elif [[ "`date +%H%M`" == "0001" ]]#then#   echo "`date +%H%M` ------- set global general_log=1;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=1;'#elif [[ "`date +%H%M`" == "0002" ]]#then#   echo "`date +%H%M` ------- set global general_log=0;"#   mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;'#fi#[[ 10#`date +%H%M` -lt 10#0550 ||  10#`date +%H%M` -gt 10#0830 ]] && echo "`date +%H%M` ------- set global general_log=0;" && mysql -uroot -pooxx -h127.0.0.1 -e 'set global general_log=0;'# mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;show full processlist;SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;SELECT * FROM `information_schema`.`innodb_locks`;SHOW ENGINE INNODB STATUS\G'# --show variables like '%tx_isolation%';# --SELECT @@GLOBAL.tx_isolation, @@tx_isolation, @@session.tx_isolation;# --  SET GLOBAL tx_isolation = 'READ-COMMITTED';# # --show variables like '%timeout%';# --show variables like 'innodb_lock_wait_timeout';# --  SET GLOBAL innodb_lock_wait_timeout=60# # --show variables like 'long_query_time';# --  SET global long_query_time=3;# --  show variables like 'innodb_rollback_on_timeout';# --  show VARIABLES like '%max_allowed_packet%';# --  set global max_allowed_packet = 100*1024*1024;# 主动提交# -- show variables like 'autocommit';# 慢萌芽# -- show variables  like '%slow_query_log%';# set global 只对当前数据库生效，如不雅MySQL重启后则会掉效。如不雅要永远生效，就必须修改设备文件my.cnf（其它体系变量也是如斯）。# set global slow_query_log=1;# -- show variables like 'long_query_time%';# set global long_query_time=4;# show global variables like 'long_query_time'; # select sleep(5);# -- show variables like 'log_queries_not_using_indexes';# set global log_queries_not_using_indexes=1;# -- show variables like 'log_slow_admin_statements';# -- show global status like '%Slow_queries%';# http://www.cnblogs.com/kerrycode/p/5593204.html# -- show variables like "%time_zone%";#set global time_zone = '+8:00';#开启general_log日记# -- show variables like 'general%';#可以在my.cnf里添加,1开启（0封闭）,当然了,如许要重启才能生效,有点多余了#general-log = 1#log = /log/mysql_query.log路径#也可以设置变量那样更改,1开启（0封闭）,即时生效,不消重启,首选当然是如许的了#  set global general_log=1#这个日记对于操作频繁的库,产生的数据量会很快增长,出于对硬盘的保护,可以设置其他存放路径#set global general_log_file=/tmp/general_log.log#mysql记录客户端IP：init_connect，有super权限的用户是不记录的，# create  table t1 ( cur_user varchar(100), n_user varchar(100),in_time timestamp  default current_timestamp()) ;# set global init_connect='insert into test.t1 (cur_user,n_user) values (current_user(),user())';   # SHOW CREATE TABLE mysql.general_log\G ，开启general_log日记也行：# https://dba.stackexchange.com/questions/33654/mysql-logging-activity-from-specific-user-or-ip              #SELECT REVERSE(SUBSTRING_INDEX(REVERSE(USER()),'@',1)) as ip;#SELECT SUBSTRING(USER(), LOCATE('@', USER())+1) as ip;#select SUBSTRING_INDEX(host,':',1) as 'ip' from information_schema.processlist WHERE ID=connection_id();	
			 3/4   首页 上一页 1 2 3 4 下一页 尾页	
			

　　推荐阅读
　　厉害了！ 崇礼环卫率先迈入数字化时代
            
            环卫工人清洗垃圾桶清扫保洁二组，收到请答复?”“保洁二组收到。”“娱乐中间有大年夜片的烟头，垃圾桶外有裸露的垃圾，通知该组保洁人员尽快处理。”7月21日上>>>详细阅读


本文标题：MySQL死锁与日志相关经验分享
地址：http://www.17bianji.com/lsqh/36448.html
 1/2    1