MysqlDBA運維指令大全

作者：IT邦德
中國DBA聯盟(ACDU)成員，目前從事DBA及程式程式設計
（Web\java\Python）工作，主要服務于生産制造
現擁有 Oracle 11g  OCP/OCM、
Mysql、Oceanbase（OBCA）認證
分布式TBase\TDSQL資料庫、國産達夢資料庫以及紅帽子認證
從業8年DBA工作，在資料庫領域有豐富的經驗
      

1、 MySQL 的注釋

MySQL 注釋符有三種：
1、#...
2、"-- ..." （注意 -- 後面有一個空格）
3、/*...*/      

2、 如何檢視 MySQL 資料庫的大小

查詢所有資料的大小：
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