MysqlDBA运维命令大全

MySQL 注释符有三种：
1、#...
2、"-- ..." （注意 -- 后面有一个空格）
3、/*...*/

查询所有数据的大小：
mysql> select concat(round(sum(data_length/1024/1024),2),'MB') as data from information_schema.tables;

查看指定数据库的大小，例如查看数据库db_school 的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data 
from information_schema.tables where table_schema='db_school';

查看指定数据库的某个表的大小
例如查看数据库db_school 中 db_school表的大小：
select concat(round(sum(data_length/1024/1024),2),'MB') as data 
from information_schema.tables 
where table_schema='db_school' and table_name='db_school';

当前数据库实例的所有数据库及其容量大小：
select a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME,
sum(table_rows) as '记录数',
sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',
sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)',
sum(truncate((data_length+index_length)/1024/1024, 2)) as '总大小(MB)',
sum(truncate(max_data_length/1024/1024, 2)) as '最大值(MB)',
sum(truncate(data_free/1024/1024, 2)) as '空闲空间(MB)'
from INFORMATION_SCHEMA.SCHEMATA a
left outer join information_schema.tables b
on a.SCHEMA_NAME=b.TABLE_SCHEMA
group by a.SCHEMA_NAME, a.DEFAULT_CHARACTER_SET_NAME,a.DEFAULT_COLLATION_NAME
order by sum(data_length) desc, sum(index_length) desc;

占用空间最大的前 10 张表
SELECT
table_schema AS '数据库',
table_name AS '表名',
a.TABLE_TYPE,
a.`ENGINE`,
a.CREATE_TIME,
a.UPDATE_TIME,
a.TABLE_COLLATION,
table_rows AS '记录数',
TRUNCATE ( data_length / 1024 / 1024, 2 ) AS '数据容量(MB)',
TRUNCATE ( index_length / 1024 / 1024, 2 ) AS '索引容量(MB)',
TRUNCATE ( ( data_length + index_length ) / 1024 / 1024, 2 ) AS '总大小(MB)'
FROM information_schema.TABLES a
ORDER BY ( data_length + index_length ) DESC
LIMIT 10;

查询数据库对象：
select db as '数据库',type as '对象类型',cnt as '对象数量' from
(select 'TABLE' type,table_schema db, count(*) cnt from information_schema.`TABLES` a where table_type='BASE TABLE' group by table_schema
union all
select 'EVENTS' type,event_schema db,count(*) cnt from information_schema.`EVENTS` b group by event_schema
union all
select 'TRIGGERS' type,trigger_schema db,count(*) cnt from information_schema.`TRIGGERS` c group by trigger_schema
union all
select 'PROCEDURE' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where`ROUTINE_TYPE` = 'PROCEDURE' group by db
union all
select 'FUNCTION' type,routine_schema db,count(*) cnt from information_schema.ROUTINES d where`ROUTINE_TYPE` = 'FUNCTION' group by db
union all
select 'VIEWS' type,TABLE_SCHEMA db,count(*) cnt from information_schema.VIEWS f group by table_schema ) t
order by db,type;

有如下几种办法：
① mysql -V
② mysql> show variables like '%version_%';
③ which mysql |xargs file （linux/unix 系统）
④ echo STATUS|mysql -uroot -ppassword |grep Ver