Home > mysql > 巧用information_schema

巧用information_schema

在DBA的日常工作中可能会遇到这样的操作:一些应用下线时,将生产库中的表批量移到test库中;对数据库的的表进行批量optimize,analyze;批量删除某个用户的到数据库的connections等。这时候如果你的库中有成千上百的表,也行就有点麻烦了,时候需要借助于mysql的information_schema内部数据字典,就可以事半功倍:

—>将某用户下的表rename到其他库中:

mysql –uroot –skip-column-names -e

“select concat(‘alter table ‘,table_schema,’.’,table_name,’ rename to ‘,’test.’,table_name,’;’) from information_schema.tables where  table_schema=’king'” >>./move_king_test.txt;

root@(none) 06:31:15>source move_king_test.txt

—>optimize table:

mysql -uroot –skip-column-names -e “SELECT ‘OPTIMIZE TABLE ‘, CONCAT(TABLE_SCHEMA, ‘.’, TABLE_NAME, ‘;’)

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_SCHEMA = ‘test'”>>optimize.txt

root@(none) 06:37:05>source optimize.txt

—>批量删除connections:

root@(none) 06:38:36>select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’alimall’;

+————————+

| concat(‘KILL ‘,id,’;’) |

+————————+

| KILL 25150732;         |

| KILL 25148400;         |

| KILL 25147846;         |

| KILL 25141740;         |

| KILL 25141682;         |

| KILL 25141681;         |

| KILL 25141679;         |

| KILL 25128789;         |

mysql –uroot –skip-column-names -e “select concat(‘KILL ‘,id,’;’) from information_schema.processlist where user=’alimall'”>>kill.txt

root@(none) 06:39:02>source kill.txt

当然也可以参考这里:批量杀死MySQL连接,

也可以参考这里:Mass killing of MySQL Connections

Information_schema中还有其他很不错的视图用于诊断mysql库中lock的等待视图,如:

select * from information_schema.innodb_locks;

select * from information_schema.innodb_trx;

select * from information_schema.innodb_lock_waits;

下面的查询可以得到当前状况下数据库的等待情况:【见《innodb技术内幕中》】

select r.trx_id              wait_trx_id,

r.trx_mysql_thread_id wait_thr_id,

r.trx_query           wait_query,

b.trx_id              block_trx_id,

b.trx_mysql_thread_id block_thrd_id,

b.trx_query           block_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;

Categories: mysql Tags:
  1. No comments yet.
  1. No trackbacks yet.
You must be logged in to post a comment.