【查阅】Mysql 常规运维命令
【运维常用】
【1】查看锁
mysql> show status like ‘Table%’; +—————————-+——–+ | Variable_name | Value | +—————————-+——–+ | Table_locks_immediate | 795505 | | Table_locks_waited | 0 | | Table_open_cache_hits | 0 | | Table_open_cache_misses | 0 | | Table_open_cache_overflows | 0 | +—————————-+——–+ 5 rows in set (0.00 sec) Table_locks_immediate 指的是能够立即获得表级锁的次数 Table_locks_waited 指的是不能立即获取表级锁而需要等待的次数，如果数量大，说明锁等待多，有锁争用情况 查看正在被锁定的的表 show OPEN TABLES where In_use > 0; mysql> show OPEN TABLES where In_use > 0; +————–+—————+——–+————-+ | Database | Table | In_use | Name_locked | +————–+—————+——–+————-+ | music | class_record | 1 | 0 | | vipswoole | chat_message | 3 | 0 | | music | user_account | 1 | 0 | | music | sales_channel | 1 | 0 | | music | class_room | 5 | 0 | | music | user | 1 | 0 | | music_school | user | 1 | 0 | +————–+—————+——–+————-+ 7 rows in set (0.00 sec) mysql> 如果查看到锁争用情况严重，可以再查看当前执行的SQL ： mysql>show processlist (mysqladmin -uroot -p -P 3306 processlist) mysqladmin命令有一个debug参数，可以分析当前MySQL服务的状态信息，同时也可以用来帮助我们定位当前锁的详细情况，这里我们通过该命令分析一下当前MySQL服务的详细状态，执行mysqladmin命令如下： [root@phpmysql02 data]# mysqladmin -ujss -p -S /data/3306/mysql.sock debug Enter password: debug会将状态信息生成到mysql的错误文件，一般锁的信息都会保存在最后几行，这里我们在操作系统层error log最后几行： [root@phpmysql02 data]# tail -10 phpmysql02.err Thread database.table_name Locked/Waiting Lock_type 2 hdpic.t_wiki_zutu Waiting - write Highest priority write lock 123890 hdpic.t_wiki_zutu_category Locked - read Low priority read lock 123890 hdpic.t_wiki_zutu_photo Locked - read Low priority read lock 123890 hdpic.t_wiki_zutu Locked - read Low priority read lock 124906 hdpic.t_wiki_zutu Waiting - read Low priority read lock 从上述信息可以看出，123890持有的读锁阻塞了2的写入和124906的读操作，这个状态符合我们的推论，接下来处理就比较单纯了，如果现状不可接受，不能继续等待，将123890杀掉，释放资源即可： mysql> kill 123890; Query OK, 0 rows affected (0.00 sec) 再次执行show processlist查看： 使用系统表进行锁查询： select r.trx_isolation_level, r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread, r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type, lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query, b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state, lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table, lb.lock_index blocking_trx_lock_index,b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id \G 涉及的3张表说明： information_shcema下的三张表（通过这三张表可以更新监控当前事物并且分析存在的锁问题） —— innodb_trx （ 打印innodb内核中的当前活跃（ACTIVE）事务） —— innodb_locks （ 打印当前状态产生的innodb锁 仅在有锁等待时打印） —— innodb_lock_waits （打印当前状态产生的innodb锁等待 仅在有锁等待时打印） 1) innodb_trx表结构说明 （摘取最能说明问题的8个字段） 字段名 说明 trx_id innodb存储引擎内部唯一的事物ID trx_state 当前事物状态（running和lock wait两种状态） trx_started 事物的开始时间 trx_requested_lock_id 等待事物的锁ID，如trx_state的状态为Lock wait，那么该值带表当前事物等待之前事物占用资源的ID，若trx_state不是Lock wait 则该值为NULL trx_wait_started 事物等待的开始时间 trx_weight 事物的权重，在innodb存储引擎中，当发生死锁需要回滚的时，innodb存储引擎会选择该值最小的进行回滚 trx_mysql_thread_id mysql中的线程id, 即show processlist显示的结果 trx_query 事物运行的SQL语句 2）innodb_locks表结构说明 字段名 说明 lock_id 锁的ID lock_trx_id 事物的ID lock_mode 锁的模式（S锁与X锁两种模式） lock_type 锁的类型 表锁还是行锁（RECORD） lock_table 要加锁的表 lock_index 锁住的索引 lock_space 锁住对象的space id lock_page 事物锁定页的数量，若是表锁则该值为NULL lock_rec 事物锁定行的数量，若是表锁则该值为NULL lock_data 事物锁定记录主键值，若是表锁则该值为NULL（此选项不可信） 3）innodb_lock_waits表结构说明 字段名 说明 requesting_trx_id申请锁资源的事物ID requested_lock_id申请的锁的ID blocking_trx_id阻塞其他事物的事物ID blocking_lock_id阻塞其他锁的锁ID 可以根据这三张表进行联合查询，得到更直观更清晰的结果，可以参考如下SQL（可根据自己的分析习惯适进行调整）
【2】查看库大小、表大小、表碎片率
-- 所有库大小 SELECT TABLE_SCHEMA,CONCAT(TRUNCATE(SUM(data_length)/1024/1024,2),'MB') AS data_size, CONCAT(TRUNCATE(SUM(data_free)/1024/1024,2),'MB') AS data_free_size, CONCAT(TRUNCATE(SUM(index_length)/1024/1024,2),'MB') AS index_size FROM information_schema.tables GROUP BY TABLE_SCHEMA ORDER BY SUM(data_length) DESC; -- 某个库下所有表大小 SELECT concat_ws('.',a.table_schema ,a.table_name) ,CONCAT(a.table_schema,'.',a.table_name) , CONCAT(ROUND(table_rows/1000,4),'KB') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024),4),',') AS 'data_size', CONCAT(ROUND(index_length/(1024*1024),4),'M') AS 'index_size', CONCAT(ROUND((data_length+index_length)/(1024*1024),4),'M') AS'Total' FROM information_schema. TABLES a a.table_schema = 'test_yyz'; -- 碎片率 SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, AVG_ROW_LENGTH, DATA_LENGTH, MAX_DATA_LENGTH, INDEX_LENGTH, DATA_FREE, ROUND(DATA_FREE/(DATA_LENGTH+DATA_FREE),2) AS Fragment FROM information_schema.TABLES
【3】如何释放表空间/收缩表空间/统计信息
详细参考：MySQL check table/optimize table/analyze table/REPAIR TABLE
optimize table tab_name;(alter table tabName engine=innodb;) -- 收缩表空间
ANALYZE TABLE tab_name; -- 更新统计信息
【4】查看实例信息
登录mysql后，输入 \s
【5】索引与表信息
/*查看是否包含非innodb的表*/ select * from information_schema.tables where table_schema not in ('mysql','sys','information_schema','performance_schema','mosquitto') and ENGINE <> 'INNODB'; #查看库的总量 select count(1) from information_schema.SCHEMATA where schema_name not in ('mysql','information_schema','performance_schema','sys'); #查看表的总量 select count(1) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys'); #查看每个库下多少个表： select TABLE_SCHEMA,count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') group by TABLE_SCHEMA; #查看100W+的表的个数： select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and TABLE_ROWS >=1000000; #查看10G+表的个数： select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and DATA_FREE+DATA_LENGTH+INDEX_LENGTH>=10737418240; #查看非innodb表个数： select count(*) from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') and engine !='InnoDB'; 表的碎片率排行top10 select table_schema,table_name,table_rows,data_length,index_length,data_free , data_free/(data_length+index_length+data_free) as free_rate from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') order by free_rate desc limit 10 表的碎片大小排行top10 select table_schema,table_name,table_rows,data_length,index_length,data_free from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') order by data_free desc limit 10 表的行数top10， select table_schema,table_name,table_rows,data_length,index_length,data_free from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') order by table_rows desc limit 10 表的体积top10 select table_schema,table_name,table_rows,data_length,index_length,data_free from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') order by data_length+index_length+data_free desc limit 10 表的auto_increment top 10 select table_schema,table_name,table_rows,AUTO_INCREMENT from information_schema.tables where table_schema not in ('mysql','information_schema','performance_schema','sys') order by AUTO_INCREMENT desc limit 10 未使用的索引情况：总数，按表统计top10： select count(1) from sys.schema_unused_indexes; select object_schema,object_name,count(1) from sys.schema_unused_indexes group by object_schema,object_name order by count(1) desc limit 10; 查看指定库下指定表or所有表 select * from information_schema.tables where table_schema='mysql' and table_name='t1'; 查看指定库下指定的表的列信息（能粗略查看索引信息） select column_name,column_type,is_nullable,column_key from information_schema.columns where table_schema='svrabp' and table_name='rabp_tra_bank_account'; 准确查看索引信息： select index_name ,non_unique,concat('(',group_concat(column_name order by seq_in_index),')') as index_column from information_schema.statistics where table_schema='svrabp' and table_name='rabp_tra_bank_accountt3' group by index_name,non_unique; 查看指定库下指定表未使用的索引： select * from sys.schema_unused_indexes where object_schema='cfbtrust' and object_name='bank_sales_order_detail'; 查询实例中有哪些用户： select user,host from mysql.user where 1=1; 查询库下表的详细信息 select TABLE_NAME,table_comment,AUTO_INCREMENT,table_collation from information_schema.tables where table_schema='car'; 查询表的列信息（类似于DESC 表） SELECT TABLE_NAME, COLUMN_NAME, column_type, COLUMN_COMMENT, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_KEY, CHARACTER_SET_NAME, COLLATION_NAME, extra FROM information_schema.COLUMNS WHERE table_schema='car_ota' ORDER BY TABLE_NAME,ORDINAL_POSITION
【6】查看当前阻塞信息、当前锁等待信息
select r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id\G select r.trx_isolation_level, r.trx_id waiting_trx_id,r.trx_mysql_thread_id waiting_trx_thread, r.trx_state waiting_trx_state,lr.lock_mode waiting_trx_lock_mode,lr.lock_type waiting_trx_lock_type, lr.lock_table waiting_trx_lock_table,lr.lock_index waiting_trx_lock_index,r.trx_query waiting_trx_query, b.trx_id blocking_trx_id,b.trx_mysql_thread_id blocking_trx_thread,b.trx_state blocking_trx_state, lb.lock_mode blocking_trx_lock_mode,lb.lock_type blocking_trx_lock_type,lb.lock_table blocking_trx_lock_table, lb.lock_index blocking_trx_lock_index,b.trx_query blocking_query from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id=w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id=w.requesting_trx_id inner join information_schema.innodb_locks lb on lb.lock_trx_id=w.blocking_trx_id inner join information_schema.innodb_locks lr on lr.lock_trx_id=w.requesting_trx_id \G select waiting_pid as '被阻塞线程',waiting_query as '被阻塞SQL', blocking_pid as '阻塞线程', blocking_query as '阻塞SQL', wait_age as '阻塞时间', sql_kill_blocking_query as '建议操作' from sys.innodb_lock_waits where(unix_timestamp()-unix_timestamp(wait_started))>阻塞秒数
【7】查看表行数
系统表统计信息：会有1~3%的不精准
use information_schema; select table_name,table_rows from tables where TABLE_SCHEMA = '数据库名' order by table_rows desc;
高效推荐：
SELECT SQL_CALC_FOUND_ROWS 1 FROM tableName limit 1; SELECT found_rows() AS rowcount;
如下图:
【8】查看mysql命令状态，qps
mysqladmin -uroot -pbfgame20 -r -i 1 ext |\ awk -F"|" \ "BEGIN{ count=0; }"\ '{ if($2 ~ /Variable_name/ && ++count == 1){\ print "----------|---------|------ MySQL Command Status -------|----- Innodb row operation ----|-- Buffer Pool Read --";\ print "---Time---|---QPS---|select insert update delete replace| read inserted updated deleted| logical physical";\ }\ else if ($2 ~ /Queries/){queries=$3;}\ else if ($2 ~ /Com_select /){com_select=$3;}\ else if ($2 ~ /Com_insert /){com_insert=$3;}\ else if ($2 ~ /Com_update /){com_update=$3;}\ else if ($2 ~ /Com_delete /){com_delete=$3;}\ else if ($2 ~ /Com_replace /){com_replace=$3;}\ else if ($2 ~ /Innodb_rows_read/){innodb_rows_read=$3;}\ else if ($2 ~ /Innodb_rows_deleted/){innodb_rows_deleted=$3;}\ else if ($2 ~ /Innodb_rows_inserted/){innodb_rows_inserted=$3;}\ else if ($2 ~ /Innodb_rows_updated/){innodb_rows_updated=$3;}\ else if ($2 ~ /Innodb_buffer_pool_read_requests/){innodb_lor=$3;}\ else if ($2 ~ /Innodb_buffer_pool_reads/){innodb_phr=$3;}\ else if ($2 ~ /Uptime / && count >= 2){\ printf(" %s |%9d",strftime("%H:%M:%S"),queries);\ printf("|%6d %6d %6d %6d %6d",com_select,com_insert,com_update,com_delete,com_replace);\ printf("|%6d %8d %7d %7d",innodb_rows_read,innodb_rows_inserted,innodb_rows_updated,innodb_rows_deleted);\ printf("|%10d %11d\n",innodb_lor,innodb_phr);\ }}'
【9】重命名库脚本
#!/bin/bash set -e # exit immediately if a command exits with a non-zero status. mysql_conn="mysql --defaults-file=/etc/my.cnf" function db_GetTab # print: tab_names { local dbname="$1" ${mysql_conn} -BNe "select table_name from information_schema.tables where table_schema = '${dbname}'" } function tab_Rename { local oldTab="$1" local newTab="$2" ${mysql_conn} -BNe "rename table ${oldTab} to ${newTab}" } function db_Rename { local olddb="$1" local newdb="$2" ${mysql_conn} -BNe "create database ${newdb}" for tab in $(db_GetTab "${olddb}") do tab_Rename "${olddb}.${tab}" "${newdb}.${tab}" done # ${mysql_conn} -BNe "drop database ${olddb}" # drop old database } # __main__ if [ 2 -ne $# ] then echo "[Usage]: $0 old_dbname new_dbname" exit 1 fi db_Rename "$@" # __end__
【10】表、索引、主键、自增检查
-- 1、查看数据库中不为 InnoDB 引擎的表 SELECT TABLE_SCHEMA, TABLE_NAME, ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') AND ENGINE != 'InnoDB'; -- 2、查看数据库中表的大小及数据量 SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_ROWS, CONCAT(round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2), 'MB') as data FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') ORDER BY DATA_LENGTH + INDEX_LENGTH DESC; -- 3、查找数据库中无显式主键索引的表 SELECT t.TABLE_SCHEMA, t.TABLE_NAME FROM information_schema.TABLES t WHERE (t.TABLE_SCHEMA, t.TABLE_NAME) NOT IN ( SELECT DISTINCT TABLE_SCHEMA, TABLE_NAME FROM information_schema.COLUMNS WHERE COLUMN_KEY = 'PRI' ) AND t.TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test'); -- 4、查找数据库中主键为联合主键的表 SELECT TABLE_SCHEMA, TABLE_NAME, GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ',') cols, MAX(SEQ_IN_INDEX) len FROM information_schema.STATISTICS WHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA, TABLE_NAME HAVING len > 1; -- 5、查找数据库中不为自增主键的表 SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') AND (TABLE_SCHEMA,TABLE_NAME) NOT IN ( SELECT TABLE_SCHEMA, TABLE_NAME FROM information_schema.COLUMNS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') AND IS_NULLABLE = 'NO' AND COLUMN_TYPE LIKE '%int%' AND COLUMN_KEY = 'PRI' AND EXTRA = 'auto_increment' ); -- 6、查看数据库中存在外键约束的表 SELECT c.TABLE_SCHEMA, c.REFERENCED_TABLE_NAME, c.REFERENCED_COLUMN_NAME, c.TABLE_NAME, c.COLUMN_NAME, c.CONSTRAINT_NAME, t.TABLE_COMMENT, r.UPDATE_RULE, r.DELETE_RULE FROM information_schema.KEY_COLUMN_USAGE c JOIN information_schema.TABLES t ON t.TABLE_NAME = c.TABLE_NAME JOIN information_schema.REFERENTIAL_CONSTRAINTS r ON r.TABLE_NAME = c.TABLE_NAME AND r.CONSTRAINT_NAME = c.CONSTRAINT_NAME AND r.REFERENCED_TABLE_NAME = c.REFERENCED_TABLE_NAME WHERE c.REFERENCED_TABLE_NAME IS NOT NULL; -- 7、查找数据库中低区分度索引（区分度小于0.1） SELECT p.TABLE_SCHEMA, p.TABLE_NAME, c.INDEX_NAME, c.car, p.car total FROM ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, MAX(CARDINALITY) car FROM information_schema.STATISTICS WHERE INDEX_NAME != 'PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) c INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, MAX(CARDINALITY) car from information_schema.STATISTICS WHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA, TABLE_NAME ) p ON c.TABLE_NAME = p.TABLE_NAME AND c.TABLE_SCHEMA = p.TABLE_SCHEMA WHERE p.car > 0 AND c.car / p.car < 0.1; -- 8、查找数据库中重复索引前缀的索引 SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.INDEX_NAME, a.cols, b.INDEX_NAME, b.cols FROM ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols FROM information_schema.STATISTICS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) a INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') AS cols FROM information_schema.STATISTICS WHERE TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') AND INDEX_NAME != 'PRIMARY' GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) b ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.cols LIKE CONCAT(b.cols, '%') AND a.INDEX_NAME != b.INDEX_NAME; -- 9、查找数据库中包索引重复包含主键列的索引 SELECT a.*, b.pk FROM ( SELECT TABLE_SCHEMA, TABLE_NAME, INDEX_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') cols FROM information_schema.STATISTICS WHERE INDEX_NAME != 'PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME ) a INNER JOIN ( SELECT TABLE_SCHEMA, TABLE_NAME, CONCAT('| ', GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX SEPARATOR ' | '), ' |') pk FROM information_schema.STATISTICS WHERE INDEX_NAME = 'PRIMARY' AND TABLE_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema', 'test') GROUP BY TABLE_SCHEMA, TABLE_NAME ) b ON a.TABLE_NAME = b.TABLE_NAME AND a.TABLE_SCHEMA = b.TABLE_SCHEMA AND a.cols LIKE CONCAT('%', b.pk, '%'); -- 10、查找数据库中没有被使用的索引 SELECT a.OBJECT_SCHEMA, a.OBJECT_NAME, a.INDEX_NAME, b.TABLE_ROWS FROM performance_schema.TABLE_IO_WAITS_SUMMARY_BY_INDEX_USAGE a INNER JOIN information_schema.TABLES b ON a.OBJECT_SCHEMA = b.TABLE_SCHEMA AND a.OBJECT_NAME = b.TABLE_SCHEMA WHERE a.INDEX_NAME IS NOT NULL AND a.INDEX_NAME != 'PRIMARY' AND a.COUNT_STAR = 0 AND OBJECT_SCHEMA NOT IN ('sys', 'mysql', 'performance_schema', 'information_schema') ORDER BY OBJECT_SCHEMA, OBJECT_NAME; -- 11、查看数据库中的锁请求信息 SELECT r.TRX_ISOLATION_LEVEL, r.TRX_ID WAITING_TRX_ID, r.TRX_MYSQL_THREAD_ID WAITING_TRX_THREAD, r.TRX_STATE WAITING_TRX_STATE, lr.LOCK_MODE WAITING_TRX_LOCK_MODE, lr.LOCK_TYPE WAITING_TRX_LOCK_TYPE, lr.LOCK_TABLE WAITING_TRX_LOCK_TABLE, lr.LOCK_INDEX WAITING_TRX_LOCK_INDEX, r.TRX_QUERY WAITING_TRX_QUERY, b.TRX_ID BLOCKING_TRX_ID, b.TRX_MYSQL_THREAD_ID BLOCKING_TRX_THREAD, b.TRX_STATE BLOCKING_TRX_STATE, lb.LOCK_MODE BLOCKING_TRX_LOCK_MODE, lb.LOCK_TYPE BLOCKING_TRX_LOCK_TYPE, lb.LOCK_TABLE BLOCKING_TRX_LOCK_TABLE, lb.LOCK_INDEX BLOCKING_TRX_LOCK_INDEX, b.TRX_QUERY BLOCKING_QUERY FROM information_schema.INNODB_LOCK_WAITS w INNER JOIN INFORMATION_SCHEMA.INNODB_TRX b ON b.TRX_ID = W.BLOCKING_TRX_ID INNER JOIN information_schema.INNODB_TRX R ON r.TRX_ID = W.REQUESTING_TRX_ID INNER JOIN information_schema.INNODB_LOCKS lb ON lb.LOCK_TRX_ID = W.BLOCKING_TRX_ID INNER JOIN information_schema.INNODB_LOCKS lr ON lr.LOCK_TRX_ID = W.REQUESTING_TRX_ID; -- 12、检查自增是否溢出 select c.table_schema, c.table_name, c.COLUMN_NAME, c.DATA_TYPE, t.AUTO_INCREMENT, t.TABLE_ROWS, CASE WHEN c.DATA_TYPE ='int' and c.column_type not like '%unsigned%' THEN t.AUTO_INCREMENT /2147483647* 100 WHEN c.DATA_TYPE ='int' and c.column_type like '%unsigned%' THEN t.AUTO_INCREMENT /(2147483647*2) * 100 WHEN c.DATA_TYPE ='mediumint' and c.column_type not like '%unsigned%' THEN t.AUTO_INCREMENT /8388607* 100 WHEN c.DATA_TYPE ='mediumint' and c.column_type like '%unsigned%' THEN t.AUTO_INCREMENT /(8388607*2) * 100 WHEN c.DATA_TYPE ='smallint' and c.column_type not like '%unsigned%' THEN t.AUTO_INCREMENT /32767* 100 WHEN c.DATA_TYPE ='smallint' and c.column_type like '%unsigned%' THEN t.AUTO_INCREMENT /(32767*2) * 100 WHEN c.DATA_TYPE ='tinyint' and c.column_type not like '%unsigned%' THEN t.AUTO_INCREMENT /127* 100 WHEN c.DATA_TYPE ='tinyint' and c.column_type like '%unsigned%' THEN t.AUTO_INCREMENT /(127*2) * 100 END AS UTIL from information_schema.COLUMNS c, information_schema.tables t where c.table_schema not in ('information_schema','dbmon','mysql','performance_schema','test') and c.EXTRA like '%auto_increment%' and c.table_schema = t.table_schema and c.table_name=t.table_name having util>'70' order by t.AUTO_INCREMENT desc ; -- 13.外键检查对主表记录的S锁 SELECTr.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query, (Unix_timestamp() - Unix_timestamp(r.trx_started)) blocked_time from information_schema.innodb_lock_waits w inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id -- 14.大事物 SELECT a.trx_id, now() now, a.trx_started, unix_timestamp(now())-unix_timestamp(trx_started) seconds, b.time_ms, b.user, b.host, a.trx_mysql_thread_id, CASE WHEN a.trx_query is NOT NULL THEN a.trx_query ELSE d.SQL_TEXT END trx_query FROM information_schema.innodb_trx a JOIN information_schema.processlist b ON a.trx_mysql_thread_id = b.id LEFT JOIN performance_schema.threads c ON a.trx_mysql_thread_id = c.PROCESSLIST_ID LEFT JOIN performance_schema.events_statements_current d ON d.THREAD_ID = c.THREAD_ID WHERE a.trx_started > '0000-00-00 00:00:00' AND unix_timestamp(now())-unix_timestamp(trx_started) > 1 AND NOT(filter_cond = "b.user IN ('superadmin','sysadmin') AND trx_query LIKE '%SELECT /*!40001 SQL_NO_CACHE */ * FROM%bak%') having seconds > 10 order by seconds desc
【11】查看现有表分区信息
SELECT table_schema,table_name,PARTITION_NAME,PARTITION_METHOD, TABLE_ROWS,SUBPARTITION_NAME FROM information_schema.PARTITIONS where partition_name is not null;
【12】查看大事务
select trx_id,trx_mysql_thread_id prs_id,now(),trx_operation_state,trx_started,trx_isolation_level,to_seconds(now())-to_seconds(trx_started) last_trx_es_time
,user,host,db,state,info,Time from information_schema.innodb_trx trx,information_schema.processlist ps
where ps.id=trx.trx_mysql_thread_id
and to_seconds(now())-to_seconds(trx_started) >100 \G
【13】查看复制相关
-- 【基本查阅】 SHOW SLAVE HOSTS; -- 从库，查看状态，查看复制源 show slave status\G
select * from mysql.slave_master_info;
select *from performance_schema.replication_connection_configuration; -- 【Performance_schema】 replication_connection_configuration 连接主库的配置参数 -- 与SHOW SLAVE STATUS相比，额外提供了心跳间隔相关的信息(HEARTBEAT_INTERVAL) replication_connection_status 当前与主库的连接状态 -- 与SHOW SLAVE STATUS相比，额外提供了上次心跳时间和心跳次数信息(LAST_HEARTBEAT_TIMESTAMP,COUNT_RECEIVED_HEARTBEATS) 应用日志情况 replication_applier_configuration 从库上应用事务的配置 显示了MASTER_DELAY信息(DESIRED_DELAY) replication_applier_status 从库上应用事务的状态 与SHOW SLAVE STATUS相比，额外提供从库重试应用事务的次数(COUNT_TRANSACTIONS_RETRIES) 多线程复制情况 replication_applier_status_by_coordinator 多线程复制的协调器工作情况，当slave_parallel_workers=0时复制是单线程的，该表结果为空 replication_applier_status_by_worker 单线程复制中SQL线程的状态/多线程复制中工作线程的状态 需要关注LAST_SEEN_TRANSACTION列，表示工作线程最后处理的一个事务，这个事务可能没有提交，仍在进行中。 当gtid_mode=OFF时，该列为ANONYMOUS 当gtid_mode=ON时： 如过没有事务执行，该列为空 当事务开始执行时，该列值等于gtid_next，事务执行完毕后不会更新该值。 在下一个事务执行时更新该列值。 复制过滤情况 replication_applier_global_filters （8.0） replication_applier_filters （8.0） 组复制相关信息 replication_group_members replication_group_member_stats
【show系列】
--MySQL 常用show命令 a. show tables或show tables from database_name; -- 显示当前数据库中所有表的名称。 b. show databases; -- 显示mysql中所有数据库的名称。 c. show columns from table_name from database_name; 或show columns from database_name.table_name; -- 显示表中列名称。 d. show grants for user_name; -- 显示一个用户的权限，显示结果类似于grant 命令。 e. show index from table_name; -- 显示表的索引。 f. show status; -- 显示一些系统特定资源的信息，例如，正在运行的线程数量。 g. show variables; -- 显示系统变量的名称和值。 h. show processlist; -- 显示系统中正在运行的所有进程，也就是当前正在执行的查询。大多数用户可以查看他们自己的进程，但是如果他们拥有process权限，就可以查看所有人的进程，包括密码。 i. show table status; -- 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间。 j. show privileges; -- 显示服务器所支持的不同权限。 k. show create database database_name; -- 显示create database 语句是否能够创建指定的数据库。 l. show create table table_name; -- 显示create database 语句是否能够创建指定的数据库。 m. show engies; -- 显示安装以后可用的存储引擎和默认引擎。 n. show innodb status; -- 显示innoDB存储引擎的状态。 o. show logs; -- 显示BDB存储引擎的日志。 p. show warnings; -- 显示最后一个执行的语句所产生的错误、警告和通知。 q. show errors; -- 只显示最后一个执行语句所产生的错误。 r. show [storage] engines; --显示安装后的可用存储引擎和默认引擎。 --MySQL自带的mysqlshow命令 --可以让我们在不连接到MySQL客户端的情况下查看MySQL的一些参数、数据库、表、列、索引等信息，其使用方法如下： 1. mysqlshow -uroot -p --查看都有哪些库 2. mysqlshow -uroot -p wyzc --查看某个库里（wyzc）都有哪些表 3. mysqlshow -uroot -p -v wyzc --加上-v参数后可以显示每张表有多少列 4. mysqlshow -uroot -p -v -v wyzc --加2个-v参数，可以显示出每张表有多少行 5. mysqlshow -uroot -p wyzc City --在库名（wyzc）后面加上表名（City），可以查看该表的详细信息。注：加上-i参数，可以查看该表的所有详细信息 6. mysqlshow -uroot -p wyzc City ID --查看某张表的某一个列的信息 7. mysqlshow -uroot -p -k wyzc City --加上-k参数查看该表的索引和列信息 8. mysqlshow -uroot -p -k wyzc City invalid_col_name --只查看某表索引信息
【2】运维巡检
（2.1）常规巡检脚本
##!/bin/bash ## by shark 大汉客家族数据库工程院士 ## 本脚本需要开启PERFORMANCE_SCHEMA 所有功能检测！ set @dbname='acquire-test'; ##设置巡检的数据库名也就是SCHEMA select "1.检查逻辑读 >20000 " as '----------------------------------------------' from dual; SELECT SCHEMA_NAME,DIGEST AS digest,DIGEST_TEXT,COUNT_STAR,AVG_TIMER_WAIT, ROUND(SUM_ROWS_AFFECTED/COUNT_STAR, 0) AS rows_affected_avg, ROUND(SUM_ROWS_SENT/COUNT_STAR, 0) AS rows_sent_avg, ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) AS rows_examined_avg, FIRST_SEEN,LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where 1=1 and DIGEST_TEXT not like '%SHOW%' and DIGEST_TEXT not like 'desc%' and SCHEMA_NAME=@dbname and ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) >20000 and COUNT_STAR >200 and last_seen > date_sub(curdate(),interval 10 day) order by ROUND(SUM_ROWS_EXAMINED/COUNT_STAR, 0) desc; select "2.检查大事务 影响行数 >10,0000 " as '----------------------------------------------' from dual; SELECT SCHEMA_NAME, DIGEST AS digest, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT, ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg, ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg, ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg, FIRST_SEEN, LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT not like '%SHOW%' and DIGEST_TEXT not like 'desc%' -- and SCHEMA_NAME is not null -- and SCHEMA_NAME not in ('information_schema','mysql','performance_schema','sys') and SCHEMA_NAME=@dbname and ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) >100000 and COUNT_STAR >200 and last_seen > date_sub(curdate(),interval 10 day) order by ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) desc ; select "3. 检查返回行数 >1000 " as '----------------------------------------------' from dual; SELECT SCHEMA_NAME, DIGEST AS digest, DIGEST_TEXT, COUNT_STAR, AVG_TIMER_WAIT, ROUND(SUM_ROWS_AFFECTED/ COUNT_STAR, 0) AS rows_affected_avg, ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) AS rows_sent_avg, ROUND(SUM_ROWS_EXAMINED / COUNT_STAR, 0) AS rows_examined_avg, FIRST_SEEN, LAST_SEEN FROM performance_schema.events_statements_summary_by_digest where DIGEST_TEXT not like '%SHOW%' and DIGEST_TEXT not like 'desc%' and SCHEMA_NAME=@dbname and ROUND(SUM_ROWS_SENT / COUNT_STAR, 0)>1000 and COUNT_STAR >200 and last_seen > date_sub(curdate(),interval 10 day) order by ROUND(SUM_ROWS_SENT / COUNT_STAR, 0) ; select "4.检查 冗余索引" as '----------------------------------------------' from dual; select table_schema, table_name, redundant_index_name, redundant_index_columns, dominant_index_name, dominant_index_columns, sql_drop_index from sys.schema_redundant_indexes where table_schema=@dbname order by table_name; select "5.检查 不合规数据类型 " as '----------------------------------------------' from dual; select TABLE_SCHEMA, TABLE_NAME,COLUMN_NAME,DATA_TYPE from information_schema.COLUMNS where DATA_TYPE in ('enum','set','bit','binary') and table_schema not in ('information_schema','mysql','performance_schema','sys') and table_schema=@dbname order by table_name; select "6.检查索引数量 >5 " as '----------------------------------------------' from dual; select table_schema, table_name,count(*) num_idx from (select distinct table_schema,table_name, INDEX_NAME from information_schema.STATISTICS where table_schema=@dbname ) a group by table_schema,table_name having num_idx>5 order by table_schema,num_idx desc,table_name ; select "7.检查没有主键设置 " as '----------------------------------------------' from dual; select t.table_name from information_schema.tables t left join (select table_name from information_schema.STATISTICS where INDEX_NAME='PRIMARY' and table_schema =@dbname group by table_name ) a on t.table_name=a.table_name where t.table_schema =@dbname and a.table_name is null order by table_name; select "8.检查组合索引 字段大于5 " as '----------------------------------------------' from dual; select table_schema, table_name,index_name,count(index_name) num_col from information_schema.STATISTICS where table_schema=@dbname and NON_UNIQUE=1 group by table_schema,table_name,index_name having num_col>5 order by table_schema, num_col,table_name,index_name; select "9.检查表名，库名，列名 >32 " as '----------------------------------------------' from dual; select * from information_schema.schemata where char_length(SCHEMA_NAME) > 32; select TABLE_schema, TABLE_name from information_schema.tables where char_length(TABLE_name) > 32 and table_schema=@dbname order by table_name; select TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from information_schema.columns where char_length(COLUMN_NAME) > 32 and table_schema=@dbname order by table_name; select "10.检查使用非INNODB " as '----------------------------------------------' from dual; select TABLE_SCHEMA,TABLE_NAME,ENGINE from information_schema.tables where ENGINE <>'InnoDB' and table_schema=@dbname order by table_name ; select "11.检查没有有字符集utf8mb4 " as '----------------------------------------------' from dual; select schema_name,default_character_set_name,default_collation_name from information_schema.schemata where default_character_set_name not like '%utf8mb4%' and schema_name=@dbname; select TABLE_SCHEMA, TABLE_NAME,TABLE_COLLATION from information_schema.tables where TABLE_COLLATION not like 'utf8mb4%' and table_schema=@dbname order by table_name; select TABLE_SCHEMA, TABLE_NAME,column_name, COLLATION_NAME from information_schema.columns where table_schema=@dbname and COLLATION_NAME not like 'utf8mb4%' order by table_name; select "12检查无注解" as '----------------------------------------------' from dual; select TABLE_SCHEMA,TABLE_NAME from information_schema.TABLES where table_schema=@dbname and TABLE_COMMENT='' order by table_name; select distinct TABLE_SCHEMA,TABLE_NAME,column_name from information_schema.COLUMNS where COLUMN_COMMENT='' and table_schema=@dbname order by table_name; select "13.检查表字段 >100 " as '----------------------------------------------' from dual; select TABLE_SCHEMA, TABLE_NAME,count(COLUMN_NAME) num_col from information_schema.COLUMNS where table_schema=@dbname group by TABLE_SCHEMA, TABLE_NAME having num_col>100 order by table_name; select "14.检查触发器 " as '----------------------------------------------' from dual; select TRIGGER_SCHEMA,TRIGGER_NAME from information_schema.TRIGGERS where TRIGGER_SCHEMA=@dbname order by TRIGGER_NAME ; select "15.检查HASH分区表 " as '----------------------------------------------' from dual; select distinct TABLE_NAME from information_schema.PARTITIONS where table_schema=@dbname and PARTITION_METHOD is not null and PARTITION_METHOD<>'HASH' order by TABLE_NAME ; select "16.检查主键字段 >3 " as '----------------------------------------------' from dual; select table_schema, table_name,index_name,count(COLUMN_NAME) num_col from information_schema.STATISTICS where INDEX_NAME='PRIMARY' and table_schema=@dbname group by table_schema,table_name having num_col>3 order by table_schema, num_col,table_name,index_name; select "17.检查索引第一个字段分布情况 " as '----------------------------------------------' from dual; select first.TABLE_SCHEMA,first.TABLE_NAME,first.INDEX_NAME,first.COLUMN_NAME col1,first.CARDINALITY CARDINALITY1 ,second.COLUMN_NAME col2 ,second.CARDINALITY CARDINALITY2 from ( (select TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME, SEQ_IN_INDEX,CARDINALITY from information_schema.STATISTICS where table_schema=@dbname and SEQ_IN_INDEX=1 ) first, (select TABLE_SCHEMA,TABLE_NAME,INDEX_SCHEMA,INDEX_NAME,COLUMN_NAME, SEQ_IN_INDEX,CARDINALITY from information_schema.STATISTICS where table_schema=@dbname and SEQ_IN_INDEX=2 ) second ) where first.TABLE_SCHEMA=second.TABLE_SCHEMA and first.TABLE_NAME=second.TABLE_NAME and first.INDEX_NAME=second.INDEX_NAME and second.CARDINALITY>first.CARDINALITY order by first.TABLE_NAME ; select "18.检查有没有外键 " as '----------------------------------------------' from dual; select table_name,column_name,constraint_name,referenced_table_name,referenced_column_name from information_schema.key_column_usage where referenced_table_name is not null and constraint_schema=@dbname order by TABLE_NAME ; select "19.有没有DISTINCT * " as '----------------------------------------------' from dual; select SCHEMA_NAME,DIGEST ,DIGEST_TEXT from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '%DISTINCTROW \*%' and SCHEMA_NAME=@dbname ; select "20. 检查排序随机" as '----------------------------------------------' from dual; select SCHEMA_NAME,DIGEST ,DIGEST_TEXT from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '%ORDER BY \`rand\`%' and SCHEMA_NAME=@dbname ; select "21. 检查是否有SELECT * " as '----------------------------------------------' from dual; select count_star,SCHEMA_NAME, DIGEST ,DIGEST_TEXT from performance_schema.events_statements_summary_by_digest where DIGEST_TEXT like '%select \*%' and SCHEMA_NAME=@dbname order by count_star desc ; select "22.no ALL privileges " as '----------------------------------------------' from dual; select "22.no global ALL privileges " as '----------------------------------------------' from dual; select distinct concat(user,'@',host) from mysql.user where Select_priv='Y' and Insert_priv='Y' and Update_priv='Y' and Delete_priv='Y' and Create_priv='Y' and Drop_priv='Y' and Reload_priv='Y' and Shutdown_priv='Y' and Process_priv='Y' and File_priv='Y' and Grant_priv='Y' and References_priv='Y' and Index_priv='Y' and Alter_priv='Y' and Show_db_priv='Y' and Super_priv='Y' and Create_tmp_table_priv='Y' and Lock_tables_priv='Y' and Execute_priv='Y' and Repl_slave_priv='Y' and Repl_client_priv='Y' and Create_view_priv='Y' and Show_view_priv='Y' and Create_routine_priv='Y' and Alter_routine_priv='Y' and Create_user_priv='Y' and Event_priv='Y' and Trigger_priv='Y' and Create_tablespace_priv='Y' order by concat(user,'@',host) ; select "22.no ALL privileges on DB " as '----------------------------------------------' from dual; select distinct concat(user,'@',host) from mysql.db where Select_priv='Y' and Insert_priv='Y' and Update_priv='Y' and Delete_priv='Y' and Create_priv='Y' and Drop_priv='Y' and References_priv='Y' and Index_priv='Y' and Alter_priv='Y' and Create_tmp_table_priv='Y' and Lock_tables_priv='Y' and Create_view_priv='Y' and Show_view_priv='Y' and Create_routine_priv='Y' and Alter_routine_priv='Y' and Execute_priv='Y' and Event_priv='Y' and Trigger_priv='Y' order by concat(user,'@',host) ; select "22.no ALL privileges on table " as '----------------------------------------------' from dual; select distinct concat(user,'@',host) from mysql.tables_priv where Table_priv='Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger' order by concat(user,'@',host) ; select "23.no DML on system databases " as '----------------------------------------------' from dual; select distinct User from mysql.db where Db in ('inforation_schema','mysql','performance_schema','sys') and user not in ('mysql.session','mysql.sys') and (Insert_priv='Y' or Update_priv='Y' or Delete_priv='Y') order by User ;
默认情况下，setup_instruments表只打开了statement和wait/io部分的指令，setup_consumer表中很多consumer也没有打开。为了打开需要的选项，可以通过update语句直接修改配置表，并且修改后可以立即生效，但这种方式必需得启动服务器后才可以修改，并且无法持久化，重启后，又得重新设置一遍。从5.6.4开始提供了my.cnf的配置方式，格式如下：
1.设置采集的instrument
performance_schema_instrument='instrument_name=value'
(1)打开wait类型的指令
performance_schema_instrument='wait/%'
(2)打开所有指令
performance_schema_instrument='%=on'
2.设置consumer
performance_schema_consumer_xxx=value
(1)打开 events_waits_history consumer
performance_schema_consumer_events_waits_current=on
performance_schema_consumer_events_waits_history=on
这里要注意consumer的层次关系， events_waits_history处于第4层，因此设置它时，要确保events_statements_current，thread_instrumentation和global_instrumentation的ENABLED状态都为YES，才能生效。由于默认thread_instrumentation和global_instrumentation都是YES，因此只需要显示设置events_waits_current和events_waits_current即可。
3.设置统计表大小
所有的performance_schema表均采用PERFORMANCE_SCHEMA存储引擎，表中的所有数据只存在内存，表的大小在系统初始化时已经
固定好，因此占用的内存是一定的。可以通过配置来定制具体每个表的记录数。
performance_schema_events_waits_history_size=20
performance_schema_events_waits_history_long_size=15000
（3.2）performance_schema 库
#查看是否开启： mysql>show variables like 'performance_schema'; +--------------------+-------+ | Variable_name | Value | +--------------------+-------+ | performance_schema | ON | +--------------------+-------+ 1 row in set (0.04 sec) #值为ON表示performance_schema已初始化成功且可以使用了 mysql> select version(); #从MySQL5.6开始，performance_schema引擎默认打开 +-----------+ | version() | +-----------+ | 8.0.21 | +-----------+ 1 row in set (0.00 sec) #检查performance_schema存储引擎支持情况 mysql> select engine,support from information_schema.engines where engine='PERFORMANCE_SCHEMA'; +--------------------+---------+ | engine | support | +--------------------+---------+ | PERFORMANCE_SCHEMA | YES | +--------------------+---------+ 1 row in set (0.00 sec) #查询哪些表使用performance_schema引擎， mysql> select table_name from information_schema.tables where engine='performance_schema'; #查看数据库系统中打开了文件的对象：包括ibdata文件，redo文件，binlog文件，用户的表文件等，open_count显示当前文件打开的数目，如果重来没有打开过，不会出现在表中。 mysql> select * from file_instances limit 2,5; +--------------------------------+---------------------------------------+------------+ | FILE_NAME | EVENT_NAME | OPEN_COUNT | +--------------------------------+---------------------------------------+------------+ | /video/mysql/ibdata1 | wait/io/file/innodb/innodb_data_file | 3 | | /video/mysql/#ib_16384_0.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 | | /video/mysql/#ib_16384_1.dblwr | wait/io/file/innodb/innodb_dblwr_file | 2 | | /video/mysql/ib_logfile0 | wait/io/file/innodb/innodb_log_file | 2 | | /video/mysql/ib_logfile1 | wait/io/file/innodb/innodb_log_file | 2 | +--------------------------------+---------------------------------------+------------+ 5 rows in set (0.00 sec) #查看哪个SQL执行最多 mysql> select schema_name,digest_text,count_star,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by count_star desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: BEGIN count_star: 228311169 #执行次数 sum_rows_sent: 0 sum_rows_examined: 0 first_seen: 2021-03-16 10:43:00.830886 last_seen: 2022-01-27 22:56:10.547003 1 row in set (0.04 sec) #哪个SQL平均响应时间最多 mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by avg_timer_wait desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: SELECT `clock` , `ns` , VALUE FROM HISTORY WHERE `itemid` = ? AND `clock` > ? AND `clock` <= ? count_star: 576 avg_timer_wait: 250369909000 #该SQL平均响应时间，皮秒（1000000000000皮秒=1秒） sum_rows_sent: 1564 sum_rows_examined: 1564 first_seen: 2021-03-18 02:10:54.662146 last_seen: 2022-01-24 17:04:28.496527 1 row in set (0.00 sec) #哪个SQL扫描的行数最多 mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,first_seen,last_seen from events_statements_summary_by_digest order by sum_rows_examined desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: SELECT DISTINCTROW `d` . `triggerid_down` , `d` . `triggerid_up` FROM `trigger_depends` `d` , TRIGGERS `t` , HOSTS `h` , `items` `i` , `functions` `f` WHERE `t` . `triggerid` = `d` . `triggerid_down` AND `t` . `flags` != ? AND `h` . `hostid` = `i` . `hostid` AND `i` . `itemid` = `f` . `itemid` AND `f` . `triggerid` = `d` . `triggerid_down` AND `h` . `status` IN (...) count_star: 456338 avg_timer_wait: 14573128000 sum_rows_sent: 209333096 sum_rows_examined: 4484007395 ##主要关注 first_seen: 2021-03-16 10:43:14.363971 last_seen: 2022-01-27 23:01:58.261088 1 row in set (0.01 sec) #哪个SQL使用的临时表最多 mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,sum_created_tmp_disk_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_created_tmp_tables desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: SELECT DISTINCTROW `g` . `graphid` , `g` . `name` , `g` . `width` , `g` . `height` , `g` . `yaxismin` , `g` . `yaxismax` , `g` . `show_work_period` , `g` . `show_triggers` , `g` . `graphtype` , `g` . `show_legend` , `g` . `show_3d` , `g` . `percent_left` , `g` . `percent_right` , `g` . `ymin_type` , `g` . `ymin_itemid` , `g` . `ymax_type` , `g` . `ymax_itemid` , `g` . `discover` FROM `graphs` `g` , `graphs_items` `gi` , `items` `i` , `item_discovery` `id` WHERE `g` . `graphid` = `gi` . `graphid` AND `gi` . `itemid` = `i` . `itemid` AND `i` . `itemid` = `id` . `itemid` AND `id` . `parent_itemid` = ? count_star: 7359187 avg_timer_wait: 443879000 sum_rows_sent: 14083781 sum_rows_examined: 306592148 sum_created_tmp_tables: 7359204 ##主要关注 sum_created_tmp_disk_tables: 0 first_seen: 2021-03-16 10:43:02.510002 last_seen: 2022-01-27 23:05:19.469465 1 row in set (0.00 sec) #哪个SQL返回的结果集最多 mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_created_tmp_tables,first_seen,last_seen from events_statements_summary_by_digest order by sum_rows_sent desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: SELECT `i` . `itemid` , `i` . `hostid` , `i` . `templateid` FROM `items` `i` INNER JOIN HOSTS `h` ON `i` . `hostid` = `h` . `hostid` WHERE `h` . `status` = ? count_star: 456344 avg_timer_wait: 5604268000 sum_rows_sent: 1706726560 #主要关注 sum_rows_examined: 1770168229 sum_created_tmp_tables: 0 first_seen: 2021-03-16 10:43:14.292694 last_seen: 2022-01-27 23:07:58.857928 1 row in set (0.00 sec) #哪个SQL排序数最多，其中IO(SUM_ROWS_EXAMINED)，CPU消耗(SUM_SORT_ROWS)，网络带宽(SUM_ROWS_SENT) mysql> select schema_name,digest_text,count_star,avg_timer_wait,sum_rows_sent,sum_rows_examined,sum_sort_rows,first_seen,last_seen fromevents_statements_summary_by_digest order by sum_sort_rows desc limit 1\G *************************** 1. row *************************** schema_name: zabbix digest_text: SELECT `pp` . `item_preprocid` , `pp` . `itemid` , `pp` . `type` , `pp` . `params` , `pp` . `step` , `i` . `hostid` , `pp` . `error_handler` , `pp` . `error_handler_params` , `i` . `type` , `i` . `key_` , `h` . `proxy_hostid` FROM `item_preproc` `pp` , `items` `i` , HOSTS `h` WHERE `pp` . `itemid` = `i` . `itemid` AND `i` . `hostid` = `h` . `hostid` AND ( `h` . `proxy_hostid` IS NULL OR `i` . `type` IN (...) ) AND `h` . `status` IN (...) AND `i` . `flags` != ? ORDER BY `pp` . `itemid` count_star: 456346 avg_timer_wait: 14210098000 sum_rows_sent: 1069260615 sum_rows_examined: 3582380844 sum_sort_rows: 1069260615 #主要关注 first_seen: 2021-03-16 10:43:14.313793 last_seen: 2022-01-27 23:09:59.091879 1 row in set (0.00 sec) #哪个表、文件逻辑IO最多（热数据）：file_summary_by_instance表，意味着这个表经常需要访问磁盘IO mysql> select file_name,event_name,count_read,sum_number_of_bytes_read,count_write,sum_number_of_bytes_write from file_summary_by_instance order by sum_number_of_bytes_read+sum_number_of_bytes_write desc limit 2\G *************************** 1. row *************************** file_name: /video/mysql/#ib_16384_0.dblwr event_name: wait/io/file/innodb/innodb_dblwr_file count_read: 1 sum_number_of_bytes_read: 1179648 count_write: 5546947 sum_number_of_bytes_write: 523535138816 *************************** 2. row *************************** file_name: /video/mysql/ib_logfile1 event_name: wait/io/file/innodb/innodb_log_file count_read: 0 sum_number_of_bytes_read: 0 count_write: 180237316 sum_number_of_bytes_write: 266296773120 2 rows in set (0.00 sec) #查看哪个表逻辑IO最多，亦即最“热”的表 mysql> SELECT -> object_name, -> COUNT_READ, -> COUNT_WRITE, -> COUNT_FETCH, -> SUM_TIMER_WAIT -> FROM table_io_waits_summary_by_table -> ORDER BY sum_timer_wait DESC limit 1\G *************************** 1. row *************************** object_name: history #关注 COUNT_READ: 384830011 COUNT_WRITE: 750076101 COUNT_FETCH: 384830011 SUM_TIMER_WAIT: 29159481894425960 1 row in set (0.01 sec) #获得系统运行到现在，哪个表的具体哪个索引(包括主键索引，二级索引)使用最多 mysql> select object_name,index_name,count_fetch,count_insert,count_update,count_delete from table_io_waits_summary_by_index_usage order by sum_timer_wait desc limit 1\G *************************** 1. row *************************** object_name: history index_name: NULL count_fetch: 0 count_insert: 374268229 count_update: 0 count_delete: 0 1 row in set (0.00 sec) +-------------+------------+-------------+--------------+--------------+--------------+ | object_name | index_name | count_fetch | count_insert | count_update | count_delete | +-------------+------------+-------------+--------------+--------------+--------------+ | history | NULL | 0 | 374268557 | 0 | 0 | +-------------+------------+-------------+--------------+--------------+--------------+ #获取哪个索引没有使用过 mysql> select object_schema,object_name,index_name from table_io_waits_summary_by_index_usage \ -> where index_name is not null and \ -> count_star =0 and \ -> object_schema <> 'mysql' order by object_schema,object_name; +--------------------+------------------------------------------------------+----------------------------------+ | object_schema | object_name | index_name | +--------------------+------------------------------------------------------+----------------------------------+ | performance_schema | accounts | ACCOUNT | | performance_schema | cond_instances | PRIMARY | | performance_schema | cond_instances | NAME | | performance_schema | data_lock_waits | BLOCKING_THREAD_ID | | performance_schema | data_lock_waits | REQUESTING_ENGINE_LOCK_ID | | performance_schema | data_lock_waits | BLOCKING_ENGINE_LOCK_ID | | performance_schema | data_lock_waits | REQUESTING_ENGINE_TRANSACTION_ID | | performance_schema | data_lock_waits | BLOCKING_ENGINE_TRANSACTION_ID | | performance_schema | data_lock_waits | REQUESTING_THREAD_ID | | performance_schema | data_locks | OBJECT_SCHEMA | | performance_schema | data_locks | THREAD_ID | | performance_schema | data_locks | ENGINE_TRANSACTION_ID | | performance_schema | data_locks | PRIMARY | | performance_schema | events_errors_summary_by_account_by_error | ACCOUNT | | performance_schema | events_errors_summary_by_host_by_error | HOST | | performance_schema | events_errors_summary_by_thread_by_error | THREAD_ID | | performance_schema | events_errors_summary_by_user_by_error | USER | | zabbix | task_result | task_result_1 | | zabbix | task_result | PRIMARY | | zabbix | timeperiods | PRIMARY | | zabbix | trigger_depends | PRIMARY | | zabbix | trigger_depends | trigger_depends_2 | | zabbix | trigger_tag | trigger_tag_1 | | zabbix | trigger_tag | PRIMARY | | zabbix | triggers | triggers_1 | | zabbix | triggers | triggers_2 | | zabbix | triggers | triggers_3 | | zabbix | users_groups | users_groups_2 | | zabbix | users_groups | users_groups_1 | | zabbix | users_groups | PRIMARY | | zabbix | widget_field | widget_field_5 | | zabbix | widget_field | PRIMARY | | zabbix | widget_field | widget_field_4 | | zabbix | widget_field | widget_field_6 | | zabbix | widget_field | widget_field_3 | | zabbix | widget_field | widget_field_2 | | zabbix | widget_field | widget_field_1 | +--------------------+------------------------------------------------------+----------------------------------+ 331 rows in set (0.00 sec) #查询哪个等待事件消耗的时间最多 mysql> select event_name,count_star,sum_timer_wait,avg_timer_wait from events_waits_summary_global_by_event_name where event_name != 'idle' order by sum_timer_wait desc limit 1; +-------------------------+------------+---------------------+----------------+ | event_name | count_star | sum_timer_wait | avg_timer_wait | +-------------------------+------------+---------------------+----------------+ | wait/io/file/sql/binlog | 195515509 | 1538632653524464190 | 7869619450 | +-------------------------+------------+---------------------+----------------+ 1 row in set (0.02 sec) #InnoDB监控 #打开标准的innodb监控： CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB; #打开innodb的锁监控： CREATE TABLE innodb_lock_monitor (a INT) ENGINE=INNODB; #打开innodb表空间监控： CREATE TABLE innodb_tablespace_monitor (a INT) ENGINE=INNODB; #打开innodb表监控： CREATE TABLE innodb_table_monitor (a INT) ENGINE=INNODB; #剖析某条SQL的执行情况，该SQL在执行各个阶段的时间消耗，可查看events_statements_xxx表和events_stages_xxx表，包括statement信息，stage信息和wait信息 #eg：比如分析包含count(*)的某条SQL语句 mysql> SELECT -> EVENT_ID, -> sql_text -> FROM events_statements_history -> WHERE sql_text LIKE '%count(*)%'; +----------+-------------------------------------------------------------------------------------------+ | EVENT_ID | sql_text | +----------+-------------------------------------------------------------------------------------------+ | 246 | select count(*) from zabbix.task | | 248 | select count(*) from zabbix.items | | 242 | SELECT EVENT_ID, sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%' | | 243 | select count(*) from zabbix.tasks | +----------+-------------------------------------------------------------------------------------------+ 4 rows in set (0.00 sec) #查看每个阶段的时间消耗，时间单位以皮秒表示，注意默认情况下events_stages_history表中只为每个连接记录了最近10条记录，为了确保获取所有记录，需要访问events_stages_history_long表 mysql> SELECT -> event_id, -> EVENT_NAME, -> SOURCE, -> TIMER_END - TIMER_START -> FROM events_stages_history_long -> WHERE NESTING_EVENT_ID = 248; #查看某个阶段的锁等待情况；针对每个stage可能出现的锁等待，一个stage会对应一个或多个wait，events_waits_history_long这个表容易爆满[默认阀值10000]。由于select count(*)需要IO(逻辑IO或者物理IO)，所以在stage/sql/Sending data阶段会有io等待的统计 mysql> SELECT -> event_id, -> event_name, -> source, -> timer_wait, -> object_name, -> index_name, -> operation, -> nesting_event_id -> FROM events_waits_history_long -> WHERE nesting_event_id = 248; Empty set (0.00 sec)
（3.3）sys 库
#查看用户、连接情况 查看每个客户端IP过来的连接消耗资源情况。 mysql> select host,current_connections,total_connections,unique_users,current_memory,total_memory_allocated from sys.host_summary; +-----------+---------------------+-------------------+--------------+----------------+------------------------+ | host | current_connections | total_connections | unique_users | current_memory | total_memory_allocated | +-----------+---------------------+-------------------+--------------+----------------+------------------------+ | localhost | 20 | 458106 | 2 | 20.99 GiB | 46.42 TiB | +-----------+---------------------+-------------------+--------------+----------------+------------------------+ 1 row in set (0.01 sec) #查看每个用户消耗资源情况 mysql> select user,table_scans,file_ios,file_io_latency,current_connections,current_memory from sys.user_summary; +------------+-------------+-----------+-----------------+---------------------+----------------+ | user | table_scans | file_ios | file_io_latency | current_connections | current_memory | +------------+-------------+-----------+-----------------+---------------------+----------------+ | zabbix | 12575536 | 195754143 | 17.84 d | 19 | 10.49 GiB | | root | 32 | 84 | 335.81 ms | 1 | 2.48 MiB | | background | 0 | 442868581 | 14.88 d | 53 | 233.65 MiB | +------------+-------------+-----------+-----------------+---------------------+----------------+ 3 rows in set (0.01 sec) #查看当前正在执行的SQL mysql> select conn_id,pid,user,db,command,current_statement,last_statement,time,lock_latency from sys.session; +---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+ | conn_id | pid | user | db | command | current_statement | last_statement | time | lock_latency | +---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+ | 28 | 17167 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 203 | 46.00 us | | 15 | 17168 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 164 | 52.00 us | | 24 | 17147 | zabbix@localhost | zabbix | Sleep | select co.corr_operationid,co. ... o.correlationid and c.status=0 | select co.corr_operationid,co. ... o.correlationid and c.status=0 | 58 | 44.00 us | | 30 | 17169 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 44 | 42.00 us | | 26 | 17166 | zabbix@localhost | zabbix | Sleep | commit | commit | 40 | 0 ps | | 10 | 17149 | zabbix@localhost | zabbix | Sleep | commit | commit | 13 | 0 ps | | 16 | 17151 | zabbix@localhost | zabbix | Sleep | select dcheckid,type,key_,snmp ... re druleid=3 order by dcheckid | select dcheckid,type,key_,snmp ... re druleid=3 order by dcheckid | 6 | 48.00 us | | 22 | 17170 | zabbix@localhost | zabbix | Sleep | select itemid from items where ... and flags<>2 and hostid=10084 | select itemid from items where ... and flags<>2 and hostid=10084 | 5 | 60.00 us | | 21 | 17154 | zabbix@localhost | zabbix | Sleep | commit | commit | 5 | 0 ps | | 14 | 17165 | zabbix@localhost | zabbix | Sleep | commit | commit | 4 | 0 ps | | 23 | 17153 | zabbix@localhost | zabbix | Sleep | commit | commit | 3 | 0 ps | | 8 | 17159 | zabbix@localhost | zabbix | Sleep | select taskid,type,clock,ttl f ... tatus in (1,2) order by taskid | select taskid,type,clock,ttl f ... tatus in (1,2) order by taskid | 3 | 100.00 us | | 20 | 17156 | zabbix@localhost | zabbix | Sleep | select escalationid,actionid,t ... ,triggerid,itemid,escalationid | select escalationid,actionid,t ... ,triggerid,itemid,escalationid | 3 | 47.00 us | | 9 | 17150 | zabbix@localhost | zabbix | Sleep | select min(t.nextcheck) from h ... tus=0 or h.maintenance_type=0) | select min(t.nextcheck) from h ... tus=0 or h.maintenance_type=0) | 2 | 62.00 us | | 11 | 17155 | zabbix@localhost | zabbix | Sleep | commit | commit | 2 | 0 ps | | 458094 | 8986 | root@localhost | sys | Query | SET @sys.statement_truncate_le ... ('statement_truncate_len', 64) | NULL | 1 | 996.00 us | | 13 | 17182 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31891 | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31891 | 1 | 59.00 us | | 17 | 17181 | zabbix@localhost | zabbix | Sleep | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31832 | select h.hostid,h.host,h.name, ... tid and hd.parent_itemid=31832 | 1 | 81.00 us | | 18 | 17152 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps | | 19 | 17183 | zabbix@localhost | zabbix | Sleep | commit | commit | 1 | 0 ps | | 5 | NULL | sql/event_scheduler | NULL | Sleep | NULL | NULL | NULL | NULL | +---------+-------+---------------------+--------+---------+-------------------------------------------------------------------+-------------------------------------------------------------------+------+--------------+ 21 rows in set (0.08 sec) #查看发生IO请求前5名的文件 mysql> select * from sys.io_global_by_file_by_bytes order by total limit 5; #查看总共分配了多少内存 mysql> select * from sys.memory_global_total; mysql> select * from sys.memory_global_by_current_bytes; #每个库（database）占用多少buffer pool;pages是指在buffer pool中的page数量；pages_old指在LUR 列表中处于后37%位置的page。当出现buffer page不够用时，就会征用这些page所占的空间。37%是默认位置，具体可以自定义。 mysql> select * from sys.innodb_buffer_stats_by_schema order by allocated desc; +---------------+-----------+------------+--------+--------------+-----------+-------------+ | object_schema | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+-----------+------------+--------+--------------+-----------+-------------+ | mysql | 5.95 MiB | 3.33 MiB | 381 | 185 | 275 | 1380 | | zabbix | 2.25 GiB | 1.74 GiB | 147654 | 104738 | 55439 | 401231 | | sys | 16.00 KiB | 338 bytes | 1 | 0 | 1 | 6 | +---------------+-----------+------------+--------+--------------+-----------+-------------+ 3 rows in set (2.58 sec) #统计每张表具体在InnoDB中具体的情况，比如占多少页 mysql> select * from sys.innodb_buffer_stats_by_table; +---------------+------------------------------+------------+-------------+-------+--------------+-----------+-------------+ | object_schema | object_name | allocated | data | pages | pages_hashed | pages_old | rows_cached | +---------------+------------------------------+------------+-------------+-------+--------------+-----------+-------------+ | zabbix | history | 873.78 MiB | 629.83 MiB | 55922 | 36446 | 25499 | 9101216 | | zabbix | history_uint | 598.88 MiB | 449.53 MiB | 38328 | 27248 | 16863 | 6572995 | | zabbix | trends | 457.16 MiB | 388.25 MiB | 29258 | 22582 | 7277 | 7040277 | | zabbix | trends_uint | 337.12 MiB | 291.23 MiB | 21576 | 17373 | 4991 | 5291601 | | zabbix | events | 16.50 MiB | 11.24 MiB | 1056 | 582 | 263 | 66166 | | zabbix | history_str | 5.12 MiB | 2.52 MiB | 328 | 40 | 86 | 29315 | #查询每个连接分配了多少内存 mysql> SELECT b.USER, current_count_used, current_allocated, current_avg_alloc, current_max_alloc, total_allocated, current_statement FROM sys.memory_by_thread_by_current_bytes a, sys.session b WHERE a.thread_id = b.thd_id; #查看表自增字段最大值和当前值，有时候做数据增长的监控，可以作为参考 mysql> select * from sys.schema_auto_increment_columns; #MySQL索引使用情况统计 mysql> select * from sys.schema_auto_increment_columns; #MySQL中有哪些冗余索引和无用索引;若库中展示没有冗余索引，则没有数据；当有联合索引idx_abc(a,b,c)和idx_a(a)，那么idx_a就算冗余索引了。 mysql> select * from sys.schema_redundant_indexes; #查看INNODB 锁信息 mysql> select * from sys.innodb_lock_waits; #查看库级别的锁信息，这个需要先打开MDL锁的监控： #打开MDL锁监控 update performance_schema.setup_instruments set enabled='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl'; mysql> select * from performance_schema.setup_instruments where name='wait/lock/metadata/sql/mdl'; +----------------------------+---------+-------+------------+------------+---------------+ | NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION | +----------------------------+---------+-------+------------+------------+---------------+ | wait/lock/metadata/sql/mdl | YES | YES | | 0 | NULL | +----------------------------+---------+-------+------------+------------+---------------+ 1 row in set (0.00 sec) #MySQL内部有多个线程在运行，线程类型及数量 mysql> select user,count(*) from sys.`processlist` group by user; #查看MySQL自增id的使用情况 mysql> SELECT -> table_schema, -> table_name, -> ENGINE, -> Auto_increment -> FROM -> information_schema.TABLES -> WHERE -> TABLE_SCHEMA NOT IN ( "INFORMATION_SCHEMA", "PERFORMANCE_SCHEMA", "MYSQL", "SYS" ); +--------------------+------------------------------------------------------+--------------------+----------------+ | TABLE_SCHEMA | TABLE_NAME | ENGINE | AUTO_INCREMENT | +--------------------+------------------------------------------------------+--------------------+----------------+ | information_schema | ADMINISTRABLE_ROLE_AUTHORIZATIONS | NULL | NULL |
