孙某某的运维之路

孙富阳， 江湖人称没人称。多年互联网运维工作经验，曾负责过孙布斯大规模集群架构自动化运维管理工作。擅长Web集群架构与自动化运维，曾负责国内某大型博客网站运维工作。

1.show语句获取

show databases；
show tables;
show privileges;
show grants for;
show create database/table
show charset
show collation
show processlist
show variables
show status
show index from
show engines
show binary logs
show binlog events in
show master status
show slave status
show slave hosts
show plugins
show engine innodb status

2.information_schema

1.介绍

视图.查询元数据的视图及方法,可以看成给语句设置个别名
mysql> select  student.sid, student.sname,avg(num),count(course.cname),group_concat(cname) from student join score  join course on student.sid=score.student_id where score.course_id=course.cid group by student.sid,student.sname having avg(num)>60 ;

给上面的语句创建个视图
mysql> create view sfy as  (select  student.sid, student.sname,avg(num),count(course.cname),group_concat(cname) from student join score  join courrse on student.sid=score.student_id where score.course_id=course.cid group by student.sid,student.sname having avg(num)>60) ;
然后执行如下命令###可以看到查询结果与上面的一致
mysql> select * from sfy;

2.tables视图应用

mysql> use information_schema;

TABLE_SCHEMA（表所在库）
TABLE_NAME（表名）
ENGINE（存储引擎）
TABLE_ROWS（表的行数（粗略统计））
AVG_ROW_LENGTH（平均行长度（粗略统计））
INDEX_LENGTH（索引长度（粗略统计））
DATA_FREE(碎片量)
TABLE_COMMENT表注释

例子1.统计所有库，对应的表个数和名字列表

库名  表个数   表名
world  3       city，a,b
mysql> select TABLE_SCHEMA as '库名',count(TABLE_NAME) as '表个数',group_concat(TABLE_NAME) as '表名' from tables group by TABLE_SCHEMA having  表个数<10 ;

例子2.统计每个库的数据量大小

mysql> select TABLE_SCHEMA as '库名',sum(TABLE_ROWS*AVG_ROW_LENGTH+INDEX_LENGTH) as '数据大小' from tables group by TABLE_SCHEMA ;

例子3.拼接查询，查询一下业务数据库中，非Innodb的表

mysql> create table test.t2 (id int)engine=myisam;
mysql> select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','sys','mysql') and (ENGINE!='InnoDB' or ENGINE is null);

例子4.将例3执行结果进行拼接，将非Innodb的表替换为Innodb的表

mysql> select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE=InnoDB") from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','sys','mysql','school') and (ENGINE!='InnoDB' or ENGINE is null);
可以将其输出到文件里，需要在配置文件里添加参数secure-file-priv=/tmp
select concat("alter table ",TABLE_SCHEMA,".",TABLE_NAME," ENGINE=InnoDB") from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','sys','mysql','school') and (ENGINE!='InnoDB' or ENGINE is null) into outfile '/tmp/sql.sql';

导出拼接的文件，然后source文件批量修改表的存储引擎为InnoDB

例子4.将执行结果进行拼接，实现所有库的单表备份

mysql> select concat("mysqldump ",TABLE_SCHEMA," ",TABLE_NAME," > /tmp/","",TABLE_SCHEMA,"_",TABLE_NAME,".sql") from information_schema.tables where TABLE_SCHEMA not in ('information_schema','performance_schema','sys','mysql')  into outfile '/tmp/alter_test.sh' ;

3.COLUMNS视图

TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
DATA_TYPE
COLUMN_KEY
COLUMN_COMMENT

例子.查询一下每个库每张表都有哪些列

mysql> select TABLE_SCHEMA,TABLE_NAME,group_concat(COLUMN_NAME) from COLUMNS where TABLE_SCHEMA not in ('information_schema','performance_schema','sys','mysql') group by TABLE_SCHEMA,TABLE_NAME;