MySQL运维常用的SQL语句
###################操作类###############################################################[*] 分析binglog的内容
mysqlbinlog--base64-output=decode-rows -v--start-datetime='2018-03-18 15:46:30'--stop-datetime='2018-03-18 15:47:40'mysql-bin.000001 > /tmp/binlog.sql
mysqlbinlog --base64-output=decode-rows -v–-start-position=100049070 –-stop-position=100049079 mysql-bin.000001> /tmp/tmp.sql
[*] mysqldump的特殊用法
mysqldump -uroot -w "id=6032" -p db_nametbl_name > /tmp/where.sql#导出表的部分数据
mysqldump -uroot-p db_nametbl_name> /tmp/table.sql #导出表的数据
mysqldump -uroot-p--ignore-table=db_name.tbl_namedb_name > /tmp/table.sql #不导出某个表的数据
[*] grep 关键字的前n行,后m行
grep -Am -Bnkeyword filename
[*] mysql 预热数据
select * from tb_name limit 100000000,1;
###################分析类###############################################################
-- 查询表中数据超过1000行的表
select concat(table_schema,'.',table_name) as table_name,table_rows
from information_schema.tables where table_rows > 1000
order by table_rows desc;
-- 查看分区数据
SELECT PARTITION_NAME,TABLE_ROWS FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME = 'xxxx';
-- 查询5个最大表
SELECT concat(table_schema,'.',table_name) table_name,
concat(round(data_length/(1024*1024),2),'M') data_length
FROM information_schema.TABLES
ORDER BY data_length DESC LIMIT 5;
-- 查询没有主键的表
SELECT CONCAT(t.table_schema,".",t.table_name) as table_name
FROM information_schema.TABLES t
LEFT JOIN information_schema.TABLE_CONSTRAINTS tc
ON t.table_schema = tc.table_schema
AND t.table_name = tc.table_name
AND tc.constraint_type = 'PRIMARY KEY'
WHERE tc.constraint_name IS NULL
AND t.table_type = 'BASE TABLE';
###################故障诊断###############################################################
-- 按MySQL中执行时间反向排序
mysqladmin processlist --verbose |grep 'Query'|awk -F "|" '{print $7 $2 $9}'|sort -rn -k1
-- cat killLongQuerySession.sh杀掉执行时间超过300的session
#!/bin/bash
executetime=(`mysql -Bse 'showprocesslist'| grep 'Query'|awk'{print $6 " " $1}'|sort -rn|head -1`)#第6列是运行时间，第一列为session> time=${executetime}
id=${executetime}
while :
do
maxtime=300
if [ $time-gt$maxtime] ; then
echo $time $id >> /tmp/killqueryid.log
mysql -Bse "kill$id"
#else
# echo $time $id
fi
sleep 10 #睡眠10s
done
-- cat killWaitSession.sh 杀掉等待select中有Waiting的会话
#!/bin/bash
for i in `mysql -Bse 'show full processlist' | grep -i select |grep -i "Waiting | awk'{print $1}'`
do
mysql -Bse "kill$i"
done
页:
[1]