MySQL死鎖與日志相關(guān)經(jīng)驗(yàn)分享
最近線上 MySQL 接連發(fā)生了幾起數(shù)據(jù)異常,都是在凌晨爆發(fā),由于業(yè)務(wù)場(chǎng)景屬于典型的數(shù)據(jù)倉(cāng)庫(kù)型應(yīng)用,白天壓力較小無(wú)法復(fù)現(xiàn)。甚至有些異常還比較詭異,*** root cause 分析頗費(fèi)周折。那實(shí)際業(yè)務(wù)當(dāng)中咱們?nèi)绾文芸焖俚亩ㄎ痪€上 MySQL 問(wèn)題,修復(fù)異常呢?下文我會(huì)根據(jù)兩個(gè)實(shí)際 case,分享下相關(guān)的經(jīng)驗(yàn)與方法。
1、Case1:部分?jǐn)?shù)據(jù)更新失敗
某天渠道同學(xué)反饋某報(bào)表極個(gè)別渠道數(shù)據(jù)為 0,大部分渠道數(shù)據(jù)正常。這個(gè)數(shù)據(jù)是由一個(gè)統(tǒng)計(jì)程序每天凌晨例行更新的,按理來(lái)說(shuō),要么全部正常,要么全部失敗,那會(huì)是什么原因?qū)?**個(gè)別數(shù)據(jù)異常呢?
首先我們能想到的自然是根據(jù)統(tǒng)計(jì)任務(wù)日志來(lái)看了,但是看了統(tǒng)計(jì)程序打印的日志沒(méi)有發(fā)現(xiàn)諸如 SQL update 失敗的異常描述,那當(dāng)時(shí)的數(shù)據(jù)庫(kù)究竟發(fā)生了什么呢?在查看 MySQL-server 日志之前,習(xí)慣性的看了下數(shù)據(jù)庫(kù)狀態(tài):
SHOW ENGINE INNODB STATUS\G
恰好看到了凌晨這個(gè) update 發(fā)生了死鎖:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2017-07-17 04:09:01 0x7f6de03c8700 *** (1) TRANSACTION: TRANSACTION 215208479, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 LOCK WAIT 5 lock struct(s), heap size 1136, 3 row lock(s) MySQL thread id 27844824, OS thread handle 140092183037696, query id 412503674 10.126.95.84 zeye Searching rows for update update t_channel_final_datas set nr_register=133,nr_add_goods=29,nr_order_normal=11,nr_pay_normal=8,nr_order_special=0,nr_pay_special=0,n_add_user_num=16 where count_date='2017-07-16' and channel_id='16' and channel_type='10' and terminal='26' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208479 lock_mode X locks rec but not gap waiting Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 1: len 1; hex 30; asc 0;; 2: len 4; hex 80000010; asc ;; 3: len 4; hex 8009055e; asc ^;; *** (2) TRANSACTION: TRANSACTION 215208474, ACTIVE 0 sec fetching rows mysql tables in use 3, locked 3 6 lock struct(s), heap size 1136, 7 row lock(s) MySQL thread id 27844825, OS thread handle 140109890225920, query id 412503669 10.135.6.41 zeye Searching rows for update update t_channel_final_datas set nr_register=24,nr_add_goods=32,nr_order_normal=0,nr_pay_normal=0,nr_order_special=0,nr_pay_special=0,n_add_user_num=11 where count_date='2017-07-16' and channel_id='114' and channel_type='10' and terminal='116' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 464 page no 5459 n bits 392 index index_countdate_type_terminal of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap Record lock, heap no 304 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 10; hex 323031372d30372d3136; asc 2017-07-16;; 1: len 1; hex 30; asc 0;; 2: len 4; hex 80000010; asc ;; 3: len 4; hex 8009055e; asc ^;; ... *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 464 page no 4743 n bits 264 index PRIMARY of table `db_zz_flow`.`t_channel_final_datas` trx id 215208474 lock_mode X locks rec but not gap waiting Record lock, heap no 168 PHYSICAL RECORD: n_fields 32; compact format; info bits 0 0: len 4; hex 80090569; asc i;; 1: len 6; hex 00000cd3b9d0; asc ;; ... *** WE ROLL BACK TRANSACTION (1)
篇幅所限,上下文我這里省略了很多,從這段日志里可以看到,TRANSACTION 1 和 TRANSACTION 2 分別持有一定數(shù)量的行鎖,然后又等待對(duì)方的鎖,*** MySQL 檢測(cè)到 deadlock ,然后選擇回滾了 TRANSACTION 1:Innodb目前處理死鎖的方法是將持有最少行級(jí)排他鎖的事務(wù)進(jìn)行回滾。
那這里就有 3 個(gè)問(wèn)題了:
(1)innodb 行鎖不是只鎖一行?
因?yàn)檫@張表是 innodb 引擎的,InnoDB 支持行鎖和表鎖。而InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的,這一點(diǎn)MySQL與Oracle不同,后者是通過(guò)在數(shù)據(jù)塊中對(duì)相應(yīng)數(shù)據(jù)行加鎖來(lái)實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味著:只有通過(guò)索引條件檢索數(shù)據(jù),InnoDB才使用行級(jí)鎖,否則,InnoDB將使用表鎖,會(huì)把所有掃描過(guò)的行都鎖定!在實(shí)際應(yīng)用中,要特別注意InnoDB行鎖的這一特性,不然的話,可能導(dǎo)致大量的鎖沖突,從而影響并發(fā)性能。由于MySQL的行鎖是針對(duì)索引加的鎖,不是針對(duì)記錄加的鎖,所以雖然是訪問(wèn)不同行的記錄,但是如果是使用相同的索引鍵,是會(huì)出現(xiàn)鎖沖突的。當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù),并請(qǐng)求共享或排他鎖時(shí),InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加鎖;另外間隙鎖也會(huì)鎖多行,InnoDB除了通過(guò)范圍條件加鎖時(shí)使用間隙鎖外,如果使用相等條件請(qǐng)求給一個(gè)不存在的記錄加鎖,InnoDB也會(huì)使用間隙鎖!
話都說(shuō)到這了,那就看下咱們業(yè)務(wù)表的索引情況:
show INDEX from `db_zz_flow`.`t_channel_final_datas`; +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_namt | Packed | Null | Index_type | Comment | Index_comment | |-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------| | t_channel_final_datas | 0 | PRIMARY | 1 | id > | <null> | | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 1 | count_date> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 2 | channel_ty> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_type_terminal | 3 | terminal > | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_channelid | 1 | count_date> | <null> | YES | BTREE | | | | t_channel_final_datas | 1 | index_countdate_channelid | 2 | channel_id> | <null> | YES | BTREE | | | +-----------------------+--------------+-------------------------------+----------------+-------------+----------+--------+--------------+-----------+-----------------+
可以看到這張表的索引極不合理:有3個(gè)索引,但是 update 卻沒(méi)有完全的用上索引,導(dǎo)致 update 沒(méi)有精確的用上索引,需要鎖定多行范圍數(shù)據(jù),從而引發(fā)死鎖。
知道原理后,咱們?cè)倬臉?gòu)建一個(gè)四字段的組合索引即可讓 update 精準(zhǔn)的走 innodb 索引,實(shí)際上,我們更新索引后,這個(gè)死鎖問(wèn)題即得到了解決。
注:innodb不僅會(huì)打印出事務(wù)和事務(wù)持有和等待的鎖,而且還有記錄本身,不幸的是,它可能超過(guò)innodb為輸出結(jié)果預(yù)留的長(zhǎng)度(只能打印1M的內(nèi)容且只能保留最近一次的死鎖信息),如果你無(wú)法看到完整的輸出,此時(shí)可以在任意庫(kù)下創(chuàng)建innodb_monitor或innodb_lock_monitor表,這樣innodb status信息會(huì)完整且每15s一次被記錄到錯(cuò)誤日志中。如:create table innodb_monitor(a int)engine=innodb;,不需要記錄到錯(cuò)誤日志中時(shí)就刪掉這個(gè)表即可。
(2)回滾的話,為什么只有部分 update 語(yǔ)句失敗,而不是整個(gè)事務(wù)里的所有 update 都失敗?
這是因?yàn)樵蹅兊?innodb 默認(rèn)是自動(dòng)提交的:
show variables like 'autocommit'; +-----------------+---------+ | Variable_name | Value | |-----------------+---------| | autocommit | ON | +-----------------+---------+
在多個(gè) update 或 insert 語(yǔ)句情況下,每執(zhí)行完一條 SQL,innodb 就立即 commit 一次以持久化變更,同時(shí)釋放鎖,這也正是本例中死鎖回滾事務(wù)后只有極個(gè)別語(yǔ)句失敗的原因。
需要注意的是,通常還有另外一種情況也可能導(dǎo)致部分語(yǔ)句回滾,需要格外留意。在 innodb 里有個(gè)參數(shù)叫:innodb_rollback_on_timeout
show VARIABLES LIKE 'innodb_rollback_on_timeout' +----------------------------+---------+ | Variable_name | Value | |----------------------------+---------| | innodb_rollback_on_timeout | OFF | +----------------------------+---------+
官方手冊(cè)里這樣描述:
In MySQL 5.1, InnoDB rolls back only the last statement on a transaction timeout by default. If –innodb_rollback_on_timeout is specified, a transaction timeout causes InnoDB to abort and roll back the entire transaction (the same behavior as in MySQL 4.1). This variable was added in MySQL 5.1.15.
解釋:這個(gè)參數(shù)關(guān)閉或不存在的話遇到超時(shí)只回滾事務(wù)***一個(gè)Query,打開(kāi)的話事務(wù)遇到超時(shí)就回滾整個(gè)事務(wù)。
(3)怎樣降低 innodb 死鎖幾率?
死鎖在行鎖及事務(wù)場(chǎng)景下很難完全消除,但可以通過(guò)表設(shè)計(jì)和SQL調(diào)整等措施減少鎖沖突和死鎖,包括:
- 盡量使用較低的隔離級(jí)別,比如如果發(fā)生了間隙鎖,你可以把會(huì)話或者事務(wù)的事務(wù)隔離級(jí)別更改為 RC(read committed)級(jí)別來(lái)避免,但此時(shí)需要把 binlog_format 設(shè)置成 row 或者 mixed 格式
- 精心設(shè)計(jì)索引,并盡量使用索引訪問(wèn)數(shù)據(jù),使加鎖更精確,從而減少鎖沖突的機(jī)會(huì);
- 選擇合理的事務(wù)大小,小事務(wù)發(fā)生鎖沖突的幾率也更??;
- 給記錄集顯示加鎖時(shí),***一次性請(qǐng)求足夠級(jí)別的鎖。比如要修改數(shù)據(jù)的話,***直接申請(qǐng)排他鎖,而不是先申請(qǐng)共享鎖,修改時(shí)再請(qǐng)求排他鎖,這樣容易產(chǎn)生死鎖;
- 不同的程序訪問(wèn)一組表時(shí),應(yīng)盡量約定以相同的順序訪問(wèn)各表,對(duì)一個(gè)表而言,盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機(jī)會(huì);
- 盡量用相等條件訪問(wèn)數(shù)據(jù),這樣可以避免間隙鎖對(duì)并發(fā)插入的影響;
- 不要申請(qǐng)超過(guò)實(shí)際需要的鎖級(jí)別;除非必須,查詢時(shí)不要顯示加鎖;
- 對(duì)于一些特定的事務(wù),可以使用表鎖來(lái)提高處理速度或減少死鎖的可能。
2、Case2:詭異的 Lock wait timeout
連續(xù)幾天凌晨6點(diǎn)和早上8點(diǎn) 都分別有一個(gè)任務(wù)失敗,load data local infile 的時(shí)候報(bào) Lock wait timeout exceeded try restarting transaction innodb 的 Java SQL 異常,和平臺(tái)的同學(xué)溝通得知,這是我們自己的業(yè)務(wù)數(shù)據(jù)庫(kù)的 Lock 時(shí)間太短或者鎖沖突的問(wèn)題。但是回頭一想不應(yīng)該???這不一直好好的嗎?而且基本都是單表單任務(wù),不存在多人沖突。
甭管誰(shuí)的問(wèn)題,那咱們還是先看自己的數(shù)據(jù)庫(kù)有沒(méi)有問(wèn)題:
show variables like 'innodb_lock_wait_timeout'; +--------------------------+---------+ | Variable_name | Value | |--------------------------+---------| | innodb_lock_wait_timeout | 50 | +--------------------------+---------+
默認(rèn) lock 超時(shí)時(shí)間 50s,這個(gè)時(shí)間真心不短了,估計(jì)調(diào)了也沒(méi)用,事實(shí)上確實(shí)死馬當(dāng)活馬醫(yī)的試了下沒(méi)用。。。
而且這次 SHOW ENGINE INNODB STATUS\G 也沒(méi)出現(xiàn)任何死鎖信息,然后又將目光轉(zhuǎn)向 MySQL-server 日志,希望能從日志里看一看那個(gè)時(shí)刻前后數(shù)據(jù)究竟在做什么操作。這里先簡(jiǎn)單的介紹下MySQL日志文件系統(tǒng)的組成:
(a) error 日志:記錄啟動(dòng)、運(yùn)行或停止 mysqld 時(shí)出現(xiàn)的問(wèn)題,默認(rèn)開(kāi)啟。
(b) general 日志:通用查詢?nèi)罩?,記錄所有語(yǔ)句和指令,開(kāi)啟數(shù)據(jù)庫(kù)會(huì)有 5% 左右性能損失。
(c) binlog 日志:二進(jìn)制格式,記錄所有更改數(shù)據(jù)的語(yǔ)句,主要用于 slave 復(fù)制和數(shù)據(jù)恢復(fù)。
(d) slow 日志:記錄所有執(zhí)行時(shí)間超過(guò) long_query_time 秒的查詢或不使用索引的查詢,默認(rèn)關(guān)閉。
(e) Innodb日志:innodb redo log、undo log,用于恢復(fù)數(shù)據(jù)和撤銷操作。
從上面的介紹可以看到,目前這個(gè)問(wèn)題的日志可能在 d 和 b 中,看了下 d 中沒(méi)有,那就只能開(kāi)啟 b 了,但 b 對(duì)數(shù)據(jù)庫(kù)的性能有一定損耗,由于是全量日志,量非常巨大,所以開(kāi)啟一定要謹(jǐn)慎:
-- general_log 日志默認(rèn)關(guān)閉,開(kāi)啟會(huì)影響數(shù)據(jù)庫(kù) 5% 左右性能: show variables like 'general%'; +------------------+---------------------------------+ | Variable_name | Value | |------------------+---------------------------------| | general_log | OFF | | general_log_file | /opt/data/mysql/tjtx-103-26.log | +------------------+---------------------------------+ -- 全局 session 級(jí)別開(kāi)啟: set global general_log=1 -- 如果需要對(duì)當(dāng)前 session 生效需要: set general_log=1 -- set 指令設(shè)置的動(dòng)態(tài)參數(shù)在 MySQL 重啟后失效,如果需要***生效需要在 /etc/my.cnf 中配置靜態(tài)變量/參數(shù)。 -- 如果不知道 my.cnf 位置,可以根據(jù) mysql -? | grep ".cnf" 查詢 order of preference, my.cnf, $MYSQL_TCP_PORT, /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf
我這里只是每天在出問(wèn)題的前后半小時(shí)開(kāi)啟下全量日志,結(jié)果沒(méi)有發(fā)現(xiàn)任何 MySQL-client 請(qǐng)求到我們的業(yè)務(wù)數(shù)據(jù)庫(kù)!該日志格式如下,記錄了所有的連接與命令:
/usr/sbin/mysqld, Version: 5.7.12-log (MySQL Community Server (GPL)). started with: Tcp port: 3306 Unix socket: /opt/data/mysql/mysql.sock Time Id Command Argument 2017-07-20T21:45:01.880828Z28556028 Quit 2017-07-20T21:45:02.708621Z28401469 Query SELECT 1 2017-07-20T21:45:02.736734Z28556029 Connect ooxx@127.0.0.1 on db_zz_system using TCP/IP 2017-07-20T21:45:02.737242Z28556029 Query /* mysql-connector-java-5.1.6 ( Revision: ${svn.Revision} ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'init_connect' 2017-07-20T21:45:02.738868Z28556029 Query SHOW COLLATION 2017-07-20T21:45:02.739941Z28556029 Query SET character_set_results = NULL 2017-07-20T21:45:02.740275Z28556029 Query SET autocommit=1
那問(wèn)題基本確定了,客戶端請(qǐng)求都沒(méi)到我們這邊就拋出了上述的異常,和平臺(tái)方再三溝通確認(rèn)下,***平臺(tái)方查證是因?yàn)樵趫?zhí)行插入前他們需要先從 SQL task 表取出 SQL 和更新 task 狀態(tài),結(jié)果這張表由于在整點(diǎn)存在大量 insert 和 update 并發(fā),導(dǎo)致部分 SQL 等待 lock 超時(shí)了。。。
3、MySQL 日志分析腳本
由于凌晨是數(shù)據(jù)倉(cāng)庫(kù)的業(yè)務(wù)高峰,很多問(wèn)題都是在這個(gè)時(shí)候爆發(fā),一些詭異的問(wèn)題往往是過(guò)了這個(gè)村就沒(méi)這個(gè)店了,白天無(wú)法復(fù)現(xiàn)。如何能捕獲我們關(guān)心的日志,便于快速的定位問(wèn)題,這個(gè)是重中之重,這里我寫(xiě)了個(gè)小腳本,crontab 部署,可以選擇時(shí)間范圍開(kāi)啟,每分鐘采樣一次日志,需要說(shuō)明的是 general log 沒(méi)事別輕易開(kāi)啟,否則對(duì)數(shù)據(jù)庫(kù)性能損耗較大。
# crontab 部署方式: # */1 0-10 * * * cd /opt/ooxx/script && bash mysql_perf.sh >> logs/mysql_perf.log.`date -I` 2>&1 date -Iseconds echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> top -bn1|head' top -bn1|head echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SHOW ENGINE INNODB STATUS\G ' mysql -uroot -pooxx -h127.0.0.1 -e 'SHOW ENGINE INNODB STATUS\G' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show open tables where in_use>0;' mysql -uroot -pooxx -h127.0.0.1 -e 'show open tables where in_use>0;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show full processlist;' mysql -uroot -pooxx -h127.0.0.1 -e 'show full processlist;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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`;' echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> SELECT * FROM `information_schema`.`innodb_locks`;' mysql -uroot -pooxx -h127.0.0.1 -e 'SELECT * FROM `information_schema`.`innodb_locks`;' echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show status like '%lock%';" mysql -uroot -pooxx -h127.0.0.1 -e "show status like '%lock%';" echo echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> show global status like "table_locks%";' mysql -uroot -pooxx -h127.0.0.1 -e 'show global status like "table_locks%";' echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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" echo echo ">>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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" echo date -Iseconds echo '>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>==================================================================================' echo file_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 -delete fi ################ # 開(kāi)啟 general_log 全量明細(xì)日志會(huì)降低數(shù)據(jù)庫(kù) 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; # 自動(dòng)提交 # -- show variables like 'autocommit'; # 慢查詢 # -- show variables like '%slow_query_log%'; # set global 只對(duì)當(dāng)前數(shù)據(jù)庫(kù)生效,如果MySQL重啟后則會(huì)失效。如果要***生效,就必須修改配置文件my.cnf(其它系統(tǒng)變量也是如此)。 # 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'; #開(kāi)啟general_log日志 # -- show variables like 'general%'; #可以在my.cnf里添加,1開(kāi)啟(0關(guān)閉),當(dāng)然了,這樣要重啟才能生效,有點(diǎn)多余了 #general-log = 1 #log = /log/mysql_query.log路徑 #也可以設(shè)置變量那樣更改,1開(kāi)啟(0關(guān)閉),即時(shí)生效,不用重啟,***當(dāng)然是這樣的了 # set global general_log=1 #這個(gè)日志對(duì)于操作頻繁的庫(kù),產(chǎn)生的數(shù)據(jù)量會(huì)很快增長(zhǎng),出于對(duì)硬盤(pán)的保護(hù),可以設(shè)置其他存放路徑 #set global general_log_file=/tmp/general_log.log #mysql記錄客戶端IP:init_connect,有super權(quán)限的用戶是不記錄的, # 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 ,開(kāi)啟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();