数据库问题排查

数据库若干查询语句记录

 

##连接某个数据库
mysql   -h dbip   -u  username  -p  password

##进入某个数据库
use  henry;

##显示 若干表
show tables;

##查询锁的状态
show status like 'innodb_row_lock%';

###查看 sql执行情况 
use fupan; 
desc  select * from blockData where  date >0 and  stockType=880024 order by date DESC limit 240 ;
##查看数据库状态
show  status;
##查看锁的最新状态
show engine innodb status\G;

##查看全部线程
show  processlist;
show  full processlist;
select * from  information_schema.processlist;

###按客户端ip分组，看看哪个客户端链接最多
select id, client_ip, count(client_ip) as client_num 
from (select substring_index(host,':', 1) as client_ip,id from information_schema.processlist) as connect_info 
group by client_ip order by client_num desc;

##查看最大连接数 
show VARIABLES like '%max_connections%';

###重启数据库
systemctl  restart  mysql 

###查看mysql进程
ps  -ef  |  grep  mysql

###杀死某个进程
killall   -9   process_id


###查看是否 忽略大小写
 show variables like "%case%";

 

### 查看是否忽略表名大小写
MySQL [fupan]>  show variables like "%case%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+
2 rows in set (0.00 sec)

lower_case_table_names = 1 ，不区分大小写。