#查询当前正在执行的语句
select user 用户,substring(host,1,instr(host,':')-1) IP,substring(host,instr(host,':')+1) 端口,time 执行时间,info 执行语句 from information_schema.processlist where command='Query';
#查询锁等待的相关信息（不针对MDL锁）
查询出的结果按阻碍次数排名，排名最高的那个阻碍事务ID就是这一串锁的源头（但这个ID对应的语句通常已经是sleep状态了，不会在processlist语句里面显示出来）
select c.`阻碍事务ID`,b.pm 阻碍次数,c.`阻碍情况`,c.`被阻事务ID`,c.`进程ID` 被阻碍事务进程ID,c.`等待时间`,c.`当前进程号对应的被阻语句` 被阻语句,c.用户,c.IP,c.`端口` from
(
select a.阻碍事务ID,count(a.阻碍事务ID) pm from
(
select p.user 用户,
substring(p.host,1,instr(p.host,':')-1) IP,
substring(p.host,instr(p.host,':')+1) 端口,
p.id 进程ID,
t.trx_id 被阻事务ID,
lw.blocking_trx_id 阻碍事务ID,
concat(if(lw.blocking_trx_id is not null,lw.blocking_trx_id,'未产生'),'阻碍',if(t.trx_id is not null,t.trx_id,'')) 阻碍情况,
p.time 等待时间,
p.info 当前进程号对应的被阻语句
from information_schema.processlist p
left join information_schema.INNODB_TRX t
on p.id=t.trx_mysql_thread_id
left join information_schema.INNODB_lock_waits lw
on t.trx_id=lw.requesting_trx_id
where command='Query'
) a
group by a.阻碍事务ID
) b,
(
select p.user 用户,
substring(p.host,1,instr(p.host,':')-1) IP,
substring(p.host,instr(p.host,':')+1) 端口,
p.id 进程ID,
lw.blocking_trx_id 阻碍事务ID,
t.trx_id 被阻事务ID,
concat(if(lw.blocking_trx_id is not null,lw.blocking_trx_id,'未产生'),'阻碍',if(t.trx_id is not null,t.trx_id,'')) 阻碍情况,
p.time 等待时间,
p.info 当前进程号对应的被阻语句
from information_schema.processlist p
left join information_schema.INNODB_TRX t
on p.id=t.trx_mysql_thread_id
left join information_schema.INNODB_lock_waits lw
on t.trx_id=lw.requesting_trx_id
where command='Query'
) c
where b.阻碍事务ID=c.阻碍事务ID
order by b.pm desc;
#根据源头的阻碍事物ID（上图红框）找到对应的thread_id，视情况将其kill掉可以解决这一连串的锁等待事件
select id,info from information_schema.`PROCESSLIST`
where id=
(select trx_mysql_thread_id from information_schema.innodb_trx
where trx_id='1307906799'
);
#删除索引报错
error:Invalid default value
查询sql_mode，去掉NO_ZERO_IN_DATE,NO_ZERO_DATE
set global sql_mode='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
查询表空间大小
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
group by TABLE_NAME
order by data_length desc;
binlog转换成可读文件
mysqlbinlog -v --base64-output=DECODE-ROWS mysql-bin.000346 > 346.log
授权
#DML
grant select, insert, update, delete on testdb.* to common_user@'%'
#DDL
grant create,alter,drop on testdb.* to developer@'192.168.0.%';
#临时表（注意是tables不是table）
grant create temporary tables on testdb.* to developer@'192.168.0.%';
#索引
grant index on testdb.* to developer@'192.168.0.%';
#视图权限
grant create view on testdb.* to developer@'192.168.0.%';
grant show view on testdb.* to developer@'192.168.0.%';
#索引权限
grant index on testdb.* to developer@'192.168.0.%';
#存储过程权限
grant create routine on testdb.* to developer@'192.168.0.%';
grant alter routine on testdb.* to developer@'192.168.0.%';
grant execute on testdb.* to developer@'192.168.0.%';
#撤销权限
revoke all on *.* from dba@localhost;
MySQL命令行小技巧pager
命令行在使用的时候，会自动翻页输出所有数据，非常的操蛋
使用pager命令之后，在linux中相当于以linux的习惯来使用mysql
eg.1 more
#使用more命令来查询某个表的数据
pager more
select * from xxx...
需要注意的是，这种方法实际上是将结果查询出来之后缓存起来，然后通过more命令的方式一点点的显示，如果你缓存的是一张大表，完全有可能把你本地的mysql服务给搞崩掉
查询中使用q退出
查询外使用pager结束前面的pager more命令
eg.2 grep
pager grep Slave
pager grep lock
pager grep sequence
