Specific view of the library, and the size of the table under MySQL instance

1.mysql  View instance, the size of each library Select table_schema, concat (truncate (SUM (data_length)/1024/1024,2), '   MB ')  as data_size,concat (Truncate (SUM (index_length)/1024/1024,2), ' MB ')  as index_sizefrom  information_schema.tablesgroup by table_schemaorder by data_length desc;2.mysql   View the size of a table within a specific library. Select concat (Round (sum (data_length/1024/1024), 2), ' M ')  from tables where table_schema =\ "${db}\"  and table_name=\ "${table}\" 3. View the size of each table under the mysql  instance, under all libraries. #!/bin/bash>allsize.txtfor n in  ' Cat mysqlbackuplist.txt ' do         echo ========= ${n} =========  >>allsize.txt     for db in  ' mysql -uroot -ppassword -h$n -e  ' show  databases; '  |awk   ' nr>1{print $1} ' |egrep -v  ' Schema|tmp|sys|innodb|mysql|tigasedb '     do        >${db}size.txt         echo ========= ${db} =========        echo  ========= ${db} ========= >>allsize.txt         for table in  ' mysql -uroot -ppassword -h$n -e  ' use ${db};  show tables "'         do             selectsql= "Select concat (Round (sum (data_length/1024/1024), 2), ' M ')  from tables where table_schema=\ "${db}\"  and table_name=\ "${table}\"; "             mysql -uroot -ppassword  -h$n -e  "use information_schema;  $selectsql"  |awk -v table= $table   ' nr>1{print table  '    " $1}   >>${db}size.txt                     done         cat ${db}size.txt|sort -k 2 -rn >> allsize.txt     done done

This article is from the "Struggle Bar" blog, please be sure to keep this source http://lvnian.blog.51cto.com/7155281/1953555

Specific view of the library, and the size of the table under MySQL instance