数据库问题排查
数据库若干查询语句记录
##连接某个数据库 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 ,不区分大小写。