Mysql常用命令

清除所有sql进程

select concat('KILL ',id,';') from information_schema.processlist where id > 200  

查看完整sql查询  

select  *  from  information_schema.processlist;

查看完整sql语句进程命令

select * from information_schema.`PROCESSLIST` where info is not null;

查看3306端口的使用进程

lsof -i:3306

查看mysql服务器登录失败的ip

grep "Failed password" /var/log/secure |awk '{print$(NF-3)}'

查询某个库的某个表数据量

select concat(round(sum(data_length/1024/1024),2),'mb')  as data from tables where table_schema = '库名' and table_name ='表名';

查询对应表的字段以及备注

SELECT COLUMN_NAME,COLUMN_COMMENT FROM INFORMATION_SCHEMA.Columns WHERE table_name= '表名'

Mysql服务器登录失败ip排行

grep "Failed password" /var/log/secure |awk '{print$(NF-3)}'|sort|uniq -c|sort -nr

匹配恶意登录次数大于5次的ip

grep "Failed password" /var/log/secure |awk '{print$(NF-3)}'|sort|uniq -c|sort -nr|awk '{if ($1>=5) print $2}'

数据库重启：

systemctl restart mysqld

更改数据库字段属性时报错1067

/etc/my.cnf中开启sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION，然后重启数据库