Bash script: report largest InnoDB files
The following script will report the largest InnoDB tables under the data directory: schema, table & length in bytes. The tables could be non-partitioned, in which case this is simply the size of the corresponding .ibd file, or they can be partitioned, in which case the reported size is the sum of all partition files. It is assumed tables reside in their own tablespace files, i.e. created with innodb_file_per_table=1.
1 ( 2 mysql_datadir=$(grep datadir /etc/my.cnf | cut -d "=" -f 2) 3 cd $mysql_datadir 4 for frm_file in $(find . -name "*.frm") 5 do 6 tbl_file=${frm_file//.frm/.ibd} 7 table_schema=$(echo $frm_file | cut -d "/" -f 2) 8 table_name=$(echo $frm_file | cut -d "/" -f 3 | cut -d "." -f 1) 9 if [ -f $tbl_file ] 10 then 11 # unpartitioned table 12 file_size=$(du -cb $tbl_file 2> /dev/null | tail -n 1) 13 else 14 # attempt partitioned innodb table 15 tbl_file_partitioned=${frm_file//.frm/#*.ibd} 16 file_size=$(du -cb $tbl_file_partitioned 2> /dev/null | tail -n 1) 17 fi 18 file_size=${file_size//total/} 19 # Replace the below with whatever action you want to take, 20 # for example, push the values into graphite. 21 echo $file_size $table_schema $table_name 22 done 23 ) | sort -k 1 -nr | head -n 20
We use this to push table statistics to our graphite service; we keep an eye on table growth (we actually do not limit to top 20 but just monitor them all). File size does not report the real table data size (this can be smaller due to tablespace fragmentation). It does give the correct information if you‘re concerned about disk space. For table data we also monitor SHOW TABLE STATUS /INFORMATION_SCHEMA.TABLES, themselves being inaccurate. Gotta go by something.
参考：
http://code.openark.org/blog/mysql/bash-script-report-largest-innodb-files
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
