Powered by GitBook

日志

1.Mysql日志文件系统的组成

(a) error 日志：记录启动、运行或停止 mysqld 时出现的问题，默认开启。
(b) general 日志：通用查询日志，记录所有语句和指令，开启数据库会有 5% 左右性能损失。
(c) binlog 日志：二进制格式，记录所有更改数据的语句，主要用于 slave 复制和数据恢复。
(d) slow 日志：记录所有执行时间超过 long_query_time 秒的查询或不使用索引的查询，默认关闭。
(e) Innodb日志：innodb redo log、undo log，用于恢复数据和撤销操作。

2.Mysql日志分析脚本

crontab 部署，可以选择时间范围开启，每分钟采样一次日志，需要说明的是 general log 没事别轻易开启，否则对数据库性能损耗较大

# 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


################
# 开启 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();

results matching ""

No results matching ""