t;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();
Refer:
[1] 两个INSERT发生死锁原因剖析
http://bit.ly/2eJZdou
[2] MySQL 四种事务隔离级的说明
http://www.cnblogs.com/zhoujinyi/p/3437475.html
[3] innodb_rollback_on_timeout参数对锁的影响
http://q.fireflyclub.org/?/article/37
[4] 日均万条数据丢失,一个隐式骚操作导致的奇葩事故!
https://mp.weixin.qq.com/s/OpPV24ILewVc2lRDrV4vvA
[5] show tables能看到表却无法读写?
http://bit.ly/2x9a0Qy
[6] Mysql 锁机制笔记
http://www.jianshu.com/p/fa280356