- 统计当前实例中业务相关的库和表的信息（排除掉mysql sys information_schema
performance_schema）
库名 表个数 表名列表
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;
- 统计当前实例每个数据库的数据总量（排除掉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;
- 统计当前实例非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. 查询当前系统中所有非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应用
8.3.5 columns
8.3.6 processlist应用
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
