mysql日常工作_MySQL日常

#查询当前正在执行的语句

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