MySQL运维常用的SQL语句

kevin0490

　　###################操作类###############################################################

• 　　分析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_name  tbl_name > /tmp/where.sql  #导出表的部分数据
　　mysqldump -uroot  -p db_name  tbl_name> /tmp/table.sql                         #导出表的数据
　　mysqldump -uroot  -p  --ignore-table=db_name.tbl_name  db_name > /tmp/table.sql                         #不导出某个表的数据

• 　　grep 关键字的前n行,后m行
  

　　grep -Am -Bn  keyword 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 'show  processlist'| grep 'Query'|awk  '{print $6 " " $1}'|sort -rn|head -1`)  #第6列是运行时间，第一列为session>　　time=${executetime[0]}
　　id=${executetime[1]}
　　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