MySQL日常操作

1）查看表死锁

show processlist;
select trx_state, trx_started, trx_mysql_thread_id, trx_query from information_schema.innodb_trx;
找到死锁进程，KILL掉

2）在线更新shcema

sudo yum install http://www.percona.com/downloads/percona-release/redhat/0.1-4/percona-release-0.1-4.noarch.rpm

sudo yum install percona-toolkit
yum list | grep percona-xtrabackup
yum install percona-xtrabackup-24

pt-online-schema-change --user=** --password=** --host=127.0.0.1  --alter "add index IDX_TRANS_TIME(TRANS_TIME)" D=**,t=** --charset=utf8 --no-check-alter --no-check-replication-filters --alter-foreign-keys-method=auto --recursion-method=none --print --execute

3）查看存储过程列表

select `name` from mysql.proc where db = 'msx_online' and `type` = 'PROCEDURE';

4）查看数据库大小

use information_schema;

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables;  # 所有数据大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='ad';  #指定数据库大小

select concat(round(sum(data_length/1024/1024),2),'MB') as data from tables where table_schema='ad' and table_name='ad_event_log';  #制定数据库表大小

select TABLE_SCHEMA, concat(truncate(sum(data_length)/1024/1024,2),' MB') as data_size,concat(truncate(sum(index_length)/1024/1024,2),'MB') as index_size from information_schema.tables group by TABLE_SCHEMA ORDER BY data_size desc; # 查看所有库占用磁盘空间大小

select TABLE_NAME, concat(truncate(data_length/1024/1024,2),' MB') as data_size, concat(truncate(index_length/1024/1024,2),' MB') as index_size from information_schema.tables where TABLE_SCHEMA = 'ad' group by TABLE_NAME order by data_length desc; #查看单库所有表磁盘空间大小

5）批量删除表

SELECT CONCAT('DROP TABLE ', GROUP_CONCAT(table_name) ,';') as statment FROM information_schema.`TABLES` WHERE table_schema='ad' and table_name not in('ad_event_log','ad_request_event_log')