- 統(tǒng)計當前實例中業(yè)務(wù)相關(guān)的庫和表的信息（排除掉mysql sys information_schema
performance_schema）
庫名 表個數(shù) 表名列表
mysql> select table_schema,group_concat(table_name),count(*) from
information_schema.tables where table_schema not in
('sys','mysql','information_schema','performance_schema') group by table_schema;
- 統(tǒng)計當前實例每個數(shù)據(jù)庫的數(shù)據(jù)總量（排除掉mysql sys information_schema
performance_schema）
select table_schema,sum(table_rows * avg_row_length + index_length)/1024/1024 as
total_mb
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
group by table_schema;
- 統(tǒng)計當前實例非innodb的表（排除掉mysql sys information_schema performance_schema）
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';
alter table world.aaaaa engine=innodb;
- 查詢有碎片的表信息
select table_schema,table_name ,data_free
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema')
and data_free >0;
- 查詢出本地外的連接信息
select * from information_schema.processlist where host !='locakhost';
- 拼接SQL
a. 查詢當前系統(tǒng)中所有非INNODB的表薄疚。
select table_schema,table_name ,engine
from information_schema.tables
where table_schema not in
('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB';
b. 將這些非INNODB的表替換為INNODB
8.3.4 TRIGGERS咧织、views况脆、ROUTINES坐慰、EVENTS應(yīng)用
8.3.5 columns
8.3.6 processlist應(yīng)用
mysql> select concat("alter table ",table_schema,".",table_name,"
engine=innodb;") from information_schema.tables where table_schema not
in ('sys','mysql','information_schema','performance_schema') and engine <>
'INNODB' into outfile '/tmp/alter.sql';
source /tmp/alter.sql
