Linux查看BIOS信息：dmidecode命令以及SMBIOS、DMI等

################################# 脚本
### 获取系统信息
dmidecode | grep -A8 "System Information"
System Information
Manufacturer: Dell Inc.
Product Name: PowerEdge R710
Version: Not Specified
Serial Number: xxxxxxx
UUID: ***
Wake-up Type: Power Switch
SKU Number: Not Specified
Family: Not Specified


### CPU 相关
# 总核数 = 物理CPU个数 X 每颗物理CPU的核数 
# 总逻辑CPU数 = 物理CPU个数 X 每颗物理CPU的核数 X 超线程数

# 查看物理CPU个数
cat /proc/cpuinfo| grep "physical id"| sort| uniq| wc -l

# 查看每个物理CPU中core的个数(即核数)
cat /proc/cpuinfo| grep "cpu cores"| uniq

# 查看逻辑CPU的个数
cat /proc/cpuinfo| grep "processor"| wc -l

### 查询有效网卡及对应IP
grep IPADDR /etc/sysconfig/network-scripts/ifcfg-*

### 查询每个IP对应的网卡
ifconfig -a | grep -B 1 -E "inet addr" | awk 'BEGIN{str=""} {if($1=="inet"){split($2,a,":");print str,a[2];str=""}else{str=$1};} END {if(str!=""){print str}}'
ip a | grep  -E "inet.*scope global.*" | grep -v "secondary" | awk '{split($2,v,"/");print v[1], $NF}'


### 切换 VIP
ifconfig bond0:1 down
ifconfig bond0:1 10.10.100.100 netmask 255.255.255.0
arping -Uq -b -c 2 -s 10.10.100.100 -I bond0 10.10.100.254

### 查看1年前的备份档备份档
ls -l *``date -d -1year-1day +"%y-%m-%d"`` *.tar.gz
### 查看最早的10个备份档
ls *.tar.gz | awk '{if($0 ~ "full"){pos=5}else{pos=6};print "\t",substr($0,pos)}' | sort | head -n 10

### 查看所有进程ID
ls /proc/ | grep "^[0-9]"| awk '{if($0>100){print $0}}' 

### 获取swap占用最多的10个进程
# name,pid,vmswap
ls /proc/ | grep "^[0-9]"| awk '{if($0>100){print $0}}' | xargs -i grep -E "^(Pid|Name|VmSwap):" /proc/{}/status 2>/dev/null | awk 'BEGIN{str=""}{if($1=="Name:"){print str;str=""};str=str$0" ";}END{print str}' | grep VmSwap | sort -rnk 6 | head -n 10

# web: pid swap
for i in $( cd /proc;ls |grep "^[0-9]"|awk ' $0 >100') ;do awk '/Swap:/{a=a+$2}END{print '"$i"',a/1024"M"}' /proc/$i/smaps 2>/dev/null ; done | sort -k2nr | head -10

### 查看当前每个IP的连接数量,按连接数量倒叙显示
# $port
netstat -antp | grep "3306.*10" | awk '{split($5,a,":");sip=a[1];print sip}' | sort | uniq -c | sort -nr
netstat -antp | grep "3306.*ESTAB" | awk '{split($4,d,":");split($5,s,":");dip=d[1];sip=s[1];print sip"-->"dip}' | sort | uniq -c | sort -nr


### 查看mysql连接的创建时间
# $port
# netstat -antp | grep -E "$port.*ESTAB" | head -n 1 | awk '{split($7,a,"/");pid=a[1];print pid}' | xargs -i lsof -p {} | grep -E "TCP.*ESTAB" | awk '{print "/proc/"$2"/fd/"$4+0}' | xargs -i stat {}  | grep -E "(File|Modify)"
netstat -antp | grep -E "3306.*ESTAB" | head -n 1 | awk '{split($7,a,"/");pid=a[1];print pid}' | xargs -i lsof -p {} | grep -E "TCP.*ESTAB" | awk '{print "/proc/"$2"/fd/"$4+0}' | xargs -i stat {}  | grep -E "(File|Modify)"

# linux user:mysql
lsof -u mysql | grep -E "TCP.*ESTAB" | awk '{print "/proc/"$2"/fd/"$4+0}' | xargs -i stat {}
lsof -u mysql | grep -E "TCP.*ESTAB" | awk '{print "/proc/"$2"/fd/"$4+0}' | xargs -i stat {} | grep -E "(File|Modify)"
lsof -u mysql | grep -E "TCP.*ESTAB" | awk '{print "/proc/"$2"/fd/"$4+0}' | xargs -i stat {} | grep -E "(File|Access|Modify)"

### 计算modify page. 多BP情况
mysqll -u* -p * -e "show engine innodb status\G" | grep "Modified db pages" | head -n 1 | awk '{print $4}'

### 计算purge 数据量
mysql -u* -p * -e "show engine innodb status\G" | grep "History list length" | awk '{print $4}'

### 获取死锁信息
mysqll -u* -p * -e "show engine innodb status\G" | awk 'BEGIN{begin=0;} {if($0=="LATEST DETECTED DEADLOCK"){begin=1};if($0=="TRANSACTIONS"){end=1};if(end==1){exit};if(begin==1){print $0}}'

### 获取mysql的socket
sockets=`ps -ef | grep "mysql" | awk '{for(i=1;i
### 获取本机IP:不包括内网IP
ipaddr=`ifconfig | grep "inet addr" | awk '{print $2}' | awk -F':' '{print $2}' | grep -v -E '^(10\.|127\.)'`

### LOADAVG
# 从loadavg监控数据中提取session或net大于上限的内容
awk '{se=$7;split($9,a,".");net=a[1];if (se>50 || net>1000){print $0}}' /root/loadaver/2014-04-*.log | grep -v "DATE" | more

# 带颜色
awk '{pr=0;col="41;37;1m";se=$7;split($9,a,".");net=a[1];if (se>50){pr=1;se="\033["col" "se" \033[0m"}else{se=$7};if(net>1000){pr=1;net="\033["col" "net" \033[0m"}; if(pr==1){print -e $1,$2,"\t",$3,"\t",$4$5,"\t",$6,"\t",se,"\t",$8,"\t",net,"(Kb/s)\t",$10,"\t",$11}}' /root/loadaver/2014-04-*.log | grep -v "DATE" | more

awk '{print $1,"\t",$2,"\t",$3,"\t",$4,$5,"\t",$6,"\t",$7,"\t",$8,"\t",$9,"\t",$10,"\t",$11}' /tmp/gq-session/p.log | grep -v "DATE" | more

# 获取父进程ID，名称
paid=`ps axo pid,ppid | grep -E "^$$" | awk '{print $2}'`
paname=`ps -ef | grep  $paid | awk -v pid=$paid '{if($2==pid){sh="";for(i=8;i<=NF;i++){sh=sh""$i" "};{print sh}}}'`
echo "parent id=$paid, name=$paname"

### 修复zabbix 服务
su - zabbix -c "sh /usr/local/zabbix/plugins/DualMysql/bin/run_mpm_agent.sh > /tmp/run_mpm_agent.log  2>&1 &"
/etc/init.d/zabbix_agentd restart

################################# general log
# 重组general log
awk 'BEGIN{line=""} {if ($2=="Query"){gsub(/\t/," ", line);gsub(/[[:blank:]]+/," ",line);print line;line=$0}else{line=line" "$0}} END{print line}' general.log

# 提取general log中drop、truncate、delete的sql，但没有user信息
awk 'BEGIN{line=""} {if ($2=="Query"){gsub(/\t/," ", line);gsub(/[[:blank:]]+/," ",line);split(line, a," ");type=tolower(a[3]);if(type ~ "(drop|truncate|delete)"){print line};line=$0}else{line=line" "$0}} END{print line}' general.log

# 过滤 general log 中某类sql
awk 'BEGIN{line=""} {if ($2=="Query"){gsub(/\t/," ", line);gsub(/[[:blank:]]+/," ",line);split(line, a," ");type=tolower(a[3]);if(type ~ "(update|insert|delete)"){print type,line};line=$0}else{line=line" "$0}} END{print line}' general.log 

################################# binlog
# 提取binlog中不含列信息的其他sql
mysqlbinlog -vv --base64-output=DECODE-ROWS xxxx.binlog | grep -E "^(### UPDATE|### WHERE|### SET|### INSERT|### DELETE|BEGIN|COMMIT|# at|#1|SET)" > 2c.log

### mysql binlog 中事务大小分布
# 每个事务大小的分布
mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.000003 | grep "^# at" | awk 'BEGIN{lastpt=0} {curpt=$3;incre=curpt-lastpt;lastpt=$3;print incre}'

# 分档统计:100,1000, 1W, 10W+
mysqlbinlog -vv --base64-output=DECODE-ROWS mysql-bin.000007 | grep "^# at" | awk 'BEGIN{lastpt=0;cnt0=0;cn100=0;cnt1k=0;cnt1W=0;cnt10W=0} {curpt=$3;incre=curpt-lastpt;lastpt=$3;if(incre<100){cnt0=cnt0+1;}else if(incre>=100 && incre<1000){cnt100=cnt100+1;}else if(incre>=1000 && incre<10000){cnt1w=cnt1k+1;}else if(incre>=10000 && incre<100000){cnt1W=cnt1W+1}else{cnt10W=cnt10W+1}} END{print "0---100:"cnt0"\n100--1K:"cnt100"\n1K---1W:"cnt1k"\n1W--10W:"cnt1W"\n10W+   :"cnt10W}'

################################# 慢查询
# 获取 IP
grep "User@Host" mysql-slow.log | sort | uniq

# 提取慢查询日志中查询时间大于n的内容
# mysql-slow.log，按查询时间倒叙排列
grep -n -E "^#" mysql-slow.log | sort -n -r -k 3 | awk '{split($1,b,":");rn=b[1];split($3,a,".");val=a[1];if(val>0){print rn","rn+1}}' | xargs  -i sed -n "{}p" mysql-slow.log > t.slowlog

# 提取某段时间慢查询日志 
 awk -F'=' -v vmin=`date -d "2014-04-15 17:00:00" "+%s"` -v vmax=`date -d "2014-04-15 17:20:00" "+%s"`  'BEGIN{pr=0} {if ($0 ~ "^SET timestamp=") {a=substr($2,1,10);if(a >= vmin && a<=vmax) {pr=1} else {pr=0}}; if(pr==1){print $0}}'  mysql-slow.log > /tmp/slow.log

pt-query-digest /tmp/slow.log > /tmp/slow.digest
mysqlsla -lt slow /tmp/slow.log  > /tmp/slow.sla

################################# pt工具使用
# pt-query-digest
pt-query-digest slowlogfile > slowlogfile.dig

################################# 查询当前事务状态
SELECT t.`trx_mysql_thread_id` ,t.`trx_id`,w.`blocking_trx_id`,t.`trx_started`,t.`trx_state`,t.`trx_query`,k.`lock_table`,k.`lock_index`,k.`lock_type`,p.`HOST`,p.`STATE`,p.`INFO`
FROM `information_schema`.`INNODB_TRX` AS t 
INNER JOIN `information_schema`.`PROCESSLIST` AS p ON t.`trx_mysql_thread_id` = p.`ID`
INNER JOIN `information_schema`.`INNODB_LOCKS` AS k ON t.`trx_id`=k.`lock_trx_id`
LEFT JOIN `information_schema`.`INNODB_LOCK_WAITS` AS w ON t.`trx_id`=w.`requesting_trx_id` ;

################################# 查看 mysql 系统资源
# 查看配置
grep -v "^#"" /etc/security/limits.conf

# 打开的文件数量
lsof -u mysql | wc -l